It Was Just a Primary Key. What Could Go Wrong?
Turns out, databases don’t love randomness as much as I do
If you ever want to feel smart and slowly ruin your app’s performance, I highly recommend using UUIDs as your primary keys. Works like a charm.
Like many backend developers, I once believed UUIDs were a sign of architectural maturity. They’re globally unique! Secure! Future-proof! How could that possibly backfire?
So I used UUIDs for everything. Users. Orders. Logs. Probably my lunch orders too.
Everything was fine… until one day, our dashboard took 5 seconds to load user stats. Five. Full. Seconds. That’s an eternity when you’re trying to look competent in front of a customer.
At first, I did what any responsible founder does: I blamed Heroku. Then I blamed Postgres. Then I ran pg_stat_user_indexes.
And there it was. My precious users table had an index so bloated it looked like it had been living off pizza and regret. The B-tree was a mess—fragmented by months of inserting completely random UUIDs. Every new user was wedging itself into a random place in the index like a toddler shoving Legos into a DVD player.
The root cause? UUIDs don’t play nicely with B-tree indexes. They’re not sequential. So instead of nice, ordered inserts, you get chaos—page splits, cache misses, and a slowly dying database.
The fix?
I switched to uuid_generate_v1mc(), which creates roughly time-sortable UUIDs. Performance got better. My ego… stayed bruised.
So here’s the rule of thumb.
UUIDs aren’t bad. They’re just not magic.
Use them when
You need to generate IDs across distributed systems.
You don’t want people guessing URLs (/reset-password/:id).
You’re migrating or merging datasets and need guaranteed uniqueness.
Avoid them when
You care about write performance and index size.
You’re joining on them frequently.
You want to be able to debug without going cross-eyed.
Or to put it another way:
If you wouldn’t tattoo a UUID on your arm, maybe don’t use it as your primary key.