# UUID v4, v7 or ULID as a primary key?

When UUIDs make sense as primary keys, why v7 and ULID suit databases better than v4 and how to store them.

Web page: https://skriptbox.com/en/guides/uuid-as-primary-key

Sequential numbers as primary keys are simple and fast. But as soon as data is created in several places, IDs show up in URLs or datasets need to be merged, they reach their limits. A UUID is the obvious alternative – yet the wrong version can noticeably slow down a large table.

This guide compares auto-increment, UUID v4, UUID v7 and ULID, explains what happens in the index and shows how to store the IDs compactly in PostgreSQL, MySQL and MariaDB.

## Auto-increment or UUID?

An auto-increment `BIGINT` takes 8 bytes, is easy to read and is assigned by the database in ascending order – ideal for the index. The downsides:

- Only the database can assign IDs. The application doesn’t know the ID of a new record until it has been saved.
- When you merge several databases, for example from different tenants or locations, the numbers collide.
- IDs in URLs are guessable (`/invoice/1042`, `/invoice/1043`) and reveal roughly how many records exist.

A UUID (Universally Unique Identifier) is 128 bits long and can be generated anywhere – in the browser, in a microservice, offline in an app – without coordinating with a central authority. When generated correctly, collisions are practically impossible. In return, it is twice the size of a `BIGINT`, harder to read and puts a strain on the index if its values are randomly distributed.

## How a UUID is structured

The usual notation consists of 32 hexadecimal digits in five groups. Two positions have a fixed meaning:

```
fbd0a224-e753-47f1-8ab9-f8f688240eb6
              ^    ^
              |    variant (8, 9, a or b)
              version (4 here)
```

The first digit of the third group indicates the **version**, the first digit of the fourth group the **variant**. The current specification is [RFC 9562](https://www.rfc-editor.org/rfc/rfc9562.html) from May 2024; it replaces RFC 4122 and introduces versions 6, 7 and 8, among other things. With the [UUID & ULID Generator](https://skriptbox.com/en/tools/uuid-generator) you can decode an existing UUID and see its version, variant and – for time-based versions – the timestamp it contains.

## UUID v4 and the index problem

Apart from the version and variant bits, version 4 consists of 122 random bits. That is ideal for uniqueness, but not for a B-tree index – the default index type in PostgreSQL, MySQL and MariaDB:

- Every new ID lands at a random position in the index. Full index pages have to be split (**page splits**), and afterwards the pages are only partly filled.
- As a result, the index grows larger and becomes **fragmented**.
- Inserts constantly touch different pages. As long as the index fits in memory, this is barely noticeable; once it outgrows the **cache**, the database has to read from disk frequently and writes slow down considerably.

MySQL with InnoDB is particularly affected: there, the primary key is a clustered index, meaning the table rows themselves are stored sorted by ID. In addition, every secondary index contains the primary key – so a large ID makes every index on the table larger.

Sequential IDs, by contrast, are always appended at the right-hand edge of the index. UUID v7 and ULID bring back exactly this property.

## UUID v7 and ULID: sortable by time

**UUID v7** starts with a 48-bit timestamp in milliseconds since 1970 (Unix time), followed by the version, the variant and 74 bits that are random or partly used as a counter. Three v7 UUIDs, generated one millisecond apart:

```
01a1f873-c8a0-75c3-9f44-cdacf58c2bf3
01a1f873-c8a1-7074-8292-f8d38b0b8d9c
01a1f873-c8a2-7c30-8879-e52c96bfbcc4
```

The first twelve hex digits (`01a1f873c8a0`) are the timestamp: 1795169700000 milliseconds in decimal, which is 20 November 2026, 10:15:00 UTC. You can turn values like this into a readable date with the [timestamp converter](https://skriptbox.com/en/tools/unix-timestamp-converter). New IDs are therefore greater than older ones and – just like auto-increment – are inserted at the end of the index.

The **ULID** (Universally Unique Lexicographically Sortable Identifier) follows the same principle: a 48-bit millisecond timestamp and 80 random bits. Instead of hexadecimal, however, it is written in **Crockford’s Base32** – 26 characters, only uppercase letters and digits, without the easily confused letters I, L, O and U:

```
01M7W77J50RS5VA0XF7WT5AGYY
```

The first ten characters contain the timestamp. ULIDs are shorter than UUIDs in text form and can be sorted as strings. They are not an IETF standard, though, but an [open specification](https://github.com/ulid/spec). In the database, you store them as 16 bytes, just like a UUID.

### All options compared

|  | BIGINT auto-increment | UUID v4 | UUID v7 | ULID |
| --- | --- | --- | --- | --- |
| Size (binary) | 8 bytes | 16 bytes | 16 bytes | 16 bytes |
| Text form | up to 19 digits | 36 characters | 36 characters | 26 characters |
| Generated without the database | no | yes | yes | yes |
| Sorted by time | yes | no | yes | yes |
| Behavior in a B-tree index | very good | poor for large tables | good | good |
| Reveals information | count and order | nothing | creation time | creation time |
| Standard | – | RFC 9562 | RFC 9562 | own specification |

The [UUID & ULID Generator](https://skriptbox.com/en/tools/uuid-generator) generates all three as well as name-based v5 UUIDs – up to 1,000 at once if needed, as a list, JSON or SQL.

## Storing UUIDs properly

### PostgreSQL

PostgreSQL has a dedicated `uuid` data type that takes 16 bytes internally. `gen_random_uuid()` generates v4 UUIDs, and since PostgreSQL 18 there is also `uuidv7()` (see the [documentation](https://www.postgresql.org/docs/current/functions-uuid.html)):

```
CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    created_at timestamptz NOT NULL DEFAULT now()
);
```

### MySQL

MySQL has no UUID data type. Store the ID as `BINARY(16)`, not as `CHAR(36)` – that saves more than half the space in every index. For conversion there are `UUID_TO_BIN()` and `BIN_TO_UUID()`:

```
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY
);

INSERT INTO orders (id) VALUES (UUID_TO_BIN('01a1f873-c8a0-75c3-9f44-cdacf58c2bf3'));
SELECT BIN_TO_UUID(id) FROM orders;
```

MySQL’s `UUID()` function generates version 1, in which the time components are arranged unfavorably. `UUID_TO_BIN(UUID(), 1)` reorders them so the values are stored in ascending order. Use this second parameter **only for v1**; a v7 UUID is already in the right order and is stored without it. In MySQL, it is best to generate v7 UUIDs in the application.

### MariaDB

Since version 10.7, MariaDB has its own `UUID` data type, which takes 16 bytes and accepts and returns the usual text form. No conversion functions are needed.

### In the application

In Symfony, the Uid component generates v7 UUIDs (`Uuid::v7()`) and ULIDs (`new Ulid()`), and matching column types are available for Doctrine. In Laravel, the `HasUuids` trait has assigned v7 UUIDs by default since version 12, while `HasUlids` generates ULIDs.

## Caution: what an ID reveals

A v7 UUID or a ULID contains its creation time to the millisecond – anyone who sees the ID can read it. For an order number, that is usually harmless; for a user account ID, it may reveal more than intended, such as when someone signed up. If that matters, use a v4 UUID or a separate public identifier externally.

Also, never treat IDs as secrets. Even if a UUID is hard to guess, someone who knows the link to `/invoice/01a1f873-…` may only see the invoice if your application checks their permissions. Password reset links or API keys need their own random values of sufficient length, without a time component.

## Recommendation

- **Small application with a single database, IDs not public:** an auto-increment `BIGINT` is still a good choice.
- **IDs are generated in different places, data is merged or IDs appear in URLs:** UUID v7 as the primary key. It is standardized, generated natively by PostgreSQL and supported by common libraries.
- **Short, readable IDs wanted:** ULID – especially if the ID often appears in text form.
- **The creation time must not be visible:** UUID v4, ideally only as an additional public identifier alongside a sortable primary key for large tables.

Always store UUIDs in binary form (`uuid`, `BINARY(16)` or MariaDB’s `UUID`), not as text. And pick one version per table – mixing v4 and v7 in the same column loses the benefit of ordering.


## Tools for this topic

- [UUID & ULID Generator](https://skriptbox.com/en/tools/uuid-generator.md) – Generate UUIDs (v4, v7, v5) and ULIDs for free – up to 1,000 at once as a list, JSON or SQL – and decode existing IDs. Nothing is transmitted.
- [Unix Timestamp Converter](https://skriptbox.com/en/tools/unix-timestamp-converter.md) – Convert Unix timestamps to dates and back – seconds to nanoseconds, any time zone, with code for PHP, JavaScript, Python, SQL and Bash.
