Widening int to bigint Primary Keys Without Downtime

The monitoring query that nobody looked at says the events.id sequence is at 1.9 billion, and integer tops out at 2,147,483,647. At the current insert rate that is eleven days. When it runs out, every insert fails with integer out of range, and the whole write path of the product stops. The one-line fix — ALTER TABLE events ALTER COLUMN id TYPE bigint — would rewrite 1.9 billion rows and every index under ACCESS EXCLUSIVE, which on this table is many hours of complete unavailability, plus the same for every table whose foreign keys reference it. This guide performs the change online: a bigint shadow column kept in sync, a batched backfill, a unique index built concurrently, and a primary-key swap that holds its exclusive lock for well under a second. It is the hardest case in Changing Column Types Safely, and the same technique handles other key types.

Sequence exhaustion is entirely predictable; the migration takes days of calendar time, so start when headroom falls below a few months, not days.

Symptom / Error Signatures

Before exhaustion, the only symptom is the number. Check it for every integer key:

-- PostgreSQL · read-only · fraction of int4 range used by each sequence owned by an integer column
SELECT s.schemaname, s.sequencename, s.last_value,
       round(100.0 * s.last_value / 2147483647, 1) AS pct_of_int_max
FROM pg_sequences s
WHERE s.data_type = 'integer' OR s.max_value = 2147483647
ORDER BY pct_of_int_max DESC NULLS LAST;

After exhaustion, inserts fail:

ERROR:  nextval: reached maximum value of sequence "events_id_seq" (2147483647)
ERROR:  integer out of range

The first appears when the sequence itself is integer-typed; the second when a bigint sequence feeds an integer column. Foreign-key columns in other tables (event_attachments.event_id) overflow at the same moment, because they hold the same values.

Root Cause Analysis

serial columns and older GENERATED ... AS IDENTITY columns created as integer use four-byte storage, and their sequences are capped at the integer maximum. Changing the column to bigint changes its on-disk width, so PostgreSQL must rewrite every row and rebuild every index containing the column — including the primary key index — while holding ACCESS EXCLUSIVE. Referencing columns in other tables need the same change, and foreign keys must be re-validated.

The online approach separates the work that needs a lock from the work that does not:

Work	Lock needed	Duration
add `id_new bigint` column	brief ACCESS EXCLUSIVE	milliseconds
sync trigger	brief lock to create	milliseconds
backfill `id_new = id`	row locks per batch	hours, throttled
`CREATE UNIQUE INDEX CONCURRENTLY` on `id_new`	SHARE UPDATE EXCLUSIVE	tens of minutes
`NOT NULL` via validated check	brief + online validate	minutes
swap primary key and names	brief ACCESS EXCLUSIVE	under a second

Foreign keys referencing the table must follow: each referencing column gets the same treatment, and each foreign key is recreated against the new key NOT VALID and validated online, per adding foreign keys with NOT VALID and VALIDATE CONSTRAINT.

The calendar time is days; the time any query waits on an exclusive lock totals under two seconds.

Immediate Mitigation

If the sequence is days from exhaustion and the full migration cannot finish in time, buy headroom first.

1. Use the negative range. An integer column can hold values down to −2,147,483,648. Restarting the sequence at the minimum and counting upward doubles the available keys. Only do this if nothing in the application assumes ids are positive or monotonically increasing (for example, ORDER BY id for recency, or ids in URLs parsed as unsigned).

-- PostgreSQL · migration role · buys ~2.1 billion more ids
-- WARNING: ids become negative; verify no code assumes positive or increasing ids before running.
ALTER SEQUENCE events_id_seq MINVALUE -2147483648 RESTART WITH -2147483648;
-- ROLLBACK PATH: ALTER SEQUENCE events_id_seq MINVALUE 1 RESTART WITH <max(id)+1> (only if no negative ids were issued).

2. Start the online migration immediately. The steps below; plan for the backfill to take days on very large tables.

Permanent Fix / Long-Term Pattern

1. Add the shadow column and sync it.

-- PostgreSQL · migration role · brief locks only
SET lock_timeout = '3s';
ALTER TABLE events ADD COLUMN id_new bigint;
CREATE OR REPLACE FUNCTION events_id_sync() RETURNS trigger AS $$
BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$ LANGUAGE plpgsql;
CREATE TRIGGER events_id_sync BEFORE INSERT OR UPDATE OF id ON events
  FOR EACH ROW EXECUTE FUNCTION events_id_sync();
-- ROLLBACK PATH: DROP TRIGGER events_id_sync ON events; DROP FUNCTION events_id_sync(); ALTER TABLE events DROP COLUMN id_new;

2. Backfill in batches by id range, throttled on replica lag, as in Backfill Optimization:

-- PostgreSQL · repeated by a script advancing :lo · each batch its own transaction
UPDATE events SET id_new = id WHERE id >= :lo AND id < :lo + 10000 AND id_new IS NULL;

3. Build the unique index and prove NOT NULL online.

-- PostgreSQL · outside a transaction for the index; separate statements
SET lock_timeout = '3s';
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS events_id_new_uidx ON events (id_new);
ALTER TABLE events ADD CONSTRAINT events_id_new_nn CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE events VALIDATE CONSTRAINT events_id_new_nn;

4. Swap the primary key in one short transaction. Move the sequence to the new column, drop the old key, attach the new index as the key, and rename.

-- PostgreSQL 12+ · migration role · brief ACCESS EXCLUSIVE on events
-- WARNING: foreign keys referencing events.id must be dropped first and recreated against the new key.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE events ALTER COLUMN id_new SET NOT NULL;               -- instant: uses validated check
ALTER TABLE events DROP CONSTRAINT events_id_new_nn;
ALTER TABLE events DROP CONSTRAINT events_pkey;
ALTER TABLE events ADD CONSTRAINT events_pkey PRIMARY KEY USING INDEX events_id_new_uidx;
ALTER SEQUENCE events_id_seq AS bigint MAXVALUE 9223372036854775807 OWNED BY events.id_new;
ALTER TABLE events ALTER COLUMN id_new SET DEFAULT nextval('events_id_seq');
ALTER TABLE events ALTER COLUMN id DROP DEFAULT;
DROP TRIGGER events_id_sync ON events;
ALTER TABLE events RENAME COLUMN id TO id_old;
ALTER TABLE events RENAME COLUMN id_new TO id;
COMMIT;
-- ROLLBACK PATH: keep id_old for a release; a reverse swap is possible until ids exceed the int range.

5. Repeat for referencing columns and recreate foreign keys NOT VALID, then validate. 6. Drop id_old in a later release, after confirming nothing reads it. Add a standing alert at 50% of integer range for every remaining integer key, and create new tables with bigint keys by default.

Every gate is a cheap query; together they guarantee the swap transaction contains only metadata changes.

Verification Checklist

SELECT count(*) FROM events WHERE id_new IS DISTINCT FROM id returned zero immediately before the swap.
events_id_new_uidx was valid and the NOT NULL check was validated before the swap transaction.
After the swap, \d events shows id bigint as the primary key with the sequence as its default.
SELECT data_type, max_value FROM pg_sequences WHERE sequencename = 'events_id_seq' shows bigint.
Every foreign key referencing events was recreated against the new key and validated.

Frequently Asked Questions

How long before exhaustion should I start? Months, not days. The backfill on a multi-billion-row table can take days when throttled to protect replicas, and referencing tables add more. Alert at 50% of the integer range so the migration never becomes an emergency.

Is restarting the sequence at a negative value safe? Only if nothing assumes positive or increasing ids. It is a stop-gap that doubles headroom; it does not remove the need to move to bigint.

Why not just change the sequence to bigint? The sequence can produce larger values, but the integer column still cannot store them. Both the column and every referencing column must become bigint.

Does the swap transaction rewrite anything? No. Setting NOT NULL uses the validated check, attaching the primary key adopts an existing valid index, and renames and default changes are catalog updates. The transaction holds ACCESS EXCLUSIVE only for milliseconds.