PostgreSQL — nextval: reached maximum value of sequence

Fix PostgreSQL nextval reached maximum value of sequence errors, check int4 versus bigint column pairs, plan the migration without downtime, and burn nothing.

beginnerdebuggingpostgresqlsequencesschema

Symptoms

ERROR: nextval: reached maximum value of sequence "orders_id_seq" (2147483647) on INSERT
Writes fail while reads continue: the table is healthy but new rows are impossible
The column type is integer (int4) while traffic grew far beyond original estimates

Possible causes

The primary key column is int4 and its sequence has consumed all 2.1 billion values
High-churn workloads (bulk imports, retries, heavy deletes) burn sequence values faster than rows exist
The sequence was created with a custom MAXVALUE lower than the column capacity
An int4 foreign key side also caps the migration to bigint on the parent

Diagnosis steps

Check the sequence state

psql -c "SELECT last_value, max_value FROM orders_id_seq;" && psql -c "\d orders" | grep -E "id.*integer|id.*bigint"

Interpretation

last_value near 2147483647 with an integer column confirms exhaustion; a bigint column with a small sequence MAXVALUE is the rarer, cheaper variant.

Map every dependent int4 column

psql -c "SELECT conrelid::regclass, conname FROM pg_constraint WHERE confrelid = 'orders'::regclass AND contype = 'f';"

Interpretation

Foreign keys referencing the exhausted table inherit the type and must migrate in the same window.

Plan the zero-downtime path

psql -c "SELECT pg_size_pretty(pg_total_relation_size('orders'));"

Interpretation

Table size determines rewrite duration for the type change and whether to batch the migration.

Root cause

An int4 identity or serial column caps at 2147483647. When the sequence serving it exhausts, nextval fails on every insert while the table itself remains perfectly readable. The column type, not the sequence, is the real ceiling: a sequence can be extended only as far as its column can store.

Fix

Migrate the column to bigint with dependencies in one planned change: for small tables a single ALTER TABLE ... ALTER COLUMN TYPE with locks; for large tables add a bigint column, dual-write, backfill in batches, and cut over. Never reset the sequence to reuse IDs.

Risk note

Resetting the sequence to fill gaps invites primary key collisions and breaks references in external systems. Exhausted sequences are migrated, never recycled.

Verify the fix

After migration, the column type is bigint, inserts succeed, and monitoring tracks sequence headroom as a leading indicator with alerts far below the cap.

Symptom walkthrough

The error is precise about the ceiling:

ERROR: nextval: reached maximum value of sequence
"orders_id_seq" (2147483647)

2147483647 is the int4 maximum — the column type is the ceiling, not the sequence setting. Check both halves of the pair:

SELECT last_value, max_value FROM orders_id_seq;  -- at the cap

id | integer | not null default nextval(...)

Writes fail completely while reads stay healthy, which is why it presents as a sudden outage with a healthy table. High-churn patterns (bulk import rollbacks, deletes, retries) burn sequence values without leaving rows, so the exhaustion can arrive well before two billion rows exist.

Fix in practice

Small table — one statement with a lock window:

BEGIN;
ALTER TABLE orders ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE orders_id_seq AS bigint MAXVALUE 9223372036854775807;
COMMIT;

Large table — expand and contract:

-- expand
ALTER TABLE orders ADD COLUMN id_big bigint;
-- backfill in batches
UPDATE orders SET id_big = id WHERE id BETWEEN 1 AND 10000000;
-- index, dual-write via trigger or ORM, then contract
ALTER TABLE orders DROP COLUMN id;
ALTER TABLE orders RENAME COLUMN id_big TO id;

Dependent foreign keys migrate in the same plan — every int4 referencing column is part of the ceiling. And the monitoring that prevents the sequel: track sequence last_value as a fraction of the column maximum, alerting at 70 percent, so the next exhaustion is a planned migration instead of an outage.