In a synthetic test of 26 million rows, a PostgreSQL table with a random UUIDv4 primary key accepts new writes almost as fast as a sequential counter. In a fresh study by PostgresAI founder Nikolay Samokhvalov—which I spotted in Postgres Weekly—that opening stretch caught my eye right away: it vividly explains why random identifiers look completely free on a staging bench.
The catch surfaces once a project outgrows available RAM. As soon as the index no longer fits in the server's buffer cache, the database abruptly loses a third of its write throughput and the storage drive chokes on a flood of small operations. Let's look at the real cost of random keys beyond RAM and how the time-ordered UUIDv7 format restores predictable database speed.
B-tree Storage Physics: Why Random Keys Hammer Index Pages
Take a clear working scenario: an orders table with a UUIDv4 primary key has grown to 50 GB on disk, while the server's buffer pool (Shared Buffers) is allocated 16 GB.
Picture this system as a paper archive with shelves of folders. Each folder is an 8 KB B-tree leaf index page where order cards are filed strictly in ascending key order. In front of the archivist sits a workbench—our Shared Buffers memory. Only a third of the warehouse's folders fit on the desk; for the rest, the archivist has to walk to the far shelves—meaning hitting the SSD.
When orders arrive with sequential numbers, the archivist keeps one rightmost folder on the desk for fresh entries and appends cards to the end. When a folder fills up, a new one opens beside it, and the old one goes onto the shelf and is forgotten.
Now replace the counter with a classic UUIDv4 identifier from gen_random_uuid(). Out of its 128 bits, 122 bits are pure randomness. Such keys have no notion of "earlier" or "later": one order starts with f, the next with 0, the third with 8.
For the archivist, that turns into a nightmare. By B-tree sorting rules, every new card must land not at the end of the list, but in a random folder somewhere in the middle of the warehouse. Because only a small fraction of the index fits on the desk, the right folder is almost never at hand. On every insert, PostgreSQL has to flush an old modified ("dirty") page from the desk to disk, read the needed block from the shelf, and squeeze the row into the middle. And when an old folder overflows, a B-tree page split occurs: the database allocates a new block, splits the entries in half, and rewrites both pages along with pointers in the parent node.
The PostgresAI Benchmark: A One-Third Write Drop and WAL Bloat
Nikolay Samokhvalov tested these physics up to 100 million rows, comparing random UUIDv4, ordered UUIDv7, and integer sequences under RAM pressure.
As long as the table and index fit entirely inside Shared Buffers, the curves tracked neck and neck: in RAM, touching the rightmost page or a random page takes the same nanoseconds. Yet as soon as the index outgrew the buffer cache, UUIDv4 insert throughput plunged to 62–69% of the ordered key's speed—writes slowed down by roughly a third. In our orders table, that means insert throughput dropping to 65% of normal solely due to random index page reads from SSD.
The second blow hit the Write-Ahead Logging (WAL) journal, where the database synchronously writes every change before confirming a transaction (I covered how this log ties into durability and page cleanup in our breakdown of PostgreSQL architecture, WAL operation, and MVCC mechanics). When I opened Samokhvalov's WAL measurements, the numbers were even starker: UUIDv4 inserts generated 1.5–1.9x more log traffic per row than UUIDv7.
Two mechanisms drive this nearly twofold log overhead:
- Frequent page splits: on every
page split, the DBMS logs the physical movement of half the old page into a new block inWAL. - Full Page Writes: to protect against power loss after a
checkpoint, PostgreSQL saves a full copy of any page inWALon its first modification. BecauseUUIDv4chaotically touches thousands of pages across the entire tree, gigabytes of full page images pour into the log.
Anatomy of UUIDv7: How a Time Prefix Reconciles Uniqueness with Order
For years, engineers had to pick between two extremes. Integer keys (serial, bigserial, or IDENTITY) take 4–8 bytes and append writes to the right edge of the B-tree, yet they leak order volumes to competitors and are awkward in distributed systems that need an ID before hitting the database. Meanwhile, UUIDv4 lets any node generate keys autonomously, but punishes you with disk degradation as the table grows.
The RFC 9562 standard solved this contradiction in UUIDv7 by splitting the key into two zones:
- The high 48 bits hold a
Unixmillisecond timestamp, giving new writes a forward direction. - The lower bits (after version and variant markers) hold a sub-millisecond counter and cryptographic randomness to prevent collisions.
Because B-tree compares keys left to right, the time prefix lines up new identifiers in a chronological queue. An order placed a second later is guaranteed a larger key than the previous one, while the random tail separates purchases arriving within the same millisecond.
For our archivist, this is a rescue: all fresh rows in orders once again land strictly on the right edge of the tree. A handful of rightmost "hot" index pages stay resident in the buffer pool, WAL volume drops by 30–45%, and replicas stop lagging behind the primary.
Moving to uuidv7() in PostgreSQL 18 and Production Pitfalls
Starting with PostgreSQL 18, uuidv7() is built straight into the DBMS core without external extensions (I also wrote about upcoming B-tree index improvements in our review of Postgres 19 Release Candidate and UUID scan optimizations).
Let's switch the orders table from random gen_random_uuid() to ordered uuidv7() and fetch recent orders without a separate date index:
-- Instantly switch the default key generator in table metadata
ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();
-- Fetch the 50 most recent orders via a backward scan on the primary key
SELECT id, created_at, total_amount
FROM orders
ORDER BY id DESC
LIMIT 50;
The ALTER TABLE command finishes in milliseconds: it only updates the default rule in the system catalog without rewriting accumulated gigabytes. Old UUIDv4 rows stay put, while new orders immediately form an ordered region on the right edge of the index, bringing insert speed back to normal. The SELECT with ORDER BY id DESC LIMIT 50 delivers a nice bonus: thanks to the time prefix, the database instantly returns the latest rows by scanning backward across the rightmost primary key pages without a separate index on created_at.
At the same time, Nikolay Samokhvalov highlights two caveats when adopting UUIDv7:
- Database vs. client generation. If you create
UUIDv7across multiple application servers rather than viaDEFAULT uuidv7()in the database, clock drift between nodes (a ±60-second skew) breaks key monotonicity and partially brings backB-treepage splits. - Exposed timestamp in public IDs. The
UUIDv7prefix easily decodes to the exact date and millisecond a record was created, revealing order placement times in external URLs.
In my view, the best rule here is simple: let the PostgreSQL server itself call uuidv7() using its single system clock, and if a record's creation time is sensitive, don't expose the internal key in public URLs unmasked.
Ultimately, choosing the first six bytes of a primary key isn't about chasing synthetic benchmark records—it's about everyday engineering peace of mind. When a table schema cooperates with disk and memory physics, the database stops waking the team with midnight alerts about full storage and lagging replicas, leaving you free time and a clear head for creative work.
