UUIDv4: My Biggest Database Performance Mistake

A long time ago, I made a call I thought was smart and scalable: using UUIDv4 as the primary key for alerting data. It seemed forward-looking.

I would avoid potential problems with sharing the database later, didn’t have to worry about auto-increment overflow, eliminated the risk of leaking sequential IDs that could allow record enumeration attacks, and took a tiny bit of pressure off the database by generating IDs in the app instead of the DB. I thought I was clever.

That feeling lasted until we onboarded our first major client. Suddenly, queries against UUIDv4-keyed tables were slow “work_mem set to 1KB” slow. No matter how carefully we indexed, we couldn’t get the same performance we were used to with traditional keys.

The core problem is that UUIDv4 values are completely random, so inserts land all over the key space. This blows up index locality, forcing the B-tree to constantly split and rebalance. Pages end up half-full and scattered, which means fewer of them stay resident in memory.

UUIDs are also 16 bytes, double the width of a BIGINT and quadruple an INTEGER, so every index entry and table row pointer has more overhead. That extra width also means fewer entries per page, more pages overall, and more cache churn. Once those pages fall out of shared buffers, PostgreSQL has to hit disk to read them back, and query latency spikes.

Something else I learned, important enough to call out separately, is that signed BIGINTs support values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. That’s over 9 quintillion IDs. Auto-increment exhaustion is simply not a realistic concern for most applications. It’s not a good reason to choose UUIDv4.

In the end, we re-keyed everything to use BIGINT. Performance returned: smaller indexes, faster writes, and query times that made sense again. The fix worked, but it came at the cost of weeks of worry and re-work. The takeaway was clear: if you can avoid UUIDs, do it.

What About UUIDv7?

UUIDv7 indexes better than UUIDv4, but back when I made this mistake, UUIDv7 didn’t exist. If it had, I might have made a different choice.

V7 embeds a millisecond-precision timestamp into the UUID, which means inserts are monotonic instead of fully random. You still get globally unique identifiers but also have less spaced out indexes. This results in better insert performance, smaller indexes, and faster selects. These are all the properties you want for a high-performance index.

Benchmarks back this up, with tests showing UUIDv7 insert performance up to 30–35% faster than UUIDv4, producing indexes around 22% smaller, and yielding a 54% reduction in query execution time on Postgresql.

Closing Advice

Avoid UUIDv4 for primary keys unless you truly have no alternative. The performance hit is real. If you can use BIGINT, do it. It’s faster, more compact, and you’re not running out of IDs in your lifetime. Also, there are some good human-readable ID libraries out there too if you want something nicer looking IDs. If you need IDs generated outside the database, across shards, or in a distributed system, then UUIDv7 or ULIDs.