PostgreSQL Error 2200H: Sequence Generator Limit Exceeded

PostgreSQL error code 2200H occurs when a sequence object reaches its defined MAXVALUE (or MINVALUE for descending sequences) and has no CYCLE option to wrap around. This is most commonly seen on tables using SERIAL (INT4) primary keys, which cap out at approximately 2.1 billion. Once the limit is hit, every subsequent INSERT attempting to use that sequence will fail immediately.

Top 3 Causes

1. SERIAL Column Hitting the INT4 Ceiling (~2.1 Billion)

SERIAL uses a 4-byte integer under the hood. High-volume systems — logging tables, event trackers, order systems — can exhaust this faster than expected.

-- Check how close your sequences are to their limit
SELECT
    sequencename,
    last_value,
    max_value,
    ROUND((last_value::NUMERIC / max_value) * 100, 2) AS used_pct,
    (max_value - last_value) AS remaining
FROM pg_sequences
WHERE schemaname = 'public'
ORDER BY used_pct DESC;

2. Custom MAXVALUE Set Too Low

When a sequence is manually created with an artificially low MAXVALUE and NO CYCLE (the default), it will throw 2200H as soon as the cap is reached.

-- Example of a problematic sequence definition
CREATE SEQUENCE bad_sequence
    START 1
    INCREMENT 1
    MAXVALUE 10000   -- Way too low for production use
    NO CYCLE;

-- Check a specific sequence definition
SELECT * FROM pg_sequences WHERE sequencename = 'bad_sequence';

3. Rollbacks Silently Consuming Sequence Values

PostgreSQL sequences are non-transactional by design — rolled-back transactions do not return their consumed values. In retry-heavy applications or batch jobs, sequence values can be consumed far faster than actual committed rows suggest.

-- Demonstrate sequence consumption on rollback
BEGIN;
SELECT nextval('orders_order_id_seq'); -- value consumed
ROLLBACK;
-- The value is gone. nextval will skip it permanently.

-- You can check current sequence value without advancing it
SELECT last_value FROM orders_order_id_seq;

Quick Fix Solutions

Fix 1: Expand the sequence's MAXVALUE immediately (zero downtime)

-- Immediate relief with no table lock required
ALTER SEQUENCE orders_order_id_seq MAXVALUE 9223372036854775807;

Fix 2: Change the column type to BIGINT

-- Upgrade the column and its backing sequence
ALTER TABLE orders ALTER COLUMN order_id TYPE BIGINT;
ALTER SEQUENCE orders_order_id_seq MAXVALUE 9223372036854775807;

Fix 3: Reset sequence to current max (emergency recovery)

-- If inserts are already failing, resync the sequence
SELECT SETVAL(
    'orders_order_id_seq',
    (SELECT MAX(order_id) FROM orders),
    true
);

Fix 4: Migrate to IDENTITY column (recommended long-term)

-- Best practice for new tables in PostgreSQL 10+
CREATE TABLE orders_new (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Prevention Tips

1. Monitor sequence usage proactively.
Schedule the query below as a cron job or integrate it into your monitoring stack. Alert when usage exceeds 80%.

SELECT sequencename, last_value, max_value,
    ROUND((last_value::NUMERIC / NULLIF(max_value,0)) * 100, 2) AS used_pct
FROM pg_sequences
WHERE (last_value::NUMERIC / NULLIF(max_value,0)) > 0.8;

2. Standardize on BIGINT GENERATED ALWAYS AS IDENTITY or UUID for all new tables.
Treat SERIAL as a legacy type. BIGINT identity columns give you ~9.2 quintillion values, while UUID eliminates the exhaustion problem entirely.

-- Preferred modern pattern
CREATE TABLE events (
    event_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

Related Errors

• 23505 unique_violation — Can occur if CYCLE is enabled and reused values collide with existing primary keys.
• 55000 object_not_in_prerequisite_state — Raised when calling nextval() on an already-exhausted sequence without CYCLE.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.