The UUID Primary Key Problem That Shows Up at 200 Million Rows
Ninety four thousand block reads for one row, by its UUID primary key. I read that line three times, half convinced I had pasted the wrong output into my own terminal.
That query used to come back in under 20 milliseconds. At 200 million rows, it took almost four seconds. The only reason anyone noticed was that a client dashboard froze during a demo.
My honest standup answer was uncomfortable: the primary key was a UUID, and I did not think anyone had understood what that choice could cost later. That was not a complete diagnosis. It was not an indictment of every UUID primary key, either. Instead, it was the first useful lead.
A primary-key index can find a key efficiently while the surrounding access pattern has become expensive. At scale, data location, cache residency, and the way inserts spread through the index matter as much as having a primary-key predicate.
Key takeaways
- A primary-key index does not guarantee a consistently cheap path to the row.
- Random UUIDs trade locality for independently generated, globally unique identifiers.
- Timestamp-ordered UUIDs such as UUIDv7 are a design option, not a universal repair.
- Before changing a key strategy, inspect the actual plan, I/O evidence, index health, table layout, and workload.
Finding the UUID primary key is not the same as fetching the row
It is tempting to reduce the story to a simple rule: primary key means fast. Usually it is fast. However, an index lookup has at least two jobs. First, the database navigates the index to find the matching key. Then it reaches the row data, or enough row data to satisfy the query.
On a small or warm dataset, those pages may already be in memory. The lookup feels instantaneous. On a much larger table, cache residency can change. As a result, a plan that once touched a handful of warm pages may encounter a much colder, more scattered working set.
Random identifiers can contribute to that scatter. For example, a steadily increasing identifier tends to put new index entries near the active end of an index. By contrast, a random identifier distributes inserts across many locations. That is not automatically wrong. Still, the trade-off can become visible when the table, index, and workload outgrow the assumptions that were safe at launch.
Storage latency, concurrent queries, wider rows, cache churn, table maintenance, and a changed execution plan can all amplify the result. Therefore, the line showing 94, 000 block reads should begin an investigation. It should not end one.
UUID primary key trade-offs
UUIDs solve real problems. An application can generate identifiers without waiting for a central database sequence. They are useful when systems create records independently, and their values are less predictable when exposed in public URLs.
The important distinction is not simply UUID versus integer. It is also the order in which new values arrive. A random UUID and a time-ordered UUID are both UUIDs. However, they present a B-tree with very different insertion patterns.
| Choice | What it optimizes for | Trade-off to examine |
|---|---|---|
| Sequential numeric primary key | Simple, ordered inserts and compact internal joins | Use a separate public identifier if external predictability matters |
| Random UUID primary key | Independent generation and UUID semantics | Less insertion locality can matter as the table and write volume grow |
| Time-ordered UUID primary key | UUID semantics with a more ordered insertion pattern | Version support, generation approach, and workload still need testing |
None of these rows is a blanket recommendation. A random UUID key can be perfectly appropriate for a smaller table or a low-write workload. It can also suit a system where its operational advantages outweigh the performance cost. Conversely, a sequential surrogate key is not a performance amulet if queries fetch too much data or the system has other I/O problems.
How I would explain a UUID primary key incident
I would start with what we knew: this was a point lookup that had become slow, and the I/O evidence was unexpectedly large. Then I would separate observation from conclusion.
The UUID primary key was a plausible contributor because random insertion order can weaken locality. It was not enough to prove the cause. We still needed to know whether the plan was an index scan. In addition, we needed to know how many reads came from shared buffers versus storage, whether the query selected wide row data, and whether the plan had changed.
I would also ask whether index or table bloat was involved. Was a concurrent workload evicting useful pages? Was another index being used? Had recent data growth changed the working set? Those questions turn a dramatic number into a debugging path.
For PostgreSQL, the starting point is a representative plan with buffer information. Capture it safely outside the production incident:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE id = 'your-uuid-here';For MySQL, use its own plan tooling rather than copying PostgreSQL syntax:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE id = 'your-uuid-here';These commands do not reproduce the reported output. Instead, they give you evidence to compare under your own database version, schema, and workload. Be careful running analysis on a heavily loaded production system, especially if the query is not known to be safe and selective.
Better choices for new tables
If a new, write-heavy table is expected to grow large, make identifier ordering an explicit design discussion.
The conservative option is a sequential numeric primary key with a separate UUID for public or cross-system use. This keeps the internal primary key focused on storage and join behavior. Meanwhile, it preserves a non-predictable external identifier where that is useful.
If the UUID itself must be the primary key, consider a time-ordered format and test it with representative data. UUIDv7 is timestamp-ordered. PostgreSQL 18 includes native UUIDv7 support. PostgreSQL-focused references describe it as a way to improve B-tree index performance while keeping UUID-style global uniqueness. See Neon’s PostgreSQL 18 UUIDv7 guide and the Better Stack PostgreSQL 18 UUIDv7 guide.
That wording matters. UUIDv7 may improve insertion locality, but it does not guarantee that a troubled query becomes fast. Row width, cache pressure, query shape, maintenance, and hardware still matter.
Do not casually rewrite a 200-million-row primary key
Once a table is that large, changing the primary key is an operational project, not a tidy refactor. Foreign keys, replication, application contracts, backfills, deployment sequencing, downtime exposure, and rollback plans all need attention.
Do not migrate because an article said random UUIDs are bad. First, capture the real execution plan and I/O evidence. Then build a representative staging test that measures inserts and point lookups using the key strategy you are considering. Compare the result with the current schema and workload, not with a generic benchmark.
If the evidence points elsewhere, keep the key and fix the actual constraint. If it points to locality and growth, plan a migration deliberately. Include owners, compatibility steps, and a tested rollback.
The verdict
For new, large, write-heavy tables where locality matters, ordered identifiers or a surrogate primary key deserve serious consideration. For smaller tables, lower-write workloads, or systems that need independently generated identifiers, random UUIDs may remain the right trade-off.
The safest useful first action is not to replace every UUID. Capture the real plan, inspect the buffer and I/O evidence, and test the proposed identifier scheme under representative inserts and point lookups. The primary key is part of the storage design, not just an application field.
Categories: System Design
Tags: System Architecture, table