Every few months a team pings me with the same story: insert throughput on one table quietly collapsed. Same hardware, same queries, the only thing that changed is row count. Nine times out of ten the table has a uuid primary key defaulting to gen_random_uuid(), and the index has turned into a pile of half-filled pages.

It is the most common regression I diagnose in Houston SaaS workloads: UUID primary keys are the default in nearly every scaffolding tool, and the cost stays invisible until the table gets big. Here is how to prove it, what the numbers should look like, and the fix that does not require rewriting your keys.

The failure mode is index locality, not “UUIDs are slow”

A B-tree keeps keys sorted. Insert a bigint identity and every new value is larger than the last, so it lands on the rightmost leaf page — already in shared_buffers because the previous insert touched it. Fill it, split it cleanly, move on. The hot working set is a sliver at the right edge.

A random v4 UUID does the opposite: each new key sorts somewhere uniformly at random in the whole index. Four costs follow, and only the first is obvious.

• Cache locality dies. The next insert could hit any leaf page, so the hot working set becomes the entire index. Once it outgrows the buffer cache, every insert costs a page read.
• Page splits fragment everything. Inserts land in the middle of full pages and split them down the middle, leaving pages roughly 70 percent full.
• WAL amplifies. With full_page_writes on (default), the first modification of a page after each checkpoint writes a full 8 KB page image into WAL. Random inserts touch far more distinct pages between checkpoints than sequential ones.
• Every foreign key pays the multiplier. A uuid is 16 bytes versus 8 — in the PK, in every referencing FK column, and in every index on those columns, with fewer entries per page and deeper trees.

This is why the last decade produced ULIDs, snowflake IDs, and a small industry of “sequential UUID” extensions. They were all attacking the randomness, not the UUID.

Step 1: Prove the index is the problem

Two queries settle it. First, leaf density and fragmentation from pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT leaf_pages,
       avg_leaf_density,
       leaf_fragmentation,
       pg_size_pretty(index_size) AS index_size
FROM pgstatindex('orders_pkey');

On an append-mostly table, healthy is density near 90 percent and fragmentation near zero. A random-UUID index typically shows density in the low 70s and fragmentation around 50 percent. That gap is your regression, made visible. pgstatindex() reads every page, so run it off-peak or on a replica.

Second, confirm the cache is thrashing. Counters are cumulative since the last stats reset, so compare before and after a busy window rather than trusting one snapshot:

SELECT relname,
       idx_blks_read,
       idx_blks_hit,
       round(100.0 * idx_blks_hit
             / nullif(idx_blks_hit + idx_blks_read, 0), 1) AS cache_hit_pct
FROM pg_statio_user_indexes
WHERE relname = 'orders_pkey';

A primary key index at 91 percent hit rate on an insert-heavy table is a red flag — roughly 9 of every 100 index reads go to disk just to place a new row. If you want the trailing effects rather than the cause, our pg_hdr_histogram walkthrough gets you per-statement p99 without averaging it away.

Step 2: Check WAL volume around a fixed batch

WAL amplification shows up as replication lag or an archive backlog nobody attributes to an index. Measure it: snapshot wal_bytes from pg_stat_wal, insert a fixed 100,000 rows, snapshot again, then run the same batch against a copy of the table with a bigint identity key. If the UUID copy generates materially more WAL, that is a second cost a REINDEX alone will not fix.

Step 3: Change the default, not the keys

PostgreSQL 18 added uuidv7() in core. Its first 48 bits are a millisecond Unix timestamp, followed by sub-millisecond bits and randomness, so byte order equals time order — new keys insert at the right edge like a sequence, and the split, cache, and WAL problems mostly evaporate. The official 18 documentation lists it alongside a uuidv4() alias for the old random generator.

-- PostgreSQL 18+: new rows become time-ordered, existing rows untouched
ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

-- Verify what is landing in the table now
SELECT uuid_extract_version(id) AS key_version, count(*)
FROM orders
GROUP BY 1 ORDER BY 2 DESC;

Do not migrate existing key values. Every foreign key pointing at them has to change, every referencing index rebuilds, external systems holding those IDs end up with dangling references, and the UPDATE rewrites the whole table. New rows get v7, old rows keep theirs.

The index improves from the right edge outward over a few days as the hot region fills with v7 keys. Once the split rate settles, REINDEX CONCURRENTLY orders_pkey packs the historical pages too — watch it with pg_stat_progress_create_index, and expect hours on a large index.

On versions before 18, generate v7 in the application (Python 3.14 has uuid.uuid7(); the JavaScript standard uuid package exposes v7) and send it in the insert. Make sure every service uses the same algorithm — an older “ULID as UUID” shim puts the timestamp in a different position, and the two populations interleave badly in one index.

What UUIDv7 does not fix

• Size stays. 16 bytes versus 8, multiplied across the PK, every FK, and every index on those FKs. If you generate IDs centrally anyway, bigint is still cheaper.
• Never store UUIDs in text. The 36-character form costs 37 bytes instead of 16 and kills comparison performance. This one mistake explains a large share of “UUIDs are slow” reports.
• Timestamps leak. Anyone with a v7 key can read when the row was created via uuid_extract_timestamp(). Fine for an order ID, less fine for a user ID or anything in a public URL — keep a separate opaque public identifier there.
• Derived timestamps are not sargable. WHERE uuid_extract_timestamp(id) > now() - interval '1 hour' cannot use the primary key index; you need an expression index or range predicates on raw UUID boundaries.
• Multi-node interleaving is real but late. One published benchmark only saw visible fragmentation from many nodes minting keys simultaneously above roughly 100k cluster-wide inserts per second.

Reference numbers from public benchmarks, not lab conditions: a 200-million-row events table on PostgreSQL 18 showed bigint at 4.3 GB of index and baseline throughput, v4 UUID at 6.1 GB and 62 percent of baseline with 2,870 page splits per minute, and v7 UUID at 6.1 GB and 96 percent of baseline with 58 splits per minute (zeybek.dev). A million-row test put leaf density at 71.5 percent for v4 against 90.0 for v7, fragmentation 49.9 against zero (pgEdge). Your absolute numbers will differ; the shape will not.

How I decide, in practice

New tables on 18 get uuid PRIMARY KEY DEFAULT uuidv7() unless I specifically need an unguessable, non-time-ordered key. Existing v4 tables with visible regression get the default swapped today and a weekend reindex, then the redundant public_id column and its index get dropped. And before building any replacement index, model it first — testing indexes with HypoPG tells you whether it will even be used, which is cheaper than discovering that on a 200-million-row table.

If insert throughput halved for no visible reason, we run Postgres write-path and index reviews for Houston engineering teams — tell us the table, the key type, and the row count, and we will tell you where the writes are going.