It Was Just a Primary Key. What Could Go Wrong?

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.