Last month, I watched a senior engineer spend two days debugging a slow query. The culprit? UUIDs as primary keys on a 50M row table. The index was 2x larger than it needed to be, and range scans were painfully slow.
UUIDs have become the default choice for many teams. But defaults should be questioned.
UUIDs are 16 bytes. Integers are 4-8 bytes. That sounds trivial until you do the math.
A 100M row table with 5 foreign keys:
Double the storage. Double the memory pressure. Double the I/O.
But the bigger problem is index fragmentation. UUIDv4 is random, so inserts land all over the B-tree. Page splits happen constantly.
Sequential BIGINT inserts:
[1] [2] [3] [4] [5] [6] [7] [8] → append to end
B-tree stays compact. Pages fill left-to-right.
No page splits. Excellent cache locality.
Random UUIDv4 inserts:
[a7f...] [3b2...] [f91...] [0d4...] → scattered everywhere
B-tree fragments. Pages split constantly.
Random I/O. Poor cache locality.
On our production PostgreSQL setup with millions of rows, BIGINT inserts were consistently 30-40% faster than UUIDv4. Range scans? BIGINT was dramatically faster. UUIDv7 was close to BIGINT on writes, which is the main reason it matters.
UUIDs solve specific problems:
Distributed systems — You can't coordinate auto-increment across 10 database shards. UUIDs generate anywhere without coordination.
Public APIs — Sequential IDs leak information. With GET /api/users/1000, an attacker learns you have roughly 1000 users, then tries /api/users/1001, /api/users/1002... classic IDOR vulnerability. With GET /api/users/550e8400-e29b-..., they learn nothing and can't enumerate other users.
Merge conflicts — Syncing data between databases? Sequential IDs will collide. UUIDs won't.
If none of these apply to your use case, you probably don't need UUIDs.
Here's what actually works in production: use both.
Internal queries use the fast integer. APIs expose the secure UUID. Best of both.
UUIDv7 is time-ordered. The first 48 bits are a timestamp, so inserts are sequential like integers but globally unique like UUIDs.
UUIDv4 (550e8400-e29b-41d4-a716-446655440000): Random bits, no order. Fragments the B-tree index on every insert.
UUIDv7 (019006f3-2e47-7000-8000-000000000001): First 48 bits are Unix timestamp (ms), naturally sorted. Inserts go to the end of the index like integers.
Our benchmarks show UUIDv7 recovers most of the insert performance while keeping uniqueness guarantees.
Insert performance (1M rows): BIGINT is the baseline at 100%. UUIDv7 hits 92% — almost as fast. UUIDv4 drops to 68% — significant penalty.
If you need UUIDs, use v7. PostgreSQL 17+ supports it natively. For older versions, generate in application code or use the pg_uuidv7 extension.
The key isn't which one is "better." It's understanding what you're trading off. Make the choice intentionally.
There's no universal ID. Only the right one for your access pattern.
— blanho
You optimized the wrong thing. Again. Here's why guessing at performance never works.
Two servers, one resource, zero coordination. Here's how it breaks.
You have 10 million saved searches. A new item comes in. How do you find all matches without running 10 million queries?
CREATE TABLE orders (
-- Internal: fast lookups, compact indexes
id BIGSERIAL PRIMARY KEY,
-- External: safe to expose, no enumeration
public_id UUID DEFAULT gen_random_uuid() UNIQUE,
customer_id BIGINT REFERENCES customers(id), -- FK uses BIGINT
created_at TIMESTAMPTZ DEFAULT now()
);
-- Index for API lookups
CREATE INDEX idx_orders_public_id ON orders(public_id);
-- Internal query (fast, uses integer PK):
SELECT * FROM orders WHERE id = 12345;
-- API query (secure, uses UUID):
SELECT * FROM orders WHERE public_id = '550e8400-e29b-...'::uuid;