Time-Ordered Identifier

A service picks random version-4 UUIDs for its primary keys: unique without coordination, safe to expose, no central sequence to contend on. At 200 million rows, insert throughput has halved and the buffer cache hit rate is falling, in a table that is append-only.

The identifiers are not too large. They are in the wrong order. A B-tree stores entries by key, so a monotonically increasing key appends to the same right-hand leaf page - which stays in cache and is written once per checkpoint however many rows it absorbed. A random key sends each insert to a different leaf, so the hot set becomes the entire index, inserts split pages in the middle, and write-ahead volume multiplies because many engines log a full page image the first time a page is touched in a checkpoint cycle.

A time-ordered identifier keeps both properties: a timestamp in the high bits for locality, and randomness in the low bits for uniqueness and unguessability. UUIDv7, standardised in RFC 9562 (2024), places a 48-bit millisecond Unix timestamp first; ULID and snowflake-style ids do the same with different layouts.

Why it matters

Primary key choice is made in the first week of a service's life, by whoever generates the migration, and it is the single hardest thing to change later - every foreign key, every cached reference, every partner integration and every URL may depend on it.

It is also one of very few decisions where you can have both options' benefits rather than trade them. The usual framing - sequences are fast but need coordination, UUIDs are distributed but slow - stopped being true once sortable UUID formats were standardised, and a surprising amount of code is still written against the old framing.

Implementation patterns

• Generate in the application, which is the point: no round trip to a sequence, and ids can be created on a client or in a worker before the row exists.
• Store in a native 16-byte type where the engine has one. Storing a UUID as a 36-character string doubles the index and defeats much of the benefit.
• Check the byte order your library emits. Some drivers historically shuffled UUID bytes before storage, which destroys ordering in the index even when the id itself is sortable.
• Separate the clustered key from the exposed key when the exposure matters: a monotonic internal key for storage locality and a random public id as a unique secondary column. One extra index lookup per fetch-by-public-id is far cheaper than random insert locality.
• Know what the timestamp reveals. A sortable id leaks creation time and, in sequence, lets an observer estimate your creation rate. If either matters, keep the sortable id internal.

Industry example

Pinterest's documented sharded-MySQL design is the classic illustration of the general principle taken further: the shard id is embedded in the object id, so routing needs no lookup at all and the id carries structure deliberately. The id is a design artefact rather than an opaque number.

RFC 9562 (2024) is the other half of the story: version 7 exists precisely because practitioners kept reinventing timestamp-prefixed identifiers - ULID, KSUID, snowflake and a dozen internal schemes - and standardising one removes the need for a bespoke format in most services. The pattern spread because the alternative failed the same way in every organisation that tried it at scale.

Failure scenarios

• Index working set exceeding cache with random keys, which is the original failure and is silent until the crossing point.
• A hot partition in a distributed store, which is the mirror image: with range or hash partitioning, a monotonic prefix sends every write to one shard. Here the randomness was load bearing and a time-ordered key is the wrong choice.
• UUIDs stored as text, inflating every index and every secondary index entry.
• Byte-shuffling drivers that silently remove ordering, so the measured benefit never appears and the team concludes the pattern does not work.
• Information disclosure from a sortable id in a public URL - creation time, and sometimes volume, inferable by anyone who collects a few.

Trade-offs

When not to use it

In a store that partitions by key, a time-ordered id concentrates all current writes on one partition - the exact problem randomness was solving. Distributed stores want high entropy early in the key, with the timestamp later or inside a bucketed prefix.

And at small scale it does not matter. A table whose index fits comfortably in cache performs the same with either choice, so changing an existing schema purely for this is rarely worth the migration. The rule is to choose well in new schemas, where it is free, and to measure index size against available cache before migrating an old one.

Interview question

Q: A team is choosing primary keys for a new multi-tenant service: a database sequence, a random UUID, or a time-ordered UUID. Walk me through the decision, and tell me what would change your answer if the store were a partitioned key-value database rather than Postgres.

What a strong answer covers: insert locality in a B-tree and why random keys enlarge the working set, split pages and amplify the write-ahead log; the 16-byte width effect repeated in every secondary index; generation without coordination as the real reason to avoid a sequence; time-ordered formats giving both, with RFC 9562 as the standard; the inversion in a key-partitioned store, where monotonic prefixes create a hot shard; and the information-disclosure consideration for ids that appear in public URLs.

Quick check

Quiz: What does a time-ordered identifier recover that a random UUID gives up, and what does it give up in return? It recovers insert locality at the right-hand edge of the index; it gives up opacity, since the creation time is readable, and it creates a hot partition in a store that partitions by key.

Flashcard: Which part of a UUIDv7 provides ordering and which provides uniqueness? A 48-bit millisecond timestamp in the high bits gives ordering; the remaining random bits give uniqueness without any coordination.