PostgreSQL "integer out of range": When Your int Primary Key Runs Out

PostgreSQL "integer out of range" on a busy table usually means one thing: the integer primary key has reached 2,147,483,647 and there are no numbers left. Inserts stop, the application starts throwing errors, and the obvious fix, ALTER COLUMN id TYPE bigint, locks the table for as long as it takes to rewrite it. On a table with two billion rows that can take hours.

This is a slow, predictable incident. The counter grows by a known amount every day, so you can see it coming months ahead. Below: how the error shows up, how to find tables at risk, what to do if you are already out of IDs, and how to move to bigint online.

Two error messages, one cause

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

ERROR:  integer out of range

The integer type is 4 bytes and stores values from -2147483648 to +2147483647. bigint is 8 bytes and goes up to about 9.2 quintillion. Which message you see depends on the sequence behind the column:

• nextval: reached maximum value of sequence means the sequence itself is the limit. Since PostgreSQL 10, serial creates a sequence AS integer, so the sequence stops at 2147483647 together with the column.
• integer out of range on insert usually means the sequence is bigint but the column is not. Sequences created before PostgreSQL 10 generated 64-bit values, so tables that came through upgrades often look like this. The sequence happily returns 2147483648 and the cast into the integer column fails.

The same message also appears in plain arithmetic, for example when two integer values are multiplied and the result does not fit. If the failing statement is a report rather than an insert, look at the expressions before you look at the keys.

Find tables that are close to the limit

The query below lists every integer column backed by a sequence, serial or identity, and shows how much of the int range is used. It relies on pg_get_serial_sequence and the pg_sequences view.

SELECT c.oid::regclass AS table_name, a.attname AS column_name,
       s.data_type AS seq_type, s.last_value,
       round(100.0 * s.last_value / 2147483647, 1) AS pct_of_int_max
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r', 'p')
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT pg_get_serial_sequence(format('%I.%I', n.nspname, c.relname), a.attname) AS seq
) g
JOIN pg_sequences s
  ON format('%I.%I', s.schemaname, s.sequencename)::regclass = g.seq::regclass
WHERE a.atttypid = 'integer'::regtype
  AND a.attnum > 0 AND NOT a.attisdropped
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pct_of_int_max DESC NULLS LAST;

Two things to keep in mind. last_value is the last value written to disk, so with caching it can be slightly ahead of what has actually been handed out. And it reads as NULL if the sequence has never been used or if your role lacks privileges on it, so run the check as a role that can see everything.

We alert at 50% and treat 75% as a project that must be scheduled now. That is a rule of thumb, not a standard. What really matters is the rate: take the value twice a week apart and divide the remaining numbers by the daily growth. "Eight months left" and "three weeks left" need very different plans.

Already out of IDs: the first hour

If inserts are failing right now, a full rewrite is not an option for a large table. Two things buy time:

1. Use the negative range. An integer also holds about 2.1 billion negative values. Restarting the sequence there with ALTER SEQUENCE gets inserts working again in seconds:

   ALTER SEQUENCE orders_id_seq MINVALUE -2147483648 RESTART WITH -2147483648;

   This only works if nothing in the application assumes that IDs are positive or that a higher ID means a newer row. Check sorting, pagination by ID and any code that treats negative numbers as special before you do it. It is a bridge to the migration, not a fix.
2. Find what burns IDs. Values from nextval are not reused when a transaction aborts, and an INSERT ... ON CONFLICT takes its number before it detects the conflict. A retry loop or an upsert-heavy job can use up the range much faster than the row count suggests. Slowing it down stretches the time the bridge gives you.

Then plan the real fix while the bridge holds. Note that ALTER SEQUENCE blocks concurrent nextval calls while it runs, so expect a short pause on a hot table.

The simple fix, and why it hurts on big tables

BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE orders ALTER COLUMN id TYPE bigint;   -- rewrites table and indexes
ALTER SEQUENCE orders_id_seq AS bigint;           -- otherwise it still stops at 2147483647
COMMIT;

The ALTER TABLE documentation is clear about the cost. Changing a column type "will normally cause the entire table and its indexes to be rewritten". integer to bigint is not binary compatible, so the rewrite happens. The command holds an ACCESS EXCLUSIVE lock the whole time, so even reads wait, and a rewrite can temporarily need as much as double the disk space.

The second line matters too. A sequence created AS integer keeps its old maximum until you change it. According to the docs, changing the type moves the minimum and maximum automatically only if they were the defaults of the old type. If someone set an explicit MAXVALUE, change it as well.

For small tables this is fine in a maintenance window. For large ones, go online.

Online int to bigint migration, step by step

This is the standard expand and contract approach we describe in our zero-downtime PostgreSQL migrations guide. The example uses a table orders with a serial primary key id.

1. Add the new column and keep it in sync. Adding a column with no default only changes metadata, so it is fast even on huge tables. It still needs a brief exclusive lock, so set lock_timeout and retry if it times out.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN id_new bigint;      -- no default: metadata only

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

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

2. Backfill in batches. Small transactions, a pause between them, and an eye on replication lag. Every updated row creates a new row version, so autovacuum will have work to do.

-- repeat with the next range until max(id) is covered
UPDATE orders SET id_new = id
WHERE id >= 1 AND id < 50001 AND id_new IS NULL;

3. Build the index and prove there are no NULLs, without blocking writes. CREATE UNIQUE INDEX CONCURRENTLY builds the future primary key index while the table stays writable. The CHECK ... NOT VALID constraint followed by VALIDATE lets the later SET NOT NULL skip its full table scan, which the docs describe explicitly.

CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_idx ON orders (id_new);

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

4. Swap in one short transaction. Each statement here is quick: no table scan and no rewrite. ADD PRIMARY KEY USING INDEX attaches the existing index instead of building a new one.

BEGIN;
SET LOCAL lock_timeout = '5s';
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
ALTER SEQUENCE orders_id_seq AS bigint;
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;   -- scan skipped thanks to the CHECK
ALTER TABLE orders DROP CONSTRAINT orders_pkey;        -- drop referencing FKs first
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_idx;
DROP TRIGGER orders_id_sync ON orders;
ALTER TABLE orders ALTER COLUMN id_new SET DEFAULT nextval('orders_id_seq');
ALTER SEQUENCE orders_id_seq OWNED BY orders.id_new;   -- before the old column goes
ALTER TABLE orders DROP COLUMN id;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders DROP CONSTRAINT orders_id_new_nn;
COMMIT;

Pay attention to the order. The sequence is OWNED BY the old column, and dropping that column would drop the sequence with it, so move ownership first. The trigger references the old column name, so it goes before the rename. DROP COLUMN is fast because it does not physically remove the data; the space comes back as rows are rewritten over time.

5. Re-create foreign keys without a long lock. If other tables reference orders.id, you have to drop those foreign keys before step 4, because DROP CONSTRAINT orders_pkey will refuse while they depend on it. Add them back as NOT VALID and validate separately. Validation takes only a SHARE UPDATE EXCLUSIVE lock on the child table and a ROW SHARE lock on the referenced one, so normal writes continue:

ALTER TABLE order_items ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_id_fkey;

Identity columns need a slightly different swap (DROP IDENTITY on the old column, ADD GENERATED ... AS IDENTITY with the right start value on the new one). Write that script out and rehearse it on a full-size copy before running it in production.

On PostgreSQL 18 you can also add a not-null constraint itself as NOT VALID and validate it later, according to the release notes. The CHECK approach above works on all supported versions.

What changes on a multi-terabyte table

• The backfill is the long part. Two billion row updates generate a lot of WAL and leave a dead version of every row until vacuum cleans it up. Size the batches by WAL rate and replica lag, not by what feels fast. Plan for days, not hours, and make the job restartable from the last ID it processed.
• Disk usage goes up before it goes down. Each backfilled row gets a new version, and the new column and index add space. After DROP COLUMN the old data stays on disk until rows are rewritten. Check free space on the primary and every replica before you start.
• Lock queues are the real downtime risk. The swap transaction is fast, but it needs ACCESS EXCLUSIVE. If a long query holds a lock on the table, the swap waits and every new query queues behind it. Short lock_timeout, a retry loop and a check for long transactions just before the swap are mandatory.
• Logical replication does not follow you. DDL is not replicated to subscribers. Change the subscriber column to bigint first, so incoming values above 2147483647 still fit, then the publisher. Sequence values are not replicated either, which matters if a subscriber may ever become the primary.
• Partitioned tables need care per partition. ADD ... USING INDEX is not supported on partitioned tables, according to the docs, so the swap has to be planned differently there. Many partitions also mean many indexes to build.
• Watch the other 2-billion counter. Every row the backfill rewrites gets a new transaction ID stamped on it, and vacuum will have to freeze all of those versions later. Expect the next anti-wraparound vacuum on this table to be heavier than usual and keep an eye on XID age; we explain why it matters in our transaction ID wraparound article.