Choosing an ID

Primary key type is one of the few decisions that is genuinely hard to reverse. It propagates into every foreign key, every URL, every log line, every external integration, and every client that ever stored one.

Worth twenty minutes up front.

the options#

Auto-increment integer. Compact, fast, perfect index locality, human-readable in logs.

The problems are real. It leaks volume — /orders/48213 tells a competitor how many orders you have taken. It requires a round trip to the database to learn the ID, which blocks client-side generation and batching. And it makes merging data from two systems a genuine ordeal, because both start at 1.

UUIDv4. Random, globally unique, generatable anywhere without coordination, leaks nothing.

The cost is index behaviour. Random values scatter inserts across the entire B-tree, which destroys cache locality, inflates the index, and causes page splits on every insert. On a large, write-heavy table this is a genuine and measurable problem, not a theoretical one.

UUIDv7. Time-ordered UUID: a millisecond timestamp prefix, then randomness. Keeps global uniqueness and client-side generation, restores insert locality because new rows land at the end of the index.

This is the default I would now recommend for most new systems. It fixes the one serious problem with v4 while keeping everything that made v4 attractive. Postgres has uuidv7() built in; most languages have a library.

The trade: it leaks creation time. Usually fine, occasionally not.

ULID / KSUID and friends. Same idea as v7 — sortable, time-prefixed — with a more compact text encoding. Fine choices. UUIDv7 has the advantage of being a standard with native database support, which matters more than encoding length.

Natural keys. Email, ISBN, SKU. Almost always a mistake as a primary key, because "naturally unique and never changes" turns out to be false: people change email addresses, and standards get revised. Use them as unique constraints, not as the identifier everything else points at.

the two-identifier pattern#

Frequently the right answer is not to choose:

• Internal key: bigint, auto-increment. Used for foreign keys and joins. Compact, fast, never exposed.
• External ID: UUIDv7 or a prefixed string. Used in URLs, APIs, logs, support conversations. Unique index on it.

You get join performance and index locality internally, and no information leakage externally. The cost is one extra column and remembering which is which.

prefix your external IDs#

If you expose identifiers, prefix them by type:

cus_01J9F3K2M4N5P6Q7R8S9T0V1W2
ord_01J9F3K8X1Y2Z3A4B5C6D7E8F9

This is a small thing with an outsized payoff:

• A support engineer can tell what an ID refers to without asking.
• Passing a customer ID where an order ID belongs becomes detectable, and can be rejected at the API boundary rather than producing a confusing not-found.
• Logs and error reports become self-describing.
• You can rotate the encoding later without ambiguity.

Several well-run APIs do this and it is consistently one of the things developers say they appreciate about them.

the practical rules#

Never expose auto-increment integers publicly. Volume leakage plus trivial enumeration.

Do not use UUIDv4 as a clustered primary key on a table that will get large and write-heavy. If you already have, v7 for new tables and consider whether the old one needs a migration — usually it does not, but measure the index bloat before deciding.

Store UUIDs as a native uuid type, not as text. Sixteen bytes versus thirty-six, and correct comparison semantics.

Decide the case and format once, write it down, and validate at the boundary. Half your system accepting hyphenated lowercase and the other half accepting bare uppercase is a bug that surfaces years later in one integration.

Never reuse an ID. Ever. Deleted means gone; the identifier is retired with it. Reuse turns a stale reference from a clean not-found into silent corruption pointing at the wrong record.