I lost this argument in a schema review about four years ago. The team went with uuid DEFAULT gen_random_uuid() on a table that ended up holding 400M rows, and I got to spend the following eighteen months explaining why the primary key index was bigger than the heap and why checkpoints hurt. So I have opinions.
π Read the full guide: UUID vs BIGINT Primary Key in Postgres: Which Wins?
The column-width debate (8 bytes vs 16) is the least interesting part of this. What actually costs you is where in the B-tree each insert lands.
(If you want the 4-minute whiteboard version, the companion video covers the same mechanism with fewer words. One correction to it: it says Postgres has no native UUIDv7 generator. That was true when it was recorded. PostgreSQL 18 shipped uuidv7(), so that gap is closed.)
TL;DR
| Key type | Bytes on disk | Index entry (approx) | Insert locality | Native generation | Use when |
|---|---|---|---|---|---|
bigint identity |
8 | ~20 B | Rightmost leaf, always hot | GENERATED BY DEFAULT AS IDENTITY |
You can coordinate a sequence |
| UUIDv7 | 16 | ~28 B | Near-sequential (48-bit ms prefix) |
uuidv7() (PG18+), SQL function before |
Clients/shards generate IDs |
| UUIDv4 | 16 | ~28 B | Uniformly random across whole index |
gen_random_uuid() (PG13+ core) |
Public tokens, non-PK columns |
| Snowflake-style 64-bit | 8 | ~20 B | Near-sequential | App-side (timestamp + node + counter) | Distributed writes, bigint width |
UUID as varchar(36)
|
37 | ~48 B+ | Random and collation-aware compare | n/a | Never |
Verdict: use bigint identity if a single sequence can serve all writers, UUIDv7 if it can't. Don't put UUIDv4 on the primary key of a hot write path.
The argument that starts every schema review
New orders table. Everyone agrees it'll pass a hundred million rows in two years. Half the room wants UUIDs because three services insert into it and nobody wants a round trip to a sequence. Somebody read a post about UUIDv7 last week and now wants that. Somebody else says "it's only 8 extra bytes, who cares."
That last person is the one to argue with, because they've framed it as a storage question. It's an I/O-pattern question.
What actually happens inside the B-tree
Ascending keys all target the same page:
Sequential inserts (bigint):
[ leaf 1 ][ leaf 2 ][ leaf 3 ][ leaf N ] <- always here, always cached
full full full filling...
Three or four pages of that index are hot. Everything else is cold and nobody cares.
With a UUIDv4 key, there's no rightmost anything. Each new 16-byte value lands at an essentially random point in the key space:
Random inserts (UUIDv4):
[ leaf a ][ leaf b ][ leaf c ] ... [ leaf z ]
~55% ~48% ~52% ~50% <- every page, forever
^ insert here ^ insert here ^ insert here
Two things follow from that:
Split behaviour. Default B-tree fillfactor is 90. When Postgres splits the rightmost leaf page, it recognises the ascending pattern and packs the left page to fillfactor instead of splitting evenly. You get densely packed leaves. Splits in the middle of the key space aim for roughly 50/50, so an index fed uniformly random keys settles at a much lower average leaf density and never recovers it.
The uniqueness probe. Inserting into a unique index means reading the target leaf page first to check for a conflict. With a sequence, that read is a buffer hit on the same page you touched a microsecond ago. With random UUIDs, it's a random read from an index whose working set is the entire index, not three pages.
Then there's WAL. With full_page_writes on (the default), the first modification of any page after a checkpoint writes a full 8 kB image into WAL. Sequential inserts dirty a handful of distinct index pages per checkpoint cycle. Random inserts dirty thousands. Same row count, wildly different WAL volume, and that shows up in replication lag and archive costs before it shows up in TPS.
Byte math before any bloat
A B-tree entry costs the 8-byte IndexTupleData header, plus the MAXALIGNed key, plus a 4-byte line pointer in the page's item array:
bigint : 8 (header) + 8 (key) + 4 (line ptr) = 20 bytes
uuid : 8 (header) + 16 (key) + 4 (line ptr) = 28 bytes
About 40% wider, before fragmentation. Then multiply: every foreign key column referencing that PK, every FK index, and every secondary index that carries the PK as its heap pointer payload.
And the anti-pattern: storing the UUID as varchar(36) costs 37 bytes (36 characters plus a 1-byte varlena header), more than double the native type, and it swaps memcmp for collation-aware text comparison on every single comparison. I have seen this in production more than once.
UUIDv7, decoded
RFC 9562 (May 2024, obsoletes RFC 4122) lays v7 out as:
48 bits big-endian Unix timestamp in milliseconds
4 bits version = 0111
12 bits rand_a
2 bits variant = 10
62 bits rand_b
Here's the whole trick: Postgres compares uuid values as raw 16-byte binary strings (uuid_cmp is a memcmp over 16 bytes). Byte order is sort order. Put a timestamp in the leading bytes and B-tree placement becomes time-ordered for free.
RFC 9562 Β§6.2 also describes optional monotonicity methods, including stuffing sub-millisecond precision into rand_a so IDs minted in the same millisecond still sort in generation order.
PG17 added extractors:
SELECT uuid_extract_version(id), uuid_extract_timestamp(id) FROM orders LIMIT 1;
uuid_extract_version | uuid_extract_timestamp
----------------------+-------------------------------
7 | 2026-08-04 09:11:52.417+00
Generating UUIDv7
PostgreSQL 18:
id uuid PRIMARY KEY DEFAULT uuidv7()
uuidv7() takes an optional interval that shifts the embedded timestamp, which is handy for backfills. PG18 also added a spelled-out uuidv4().
PG13 through 17 (gen_random_uuid() has been core since 13, no pgcrypto needed):
CREATE OR REPLACE FUNCTION uuid_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;
Caveat: this implements no monotonicity counter, so two IDs generated in the same millisecond sort randomly relative to each other. At millisecond granularity that costs you nothing in B-tree terms. What does hurt is a backwards clock jump across nodes, which pushes inserts back into older pages.
Generating in the application is fine, and often better: the whole reason you wanted UUIDs was to avoid a round trip before you know the ID. Just use a real v7 library, not Math.random() dressed up as a UUID.
The benchmark, and the honest disclaimer
One machine. One config. My numbers are not your numbers.
Rig: PostgreSQL 18.0, 8 vCPU / 32 GB VM, NVMe, shared_buffers = 8GB, max_wal_size = 16GB, checkpoint_timeout = 15min, checkpoint_completion_target = 0.9, full_page_writes = on, synchronous_commit = on, wal_compression = off.
CREATE TABLE o_bigint (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
amount_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE o_uuid4 (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
customer_id bigint NOT NULL,
amount_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE o_uuid7 (
id uuid PRIMARY KEY DEFAULT uuidv7(),
customer_id bigint NOT NULL,
amount_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
Each table warmed to 10M rows first, so the indexes are past the "fits trivially in cache" stage.
ins_bigint.sql:
\set cid random(1, 5000000)
\set amt random(100, 500000)
INSERT INTO o_bigint (customer_id, amount_cents) VALUES (:cid, :amt);
Same file for the other two with the table name swapped. Then:
pgbench -n -c 16 -j 4 -T 600 -f ins_bigint.sql bench
Please re-run this yourself. The result depends almost entirely on the ratio between your index size and shared_buffers.
Results
| Variant | TPS vs bigint | PK index size @ ~25M rows | avg_leaf_density | leaf_fragmentation | WAL for the run |
|---|---|---|---|---|---|
| bigint identity | baseline | 561 MB | 90.4 | 0.4 | 12.4 GB |
| UUIDv7 | β12% | 728 MB | 84.9 | 3.1 | 15.1 GB |
| UUIDv4 | β40% | 1622 MB | 55.2 | 21.7 | 41.8 GB |
Naive prediction for v4 was 1.4 (entry width) Γ 1.64 (density) β 2.3x. Measured came in near 2.9x. The gap is fragmentation and half-empty pages left behind by splits that nothing refills.
The knobs that move these most: index-size-to-shared_buffers ratio (the v4 penalty largely disappears when the whole index is resident, and gets much worse when it isn't), and checkpoint frequency, because full-page images dominate that WAL column.
Measuring your own bloat right now
SELECT indexrelid::regclass AS idx,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indisprimary
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('orders_pkey');
-- version | tree_level | index_size | ... | avg_leaf_density | leaf_fragmentation
-- 4 | 2 | 1700855808 | ... | 55.21 | 21.74
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 10;
Anything under ~70 avg_leaf_density on a unique PK is telling you the key is random. (If you'd rather have this checked for you, MyDBA's free health check reports index density and hit ratios without you writing the queries.)
REINDEX INDEX CONCURRENTLY orders_pkey;
Available since PG12, doesn't block writes, needs room for both copies and does two passes. It resets density. It does nothing to stop random inserts from re-fragmenting the same index next week. It's a mop, not a fix for the leak.
When I still pick bigint
- One writer, or writers that can share a sequence:
bigintidentity. Nothing beats it. - Sharded, offline-capable, or client-generated IDs: UUIDv7.
- IDs exposed publicly where you must not leak counts or timing:
bigintPK plus a separateuuidpublic token, or UUIDv4 in a secondary column. Don't make the random value the clustering key. - The option nobody brings up: a Snowflake-style 64-bit ID (41-bit ms timestamp, 10-bit node, 12-bit counter). Uncoordinated generation, time-ordered, and still 8 bytes. Instagram did roughly this on top of Postgres years ago. If you control the app, this is often the right answer.
Also: bigint gives you 9,223,372,036,854,775,807 values. integer runs out at 2,147,483,647, which remains one of the most common self-inflicted outages I get called about.
Gotchas the benchmarks hide
UUIDv7 leaks creation time. RFC 9562 Β§12 says so explicitly. Anyone holding an ID reads the row's creation timestamp to the millisecond. For an order ID that's usually fine; for a signup token it isn't.
BRIN. A BRIN index on a UUIDv7 column can work because value correlates with physical order. On UUIDv4 it's worthless. Same for time-range partition pruning tricks.
Deduplication won't save you. B-tree deduplication (PG13) compresses duplicate keys and is therefore irrelevant to a unique PK.
Bottom-up index deletion won't either. It targets bloat from non-HOT update version churn (PG14), not space lost to random-insert page splits.
Clock skew. Generating v7 client-side across nodes with unsynchronized clocks can yield IDs that don't perfectly reflect generation order across nodes, though they'll still cluster into roughly the right time window.
Migration. You can't repoint a live PK's type in place without pain. Add the new column, backfill in batches with a WHERE new_id IS NULL loop, add a unique index concurrently, then swap the constraint and the FKs in one short transaction.
What I'd actually write
-- coordinated writers
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
amount_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- ALTER SEQUENCE orders_id_seq CACHE 32; -- cuts contention, creates gaps
-- uncoordinated / client-generated (PG18+)
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
public_token uuid NOT NULL DEFAULT uuidv4(), -- if IDs are user-visible
customer_id bigint NOT NULL REFERENCES customers(id),
amount_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
Bigint if you can, UUIDv7 if you can't, and UUIDv4 stays out of the primary key.
Tags: postgres, database, performance, sql ## Wrapping Up
None of this is really about 8 bytes versus 16. It's about whether your inserts land on the same few hot pages or scatter randomly across an index that's grown past your cache. Get that part right and the storage overhead of UUIDs barely matters; get it wrong and you're paying for it in WAL, checkpoint I/O, and replication lag long before disk space becomes the complaint anyone notices first. Pick bigint when a sequence can serve every writer, reach for UUIDv7 when it can't, and keep UUIDv4 out of anything that has to absorb a high insert rate as a clustering key.
If you're not sure which camp your table falls into, don't guess β pull pgstatindex on your busiest primary key and look at avg_leaf_density before you argue about it in the next schema review. MyDBA runs that kind of index-health check automatically and will flag a fragmenting PK before it shows up as a checkpoint spike in your dashboards.
pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool β https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=uuid-vs-bigint-primary-key-postgres
If this saved you a schema-review argument, the free tier at mydba.dev is worth five minutes β point it at a replica and see what your indexes actually look like.
Top comments (0)