Are UUIDs really slow as a primary key?

Mostly no — the cost is real but much smaller than the folklore implies, and it is almost entirely on the write path. On Postgres 16 at one million rows, a uuid primary key cost 2.6x a bigint on bulk insert and 1.4x on index size, and cached point lookups were indistinguishable. The number people usually imagine — reads being slow — did not show up at all.

The short answer

• On Postgres 16, inserting one million rows took 717–769 ms with a bigint primary key, 1,776–1,965 ms with a random UUIDv4, and 1,026–1,098 ms with a time-ordered UUIDv7 — so v4 costs about 2.6x, and v7 gives most of that back.
• A UUID primary-key index on one million rows is 30 MB packed versus 21 MB for a bigint — 1.4x, not the 4x you would guess from 16 bytes versus 8.
• Cached point lookups by primary key are the same speed. 20,000 random lookups took 82.0–85.4 ms on bigint and 80.3–83.2 ms on UUIDv4. Both indexes are three levels deep and both lookups touch the same number of pages.
• A random UUIDv4 index grows to 38 MB in place when a packed one is 30 MB — about 25% of it is empty space from page splits, at 72.31% leaf density and 49.64% fragmentation.
• UUIDv7 does not automatically fix that. Bulk-loaded a million rows at once it was worse than v4 — 40 MB, 67.01% leaf density. Fed the same keys spread across distinct milliseconds, the way a real application writes, the same index came out at 30 MB and 90.03% density with zero fragmentation.

What was measured, and on what

Postgres 16.15 in Docker on an Apple M3 laptop, 8 cores, 16 GB RAM, macOS 26.4.1, Docker 29.5.2, shared_buffers=128MB and max_wal_size=4GB. These are indicative numbers from a laptop, not a lab benchmark. Every timing below was run at least three times and reported as a range. Your absolute numbers will differ; the ratios are what to take away.

Three tables, identical apart from the key type:

CREATE TABLE t_big (id bigserial PRIMARY KEY, payload text NOT NULL);
CREATE TABLE t_u4  (id uuid      PRIMARY KEY, payload text NOT NULL);
CREATE TABLE t_u7  (id uuid      PRIMARY KEY, payload text NOT NULL);

Each got one million rows with a 32-character md5() payload.

Postgres 16 has no built-in uuidv7() — that arrived in Postgres 18. I generated v7 in SQL, with the well-known function that overlays a millisecond timestamp onto the first six bytes of a gen_random_uuid() and flips the version bits:

CREATE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
  SELECT encode(set_bit(set_bit(
    overlay(uuid_send(gen_random_uuid())
            PLACING substring(int8send(floor(extract(epoch FROM clock_timestamp())*1000)::bigint) FROM 3)
            FROM 1 FOR 6), 52,1),53,1),'hex')::uuid;
$$ LANGUAGE sql VOLATILE;

Generating keys and inserting them are separate costs, and mixing them hides the thing we care about. So the insert timings below use keys generated in advance into a plain heap table, and the generation cost is reported on its own line.

How much slower is a UUID primary key to insert?

Inserting one million pre-generated rows, INSERT INTO … SELECT, three warm runs after a TRUNCATE and CHECKPOINT:

Two things fall out of that table.

The first is that the write penalty is real. 2.6x is not a rounding error, and it is the number to quote when someone says UUIDs are slow. It comes from the btree: a random key lands on a random leaf page, so every insert dirties a different page, and pages split constantly.

The second is that the SQL UUIDv7 function costs more to run than the index locality it buys back. Generating a million v7 values took 1,007–1,040 ms against 490–512 ms for gen_random_uuid(). Inserted with keys generated inline rather than pre-generated, v7 finished in 2,605–3,474 ms against v4's 2,700–3,466 ms — a dead heat. The v7 advantage is entirely in the index, and on Postgres 16 a pure-SQL generator hands it straight back. Generate v7 in your application, or wait for Postgres 18's native uuidv7().

How much bigger is a UUID index?

After VACUUM ANALYZE, and then again after a REINDEX to see how much of the size is fragmentation rather than data:

The heap difference is exactly what arithmetic predicts: 8 extra bytes per row, one million rows, 8 MB. Nothing surprising.

The index is where the folklore is half right. A packed UUID index is 30 MB against 21 MB — 1.4x, not 2x, because a btree entry is a header plus a line pointer as well as the key, so doubling the key width does not double the entry. But as grown, the v4 index is 38 MB, a quarter of which is holes. That is the page-split cost made visible, and it is the number that shows up in your disk usage graph.

Does UUIDv7 fix it?

This is where the measurement contradicted what I expected, so it is worth being plain about.

Bulk-loading a million v7 keys produced a larger index than v4: 40 MB against 38 MB, at 67.01% leaf density against 72.31%. Time-ordered keys were supposed to be the fix, and on this test they made the index worse.

The reason is that the test compressed time. Postgres has a fast path for strictly rightmost inserts: it splits the rightmost leaf page 90/10 instead of 50/50, because it knows nothing will ever be inserted to the left of the split. UUIDv7 is only ordered to the millisecond. When a million rows arrive in under two seconds, they occupy 1,874 distinct milliseconds — about 534 keys per millisecond, all sharing a timestamp prefix and randomly ordered among themselves. That is enough randomness to defeat the rightmost fast path, so splits are 50/50 again. Worse than v4, in fact: with v4 the insert point keeps returning to old pages and topping them back up, while with v7 the insert window slides forward and abandons each page at whatever fill it happened to have.

So I re-ran it with a synthetic clock — one distinct millisecond per row, which is roughly what a real application looks like when its writes are spread over hours rather than seconds:

That is byte-for-byte identical to a freshly REINDEXed index, and the same 90% density a bigserial gets. UUIDv7 does remove the index bloat — but only when your write rate is low enough that the timestamp actually orders the keys. At a few hundred writes per millisecond it degrades to something close to random, and a v7 implementation with a sub-millisecond counter in the random bits, rather than pure randomness, is what you want at that rate.

Are UUID reads slower?

No — not measurably, once the data is cached. 20,000 random primary-key lookups in a PL/pgSQL loop, three warm runs, with the empty loop costing 3.1–3.6 ms:

All three indexes are tree_level = 2, and EXPLAIN (ANALYZE, BUFFERS) shows each lookup touching the same five buffers. Same depth, same page count, same time. UUIDv7 coming out marginally fastest is noise, not a finding.

I also tried to make the reads hurt, by capping the container at 320 MB against a 326 MB database so the indexes could not stay resident. The result was 484 ms to 13,674 ms for the same 20,000 lookups, with no consistent ordering between the three schemes across nine rounds — bigint was slowest as often as it was fastest. On a laptop, cache-miss noise completely swamps the key-type difference, so I will not pretend to a number there. What survives is the structural fact: the UUID index needs 1.4x the cache to stay resident, so you reach the point where it stops fitting at roughly 70% of the row count. That is the real read-side cost, and it is a capacity-planning number, not a latency one.

If you are trying to work out whether a slow query is index-bound at all, EXPLAIN (ANALYZE, BUFFERS) tells you directly — the read versus hit counts are the whole answer.

So when should you not use a UUID?

The honest summary is that this is a smaller decision than it is usually treated as. At one million rows on a laptop, choosing UUIDv4 over bigint costs you about a second per million inserts and 17 MB. If that matters to your application, you already know it does.

Use bigint when the table is very large and write-heavy — an events or metrics table taking sustained bulk inserts, where 2.6x on the write path compounds, or a job queue whose whole job is to churn rows. Use a UUID when identifiers are generated by clients, merged across databases, or appear in URLs where a guessable sequential integer is a leak. If you use a UUID, use v7 generated in your application, and check your write rate against the millisecond-collision problem above before assuming the ordering is buying you anything.

What is not a good reason to avoid UUIDs is read latency. That one did not reproduce.

Check it yourself

About three minutes, one container, nothing installed:

docker run -d --name uuidbench -p 55432:5432 \
  -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=bench \
  postgres:16 -c max_wal_size=4GB
sleep 12

docker exec -i uuidbench psql -U postgres -d bench -q <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgstattuple;

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
  SELECT encode(set_bit(set_bit(
    overlay(uuid_send(gen_random_uuid())
            PLACING substring(int8send(floor(extract(epoch FROM clock_timestamp())*1000)::bigint) FROM 3)
            FROM 1 FOR 6), 52,1),53,1),'hex')::uuid;
$$ LANGUAGE sql VOLATILE;

CREATE TABLE t_big (id bigserial PRIMARY KEY, payload text NOT NULL);
CREATE TABLE t_u4  (id uuid      PRIMARY KEY, payload text NOT NULL);
CREATE TABLE t_u7  (id uuid      PRIMARY KEY, payload text NOT NULL);

\timing on
INSERT INTO t_big (payload)     SELECT md5(g::text) FROM generate_series(1,1000000) g;
INSERT INTO t_u4  (id, payload) SELECT gen_random_uuid(),  md5(g::text) FROM generate_series(1,1000000) g;
INSERT INTO t_u7  (id, payload) SELECT uuid_generate_v7(), md5(g::text) FROM generate_series(1,1000000) g;
\timing off

VACUUM ANALYZE t_big, t_u4, t_u7;

SELECT t AS scheme,
       pg_size_pretty(pg_relation_size(t))          AS heap,
       pg_size_pretty(pg_relation_size(t||'_pkey')) AS pk_index,
       (SELECT round(avg_leaf_density::numeric,1)   FROM pgstatindex(t||'_pkey')) AS leaf_density,
       (SELECT round(leaf_fragmentation::numeric,1) FROM pgstatindex(t||'_pkey')) AS fragmentation
FROM unnest(ARRAY['t_big','t_u4','t_u7']) t;

REINDEX TABLE t_big; REINDEX TABLE t_u4; REINDEX TABLE t_u7;

SELECT t AS scheme, pg_size_pretty(pg_relation_size(t||'_pkey')) AS pk_index_packed
FROM unnest(ARRAY['t_big','t_u4','t_u7']) t;
SQL

docker rm -f uuidbench

Two runs of that on a fresh container gave leaf densities of 90.1 / 72.0 / 65.8 and 90.1 / 71.4 / 64.3, with both UUID indexes packing down to exactly 30 MB. The sizes reproduce; the timings on the first run do not, because the first million inserts are dominated by WAL file allocation. Run it twice.

To see the effect the article is about, compare the pk_index column against pk_index_packed. The gap is the page splits.