Skip to content

URL shortener · 5 of 6

Step 1 · The pattern

Every row gets the next number

Each new link takes the next ID from an auto-incrementing column. It is a plain 32-bit integer, the default in many frameworks for years. There are two billion values to use.

  • create link
  • click
  • OK

In short

32-bit ID overflow: how it works and how it breaks

An auto-incrementing ID has a type, and the type has a maximum. A signed 32-bit integer stops at 2,147,483,647, a little over two billion. That sounds unreachable on day one. A busy table that records every click or event can use it up in a few years, and failed inserts and rolled-back transactions consume IDs too. When the last value is issued, the next insert fails with an out-of-range error. Reads are unaffected, so existing links keep redirecting and dashboards stay green, while nothing new can be created. Basecamp was read-only for about five hours in 2018 for exactly this reason. The fix is a 64-bit column, whose range will not run out in practice. Changing the type on a huge live table is slow and locks it, so do it early. Until then, alert when any sequence passes about half its range.

How this comes up in interviews

"Creates start failing with an out-of-range error on a table that has worked for years, while reads are fine. What happened?"

Then they ask

  1. How would you have detected this months before it happened?
  2. The table is huge and live. How do you move it to a wider ID type safely?
  3. Why can the ID counter be much higher than the number of rows in the table?

A strong answer covers

  • A signed 32-bit auto-increment column stops at 2,147,483,647, and the next insert fails.
  • Reads are unaffected, so dashboards stay green while nothing new can be created.
  • IDs are never reused: deleted rows, failed inserts and rolled-back transactions all consume them.
  • The fix is a 64-bit column. Altering a huge live table is slow and locks it, so do it early.
  • Alert when any sequence passes about half its type's range.

Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.

On one page: the track cheat sheet.

Last updated: Official postmortem

Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.