Database

Sequence Exhaustion Calculator

Says when an auto-increment key runs out, at your insert rate and growth.

Loading the tool…

Processing happens locally in your browser. What you paste or load is processed by this page and is not uploaded to a server. Nothing is stored unless you use a control that says it stores something, and you can clear anything this site has kept from the privacy page.

How to use this tool

  1. Write the insert rate one setting per line: rows per day, and optionally yearly growth, current value and gap factor.
  2. Choose the key type the table uses today.
  3. Select Project the sequence.
  4. Compare the row for your type against the bigint row — that difference is what the migration buys you.

What sequence exhaustion calculator does

A 32-bit key holds a little over two billion values, which sounds like plenty right up until it is not. Four million rows a day gets there in about eighteen months from empty, and rather sooner if the table already has history in it. Growth makes it worse than the division suggests, because the rate compounds. And the sequence burns numbers faster than the table gains rows: a rolled-back insert keeps its number, a failed batch keeps all of them, and a cached sequence block loses whatever was left in it on restart.

Give this page the rows per day, optionally a yearly growth rate, where the sequence is now, and a gap factor to account for the numbers you burn without keeping. It walks the growth forward year by year rather than dividing, because compounding matters here, and shows how long each of the four common key widths would last along with the approximate date it runs out. If the type you selected has under ten years left it says so plainly — widening a key later means rewriting every row and every index that references it, which is much cheaper to do while the table is small.

Frequently asked questions

The rate at which you burn sequence numbers without keeping a row. A rolled-back insert does not give its number back, a failed batch loses all of them, and engines that cache blocks of the sequence throw away whatever is unused on restart. If most inserts succeed, 1.0 is fine. If you have a lot of conflict or use large sequence caches, 1.2 to 2.0 is more honest.

For a primary key on a table that will ever be large, that is usually the right call — eight bytes against four is a small price for never having to do this migration. The counter-argument is real but narrow: the key is copied into every foreign key column and every secondary index, so on a genuinely huge, narrow table the four extra bytes multiply. Size it deliberately rather than by habit in either direction.

It gets worse in proportion to the table. Widening a key means rewriting every row, rebuilding every index, and altering every foreign key column that points at it — typically with a long lock or a careful online rebuild. Doing that at ten million rows is an afternoon. Doing it at two billion, under load, with the sequence three weeks from the ceiling, is not.

Because it compounds. Thirty percent a year is a doubling every two and a half years, so a simple division by today’s rate gives an answer that is comfortably wrong in the direction that hurts. This tool walks the years forward one at a time, applying the growth to the rate each year, which is why the figure it returns is shorter than the one you get on the back of an envelope.