Running Out of 32-Bit Keys in PostgreSQL: Measuring Headroom, Sequences Versus Columns, and an Online Migration to bigint

A limit you meet suddenly

PostgreSQL has two integer types in common use for keys: integer (also int4), which is a signed 32-bit value with a maximum of 2,147,483,647, and bigint (int8), a signed 64-bit value whose maximum is over nine quintillion. A table keyed by a plain integer or serial can therefore accept about 2.1 billion inserts before the key space is gone. The failure is not gradual: the next nextval raises ERROR: nextval: reached maximum value of sequence, and every insert that needs a new key fails from that moment. Because the limit is hit by cumulative inserts, not by the number of live rows, a table that has only a few million rows can still be near the ceiling if it has churned through billions of inserts and deletes over the years.

The sequence and the column exhaust separately

Two distinct things carry the 32-bit limit, and they fail for different reasons. The sequence behind a serial column has its own maximum, set when the column type is chosen; it exhausts when its counter reaches that maximum, regardless of what values are actually stored. The column has the type limit; it rejects any value above 2,147,483,647 even if you supply the key yourself. Widening one without the other does not help: a bigint column still fed by an int4-max sequence stops at the sequence limit, and a wide sequence feeding an integer column fails when it produces a value the column cannot hold.

Illustrative sql, checked for syntax; adapt names and storage to your system.

-- Sequences approaching the 32-bit column ceiling (2,147,483,647).
-- last_value is what matters, not the row count: a churned table burns values.
SELECT schemaname, sequencename, last_value,
       round(100.0 * last_value / 2147483647, 2) AS pct_of_int4_max
FROM pg_sequences
WHERE last_value > 2147483647 * 0.7    -- alert well before the ceiling
ORDER BY last_value DESC;

Run this regularly and alert well before any sequence crosses, say, eighty percent. The number you care about is last_value against the type maximum, not the row count, because a heavily churned table burns sequence values that deletes never give back.

The migration: add, backfill, swap

Changing the column type in place with ALTER TABLE ... ALTER COLUMN ... TYPE bigint rewrites the whole table and holds an ACCESS EXCLUSIVE lock for the duration, which on a billion-row table is an outage. The online alternative adds a new wide column, backfills it in batches, keeps it in sync, and swaps. The widening of the column is the goal; the sequence is widened alongside it.

1. Add a nullable bigint column: ALTER TABLE t ADD COLUMN id8 bigint; This is metadata-only and fast.
2. Add a trigger so every new or updated row copies id into id8, keeping the two columns in sync going forward.
3. Backfill existing rows in bounded batches (for example a few thousand keys at a time) so no single statement locks the table for long.
4. Make id8 NOT NULL without a long lock: add CHECK (id8 IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT (which scans without an ACCESS EXCLUSIVE lock), then SET NOT NULL, which on PostgreSQL 12 and later is fast because it trusts the validated constraint. A bare SET NOT NULL would scan the whole table under ACCESS EXCLUSIVE, the outage this migration exists to avoid.
5. Build a unique index on id8 concurrently, then add the primary-key or unique constraint USING INDEX (the constraint needs the column to be NOT NULL, which the previous step guaranteed cheaply).
6. Point foreign keys at id8, widen them the same way, then in one short transaction drop the old column and rename id8 to id.
7. Reset the sequence to a wide maximum and attach it to the renamed column, so new keys are drawn from the full 64-bit range.

A worked example

An events table was created in 2019 with id serial PRIMARY KEY. It holds 400 million live rows but has processed 1.9 billion inserts over its life, so the sequence last_value is 1,900,000,000, about 88 percent of the int4 maximum. At the current rate of 3 million inserts a day it will exhaust in roughly 80 days. The in-place ALTER would lock the table for the rewrite of 400 million rows, far longer than any maintenance window allows. The add-backfill-swap path runs in the background over days: the trigger keeps new rows consistent, small backfill batches bound transaction duration, while the final swap still needs locks whose acquisition can wait behind other work. Rehearse and set lock timeouts. The headroom query is what turned an eventual hard stop into a scheduled migration, which is the whole point of measuring early.

Why the headroom is not the row count

For an ordinary ascending, noncycling serial sequence that is not manually reset, consumed values keep advancing even when rows are deleted. Every INSERT that draws a default key consumes a sequence value, and a DELETE never returns it; neither does a rolled-back transaction, because the sequence advance is not transactional, which is deliberate so that concurrent inserters never block each other on the counter. A table that inserts and deletes a few million rows a day can therefore hold a steady, small live set while its sequence climbs by millions every day. Caching, where each backend reserves a block of sequence values at once, widens the gap further, because values reserved but not used on a clean shutdown are simply skipped. So the only honest measure of how close you are is the sequence last_value against the type maximum, and it is the number to alert on.

When you do not need to migrate

If a table will plausibly never exceed two billion lifetime inserts, integer is the right choice and bigint wastes four bytes per row and per index entry; on a narrow, high-row-count table that is real storage and cache cost. The migration is for tables whose lifetime insert count, not live row count, is on a path to the ceiling. New tables that you expect to churn should start at bigint (or an identity column defined as bigint) so the question never arises.

Failure modes

• Widening the column but leaving the sequence capped at the int4 maximum, so inserts still fail at 2.1 billion.
• Watching row count instead of sequence last_value, and missing that churn has burned the key space.
• Running the in-place ALTER TABLE ... TYPE on a huge table and taking an unplanned outage from the rewrite and its lock.
• Backfilling in one statement, which holds a long lock and bloats the table, instead of in bounded batches.
• Forgetting foreign keys: widening the parent key without widening every referencing column leaves the constraint unenforceable or the child inserts failing.

Verification

Before migrating, record the sequence last_value and the maximum stored key, and keep the headroom query on a schedule with an alert. During the migration, after each backfill batch assert that id8 equals id for every row touched, and that the counts of the two columns match where both are set. After the swap, insert a key above the old int4 maximum and confirm it succeeds, which proves both the column and the sequence are genuinely 64-bit. Keep the headroom alert afterward so the next table approaching the ceiling is found with months to spare, not days.

Practice and related reading

Further reading:

• MVCC and vacuum
• DDL behind a lock queue
• Serializable isolation
• Connection pooling

Sources and scope

Checked against these sources on 23 October 2026. Examples and figures are illustrative, not measurements.

• PostgreSQL documentation: Numeric Types (integer, bigint ranges)
• PostgreSQL documentation: ALTER TABLE
• PostgreSQL documentation: CREATE SEQUENCE
• PostgreSQL documentation: Numeric Types (integer, bigint ranges)