Identifier Conventions

An identifier ends up everywhere: foreign keys, URLs, logs, caches, webhook payloads, other companies' databases. Changing its format later means migrating all of them, so it's one of the few decisions worth getting right on day one.

My default is UUID v7. The rest of this doc is why, and when something else is better.

What an ID Has to Do

Four questions decide the format:

1. Who generates it? One database, or many services and clients without coordinating?
2. What does it reveal? How many rows exist, how fast they're created, when each was made?
3. How does it index? Random keys scatter inserts across a B-tree; ordered keys append to the end.
4. Who reads it? Humans in support tickets, URLs, other systems?

Auto-Increment Integers

BIGSERIAL keys are small (8 bytes), fast, ordered, and easy to read aloud. Their problems all come from being a single counter:

• They leak. /invoices/10482 tells a competitor how many invoices you've issued, and two invoices a week apart tell them your growth rate. Sequential IDs also make enumeration attacks trivial: increment and try the next one.
• They need one writer. Only the database can hand them out, so a client can't create an ID before the insert, and two databases (a sharded table, a merger, an offline client) will collide.

Use them inside a single database for things that are never exposed: join tables, internal queues, and warehouse tables where 8-byte keys measurably shrink indexes.

UUID v7: The Default

A UUID v7 is 128 bits: a 48-bit Unix timestamp in milliseconds, then random bits.

01932c6c-7616-7590-a8db-cb4b0e4d1c8a

• Generated anywhere. Services, clients and batch jobs create IDs without asking anyone, which also means a client can know an object's ID before the insert, and an import can be retried without duplicating.
• Index-friendly. New IDs sort after old ones, so inserts append to the B-tree instead of splitting pages all over it. On large tables that's the difference that matters most against v4.
• Sorted by creation. Sorting by id is sorting by creation time, which makes cursor pagination simple (Pagination).

What it costs:

• It reveals when it was created, to the millisecond. Usually that's fine, since created_at is in the response anyway. When creation time is itself sensitive (an anonymous report, a signup that shouldn't be correlated with an event), use v4. RFC 9562 recommends v4 for security-sensitive uses for the same reason.
• Order within a millisecond isn't guaranteed unless the generator uses one of RFC 9562's monotonic methods (a counter or extra clock precision). If two IDs from the same millisecond must sort in creation order, use a generator that does, or don't depend on it.

-- PostgreSQL 18+
CREATE TABLE posts (
  id         UUID PRIMARY KEY DEFAULT uuidv7(),
  title      TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

PostgreSQL 18 generates v7 natively with uuidv7(). On older versions, generate it in the application; it's a few lines (timestamp into the first 48 bits, random bytes for the rest, then the version and variant bits).

UUID v4: Pure Randomness

122 random bits, nothing else. Use v4 when the ID must reveal nothing, including when it was made. The cost is index locality: random inserts touch pages all over the index, which on a large, write-heavy table means more page splits, more cache misses and more write amplification.

UUID v5: Derived From a Name

A v5 UUID is a hash of a namespace and a name. The same input always produces the same ID, and that's the point:

import uuid
uuid.uuid5(uuid.NAMESPACE_URL, "https://example.com/users/42")
# always 38fcaf6d-63bc-5c9f-8a4f-de8cf4e651ed

• Idempotent imports. Derive each record's ID from the source system's key, and re-running an import updates rows instead of duplicating them.
• Shared identity without a mapping table. Two systems that agree on a namespace compute the same ID for the same entity independently.
• Stable test fixtures. uuid5(ns, "user-admin") is the same on every run, so fixtures don't hard-code UUIDs (Testing Strategy).

Don't use v5 for anything a user can guess the input of. Anyone who knows the namespace and the name can compute the ID, so it's not an access control, and it can leak the input it was derived from.

v1, v6 and ULID

• v1 embeds a timestamp and a node ID (historically the MAC address) in an order that doesn't sort. Don't create new ones.
• v6 is v1 reordered to sort by time. It exists for systems migrating from v1; the RFC says everything else should use v7.
• ULID is v7's idea in a different encoding: 26 characters of Crockford Base32, case-insensitive, no dashes. It's nicer in a URL, but most databases have a native 16-byte UUID type and no ULID type, so ULIDs usually end up stored as strings, larger and slower. Prefer v7 unless the string form genuinely matters.

Choosing

Format	Generated by	Reveals	Index locality	Use it for
Integer	One database	Count and rate	Excellent	Internal-only keys, warehouses
UUID v7	Anyone	Creation time	Good	The default
UUID v4	Anyone	Nothing	Poor at scale	IDs where timing is sensitive
UUID v5	Anyone, from a name	The name, if guessable	Poor	Imports, shared identity, fixtures
ULID	Anyone	Creation time	Good	When the 26-character string matters

Conventions

• The ID is opaque to clients. It's a string to them, even though it's time-ordered. Parsing timestamps out of IDs couples clients to the format.
• Public IDs never change. If an internal key has to change, the public one doesn't.
• Column names describe roles, not types: id, user_id, stripe_charge_xid for external IDs. See Database Naming Conventions.

This Site

The UUID/ULID Generator generates and inspects v7, v4, v1 and ULIDs. The API's /uuid endpoint returns v7 by default and v4 with ?version=4, and the API's Request-Id header is a v7. Its generator is the simple kind: IDs created in the same millisecond, like a batch from ?count=100, aren't guaranteed to be in order.

References

Specifications

• RFC 9562: Universally Unique IDentifiers (UUIDs), every version, v7 monotonicity methods, and security considerations
• ULID specification, the format, Crockford Base32 encoding and sort order

Reference

• PostgreSQL: UUID Functions, uuidv7() and uuid_extract_timestamp()
• PostgreSQL: UUID Type, native storage as 16 bytes
• MDN: Crypto.randomUUID(), generating v4 UUIDs in the browser and Node
• MDN: UUID, a short primer

In practice

• Buildkite: Goodbye to sequential integers, hello UUIDv7, a production migration to time-ordered UUIDs