PostgreSQL inserts fail with 'reached maximum value of sequence': moving an int primary key to bigint

#The symptom

Inserts into one table start failing and nothing else is wrong. The log shows one of these:

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

The first means the sequence itself is AS integer and has hit its ceiling. The second usually means a bigint sequence (all sequences created before PostgreSQL 10 are bigint) is still feeding an integer column, or a foreign key column is still integer.

#1. Find every key at risk

Run this in each database. It joins every integer/smallint column that owns a sequence (serial or identity) to that sequence's current value:

SELECT c.table_schema, c.table_name, c.column_name, c.data_type,
       s.data_type AS seq_type, s.last_value,
       round(100.0 * s.last_value /
             CASE c.data_type WHEN 'smallint' THEN 32767 ELSE 2147483647 END, 1) AS pct_used
FROM information_schema.columns c
JOIN pg_sequences s
  ON pg_get_serial_sequence(quote_ident(c.table_schema) || '.' || quote_ident(c.table_name),
                            c.column_name)
     = quote_ident(s.schemaname) || '.' || quote_ident(s.sequencename)
WHERE c.data_type IN ('integer', 'smallint')
ORDER BY pct_used DESC NULLS LAST;

last_value is NULL for a sequence that has never been used. Columns whose default calls nextval() on a sequence that is not OWNED BY the column won't show up here; search column_default LIKE 'nextval%' in information_schema.columns to catch them.

Then find the child columns that will need the same change:

SELECT conrelid::regclass AS child_table, a.attname AS child_column,
       format_type(a.atttypid, a.atttypmod) AS type
FROM pg_constraint con
JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = ANY (con.conkey)
WHERE con.contype = 'f'
  AND con.confrelid = 'public.orders'::regclass;

Alert on anything above 70%. At a few million inserts a day that still leaves months; at high volume it can be weeks.

#2. Already failing: buy time

If inserts are failing now, you need values today and a proper fix this week. If the key has never been negative and the application does not assume positive IDs, use the negative half of the range:

ALTER SEQUENCE public.orders_id_seq
  MINVALUE -2147483648
  RESTART WITH -2147483648
  INCREMENT BY 1;

That gives roughly two billion more values counting up towards zero before they collide with existing rows.

Warning

Check the application first. Code that sorts by ID to mean "newest", validates id > 0, or stores IDs in an unsigned or 32-bit field elsewhere will misbehave. Tell downstream consumers (ETL, CDC, reporting) before you do this. It is a stopgap, not the fix.

If negative IDs are not acceptable and the table is small enough to rewrite during an outage (see step 3), go straight to the rewrite.

#3. Fix option A: rewrite in place

For tables up to a few GB, or when you can take an outage, change the type directly:

SET lock_timeout = '5s';
BEGIN;
ALTER TABLE public.order_lines ALTER COLUMN order_id TYPE bigint;
ALTER TABLE public.orders      ALTER COLUMN id       TYPE bigint;
COMMIT;

Then confirm the sequence type:

SELECT sequencename, data_type, max_value FROM pg_sequences
WHERE sequencename = 'orders_id_seq';

-- If it is still integer:
ALTER SEQUENCE public.orders_id_seq AS bigint;

ALTER SEQUENCE ... AS bigint raises max_value automatically when the old maximum was the integer maximum. If you used the negative-range stopgap, also reset MINVALUE and RESTART above the current maximum ID.

Caution

ALTER COLUMN ... TYPE bigint rewrites the table and every index on it while holding an ACCESS EXCLUSIVE lock. Reads and writes stop for the whole duration, and you need free disk roughly equal to the table plus its indexes. Test the timing on a restored copy before choosing this path.

#4. Fix option B: online add, backfill and swap

For large tables that can't go offline, build a new column alongside the old one.

#Add the column and keep it in sync

ALTER TABLE public.orders ADD COLUMN id_new bigint;

CREATE OR REPLACE FUNCTION public.orders_sync_id() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END $$;

CREATE TRIGGER orders_sync_id
BEFORE INSERT OR UPDATE ON public.orders
FOR EACH ROW EXECUTE FUNCTION public.orders_sync_id();

Adding a nullable column with no default is a catalog-only change. (EXECUTE FUNCTION needs PostgreSQL 11+; older versions use EXECUTE PROCEDURE.)

#Backfill in batches

UPDATE public.orders
SET id_new = id
WHERE id BETWEEN 1 AND 100000 AND id_new IS NULL;

Drive the ranges from a script, commit each batch, and watch replication lag and autovacuum between batches. Each batch creates dead tuples, so expect some bloat.

#Build the constraint support without long locks

CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_uq ON public.orders (id_new);

ALTER TABLE public.orders
  ADD CONSTRAINT orders_id_new_nn CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE public.orders VALIDATE CONSTRAINT orders_id_new_nn;

VALIDATE CONSTRAINT scans the table but takes only a SHARE UPDATE EXCLUSIVE lock, so DML continues. On PostgreSQL 12+, a validated CHECK (col IS NOT NULL) lets SET NOT NULL skip its own full scan.

#Swap in one short transaction

SET lock_timeout = '5s';
BEGIN;
LOCK TABLE public.orders IN ACCESS EXCLUSIVE MODE;
ALTER TABLE public.order_lines DROP CONSTRAINT order_lines_order_id_fkey;
ALTER TABLE public.orders DROP CONSTRAINT orders_pkey;
ALTER TABLE public.orders ALTER COLUMN id_new SET NOT NULL;
ALTER TABLE public.orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_uq;
ALTER TABLE public.orders ALTER COLUMN id DROP DEFAULT;
ALTER TABLE public.orders RENAME COLUMN id TO id_old;
ALTER TABLE public.orders RENAME COLUMN id_new TO id;
ALTER SEQUENCE public.orders_id_seq AS bigint OWNED BY public.orders.id;
ALTER TABLE public.orders ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq');
DROP TRIGGER orders_sync_id ON public.orders;
ALTER TABLE public.orders DROP CONSTRAINT orders_id_new_nn;
COMMIT;

This version is for a serial column. If id is an identity column, use ALTER TABLE ... ALTER COLUMN id_old DROP IDENTITY and add identity to the new column with ALTER TABLE ... ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH <max+1>) in place of the sequence and default lines. Rehearse the whole script on a copy.

#Put the foreign keys back

Convert child columns first (same add-backfill-swap pattern if they are large), then recreate the foreign keys without a long lock:

ALTER TABLE public.order_lines
  ADD CONSTRAINT order_lines_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES public.orders (id) NOT VALID;
ALTER TABLE public.order_lines VALIDATE CONSTRAINT order_lines_order_id_fkey;

Drop id_old once the application has run cleanly for a while. Dropping a column is catalog-only; the space comes back as rows are rewritten.

Tip

Logical replication subscribers, Debezium connectors and data warehouse loads all see the type change. Change the target schema to bigint before you swap, or they will fail on the first value above 2,147,483,647.

#5. Verify

SELECT pg_get_serial_sequence('public.orders', 'id');
SELECT data_type FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders' AND column_name = 'id';
INSERT INTO public.orders (customer_id) VALUES (42) RETURNING id;

Check that the application's ORM mapping uses a 64-bit type. A Java int or C# Int32 field will fail as soon as a value crosses the old limit.

#Prevent it next time

• Use bigint (bigserial or bigint GENERATED ... AS IDENTITY) for every new surrogate key. The storage difference is 4 bytes per row and per index entry.
• Schedule the query from step 1 weekly and alert above 70%.
• Add a schema review rule that rejects new integer primary keys and integer FK columns pointing at bigint keys.
• Remember that rolled-back inserts, INSERT ... ON CONFLICT and sequence caching all burn values, so the sequence moves faster than the row count.