UUID vs BIGINT Primary Key in Postgres: Which Wins?

UUID vs BIGINT primary key in Postgres: a storage-locality decision

Short answer: BIGINT primary keys pack tightly and stay cache-hot because inserts always land on the rightmost B-tree leaf. UUIDv4 keys scatter across the index, cause 50/50 page splits, and bloat WAL. UUIDv7 (native in PostgreSQL 18) fixes most of that by putting a timestamp in the high bits — you get near-bigint write locality at the cost of a wider key. The right choice depends on whether you need DB-generated IDs, cross-service ID generation, or unguessable public identifiers. Below is the mechanism, the benchmark, and the migration path.

The argument you're actually having

The ticket says "create the orders table." Someone opens a PR with BIGSERIAL. Someone else comments that three services will be inserting orders and they'd rather generate IDs client-side, so make it uuid. A third person, who read a blog post last week, says use UUIDv7 and everyone stops typing for a moment because nobody is quite sure whether Postgres can even generate one.

I've sat in that meeting more times than I can count, and the framing is almost always wrong. People argue about it like a taste question — tabs versus spaces. It isn't. Your primary key type decides where in the B-tree each new row lands, and that decides how much of the index stays resident in shared_buffers, how densely leaf pages pack, and how much WAL each insert drags behind it. Those are physical consequences you can measure, and they're the same on your laptop as on a 96-core box.

There's a three-minute version of this on video if you want the shape of the argument before a meeting. This page goes further: the exact SQL, the pgbench harness with the postgresql.conf we used, the PostgreSQL 18 changes that make half the advice on the internet out of date, and the online migration playbook for teams already carrying a UUIDv4 key in production.

UUID vs BIGINT Primary Key in Postgres: Which Wins?

Why the B-tree cares where your keys land

A Postgres B-tree stores index entries sorted by key across leaf pages of 8 kB each. Insert a key and nbtree descends to the leaf that should hold it, pins the page, and writes the entry. If the page is full, it splits.

Now hold two workloads side by side.

Ascending keys stay cache-hot

Every insert sorts after every existing key, so every insert lands on the same page: the rightmost leaf. One page, pinned in shared buffers, touched thousands of times before it fills. nbtree specifically detects this pattern — _bt_findsplitloc() in nbtinsert.c checks whether the insert point is at the right edge of the index — and doesn't split that page down the middle. It packs the left page and starts a fresh right page, because it knows the left page will never receive another insert. Leaf density stays close to the 90% default fillfactor. The working set for your index writes is a handful of pages, no matter how big the index gets.

Random keys cause B-tree page splits on every insert

Every insert sorts to an arbitrary position, so every insert lands on a different leaf. That leaf almost certainly isn't in shared buffers once the index outgrows cache, so you get a read before every write. When a page fills, nbtree has no reason to believe the insert pattern is one-directional, so it splits roughly 50/50. Both halves are now half empty, and both will slowly fill from random directions. Average leaf density settles well below fillfactor.

That mechanism is durable — it's how nbtree works today and how it worked five years ago, and it isn't workload-dependent. Everything below is either arithmetic on top of that mechanism or a measurement, and I'll label which is which.

The byte arithmetic nobody does before choosing

Per index entry, a B-tree costs you:

A bigint is 8 bytes, already aligned: 20 bytes per entry. A uuid is 16 bytes, already aligned: 28 bytes per entry. That's a 1.4x floor before a single page split has happened, before fragmentation, before anything workload-specific.

The floor is the least of it, because the primary key doesn't stay in one index. It's copied into every foreign key column in every child table, and into the supporting index on each of those FK columns, and into any secondary index that includes the key. A uuid PK on orders with four child tables means four extra uuid columns at 16 bytes per row and four more indexes at 28 bytes per entry.

Here's what our harness actually measured at 10M rows on the seed table, one column, one index each (method and config below):

| Key type | pg_relation_size of PK index | avg_leaf_density | |---|---|---| | bigint identity | 214 MB | 89.7% | | uuidv7() | 302 MB | 87.2% | | gen_random_uuid() | 397 MB | 65.4% |

The v7 index is 1.41x the bigint one — exactly the entry-width ratio. The v4 index is 1.85x, and the gap between 1.41 and 1.85 is entirely leaf density collapse from random splits. Key width is arithmetic; density is a measurement. Don't conflate them.

gen_random_uuid() performance: what random actually costs

gen_random_uuid() performance: what random actually costs

gen_random_uuid() has been built in since PostgreSQL 13 (it used to live in pgcrypto) and returns a version 4, essentially random UUID. It's genuinely attractive, and nobody choosing it is being careless. You can generate an ID in a mobile client that's been offline for two days, in a Kafka consumer, in three services that never talk to each other, with no coordination, no sequence, no round trip to the database to learn the row's identity. That's a real constraint and it solves it cleanly.

The costs are real too, and there are three.

Cache working set. With ascending keys, the write-hot part of the index is the rightmost path: a leaf, its parent, the root. With random keys, the write-hot part is the entire index. Once it exceeds shared_buffers, most inserts require reading a leaf page from OS cache or disk before they can write it.

Leaf density. 50/50 splits mean average density drifts toward 50–70%. That's the 397 MB above instead of ~300 MB. Note what does not rescue you here: B-tree deduplication (PG13) is useless on a unique PK because every value is distinct, and bottom-up index deletion (PG14) targets version churn from non-HOT updates, not fragmentation from random insert placement. Those two features are frequently cited as "modern Postgres handles this now." They don't handle this.

WAL amplification. full_page_writes is on by default, so the first modification of any page after a checkpoint writes a full 8 kB image into WAL. Sequential inserts touch a few distinct index pages per checkpoint cycle. Random inserts touch thousands. Same row count, dramatically more full-page images.

You can see all three on your own system:

-- WAL cost of a single statement
EXPLAIN (ANALYZE, BUFFERS, WAL)
INSERT INTO orders_v4 (customer_id, total)
SELECT g, g * 1.5 FROM generate_series(1, 10000) g;

-- WAL deltas across a workload window
SELECT wal_records, wal_fpi, wal_bytes, stats_reset FROM pg_stat_wal;

Take a pg_stat_wal snapshot, run five minutes of load, snapshot again, subtract. The ratio you care about is wal_fpi / wal_records.

UUIDv7 Postgres: the middle option, and its fine print

RFC 9562 (May 2024, obsoleting RFC 4122) standardises version 7. The first 48 bits are a big-endian Unix epoch timestamp in milliseconds, followed by version and variant bits and random data.

Why that matters in Postgres specifically: uuid_cmp() is a memcmp over the 16 bytes. Byte-wise comparison of a big-endian timestamp prefix means index order matches generation order. You get the rightmost-leaf insert path back, you get packed splits back, you get low WAL FPI back. What you keep paying is the 28-byte entry width, forever, everywhere the key is copied.

A bonus most people miss: because the column is time-correlated, uuid_minmax_ops BRIN indexes become useful on it. Check yours with \dAc brin uuid. A UUIDv4 column can't do that in any meaningful sense.

The fine print, and I'd rather you hear it here than in an incident review:

Sub-millisecond ordering is generator-dependent. RFC 9562 section 6.2 defines optional monotonicity methods (added clock precision, a monotonic random counter, dedicated counter bits). Two values generated in the same millisecond may not be ordered relative to each other unless your generator implements one of those. For index locality this is irrelevant. If you're planning to use the key as a strict event ordering, it matters a lot.

Clock skew across nodes interleaves values inside a narrow window. Fine for locality, not a distributed ordering primitive.

The timestamp is extractable. RFC 9562 section 8 calls out timestamp disclosure explicitly. If your order IDs are in URLs, anyone can read creation times out of them. For some systems that's nothing. For others it's an enumeration or competitive-intelligence problem, and it's the one legitimate reason I've seen teams stay on v4.

Generating uuidv7() in PostgreSQL 18 (and before)

The advice that "Postgres has no native UUIDv7" is stale. PostgreSQL 18 added uuidv7() and uuidv4(), plus uuid_extract_timestamp() and uuid_extract_version(). On 17 and earlier you generate in the application or with a user-defined function.

All three table variants, copy-paste — the bigserial vs uuid comparison in one place:

-- 1. bigint identity (PG10+)
CREATE TABLE orders_bigint (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total       numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- 2. UUIDv4 (PG13+)
CREATE TABLE orders_v4 (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id bigint NOT NULL,
  total       numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- 3. UUIDv7 (PG18+)
CREATE TABLE orders_v7 (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id bigint NOT NULL,
  total       numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

On pre-18, prefer a maintained application library over a hand-rolled SQL generator. I've reviewed several homegrown plpgsql UUIDv7 functions and roughly half had a bug: byte order reversed on the timestamp (which destroys the entire point), or the version and variant nibbles not set, producing values that aren't legal v7 and confuse every client library that parses them.

Whatever you use, verify it round-trips:

SELECT uuid_extract_version(id)            AS ver,
       uuid_extract_timestamp(id)          AS gen_time,
       created_at,
       created_at - uuid_extract_timestamp(id) AS skew
FROM orders_v7
ORDER BY random() LIMIT 10;

Version should be 7 on every row and skew should be milliseconds, not hours. If gen_time comes back in 1970 or 2262, your byte order is wrong.

The benchmark: harness, method, numbers

The benchmark: harness, method, numbers

This is one data point from our hardware. Print your own before you quote mine.

Configuration card

PostgreSQL 18.0, Debian 12
8 vCPU, 32 GB RAM, local NVMe SSD
shared_buffers = 8GB
max_wal_size = 16GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
full_page_writes = on   (default)
wal_compression = off
synchronous_commit = on
autovacuum = on (defaults)
Seed: 10,000,000 rows per table, loaded then VACUUM ANALYZE, then CHECKPOINT

Three identical tables from the DDL above. pgbench custom script, one per variant:

-- insert_v7.sql
\set cust random(1, 1000000)
INSERT INTO orders_v7 (customer_id, total) VALUES (:cust, 42.00);
pgbench -n -f insert_v7.sql -c 16 -j 4 -T 900 -P 60 bench

Warm run: seed, checkpoint, 5-minute discarded warmup, then 15 minutes measured. Cold run: restart Postgres and drop OS page cache before the measured window. Each variant run three times, median reported. Tables tested one at a time so WAL and checkpoint activity aren't shared.

Result: warm run, index larger than available shared_buffers

| Variant | TPS vs baseline | PK index size | avg_leaf_density | wal_fpi per 1k txn | |---|---|---|---|---| | bigint identity | baseline | 214 MB | 89.7% | low | | uuidv7() | about 12% lower | 302 MB | 87.2% | low | | gen_random_uuid() | about 40% lower | 397 MB | 65.4% | substantially higher |

Two honest caveats. First, the v4 gap widens as the index grows past what fits in cache, and narrows to almost nothing when the whole index is resident. If your table is 200k rows and will never be bigger, this entire article is academic for you — pick whichever key your team likes. Second, the knobs that move this most on your hardware are shared_buffers relative to total index size, checkpoint frequency (more checkpoints means more full-page images, punishing random writes harder), and storage random-read latency.

Measuring UUID index bloat on your own database

This is the audit I run before the architecture meeting, not after.

-- 1. index sizes, largest first
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 2. leaf density and fragmentation
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT index_size, leaf_pages, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('orders_v4_pkey');

-- 3. per-index cache hit ratio
SELECT indexrelname,
       idx_blks_hit,
       idx_blks_read,
       round(100.0 * idx_blks_hit
             / nullif(idx_blks_hit + idx_blks_read, 0), 2) AS hit_pct
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC
LIMIT 20;

-- 4. which index pages are actually resident
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname, count(*) AS buffers,
       pg_size_pretty(count(*) * 8192) AS cached
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relfilenode(c.oid)
WHERE c.relkind = 'i'
GROUP BY c.relname ORDER BY 2 DESC LIMIT 15;

Reading pgstatindex output: on a freshly reindexed B-tree with default fillfactor you should see avg_leaf_density around 90. Anything in the 60s on a unique key that only ever receives inserts is the random-split signature. leaf_fragmentation above about 20 means logically adjacent leaves are physically scattered, which hurts range scans and index-only scans more than point lookups.

Already on UUIDv4? Triage before you migrate

Don't start with a type change. Work the ladder.

1. Measure. Run pgstatindex on the PK and every FK index. If density is fine, your problem is elsewhere and a migration would be expensive theatre. 2. Reclaim. REINDEX INDEX CONCURRENTLY has been available since PG12 and rebuilds without blocking writes. It resets density to fillfactor. It doesn't stop the drift from recurring, so treat it as scheduled maintenance rather than a fix. Budget disk for both copies existing at once. 3. Tune. Lower fillfactor on heavily-updated random-key indexes so splits are less frequent, and lengthen checkpoint_timeout / raise max_wal_size to cut full-page-image volume. Both are cheap and reversible, but a longer checkpoint_timeout also means longer crash-recovery time — weigh that against the WAL savings before you push it out too far. 4. Then, and only then, consider changing the key.

We wrote up the density-to-REINDEX decision path in more detail in our index bloat and REINDEX guide. MyDBA's free health check does step 1 and 2 for you across the whole estate: per-index bloat estimates with the exact REINDEX INDEX CONCURRENTLY command to run, ranked by reclaimable bytes.

Migrating a primary key without a maintenance window

The naive path is ALTER TABLE ... ALTER COLUMN id TYPE .... That rewrites the entire table, rebuilds every dependent index, and holds ACCESS EXCLUSIVE for the duration. On a 400 GB table that's a multi-hour outage.

The online pattern

1. Add the new column nullable, no default: ALTER TABLE orders ADD COLUMN id_new bigint; 2. Add a BEFORE INSERT trigger (or application dual-write) so new rows populate both. 3. Backfill in batches with a throttle. Small batches, commit each, pg_sleep between them, watch replication lag and autovacuum. 4. CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_key ON orders (id_new); 5. Set NOT NULL via a validated CHECK then promote, to avoid a full-table ACCESS EXCLUSIVE scan. 6. Swap the primary key constraint using the existing index (ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX ...). 7. Repoint foreign keys one child table at a time: add the new column, backfill, ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT. Don't try to do this across five child tables in one transaction. 8. Drop the old column much later, after a full rollback window has passed.

Disk headroom. Any path that rewrites needs roughly 2x the table plus index footprint free before you start, and the concurrent index builds need their own space on top. I've watched a "safe" online migration take down a cluster because nobody checked free space on the tablespace. Check it, then check the WAL volume too.

The pattern I actually recommend most often

Dual key. A bigint identity column as the real primary key, used for joins and every foreign key in the schema, and a uuid (v7) public_id column with its own unique index, used in URLs, APIs and cross-service messages.

CREATE TABLE orders (
    id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    public_id   uuid NOT NULL DEFAULT uuidv7() UNIQUE,
    customer_id bigint NOT NULL,
    total       numeric(12,2) NOT NULL
);

You pay for one extra 16-byte column and one extra index on the parent table. What you get: the 16-byte width never propagates into child tables or their FK indexes, joins stay on 8-byte keys, and external systems still get a globally unique identifier they can generate or reference without touching your sequence.

This is over-engineering for a single-service app with three tables. It's the right call for anything with a wide schema, external API consumers, or a plausible future where a service gets extracted.

Recommendation matrix

| Situation | Choice | Write locality | Index size | Cache behaviour | Ordering | Coordination needed | |---|---|---|---|---|---|---| | Single node, DB-generated IDs | bigint identity | Rightmost leaf | Smallest (20 B/entry) | Tiny hot set | Strict | Sequence | | Multi-service or offline ID generation | UUIDv7 | Rightmost region | 1.4x bigint | Small hot set | Approximate, ms-level | None | | IDs must be unguessable and un-timeable | UUIDv4 | Scattered | 1.4x width, worse density | Whole index | None | None | | Merging datasets, multi-region active-active | UUIDv7 with per-node monotonic counter | Rightmost region, interleaved | 1.4x bigint | Small hot set | Per-node monotonic | Clock sync helps, not required | | Wide schema with external API | bigint PK + uuidv7 public id | Rightmost leaf | bigint everywhere + one extra index | Tiny hot set | Strict internally | Sequence |

If privacy pushes you to v4, consider keeping a bigint internal PK and exposing a v4 as a separate public column. You get unguessable external identifiers without paying random-insert costs on every FK index in the schema.

What to monitor after you decide

The decision isn't the end of it. Four things belong on a dashboard:

And keep disk headroom alerting honest: if you can't fit a second copy of your largest index, you can't run REINDEX CONCURRENTLY, which means your only remediation for bloat is an outage. That's the constraint that turns a slow index into an incident, and it's the one people notice last.