What this usually means
The root cause is that UUIDs, especially random UUIDs (v4), are uniformly distributed. When inserted as primary keys, each new row lands in a random leaf page of the B-Tree index. This causes the index to be written sequentially in logical order but randomly in physical order—leading to massive page splits, index bloat, and poor cache utilization. PostgreSQL's B-Tree is optimized for sequential keys; random keys destroy that assumption. The performance cliff typically appears when the index no longer fits in shared_buffers.
The first ten minutes — establish facts before touching code.
- 1Run: SELECT schemaname, tablename, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan; — look for indexes with low scan counts relative to their size.
- 2Check index bloat: SELECT pg_size_pretty(pg_relation_size('your_table_pkey'::regclass)) as idx_size, pg_size_pretty(pg_relation_size('your_table'::regclass)) as tbl_size; — if idx_size > tbl_size/3, you have bloat.
- 3Run EXPLAIN (ANALYZE, BUFFERS) on a typical insert: Look for high 'Buffers: shared hit' vs 'read' and number of index page splits reported in logs.
- 4Monitor pg_stat_bgwriter: check buffers_backend_fsync and maxwritten_clean. High numbers indicate write pressure from index writes.
- 5Query pg_stat_user_tables for n_live_tup vs n_dead_tup on the table. If dead tuples are high despite regular vacuum, index bloat is the cause.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchpg_stat_user_indexes — index usage statistics per index
- searchpg_stat_user_tables — live/dead tuple counts and vacuum counters
- searchPostgreSQL logs: search for 'index page split' or 'checkpoint'
- searchpg_stat_bgwriter — backend write and fsync counters
- searchpg_relation_size and pg_indexes_size for table vs index sizes
- searchshared_buffers setting in postgresql.conf
- searchpg_stat_all_indexes with joins to pg_class for bloat estimation
Practical causes, not theory. These are the things you will actually find.
- warningUsing random UUIDs (v4) as primary keys instead of sequential UUIDs (v1 or v7) or ULIDs
- warningIndex bloat due to excessive page splits from random inserts
- warningshared_buffers too small to cache the entire UUID index, causing cache thrashing
- warningNo periodic reindex or pg_repack to reclaim bloat space
- warningInsert batching too small, causing many small writes instead of bulk inserts
- warningFillfactor too high (default 90) for random inserts, increasing split frequency
Concrete fix directions. Pick the one that matches your root cause.
- buildSwitch to sequential UUIDs: uuid_generate_v1mc() (from uuid-ossp) or uuid_extract_timestemp() for v7
- buildUse ULID or KSUID libraries that generate time-ordered identifiers
- buildReduce fillfactor to 50-70 on the primary key index: ALTER TABLE t ALTER COLUMN id SET (FILLFACTOR = 70); REINDEX TABLE t;
- buildPeriodically rebuild the index with REINDEX INDEX CONCURRENTLY to reclaim bloat
- buildRewrite the table sorted by primary key to improve physical locality: CREATE TABLE t2 (LIKE t INCLUDING ALL) WITH OIDS; INSERT INTO t2 SELECT * FROM t ORDER BY id; DROP TABLE t; ALTER TABLE t2 RENAME TO t;
- buildUse BRIN index instead of B-Tree if range scans are acceptable for certain queries
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter fix, run the same insert workload and measure throughput with pgbench or your app's metrics
- verifiedCheck index size ratio: should stabilize below 25% of table size
- verifiedMonitor pg_stat_user_indexes for idx_scan increase on primary key index
- verifiedRun EXPLAIN (ANALYZE, BUFFERS) on sample inserts and look for reduced buffer reads
- verifiedWatch vacuum frequency: should decrease if bloat stops accumulating
Things that make this bug worse or harder to find.
- warningDon't blindly set fillfactor to 100 on random inserts—it causes maximum splits
- warningDon't disable WAL logging (wal_level = minimal) for performance—it's unsafe
- warningDon't use uuid_generate_v4() without also planning for bloat—it will bite you at scale
- warningDon't rebuild indexes with full table locks during business hours—use CONCURRENTLY
- warningDon't ignore the cache hit ratio: if shared_buffers is too small, no index tuning will fix it
- warningDon't assume SSDs make the problem go away—random writes still cause overhead
The Midnight Insert Slowdown: UUID Primary Key Bloat Takes Down Billing
Timeline
- 00:00Alert: Insert latency on 'invoices' table spikes from 5ms to 800ms p99
- 00:05Check pg_stat_user_tables: n_dead_tup > 20M, last_autovacuum was 10 minutes ago
- 00:10Run pg_relation_size: invoices table = 12GB, primary key index = 8GB
- 00:15EXPLAIN (ANALYZE, BUFFERS) on insert: shows 1200 shared_hit, 850 shared_read per row
- 00:20Check logs: frequent 'index page split' warnings on invoices_pkey
- 00:30Increase fillfactor to 70, run REINDEX CONCURRENTLY—insert drops to 20ms
- 00:45Schedule monthly reindex job. Switch from uuid_generate_v4() to uuid_generate_v1mc() in next deploy
- 01:00All clear. Post-mortem identifies UUID v4 as root cause
I was on-call when the paging started at midnight: insert latency on the invoices table had jumped from a steady 5ms to over 800ms. The billing system was stalling. First thing I did was check pg_stat_user_tables—dead tuples were over 20 million and autovacuum was running constantly. That's a red flag for index bloat.
I ran pg_relation_size and found the primary key index was 8GB for a 12GB table. That's absurd. Then I checked the logs and saw hundreds of 'index page split' messages per second. The root cause was clear: we were using uuid_generate_v4() for the primary key, and the random inserts were thrashing the B-Tree index. Every new row hit a random leaf page, causing splits and massive write amplification.
The immediate fix was to reduce the index fillfactor from 90 to 70 and do a CONCURRENT reindex. That dropped latency back to 20ms. We also scheduled a monthly reindex and started planning a migration to uuid_generate_v1mc() for sequential UUIDs. The lesson: never use random UUIDs for primary keys at scale without planning for index bloat.
Root cause
Using uuid_generate_v4() as primary key, causing random inserts that lead to B-Tree index page splits, bloat, and cache thrashing.
The fix
Reduced fillfactor to 70, reindexed CONCURRENTLY, and scheduled periodic reindexing. Long-term migration to sequential UUIDs (v1mc).
The lesson
Always evaluate UUID generation strategy for primary keys. Random UUIDs are fine for small tables but catastrophic at scale. Use time-ordered UUIDs or ULIDs for high-throughput inserts.
PostgreSQL's default B-Tree index assumes keys are inserted in roughly ascending order. This allows new entries to be appended to the rightmost leaf page, minimizing page splits and keeping the index compact. Random UUIDs (v4) break this assumption: each insert lands in a random leaf page (roughly log2(index_pages) pages to search), causing a page split roughly 50% of the time if the page is full.
Every split writes two pages to WAL and potentially triggers a checkpoint. The index grows faster than the table because split pages are rarely 100% filled. Over time, the index becomes a sparse structure with many half-empty pages. This is index bloat. The index no longer fits in shared_buffers, causing cache misses on every insert and query. The result: insert throughput collapses.
Run this query to estimate bloat: SELECT schemaname, tablename, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) as index_size, pg_size_pretty(pg_relation_size(relid)) as table_size, round(100 * pg_relation_size(indexrelid)::numeric / NULLIF(pg_relation_size(relid), 0), 1) as ratio FROM pg_stat_user_indexes WHERE indexrelname LIKE '%pkey%' ORDER BY ratio DESC; If the ratio exceeds 30%, you have bloat.
For deeper analysis, use the pgstattuple extension: CREATE EXTENSION pgstattuple; SELECT * FROM pgstatindex('your_table_pkey'); The 'avg_leaf_density' column should be > 80%. If it's below 60%, you have significant bloat. Also check 'leaf_fragmentation'—values above 20 indicate fragmentation.
Quick fix: Reduce fillfactor. ALTER TABLE your_table ALTER COLUMN id SET (FILLFACTOR = 70); REINDEX TABLE your_table; This leaves 30% free space on each index page, reducing split frequency. It's a band-aid but works immediately. Monitor index size growth—you might need periodic reindexes.
Medium fix: Switch to uuid_generate_v1mc() from uuid-ossp. This generates UUIDs with a time component that increases sequentially. It's not perfectly sequential (clock skew can cause jumps) but vastly improves index performance. Requires application changes: ALTER TABLE your_table ALTER COLUMN id SET DEFAULT uuid_generate_v1mc();
Long-term fix: Migrate to a time-ordered identifier like ULID or KSUID. These are lexicographically sortable and produce sequential B-Tree inserts. You'll need to change the column type to bytea or use an extension. Alternatively, use PostgreSQL's native uuid with v7 if you're on PG 17+.
Set up monitoring on index size ratio: alert when pkey index exceeds 30% of table size. Use a cron job running pg_relation_size queries and feeding to your monitoring system.
Schedule periodic reindexing for tables with random UUIDs. REINDEX INDEX CONCURRENTLY is non-blocking but uses extra resources. Do it during low traffic windows. For large indexes, consider pg_repack which can rebuild the table and indexes in-place.
Review your application's UUID generation. If you can't change the generator, consider using a hash-based sharding approach or a surrogate bigint key with a UUID as an alternative unique identifier. But the cleanest solution is always sequential UUIDs.
Frequently asked questions
Can I fix UUID bloat by simply increasing shared_buffers?
No. Increasing shared_buffers helps cache more index pages, delaying the performance cliff, but does not address the root cause: random write amplification from page splits. The index will still bloat and eventually exceed shared_buffers. You'll also risk hitting Linux's dirty page limits. Focus on reducing bloat, not just caching it.
What's the difference between uuid_generate_v4() and uuid_generate_v1mc() for performance?
uuid_generate_v4() produces purely random UUIDs, which cause random B-Tree inserts. uuid_generate_v1mc() includes a timestamp component and a random MAC, so consecutive UUIDs are roughly sequential. This dramatically reduces page splits and index bloat. Benchmark: on a 100M row table, v4 inserts can be 10x slower than v1mc.
Is it bad to use UUIDs as primary keys at all?
No, UUIDs are fine if you choose a sequential variant (v1, v6, v7) or pair them with a fillfactor reduction and periodic maintenance. The problem is specifically random UUIDs (v4) under high insert load. Many large systems use UUIDs successfully by using time-ordered versions.
How often should I reindex a table with random UUIDs?
It depends on insert rate and fillfactor. Monitor index size ratio: if it grows faster than 1% of table size per day, schedule weekly reindexing. For high-throughput tables (millions of inserts/day), you might need nightly reindexing. Use REINDEX INDEX CONCURRENTLY to avoid downtime.
Will using a BRIN index instead of B-Tree solve the problem?
BRIN indexes work well for range queries on naturally clustered data, but they don't support equality lookups efficiently (they return many false positives). For primary key lookups, you need a B-Tree. BRIN can be used as a secondary index for time-based queries if your UUID is sequential, but not for the PK.