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.

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 from May 2024; it replaces RFC 4122 and introduces versions 6, 7 and 8, among other things. With the UUID & ULID 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. 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. In the database, you store them as 16 bytes, just like a UUID.

All options compared

BIGINT auto-incrementUUID v4UUID v7ULID
Size (binary)8 bytes16 bytes16 bytes16 bytes
Text formup to 19 digits36 characters36 characters26 characters
Generated without the databasenoyesyesyes
Sorted by timeyesnoyesyes
Behavior in a B-tree indexvery goodpoor for large tablesgoodgood
Reveals informationcount and ordernothingcreation timecreation time
Standard–RFC 9562RFC 9562own specification

The UUID & ULID 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):

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

More guides