How to choose an identifier, and what each one costs you

What an identifier is for

An identifier has to be unique, it has to be cheap to generate, it has to be cheap to store and index, and — depending on the system — it has to not leak information. Those requirements conflict, and every option is a different answer to which one you give up.

The fifth requirement is the one that is easiest to forget. A sequential id tells everybody how many records exist and in what order they were created, which is a business metric you may not want to publish.

• Uniqueness across every node that might generate one.
• Generation cost and collision probability.
• Index locality, which is the effect people forget.
• Information leakage, which is the effect people regret.

Auto-increment integers

The smallest and the fastest. An 8-byte integer is cheaper to store and to index than any text id, and because the values are increasing, every insert lands at the end of the index, which keeps the pages contiguous and the write amplification low.

The cost is that the id is sequential, so it leaks the volume and the order of the table, and it can only be generated by one place. A single database that owns its own tables is the ideal case for this choice.

Random UUIDv4

122 bits of randomness, generated anywhere without coordination, and no coordination means no network round trip and no central point of failure. That is why it is the default in distributed systems.

The cost is index locality. A random id scatters inserts across the whole index, which fragments the pages and turns every insert into a page split. On a high-write table this is the difference between a database that keeps up and one that does not.

There is also a storage consideration: as a `uuid` column it is 16 bytes, but as a `char(36)` string it is 36 bytes, and the text form also has a random-looking index order, which is the worst of both.

Time-ordered identifiers

UUIDv7, ULID and Snowflake all solve the same problem by putting a timestamp in the leading bits. The id sorts by creation time, so inserts stay sequential in the index, while the trailing bits stay random enough that ids are not predictable.

UUIDv7 is the one to prefer if you are starting today. It is a standard, RFC 9562, supported by the major database drivers, and it keeps the UUID format so it does not force a schema change.

The trade-off is the same for all of them: the id reveals the creation time. A `users` table with ordered ids tells you exactly when each account was created, and an attacker with a user id can estimate the id range of every other user.

• UUIDv7 — standard, 16 bytes, time-ordered, RFC 9562. The default choice.
• ULID — 26 characters of Crockford base32, sortable as text. Bigger, but human-readable and monotonic within a millisecond.
• Snowflake — 64 bits, time plus node plus sequence. Compact and fast, but the epoch is custom and the format is a vendor convention.
• K-sortable IDs and the rest — variations on the same idea.

Monotonicity inside a millisecond

Two records created in the same millisecond need a tiebreak, otherwise the order between them is arbitrary. UUIDv7 handles it with a counter in the random bits, and ULID libraries usually add a monotonic increment.

This matters when you rely on the id order for something. If a client sorts by id and assumes insertion order, a non-monotonic generator will occasionally produce a record that sorts before one that was inserted earlier.

// UUIDv7, 48 bits of timestamp then random bits
2026-01-14T09:30:00Z  ->  0198f2a1-c4b7-7d3e-9c02-1e6a7b3d5f90
         ^^^^^^^^         ^^^^^^^^
         milliseconds     random, with a counter for ties

A workable default

For a new system, use UUIDv7 as a 16-byte native column, generated in the application, and accept that it reveals creation time. It needs no coordination, it does not fragment the index, and it is a standard rather than a convention.

If the tables are owned by a single database and never need to merge with another system, an auto-increment integer is still better on every axis except the ability to generate the id anywhere.

Whatever you choose, decide how the value is stored. A native `uuid` column is 16 bytes; a `varchar(36)` with a text index is 36 bytes and a random index order, and it is the most common way to accidentally undo the benefit of an ordered id.