Question
For a Postgres table taking 5k inserts/sec, is a random UUIDv4 primary key actually a problem in practice, or is that concern outdated? Answer in under 150 words.
Both say UUIDv4’s random B-tree insertion pattern can still hurt sustained 5k/s workloads once the index exceeds memory, and that UUIDv7 or an ordered numeric key is the practical alternative.
Claude presents the impact as likely visible “within days”; Codex says it can range from negligible to material. This matters because 5k/s alone does not determine the outcome—row size, index count, RAM, storage, fillfactor, checkpoints, and contention do. Settle it with a production-shaped UUIDv4-vs-v7 load test measuring p95 insert latency, WAL bytes, index size, and buffer reads.
Claude says the issue “hits every secondary index and any FK columns.” Codex limits the claim to the UUID PK index. Claude overgeneralizes: only indexes whose inserted key distribution is random UUID-like have this exact locality problem; unrelated secondary keys may not. Inspect the actual index definitions and insert distributions.
Claude usefully names the “bigint surrogate PK + unique UUID” legacy option and notes the small/in-memory-table exception. Both hold up.
Codex usefully gives concrete Postgres observability targets. pg_stat_user_indexes alone will not show bloat or WAL volume, but alongside size/WAL/latency measurements it is relevant.
Neither answer is factually wrong overall. The unresolved decision is whether your actual workload’s measured write cost justifies an ID migration; benchmark UUIDv4 against UUIDv7 under production-like conditions.
Full answers
Real, not outdated — for a b-tree index. Random UUIDv4 scatters inserts across the whole index range instead of appending at the right edge, so at 5k/sec you get:
- Poor buffer cache locality — each insert likely touches a page not in shared_buffers, forcing random reads from disk/SSD.
- Index bloat — pages split unevenly and never refill, WAL volume grows accordingly.
- Same problem hits every secondary index and any FK columns referencing the UUID.
At 5k/sec sustained this is not theoretical; it shows up as rising write latency and larger-than-expected index sizes within days.
The actual fix isn't "avoid UUIDs," it's "avoid random ones": use UUIDv7 (or ULID) for time-ordered inserts — monotonic prefix, same 128-bit uniqueness, drop-in for UUIDv4. Postgres 18 has native uuidv7(); earlier versions need an extension or app-side generation. If you're stuck on UUIDv4 for legacy reasons, keep it but consider a bigint surrogate key as PK for insert-heavy tables with UUID as a unique secondary column.
Assumption: table has one or more indexes beyond the PK, and is large enough (>RAM) that cache locality actually matters — on a tiny table that fits in shared_buffers this concern mostly evaporates.
At 5k inserts/sec, UUIDv4 primary keys can be a real Postgres performance cost—not an obsolete concern—because random values scatter B-tree inserts across leaf pages, increasing page splits, cache misses, WAL, and index bloat versus ordered keys.
Assumptions: a normal B-tree PK index, sustained write load, and a table large enough that the PK index no longer fits comfortably in memory. Under those conditions, benchmark your actual row width, indexes, storage, and checkpoint settings; the impact may range from negligible to material.
UUIDv4 is often still acceptable if the workload is modest, storage is fast, and you need decentralized ID generation. But at sustained 5k/s, prefer UUIDv7 (time-ordered), ULID with careful encoding, or bigint/identity when global client-side generation is unnecessary.
What resolves it: compare pg_stat_user_indexes, index size/bloat, WAL volume, and p95 insert latency for UUIDv4 vs UUIDv7 on production-like hardware.