Member-only story
After 3 Years and 200M Rows, I Finally Stopped Using UUIDs as Primary Keys
The alert hit my phone while I was mid bite into a sandwich, and my first thought was that someone had restarted the wrong service again.
It was not that. Same query, same table, same code we had shipped three years earlier, except that it had quietly stopped being fast.
I opened the query plan expecting a fluke and instead got a wall of numbers that make any backend engineer go still.
Index Scan using orders_pkey on orders
Buffers: shared hit=812 read=94331
Execution Time: 3841.226 msNinety four thousand block reads, for one row, by its primary key. I read that line three times, half convinced I had pasted the wrong output into my own terminal.
That same query used to come back in under 20 milliseconds. Now, sitting on top of 200 million rows, it took almost 4 seconds, and the only reason anyone even noticed was that a client’s dashboard froze mid demo.
My lead asked me to explain it in the next standup, and the honest answer I gave was, the primary key is a UUID, and I do not think anyone realized what that would cost us later.
If you have ever used a UUID as a primary key in Postgres or MySQL, you already sense where this is going.
