Sequence-generated primary keys built on a four-byte integer will eventually collide with the upper bound of the type if rows keep arriving. The failure mode is an error, not silent corruption, but the fix is a table rewrite — so the practical work is split between watching for the wall and, if you still have runway, walking a table over to bigint without blocking writes.
Two ways to get to the same wall
The traditional approach uses serial:
|
1 2 3 4 |
CREATE TABLE tab ( id serial PRIMARY KEY, ... ); |
That is shorthand: it creates a four-byte integer column with a DEFAULT, plus a sequence behind it.
|
1 2 3 4 5 6 |
\d tab Table "public.tab" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------------------------------- id | integer | | not null | nextval('tab_id_seq'::regclass) ... |
The SQL-standard alternative is an identity column:
|
1 2 3 4 |
CREATE TABLE tab ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ... ); |
This too creates a sequence internally. Either way, sustained inserts will eventually produce:
|
1 |
ERROR: nextval: reached maximum value of sequence "tab_id_seq" (2147483647) |
Table size is not the deciding factor. A table that sees a steady mix of INSERT and DELETE consumes sequence values just as quickly while staying small, so an application with high churn can hit the ceiling on a modest data set.
Why the obvious conversion hurts
The textbook remedy is to widen the column to eight-byte bigint:
|
1 |
ALTER TABLE tab ALTER id TYPE bigint; |
Because the on-disk representation differs between the two types, PostgreSQL must rewrite the whole table, and rewrite the indexes with it. Two consequences follow. First, you need free space to hold both the old and the new copies simultaneously. Second, the operation holds an ACCESS EXCLUSIVE lock, which blocks all concurrent access to the table. On a large table, that is downtime, which is precisely what the operator who ignored the original sizing advice was trying to avoid.
Know your headroom
Most integer-keyed tables never overflow, so converting everything defensively is wasted pain. Measurement is cheaper. The query below inspects every sequence-generated key in the current database and reports how much of the range is consumed:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SELECT a.attrelid::regclass AS table_name, a.attname AS column_name, s.seqrelid::regclass AS sequence_name, round( /* cast to "numeric" so we can round to 2 decimal places */ CAST ( /* * Divide the current sequence position by the maximal * value for the columns's data type. Multiply with * 100 to get a percentage. */ seq.seq::float8 * 100.0::float8 / CASE a.atttypid WHEN 'smallint'::regtype THEN 32768::float8 WHEN 'integer'::regtype THEN 2147483646::float8 WHEN 'bigint'::regtype THEN 9223372036854775808::float8 END AS numeric ), 2 ) AS percent_used FROM pg_sequence AS s /* objects hat the sequence depends on */ JOIN pg_depend AS d ON d.classid = 'pg_class'::regclass AND d.objid = s.seqrelid AND d.objsubid = 0 /* the column the sequence depends on */ JOIN pg_attribute AS a ON d.refclassid = 'pg_class'::regclass AND a.attrelid = d.refobjid AND a.attnum = d.refobjsubid /* the next value from the sequence */ CROSS JOIN LATERAL nextval(s.seqrelid) AS seq ORDER BY percent_used DESC; |
It covers both serial and identity columns. A percent_used near 100 means the column is near the limit. The query has one blind spot: it cannot see a column whose sequence has no dependency recorded on it. If you created the sequence separately from the column, you can register that link by hand:
|
1 |
ALTER SEQUENCE seq OWNED BY tab.id; |
The query reads the current position with nextval(), which advances the sequence and leaves a gap. If that is objectionable, adjust your expectations about gaps in sequences. Starting with PostgreSQL v18, pg_get_sequence_data() — undocumented — reads the sequence position without invoking nextval().
Converting without a long outage
If the limit is visible but not yet reached, the migration can be staged so that no single step holds the table for long.
Add the new column and keep it filled
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
BEGIN; SET LOCAL lock_timeout = '1s'; ALTER TABLE tab ADD id2 bigint; CREATE FUNCTION copy_id() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN NEW.id2 = NEW.id; RETURN NEW; END;$$; CREATE TRIGGER copy_id BEFORE INSERT OR UPDATE ON tab FOR EACH ROW EXECUTE FUNCTION copy_id(); COMMIT; |
Adding a column is a metadata-only change, but it still needs a brief ACCESS EXCLUSIVE lock — which is why lock_timeout is set for the transaction. While the lock is held, the trigger is created so that every subsequent row has id copied into id2. The trigger does not handle updates to the primary key; that is not a workflow you should have in the first place. Run this when no long-running transactions are in flight, or you will queue behind them and fail the timeout.
Backfill in batches
The trigger covers new rows; existing rows still need filling. One large UPDATE would double the table's size in dead tuples, and clearing that would mean VACUUM (FULL) — another operation that blocks everything. Batching avoids both problems, with a plain VACUUM between batches to reclaim space for the next one.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
UPDATE tab SET id2 = id WHERE id2 IS NULL AND id < 1000000; VACUUM tab; UPDATE tab SET id2 = id WHERE id2 IS NULL AND id BETWEEN 1000001 AND 2000000; VACUUM tab; ... |
The batch size of one million rows here is arbitrary. A batch UPDATE can deadlock against concurrent modifications, so pick the size with that risk in mind and be prepared to retry any statement that fails on a deadlock.
Constrain the new column
Setting NOT NULL in one step scans for NULLs while holding ACCESS EXCLUSIVE. A check constraint avoids that scan later, and a NOT VALID constraint can be created and validated without a long lock. In PostgreSQL v18 and later the check constraint is unnecessary, since a NOT NULL constraint can itself be added NOT VALID and validated afterwards.
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE tab ADD CONSTRAINT tab_id2_notnull CHECK (id2 IS NOT NULL) NOT VALID; ALTER TABLE tab VALIDATE CONSTRAINT tab_id2_notnull; ALTER TABLE tab ALTER id2 SET NOT NULL; ALTER TABLE tab DROP CONSTRAINT tab_id2_notnull; CREATE UNIQUE INDEX CONCURRENTLY tab_pkey2 ON tab (id2); |
Swap the primary key
This step takes ACCESS EXCLUSIVE again, but only for fast metadata operations. Any foreign keys that point at the table must be dropped beforehand.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
BEGIN; SET LOCAL lock_timeout = '1s'; /* works only if not referenced by a foreign key */ ALTER TABLE tab DROP id; DROP TRIGGER copy_id ON tab; DROP FUNCTION copy_id(); ALTER TABLE tab RENAME id2 TO id; ALTER TABLE tab ADD PRIMARY KEY USING INDEX tab_pkey2; /* start the auto-generated column values with a safe value */ ALTER TABLE tab ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (START WITH 2147483648); COMMIT; |
Afterwards, recreate the foreign keys. Add them NOT VALID and validate them as a separate step, which keeps the lock brief.
Four-byte sequence keys are a liability you can measure rather than guess at. A monitoring query tells you which columns are genuinely close to the boundary, and for those, the staged bigint conversion moves the primary key without forcing the table out of service.
