UUIDv4 vs UUIDv7: what a random primary key costs in Postgres
Postgres 18 ships uuidv7(), which generates UUIDv7 as defined in RFC 9562. Yet many tables still have id uuid default gen_random_uuid(), which is UUIDv4: 122 random bits. Does a random primary key matter?
Barely, while the index fits in memory. Once it outgrows memory, a lot: in our runs at 100M rows, UUIDv4 inserted at 0.62 to 0.69 of the rate of an ordered key on the same server, and its index crowded the table out of shared_buffers.
WAL is a separate cost, driven less by memory than by how many index leaves each checkpoint cycle touches. At 90M to 100M rows, UUIDv4 wrote 1.5 to 1.9 times the WAL per row, while UUIDv7 kept up with int8.
I asked DBLab Forge to measure it: three tables with the same schema and the same insert stream, differing only in the primary key (UUIDv4, UUIDv7 or int8), seven runs on cloud servers, and every hypothesis written down with a falsifier before the first row. This post is the short version; the full brief has every number with its source.
Kirk Wolak and I talk about it in Postgres.FM episode 194, "UUIDv4 vs UUIDv7" (transcript included). The video shows the charts as we talk.