Integer Primary Key Sequence Exhaustion
Writes to one table -- always the inserts, never the reads -- begin failing all at once with no deploy and no schema change, and the error message is...
In 10 seconds
At 2,147,483,647 inserts a table simply stops accepting writes. Every INSERT that relies on the default-valued primary key fails with the same error, the application throws 500s on creation paths while reads keep working, and nothing in the code or the data changed -- the table just reached a number. This is integer primary-key exhaustion, and it is one of the most predictable outages in PostgreSQL because the wall is a fixed constant. The trap is in the word 'serial'. When you write `id serial PRIMARY KEY` (or `bigserial`'s smaller cousins `serial` / `serial4`), PostgreSQL creates an int4 column AND an owned sequence whose MAXVALUE is set to the int4 maximum, 2,147,483,647 -- not the bigint maximum. People assume a sequence counts up to 9.2x10^18; a serial's sequence is deliberately capped at the column's type ceiling, so the sequence runs out at exactly the point the column would overflow. Once `nextval` on that sequence reaches the ceiling, the next call raises SQLSTATE 2200H, 'nextval: reached maximum value of sequence', and because the column's DEFAULT is `nextval(...)`, every ordinary INSERT now fails -- the table is effectively read-only until someone widens it. The lab makes this exact, byte-for-byte, and deterministic: a table `events` with a serial primary key is created, the sequence is fast-forwarded with setval to one short of the ceiling (the deterministic stand-in for a table that has organically burned through 2.1 billion ids over years), the last legal INSERT lands precisely on id 2147483647, and the very next INSERT raises 2200H. We read the FREE signal (pg_sequences shows the failing sequence sitting at last_value = max_value = 2147483647, 100% consumed, data_type integer), the PREMIUM signal (a fleet scan that joins every sequence to its owning column and ranks them by percent of ceiling consumed, exposing not just the table that just failed but a SECOND serial table quietly at 70% and confirming a bigint identity column has effectively unlimited headroom), then the FIX (widen BOTH the sequence and the column to bigint, after which inserts resume at 2147483648) and the PREVENTION (define new keys as bigint identity from day one, and run the int4-past-70% alert on a schedule so the next table is widened before it fails, not after).
Why This Happens
A PostgreSQL sequence is a 64-bit counter, but its MAXVALUE is a property you can set, and serial sets it deliberately. The serial pseudo-type expands to: create an int4 column, create a sequence OWNED BY that column, and set the column DEFAULT to nextval of that sequence -- and crucially the generated sequence is given MAXVALUE = 2147483647, the int4 maximum, because storing a larger value in the int4 column would itself fail. So the sequence and the column share the same ceiling by construction. nextval increments the counter and, when the result would exceed MAXVALUE, raises errcode 2200H (ERRCODE_SEQUENCE_GENERATOR_LIMIT_EXCEEDED) with the message 'nextval: reached maximum value of sequence "%s" (%lld)' rather than wrapping (a serial sequence has no CYCLE).
Because the column's DEFAULT is that nextval, every INSERT that does not supply an explicit id calls the now-exhausted sequence and fails -- the table becomes append-blocked while remaining fully readable. That is the entire mechanism, and it is why the error constant is always exactly 2147483647: it is 2^31 -- 1, baked into the sequence at CREATE time.
The fix has to address BOTH halves, and missing either one leaves you stuck. Lifting only the sequence ceiling (ALTER SEQUENCE ... AS bigint MAXVALUE 9223372036854775807) lets nextval produce 2147483648, but the int4 column cannot store it, so the INSERT would then fail with 'integer out of range' instead. Widening only the column (ALTER TABLE ... ALTER COLUMN id TYPE bigint) makes the column able to hold large values, but the owned sequence's MAXVALUE is NOT changed by a column type change -- it stays 2147483647 -- so nextval still refuses at the old ceiling.
You must do both: ALTER SEQUENCE ... AS bigint to raise the counter's ceiling, and ALTER TABLE ... TYPE bigint to widen the storage.
The column change is the expensive half: changing int4 to int8 changes the on-disk width of every row, so PostgreSQL performs a full table rewrite holding an AccessExclusiveLock for the duration -- instant on the lab's tiny table, but a carefully planned maintenance-window (or online-migration) operation on a table large enough to have exhausted an int4 in the first place. Once both are bigint the new ceiling is 9,223,372,036,854,775,807 -- at the rate that exhausted the int4 it is effectively unreachable -- and inserts resume exactly where the counter left off, at 2147483648, with no gap and no data change. Once both are bigint the new ceiling is 9,223,372,036,854,775,807 -- at the rate that exhausted the int4 it is effectively unreachable -- and inserts resume exactly where the counter left off, at 2147483648, with no gap and no data change.
Diagnose
Free Tier -- Basic Approach
The on-call's first read with zero special tooling: take the sequence named in the error message and ask pg_sequences whether it is actually sitting on its ceiling. One row answers it.
SELECT sequencename,
data_type,
last_value,
max_value,
round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE sequencename = 'events_id_seq';
sequencename | data_type | last_value | max_value | pct_used ---------------+-----------+------------+------------+---------- events_id_seq | integer | 2147483647 | 2147483647 | 100.00 (1 row)
The error names the sequence -- events_id_seq -- so the free diagnostic reads pg_sequences for exactly that sequence and computes how much of the ceiling is consumed. The single row is unambiguous: data_type = integer, last_value = 2147483647, max_value = 2147483647, and pct_used = 100.00. last_value equal to max_value, with the data_type being integer, is the complete at-a-glance diagnosis: this is an int4-backed sequence that has reached its maximum, which is why nextval -- and therefore every default-valued INSERT -- is failing.
An on-call needs nothing more than this to know the class of problem and that the fix is to widen the type. What this free read does NOT give you is the bigger picture -- which OTHER sequences in the database are quietly approaching the same wall, and the confirmation that your bigint keys are safe -- and that fleet-wide view is the premium step.
Fix
Widen BOTH halves of the serial: lift the sequence ceiling and widen the column. Doing only one leaves the table broken; doing both restores writes immediately and pushes the ceiling out to ~9.2×10^18.
ALTER SEQUENCE events_id_seq AS bigint MAXVALUE 9223372036854775807;
ALTER SEQUENCE
ALTER TABLE events ALTER COLUMN id TYPE bigint;
ALTER TABLE
SELECT sequencename, data_type, max_value FROM pg_sequences WHERE sequencename='events_id_seq'; SELECT format_type(atttypid, atttypmod) AS events_id_type FROM pg_attribute WHERE attrelid='events'::regclass AND attname='id';
sequencename | data_type | max_value ---------------+-----------+--------------------- events_id_seq | bigint | 9223372036854775807 (1 row) events_id_type ---------------- bigint (1 row)
INSERT INTO events(payload) VALUES ('first-id-past-the-wall') RETURNING id;
id ------------ 2147483648 (1 row) INSERT 0 1
Step 1, ALTER SEQUENCE events_id_seq AS bigint MAXVALUE 9223372036854775807, changes the sequence's declared type and raises its ceiling to the bigint maximum -- a metadata-only, instantaneous change. Step 2, ALTER TABLE events ALTER COLUMN id TYPE bigint, widens the column itself; because int4 and int8 have different on-disk widths this is a full table rewrite holding an AccessExclusiveLock for its duration (imperceptible on the lab table, a maintenance-window operation on a billion-row one).
The capture then reads both halves back to prove they agree: events_id_seq now reports data_type = bigint with max_value = 9223372036854775807, and events.id reports type bigint. Both ALTERs are required: had we widened only the column, the sequence's MAXVALUE would still be 2147483647 and nextval would keep refusing; had we lifted only the sequence, nextval returned 2147483648 and the int4 column would reject it with 'integer out of range'.
With both done, the INSERT that was failing now succeeds and returns id = 2147483648 -- the first id past the old wall -- proving writes are restored exactly where the counter stopped, with no gap and no change to existing rows.
Verify
Healthy-state proof: the column and sequence are now bigint with an astronomically distant ceiling, and ids keep climbing -- the outage is over.
SELECT sequencename,
data_type,
last_value,
max_value,
round(100.0 * last_value / max_value, 10) AS pct_used
FROM pg_sequences
WHERE sequencename='events_id_seq';
sequencename | data_type | last_value | max_value | pct_used ---------------+-----------+------------+---------------------+-------------- events_id_seq | bigint | 2147483648 | 9223372036854775807 | 0.0000000233 (1 row)
INSERT INTO events(payload) VALUES ('still-flowing') RETURNING id;
id ------------ 2147483649 (1 row) INSERT 0 1
The verify capture re-reads pg_sequences for events_id_seq and confirms the new healthy state: data_type = bigint, last_value = 2147483648, max_value = 9223372036854775807, and pct_used = 0.0000000233 -- the ceiling is so far away that consumption rounds to effectively zero. It then runs one more ordinary INSERT, which returns id = 2147483649, demonstrating that the sequence continues monotonically past the old int4 wall with no manual intervention on each insert.
Taken with the fix capture, this is the before/after that pins the diagnosis beyond doubt: the table, its data, and its constraints are unchanged -- only the width of the id column and the ceiling of its sequence differ -- so the type ceiling was unambiguously the cause, and widening it is unambiguously the cure. New rows now flow normally and will for ~9.2×10^18 more inserts.
⏪ Rollback
Nothing here is destructive to the test environment, and the reproduction runs entirely against a throwaway database created and dropped on a PostgreSQL instance; the lab is torn down at the end and only the literal capture files are kept. No global server setting is touched. The two operational statements worth understanding before you run them on a real system are the fix's halves. ALTER SEQUENCE events_id_seq AS bigint MAXVALUE 9223372036854775807 is metadata-only and instantaneous -- it changes the sequence's declared type and ceiling, takes a brief lock on just the sequence, and is trivially reversible (ALTER SEQUENCE ... AS integer would set it back, though there is no reason to). The consequential statement is ALTER TABLE events ALTER COLUMN id TYPE bigint: because int4 and int8 have different storage widths this REWRITES the entire table and holds an AccessExclusiveLock for the whole rewrite, blocking all reads and writes to that table until it completes. On the lab's tiny table that is imperceptible; on a billion-row table it can take a long time and must be done in a maintenance window -- or avoided entirely with an online pattern (add a new bigint column, backfill it in batches, keep it in sync with a trigger, then swap it into the primary key), which trades a long lock for a longer but non-blocking migration. There is no data loss in either approach: the id values are preserved exactly (2147483647 stays 2147483647), the sequence continues from where it stopped, and no rows are altered in content. The only thing that genuinely changes is the column's storage width and the sequence's ceiling -- and that change is the cure, not something to undo.
📦 Version Matrix (PG 14--18)
The serial-sequence cap and the 2200H wall are identical core behaviour on every supported major version. Each row is an independent run against a fresh throwaway database on that version's test environment node: create a serial table, read the sequence's MAXVALUE, fast-forward to the ceiling, and attempt the overflow INSERT:
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
serial seq max: 2147483647 | ✗ Not available -- use stats_reset age |
PG 15.18 |
serial seq max: 2147483647 | ✗ Not available -- use stats_reset age |
PG 16.14 |
serial seq max: 2147483647 | ✗ Not available -- use stats_reset age |
PG 17.10 |
serial seq max: 2147483647 | ✗ Not available -- use stats_reset age |
PG 18.4 |
serial seq max: 2147483647 | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
serial column is NOT a 64-bit counter -- this is the misconception that causes the outage. serial creates an int4 column whose owned sequence has MAXVALUE = 2147483647 (the int4 maximum), so it runs out at ~2.1 billion, not ~9.2×10^18. If you want headroom you must use bigserial / bigint GENERATED ... AS IDENTITY. Any time an INSERT fails with the constant 2147483647 in the message, that is 2^31 -- 1 and the diagnosis is integer exhaustion, full stop.integer GENERATED ... AS IDENTITY sets its sequence MAXVALUE to the int4 maximum exactly like serial does, so the same 2200H wall applies. Don't assume 'GENERATED AS IDENTITY' means safe -- check the declared TYPE; only a bigint identity (or bigserial) has the large ceiling.Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why did every INSERT into one table suddenly start failing with no code change?
The table's primary key is a serial (int4) column, and its owned sequence reached its maximum value, 2,147,483,647 (which is 2^31 -- 1, the largest signed 32-bit integer). A serial sequence's MAXVALUE is set to the int4 ceiling at creation time, so once nextval reaches it the next call raises SQLSTATE 2200H, 'nextval: reached maximum value of sequence'. Because the column's DEFAULT is that nextval, every ordinary INSERT now fails while reads keep working. Nothing in your code changed -- the table simply consumed its last available id.
I thought sequences counted up to 9 quintillion. Why did mine stop at 2 billion?
Sequences are 64-bit internally, but serial deliberately sets the sequence's MAXVALUE to the int4 maximum, 2147483647, because the int4 column it feeds cannot store anything larger. So a serial's ceiling is ~2.1 billion, not ~9.2×10^18. Only bigserial or bigint GENERATED ... AS IDENTITY gives a sequence the bigint ceiling. The 9-quintillion number applies to bigint sequences; a serial is capped at its column's type.
How do I fix it -- and why isn't changing the column type enough?
You must widen BOTH the sequence and the column. Run ALTER SEQUENCE <seq> AS bigint MAXVALUE 9223372036854775807 to lift the counter's ceiling, AND ALTER TABLE <t> ALTER COLUMN id TYPE bigint to widen the storage. Changing only the column does not raise the owned sequence's MAXVALUE -- it stays 2147483647 -- so nextval still refuses at the old wall. Changing only the sequence lets nextval produce 2147483648, but the int4 column can't store it, so the INSERT fails with 'integer out of range'. After both ALTERs the ceiling is ~9.2×10^18 and inserts resume at 2147483648, exactly where the counter left off, with no gap and no data change.
Is ALTER COLUMN ... TYPE bigint safe to run on a big production table?
It is safe in the sense that it preserves your data exactly, but it is expensive: int4 and int8 have different on-disk widths, so PostgreSQL REWRITES the entire table and holds an AccessExclusiveLock for the whole rewrite, blocking all access to that table until it finishes. On a small table that is instant; on a table large enough to have exhausted an int4 it can take a long time and must run in a maintenance window. To avoid the blocking rewrite, use an online migration: add a new bigint column, backfill it in batches, keep it in sync with a trigger, then swap it into the primary key. Remember to widen any foreign-key columns that reference the table -- each is its own rewrite.
How do I find my OTHER tables that are about to hit this before they fail?
Run the fleet scan: join pg_depend to map every sequence to its owning table.column, read last_value and max_value from pg_sequences, and rank by last_value / max_value. Anything int4-backed and above, say, 70% is a future outage. In the lab this scan immediately surfaces a second serial table (orders) sitting at 70% even though only the first table had failed -- exactly the early warning you want. Schedule the alert (int4 columns past a threshold) so you widen each table on your own timeline, in a maintenance window, instead of discovering it from a production error.
Does GENERATED ALWAYS AS IDENTITY protect me from this?
Only if it's a BIGINT identity. An identity column inherits the ceiling of its declared type: integer GENERATED ALWAYS AS IDENTITY sets the sequence MAXVALUE to 2147483647 and hits the same 2200H wall as serial. bigint GENERATED ALWAYS AS IDENTITY sets MAXVALUE to 9223372036854775807 and is effectively unlimited (the lab's audit_log table demonstrates this -- 0.0000% consumed against a 9.2×10^18 ceiling). The protection comes from the TYPE being bigint, not from the IDENTITY syntax. For any new surrogate key, default to bigint identity.
References
- Numeric Types -- serial / bigserial and their int4/int8 ranges
- CREATE SEQUENCE -- MAXVALUE, AS data_type, ownership
- ALTER SEQUENCE -- AS bigint / MAXVALUE
- ALTER TABLE -- ALTER COLUMN ... TYPE (table rewrite, AccessExclusiveLock)
- Identity Columns -- GENERATED ... AS IDENTITY (CREATE TABLE)
- pg_sequences -- the sequence-state view (data_type, last_value, max_value)
- Sequence Manipulation Functions -- nextval / setval
- pg_depend -- dependency catalog (sequence-to-column ownership)
- PostgreSQL Error Codes -- Class 22, 2200H sequence_generator_limit_exceeded