UUID vs BigInt Primary Keys in Postgres
If you're choosing between bigint and uuid for a primary key, the short answer is: bigint (via bigserial or generated always as identity) wins on raw performance and index size, but uuid wins on distributed generation and merge-friendliness. The real decision hinges on how your data is inserted and whether you're on Postgres 18, where uuidv7() closes most of the performance gap.
📖 Read the full guide: UUID vs BIGINT Primary Key in Postgres: Which Wins?
▶ Watch the video walkthrough: UUID vs BIGINT vs UUIDv7: Which Primary Key Won't Wreck Your Index Cache
https://www.youtube.com/watch?v=UMD19GIPDU0
Here's what actually happens under the hood, and how to tell if your existing UUID keys are costing you.
Why the primary key type matters more than it seems
A primary key isn't just a column — it's a B-tree index, and every foreign key referencing it copies that type into every child table. Get this choice wrong on a large table and the pain shows up years later as bloated indexes, slow writes, and painful migrations.
BigSerial vs UUID Performance
bigint values from a sequence are monotonically increasing. Every new row's key is larger than the last one, so inserts append to the rightmost edge of the B-tree. That's cheap: no page splits in the middle of the index, high cache locality, and small index entries (8 bytes vs 16 for UUID).
Random UUIDs (the classic uuidv4) break this completely. Each insert lands on a random leaf page across the entire index, because there's no ordering. On a small table you won't notice. On a table with tens of millions of rows, this is where things go wrong:
- Cache misses on every insert. The B-tree is bigger than RAM, so writes constantly pull cold pages into the buffer cache.
- More WAL amplification. Full-page writes get triggered more often because you're touching pages all over the index instead of one hot page at the tail.
- Slower inserts under load. Benchmarks consistently show 2-4x higher insert latency for random UUID PKs versus bigint at scale, and the gap widens as the table grows.
Postgres UUID Index Bloat: The B-Tree Page Split Problem
Random inserts don't just slow things down — they bloat the index. When a new UUID lands in the middle of a full B-tree page, Postgres has to split that page, and the resulting two pages are each roughly half full. Repeat this millions of times and you end up with an index that's much bigger than the data actually requires.
You can check this yourself:
SELECT relname, avg_leaf_density
FROM pgstattuple('your_table_pkey_idx');
or with the lighter pgstattuple_approx() if the table is huge and you don't want a full scan.
If avg_leaf_density is under 60, you're carrying roughly a third more index than you need to, and every insert into it is landing on a page that probably isn't in memory.
UUIDv7 in Postgres: A Real Fix
Postgres 18 ships native uuidv7(), and it changes the calculus. UUIDv7 embeds a millisecond timestamp in the leading bits, so values generated close together sort close together — just like a bigint sequence. You get:
- Sequential-ish inserts, so far fewer page splits
- The distributed-generation benefits of UUIDs (no coordination needed across services)
- Roughly the same insert profile as
bigint, at double the storage cost per row
If you're on Postgres 18 (or using an extension like pg_uuidv7 on older versions), UUIDv7 is the default answer for new schemas that need UUID-shaped keys — for API exposure, multi-region writes, or merging data from disconnected systems.
When bigint still wins
If you don't need globally unique IDs generated outside the database — no offline clients, no multi-primary replication, no external ID exposure requirements — bigint is simpler, smaller, and faster. There's no reason to pay the storage tax for UUIDs you don't need.
When UUID (v7) is worth it
- Multiple services or regions generate IDs before a row ever hits Postgres
- You're merging or importing datasets and can't risk collisions
- IDs are exposed publicly and you don't want sequential integers leaking row counts or ordering
Should You Migrate an Existing UUID Table?
This is the expensive question. Migrating a primary key on a large, live table means rewriting every row, every index, and every foreign key reference — not a small job.
Before committing to it, quantify the actual damage. Run pgstattuple on the primary key index and any foreign key indexes pointing at it. If leaf density is healthy (above 80-85%) and insert latency isn't showing up in your slow query logs, you probably don't have a problem worth solving. If it's low, try REINDEX INDEX CONCURRENTLY first — it rebuilds the index compactly without an exclusive lock and costs you nothing but disk I/O during the rebuild.
If density comes back healthy afterward and stays that way, you've bought yourself time without touching the primary key type at all. If it degrades again within weeks, that's your signal the random-UUID insert pattern is the actual bottleneck, and a UUIDv7 migration — planned as a proper maintenance window, not a hotfix — is worth scheduling. Tools like MyDBA can track leaf density and bloat trends over time so you're not reindexing blind every time someone asks if the database "feels slow."
Reindex it concurrently, see what comes back, and then decide whether the migration is worth your week.