UUID v4 vs v7 Primary Keys.

🙅The myth

"UUID primary keys are slow because they're 16 bytes instead of an 8-byte bigint."

✅Actually

The width is a rounding error. What hurts is insert order. Random keys fight the B-tree's love of locality.

🌳Why randomness is the real bill

A B-tree stores keys sorted. With a bigserial PK every insert lands on the rightmost leaf page — already in cache, already dirty, fills to ~90-100% before it splits.

With gen_random_uuid() (v4), each insert targets a random leaf among hundreds of thousands:

• that page probably isn't in shared_buffers → random read from disk
• you dirty a new page per insert → more WAL + more full-page images
• splits happen mid-page, leaving leaves ~50-70% full → the index bloats to roughly 1.5-2x

Rough shape on a 50M-row table: bigserial PK index ≈ 1.1 GB, uuid v4 PK index ≈ 2.2 GB. Throughput is fine until the index stops fitting in RAM — then it falls off a cliff. That's why "it was fast in staging" is the usual story.

🧪Prove it in 4 lines

CREATE TABLE a (id bigserial PRIMARY KEY, p text);
CREATE TABLE b (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), p text);
INSERT INTO a (p) SELECT 'x' FROM generate_series(1,2000000);
INSERT INTO b (p) SELECT 'x' FROM generate_series(1,2000000);

Then: SELECT pg_size_pretty(pg_relation_size('b_pkey')); vs a_pkey. Time both inserts.

🕒What v7 changes

UUIDv7 = 48-bit millisecond timestamp prefix + random tail. Sortable, so inserts hit the right edge again — index size and write amplification go back near bigserial, and you keep a globally unique, client-generatable ID. Postgres 18 ships uuidv7(); before that, generate it in the app.

⚖️v4 vs v7

• ordering: none vs time-ordered
• index bloat: high vs low
• leaks creation time: no vs YES (visible in the ID — avoid for things where timing is sensitive, e.g. signup order of private beta users)
• hides row count: both yes

⚠️What intermediates get wrong

1. Storing it as varchar(36). That's 37 bytes + string comparison + no native ops. Use the native uuid type (16 bytes). This is the only place "size" is actually your bug.
2. Assuming v7 is free: a monotonic key reintroduces right-edge page contention. At tens of thousands of inserts/sec, many sessions now want a lock on the same leaf page — the thing random keys accidentally avoided.
3. MySQL/InnoDB is worse than Postgres here: the PK is clustered, so a random PK scatters the table itself, and every secondary index carries a copy of the PK.
4. Switching PK type on a live table is a full table rewrite + every FK. Decide this on day one.

🎯Your exercise

Run the 4-line test with 2M rows. Report the two index sizes and the two insert times. Then install pgstattuple and compare avg_leaf_density on both indexes — that number is the whole lesson in one integer.