Learning center

How much room is left in a PostgreSQL integer ID

A serial primary key stops at 2,147,483,647, and inserts fail from the next one. Read the sequence's headroom now, while the change is still a planned migration. When you widen the key, widen every column that stores it too: a foreign key column left as integer fails on the first new ID above the limit.

Run a sequence out of values#

We ran this on PostgreSQL 16.15. setval moves the sequence close to its end so the lab does not need two billion rows.

CREATE TABLE audit_log (id serial PRIMARY KEY, action text NOT NULL);
SELECT setval('audit_log_id_seq', 2147483645);

INSERT INTO audit_log (action) VALUES ('a'), ('b');
-- INSERT 0 2
INSERT INTO audit_log (action) VALUES ('c');
ERROR:  nextval: reached maximum value of sequence "audit_log_id_seq" (2147483647)

Every insert into the table now fails. An audit or event table is often the first to reach the limit, because it grows fastest and nobody reads its IDs.

Read the headroom#

pg_sequences reports each sequence's type, last value and maximum:

SELECT sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC;
sequencenamedata_typelast_valuemax_valuepct_used
audit_log_id_seqinteger21474836472147483647100.00

The percentage is not a row count. Failed inserts, rolled-back transactions and cached sequence values all consume numbers that never reach the table. To turn the headroom into a date, sample last_value a week apart and divide the remaining values by the weekly increase.

Widening the sequence alone is not enough#

ALTER SEQUENCE ... AS bigint raises the sequence's limit, but the column still has the old type:

ALTER SEQUENCE audit_log_id_seq AS bigint MAXVALUE 9223372036854775807;
INSERT INTO audit_log (action) VALUES ('c');
-- ERROR:  integer out of range

This query lists each column fed by a sequence, with both types, so a mismatch like this one stands out:

SELECT c.oid::regclass AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS column_type,
       format_type(s.seqtypid, NULL) AS sequence_type
FROM pg_depend d
JOIN pg_sequence s ON s.seqrelid = d.objid
JOIN pg_class c ON c.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.classid = 'pg_class'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype IN ('a', 'i');
table_namecolumn_namecolumn_typesequence_type
audit_logidintegerbigint

Widen the columns that store the key#

A note table references the audit log. Widening the primary key now lets the parent table insert again, and the child fails at the same boundary:

CREATE TABLE audit_notes (
    id serial PRIMARY KEY,
    audit_id integer NOT NULL REFERENCES audit_log (id)
);
ALTER TABLE audit_log ALTER COLUMN id TYPE bigint;

INSERT INTO audit_log (action) VALUES ('c') RETURNING id;
-- 2147483649

INSERT INTO audit_notes (audit_id) VALUES (2147483648);
-- ERROR:  integer out of range

PostgreSQL accepted a foreign key from integer to bigint, so nothing warned about the narrower child column. The returned ID also skipped 2147483648, which the earlier failed insert had consumed. List every column that references the key with \d+ audit_log or pg_constraint, and include columns that hold the ID without a constraint, such as a column in another service's database or an analytics export.

Plan the migration#

ALTER COLUMN ... TYPE bigint rewrites the table and its indexes while holding an ACCESS EXCLUSIVE lock, as which column type changes rewrite the table shows. For a small table that may be acceptable. For a large one, add a bigint column, fill it in batches, keep it current with a trigger, and swap it in a short transaction. Rehearse that swap, including the primary key and every foreign key that points at it.

Check the application too. A 32-bit integer field in Java or C#, or a schema that validates the ID as a 32-bit number, fails the same way as the database column.

What to rehearse#

Run the headroom and type queries against production now and record the date you expect each sequence to run out. On a staging copy, set the sequence just below 2,147,483,647, apply the migration, and insert through every path that writes the key or a reference to it. The rehearsal passes when each insert succeeds with an ID above the old limit.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading