Why your primary key choice in a DB matters.

I'm currently learning System Design - and writing notes and blogs the explain concepts in simpler terms (which also helps me learn better). This article is a part of this learning series.

I kept reading that swapping a bigint primary key for a random UUID can quietly wreck your write performance - and I wanted to actually see the mechanism, not just take it on faith + build a simpler example to understand.

Start with a phone book

A phone book keeps names in sorted order and groups them. To find one, you don't scan every sheet - you use the thumb-tabs on the edge, jump to the right page, and just scan that page.

This is similar to how a DB And under the hood, how a B-tree (the structure behind most primary keys) works.

The whole article is really one question: when you insert a new row, where does its key land in that sorted book, and what does it cost to put it there? Every ID strategy is just a different answer to that.

The page - the unit of everything

Databases don't read one row at a time, but rather they read and write in fixed-size blocks called pages - the smallest unit the DB touches on disk.

• Postgres - 8KB
• MySQL - 16KB

One 8KB page holds hundreds of keys. These keys are kept in sorted order and the pages themselves are ordered left to right, like pages in the phone book. (As this ordering is the entire game.)

The tree above the pages

Think of these pages as hanging off a tree -

• The leaf pages - the bottom row - are where the actual index entries live.
• The root and branch pages above them are just signposts: "keys below 5000 are on the left, 5000–8000 in the middle, 8000+ on the right."

Each branch page points to hundreds of children. So even a table with 100 million rows only needs a tree about 3–4 levels deep to reach any leaf. Three or four hops, and you're at the row.

One more thing that decides everything downstream: hot vs cold pages.

Page splits -

When you insert a key into a leaf page that's already full, the DB can't just squeeze it in. It splits the page - cracks the full page into two half-full pages and updates the parent's pointers to match.

Also, a split isn't free:

• It's extra writes - two pages written instead of one + the parent update.
• Can leave the index bloated and fragmented - pages sitting half-empty, no longer neatly in physical order.

And remember - the scale at which we're doing this is millions. So imagine millions of them scattered across your table all day = database that writes slower and slower and eats more disk than it should.

This is why choosing the right primary key is important.

Now the real question - which ID?

We have three main families:

Auto-incrementing bigint -

1. Every new id is larger than the last. So every insert lands in the far-right leaf, in order.
2. Since its at the right edge, the pages are aways hot
3. You only split towards the right.
4. Bigint - 8 bytes - compact.

Random UUIDv4 -

1. Every new key is random, so it sorts into some arbitrary spot in the middle of the tree.
2. Therefore - there can be multiple cache miss after cache miss (you're constantly reaching for cold pages)
3. And splits happening everywhere, which leaves the tree left bloated and fragmented.
4. UUID - 16 bytes - twice the width, on every index and every foreign key that references it.

Time-ordered - UUIDv7 / ULID / Snowflake - think of it as the best of both the worlds.

1. These put a timestamp in the high bits of the value and randomness in the low bits.
2. Since inserts happen in time order, the high bits climb steadily - so new keys land on the right edge of the tree again ( just like the bigint.)
3. Same for split - you're only splitting towards the right.
4. UUIDv7 and ULID - 16 bytes.

Note -read about Snowflake IDs (timestamp + machine id + per-machine sequence) as an extra.

What to choose when

Auto-incrementing bigint

Best case - an internal, append-only table in a single database, where the id never leaves your backend: an events log, an audit trail, an analytics table, a job queue.

Worst case - a public-facing resource whose id shows up in a URL or API response. e.g - Picture /invoices/1042 in your SaaS and let a user swaps it to 1041, 1043, and see your entire invoice table.

Random UUIDv4

Best case - offline or distributed generation, where you need the id before the server ever sees the row, and you want it unguessable.

Worst case - making it the clustered primary key of a high-insert table in one database.

Time-ordered - UUIDv7 / ULID / Snowflake

Best case - the default modern choice for a public-facing primary key on a table that takes lots of inserts and might grow or shard.

The takeaway

Every ID strategy is just a different answer to one question - where does the next key land in the sorted book?

Sequential ids append to the hot right edge whereas random ids scatter across cold pages and split the tree apart. And time-ordered ids buy back the right edge without a central counter.

Pick the shape that matches how the table is written and who gets to see the id.