Fixing PostgreSQL 'integer out of range' Errors: A Safe Migration Guide

The Scenario

Your application is humming along perfectly until, suddenly, every new user registration or order placement fails. Your production logs are now screaming a single, cryptic message:

ERROR: integer out of range

This usually strikes during a standard INSERT on your most active table. It means a column—often an auto-incrementing primary key—has hit its absolute ceiling. The database simply refuses to accept new data, effectively freezing your application in its tracks.

Why This Happens

PostgreSQL uses a signed 32-bit integer for the standard INTEGER (or INT4) data type. This type has a hard limit:

• Minimum: -2,147,483,648
• Maximum: 2,147,483,647

Once your sequence or manual input attempts to store 2,147,483,648, the system fails. This is a classic "success problem." Your database grew faster than the original schema design expected, and you've finally exhausted the 31 bits available for positive numbers.

Quick Fix: The Direct Alter

Small tables—those with fewer than 5 million rows—can usually handle a direct change. If you can afford a few minutes of downtime or a brief table lock, use BIGINT (INT8). This expands your limit to a massive 9.22 quintillion, which is effectively infinite for most use cases.

-- Change the column type directly
ALTER TABLE orders ALTER COLUMN id TYPE bigint;

Warning: Be careful with this command. It triggers a full table rewrite and holds an ACCESS EXCLUSIVE lock. On a table with 200 million rows, this operation could take an hour, during which no one can read or write to the table.

The Safe Fix: Zero-Downtime Migration

High-traffic environments require a more surgical approach. You cannot lock your primary table for hours. Instead, use a multi-step strategy to move data in the background.

1. Add a temporary BIGINT column

Start by creating a new column that will eventually become your primary key.

ALTER TABLE orders ADD COLUMN id_new bigint;

2. Sync data in manageable batches

Never try to update 500 million rows in one go. You will bloat the Write-Ahead Log (WAL) and potentially crash your server. Instead, update in chunks of 50,000 to 100,000 rows.

-- Example of a batch update logic
UPDATE orders SET id_new = id 
WHERE id_new IS NULL 
AND id BETWEEN 1 AND 100000;

3. Keep data in sync with a trigger

While you migrate old data, new rows are still coming in. Create a trigger to automatically copy the id value into id_new for every new INSERT or UPDATE.

4. The Final Swap

Once the columns are synchronized, perform the swap inside a single transaction. This reduces the heavy locking window to just a few milliseconds.

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- Swap the names
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;
-- Update the sequence to point to the new column
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;
COMMIT;

Managing Sequences

If you use SERIAL columns, the error often stems from the column capacity, not the sequence itself. However, you must ensure the sequence doesn't reset or lag behind. Use this command to see your current position:

SELECT nextval('orders_id_seq');

If the migration causes the sequence to lose its place, manually sync it to the highest ID in your table:

SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

Verification

Check your work by describing the table in the psql console:

\d orders

The "Type" column for your ID should now read bigint. To confirm the fix, try inserting a value that previously would have failed:

INSERT INTO orders (id, status) VALUES (3000000000, 'verified');

If that row saves, your overflow issues are over.

Key Takeaways

• Default to BIGINT: For new projects, use BIGINT for all primary keys. The storage cost is 8 bytes instead of 4, but that is a tiny price to pay to avoid a production outage later.
• Check Foreign Keys: If you change a primary key to BIGINT, you must also change every foreign key that references it. If you don't, the JOIN operations will fail or become incredibly slow.
• Proactive Monitoring: Don't wait for a crash. Query information_schema.sequences regularly to find columns that have reached 80% of their 2.1 billion limit.