Random UUIDs vs time-ordered UUID v7 keys in a B-tree index: an interactive simulator
Random UUIDs are a convenient primary key: any service can make one without coordination. But a B-tree index keeps keys in order, so random keys land on random pages. Pages split in half all over the index, it ends up larger and emptier, and once it no longer fits in memory almost every insert first reads a page from disk. Time-ordered IDs such as UUID v7, ULIDs or Snowflake IDs keep the convenience and insert at the end of the index, like an auto-increment column.
The playground inserts 200,000 rows into an index of 8 KB pages holding 100 keys each, with a small, medium or large buffer pool.
Inserting 200,000 rows, 100 keys to an 8 KB page, with a buffer pool of 7.8 MB. Time-ordered keys always land on the last page, so it stays in memory: no disk reads at all, and every finished page is full, 15.6 MB of index. Random UUIDs land anywhere: pages split in half all over the index, which ends 69% full and 44% bigger, 22.5 MB. Once the index outgrows the buffer pool, about 70,000 rows in, most inserts need a page from disk first: 641 reads per 1,000 inserts by the end, 55,395 in all.
- Index size
- 22.5 MB
- Pages full
- 69%
- Page splits
- 2,881
- Disk reads
- 55,395
- Index size
- 15.6 MB
- Pages full
- 100%
- Page splits
- 1,999
- Disk reads
- 0
What the playground shows
- Time-ordered keys stay in memory. Every insert goes to the last page, which is always cached, so the whole load reads nothing from disk, and every finished page is full: 15.6 MB of index.
- Random keys bloat the index. Splitting in half everywhere leaves pages about 69% full on average, the classic result for random inserts, so the same rows take 22.5 MB, 44% more.
- Random keys thrash the buffer pool. While the index fits in memory, nothing changes. Once it does not, the share of inserts that must read a page climbs towards the share of the index that is not cached: with a 7.8 MB buffer pool, 641 disk reads per 1,000 inserts by the end, 55,395 in all.
- The same applies to secondary indexes. Any index on a random value, a hash or a random UUID foreign key, behaves the same way; it is the access pattern, not the column type, that hurts.
Time-ordered identifiers
UUID v4 9f1c2a7e-5b3d-4e8a-a0c1-6d2f7e9b4a13 122 random bits: inserts land anywhere
UUID v7 01929c3e-7b40-7a1d-9e5f-3c2b1a0d9e87 48-bit millisecond timestamp first, then random
ULID 01JB8Z3K9QXW6T2V5R7N4M8P0C same idea, 26 base32 characters
Snowflake 1852103458827468800 64 bits: time, machine, sequence
-- Postgres 18 and later
CREATE TABLE orders (id uuid PRIMARY KEY DEFAULT uuidv7(), ...);Time-ordered keys have their own trade-offs. They reveal roughly when a row was created. And because every insert goes to the same last page, very high insert rates contend on it; databases that shard by key range, such as Spanner or CockroachDB, may prefer random or hashed keys precisely to spread writes across ranges instead of creating a hot spot at the end.
In a system design interview
When you pick an ID scheme, mention the index: “IDs are UUID v7, generated by the services without coordination. Because they are time-ordered, inserts append to the end of the primary key index, which stays compact and cached, unlike random v4 UUIDs, which scatter writes and bloat the index. For a range-sharded store we would hash-prefix the key instead so writes spread across shards.” Expect the follow-ups: clock skew between generators, whether IDs leak creation times, and how sharding changes the choice.
Read the guide to unique IDs and time or database indexing, see B-trees and Snowflake IDs, or watch a strong candidate design a unique ID generator.
Questions people ask
Are UUIDs bad primary keys?
Random UUIDs, version 4, make poor clustered or B-tree keys at scale: inserts land on random pages, causing page splits, half-empty pages and cache misses once the index exceeds memory. Time-ordered UUIDs such as version 7 avoid this while keeping decentralised generation.
What is UUID v7?
A UUID format standardised in RFC 9562 whose first 48 bits are a Unix timestamp in milliseconds, followed by random bits. IDs generated later sort later, so they behave like an increasing key in an index while still being generated without coordination.
Why are random keys slow to insert into a B-tree?
Each insert must modify the leaf page where the key belongs. With random keys that page is anywhere in the index, so once the index is larger than the buffer pool most inserts read a page from disk, and pages split in half everywhere, leaving them about 69% full on average.
What is the difference between UUID v7, ULID and Snowflake IDs?
All three start with a timestamp so they sort by creation time. UUID v7 is a standard 128-bit UUID; a ULID is also 128 bits but written in 26 base32 characters; a Snowflake ID packs a timestamp, a machine id and a sequence into 64 bits, which is smaller but needs machine ids to be assigned.
Do time-ordered keys have downsides?
They reveal approximately when a record was created, and very high insert rates all hit the last index page, which can become a contention point. In range-sharded databases they send all new writes to one shard, so those systems often prefer hashed or random keys.
Should I use auto-increment or UUIDs?
Auto-increment integers are compact and append-friendly but need a central sequence and expose counts. UUID v7 gives the same index locality with decentralised generation and no guessable sequence, at twice the size of a 64-bit integer.
How do I fix an index bloated by random keys?
Rebuild or reindex it to pack pages again, and switch new keys to a time-ordered scheme. Some databases also let you lower the fill factor for random-key indexes, leaving room on each page so fewer inserts cause splits.