UUID vs BIGINT Primary Keys: Beyond Index Bloat

Most discussions around UUID vs BIGINT primary key postgres fixate on storage size—16 bytes versus 8. But the real performance penalty of random UUIDv4 keys remains hidden until your tables grow large and you start partitioning. Random keys silently defeat partition pruning and cripple autovacuum efficiency, compounding damage long after the initial index bloat sets in.

The Real Cost of Random Keys

A quick comparison of BIGINT (8 bytes), UUIDv7 (16 bytes, time-ordered), and UUIDv4 (16 bytes, fully random) reveals a stark performance gap that goes far beyond disk space.

• BIGINT delivers the fastest insert performance. New entries always go to the rightmost leaf page of the B-tree, keeping indexes dense and cache-friendly.
• UUIDv7 embeds a millisecond timestamp at the front. This gives you distributed, globally unique IDs with near-sequential write patterns—a practical compromise.
• UUIDv4 is entirely random. It scatters writes across every B-tree page, inflates index sizes by 40–100% due to persistent under-full pages, and causes insert throughput to drop 35–50% once the index outgrows shared_buffers.

If you need public-facing unguessable tokens, store a UUIDv4 in a separate, non-primary-key column. Let your clustering and partitioning keys stay sequential.

B‑tree Page Splits and Index Bloat

PostgreSQL uses B-tree indexes for primary keys. With a monotonically increasing key like BIGINT, new entries always land on the rightmost page. When that page fills, it splits, and writes continue on a fresh page. Older pages remain packed near 100% density.

UUIDv4 breaks this pattern. Every insert picks a random leaf page. If that page is full—and it soon will be—PostgreSQL performs a 50/50 page split, allocating a new page and moving half the existing keys. This creates two destructive outcomes:

1. Massive index bloat – Average page fullness drops to 50–70%. You pay for a 16-byte key and an index that is half empty, doubling your storage and cache footprint.
2. Random I/O – Instead of writing to one hot page, every insert fetches, modifies, and writes back a random index page from disk.

You can measure this with pgstattuple. On a 10-million-row test, results often look like this:

• users_bigint_pkey: 214 MB, near-zero leaf fragmentation, 1–2% empty space.
• users_uuidv4_pkey: 412 MB, 43% leaf fragmentation, over 38% empty space.

That empty space is not free. It eats buffer cache pages, pushing your working set to disk sooner. Once the index outgrows RAM, the cost becomes a wall of random I/O and CPU-heavy page splits.

UUID Partition Pruning in PostgreSQL

UUID partition pruning postgres is a trap. For time-based partitions to work efficiently, the query planner must prove a filter value can't exist in a given partition.

With BIGINT, an index on (id, created_at) or a similar composite key creates a narrow, predictable ID range within each partition. The optimizer can prune partitions cleanly. UUIDv4 destroys this correlation. Random keys provide no link to time, so the planner cannot exclude any partition based on a primary key range. Queries that should scan one partition end up touching many or all of them, multiplying overhead as partitions grow.

Vacuum Locality and the Visibility Map

PostgreSQL's visibility map tracks heap pages containing only tuples visible to all transactions. Autovacuum skips these clean pages. The map works best when writes cluster on a few pages, letting older pages become read-only and fully visible.

Sequential keys concentrate inserts at the table's tail. Older pages quickly freeze and get marked visible, making autovacuum cheap. Random keys scatter writes across the entire table. Every page stays partially dirty, the visibility map remains sparse, and autovacuum scans a much larger portion of the table with each pass. When combined with index bloat and failed partition pruning, database maintenance becomes a constant, resource-intensive battle.

PostgreSQL Primary Key Performance Strategy

A good postgres primary key performance strategy isn't just about speed; it's about long-term, scalable design.

1. Use BIGINT or bigserial for internal, high-traffic tables where you control key generation. It is the fastest and cleanest path.
2. If you need UUIDs, use UUIDv7 exclusively. Its time-ordered prefix gives you sequential UUID vs random UUID postgres benefits without giving up distributed generation.
3. Isolate public-facing tokens. Store random strings or UUIDv4 in a separate column with a unique constraint, not as your primary or partitioning key.
4. Monitor for index bloat. Periodically run pgstattuple. If a heavily written index shows over 25% empty space, it's a warning. A REINDEX CONCURRENTLY can temporarily help, but with UUIDv4, the bloat will return.

Picking the Right Key from Day One

The postgres partitioning primary key strategy you choose locks in your schema's long-term health. UUIDv4 looks harmless on a small table, but its damage compounds silently. By the time you notice the pain from slow inserts, bloated indexes, and multi-partition scans, the fix is a grueling migration. Whether you're building a SaaS app or a high-throughput event pipeline, starting with a BIGINT or UUIDv7 primary key avoids this entire class of performance failure.