Migrate a Django Integer Primary Key to BigInt Without Downtime

Problem statement

An events or audit table created with Django's 32-bit AutoField is approaching 2,147,483,647, the maximum integer. When the sequence reaches it, every insert fails with nextval: reached maximum value of sequence or integer out of range, and the feature stops. Changing the field to BigAutoField and running migrate issues ALTER COLUMN id TYPE bigint, which rewrites the table and every referencing foreign key column under an exclusive lock, which can take hours on a large table.

1. Check headroom: compare the sequence's current value with 2,147,483,647 and estimate the date it runs out.
2. Small tables (minutes to rewrite): set id = models.BigAutoField(primary_key=True) and migrate in a maintenance window.
3. Large tables: add an id_new bigint column, keep it in sync with a trigger, backfill in batches, build a unique index concurrently, then swap columns and the primary key in a short transaction.
4. Convert foreign key columns in referencing tables the same way, or before the swap.
5. As a stopgap, a sequence can continue into negative values, but plan the real fix.

Step-by-step solution

1. Measure the risk

SELECT last_value, round(100.0 * last_value / 2147483647, 1) AS pct_used
FROM events_event_id_seq;

Compare growth over a week to estimate the exhaustion date. Do this for every high-volume table; Django's DEFAULT_AUTO_FIELD setting decides which tables are still 32-bit.

2. Small tables: the simple path

class Event(models.Model):
    id = models.BigAutoField(primary_key=True)

makemigrations generates AlterField on id and on foreign keys pointing at it. On PostgreSQL this rewrites the tables. Time it on a production copy; if it takes seconds or a few minutes, run it in a maintenance window.

3. Large tables: add and sync a new column

ALTER TABLE events_event ADD COLUMN id_new bigint;

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

CREATE TRIGGER events_event_sync_id BEFORE INSERT OR UPDATE ON events_event
FOR EACH ROW EXECUTE FUNCTION events_event_sync_id();

New and updated rows now get id_new. Backfill old rows in batches:

UPDATE events_event SET id_new = id WHERE id BETWEEN 1 AND 100000 AND id_new IS NULL;
-- repeat for each range, with pauses

Then build a unique index concurrently: CREATE UNIQUE INDEX CONCURRENTLY events_event_id_new_uniq ON events_event (id_new); and add NOT NULL via a validated check constraint to avoid a long scan under lock.

4. Swap in a short transaction

BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE events_event DROP CONSTRAINT events_event_pkey;
ALTER TABLE events_event RENAME COLUMN id TO id_old;
ALTER TABLE events_event RENAME COLUMN id_new TO id;
ALTER TABLE events_event ADD CONSTRAINT events_event_pkey PRIMARY KEY USING INDEX events_event_id_new_uniq;
ALTER SEQUENCE events_event_id_seq AS bigint OWNED BY events_event.id;
ALTER TABLE events_event ALTER COLUMN id SET DEFAULT nextval('events_event_id_seq');
DROP TRIGGER events_event_sync_id ON events_event;
COMMIT;

Foreign keys referencing events_event.id must be dropped before the swap and recreated afterwards (NOT VALID, then VALIDATE CONSTRAINT), and their columns converted to bigint with the same technique. Record the change in Django's state with SeparateDatabaseAndState so makemigrations agrees with the database. Drop id_old later.

5. Stopgap if time runs out

If the sequence is about to overflow and the conversion is not ready, you can restart it at the negative range:

ALTER SEQUENCE events_event_id_seq MINVALUE -2147483648 RESTART WITH -2147483648 INCREMENT BY 1;

This gives another two billion IDs, but code that assumes positive IDs or orders by ID for recency breaks. Use it only to buy time.

Explanation

Changing a column's type from integer to bigint changes its on-disk size, so PostgreSQL must rewrite every row and rebuild indexes, holding an ACCESS EXCLUSIVE lock throughout. Foreign key columns referencing the key must change too. The online method builds the new column and its index while the table stays available, keeps it consistent with a trigger, and limits the exclusive lock to a quick swap of names and constraints. Tools such as pg-osc or pgroll automate variants of this approach.

Edge cases / notes

• Logical replication and CDC. Column swaps can confuse replication consumers; coordinate with downstream systems.
• Many referencing tables. Each foreign key adds steps; convert child columns first so the swap only touches constraints.
• MySQL. Use gh-ost or pt-online-schema-change, which copy the table with triggers and swap it in.

Next steps

• Decide defaults for new tables with Fix Django models.W042.
• Learn the lock-safe patterns in Run Django Schema Changes on Large Tables.
• Run the backfill well with Run a Django Data Migration on a Large Table.

FAQ

How do I know if my table will run out of IDs?

Compare the sequence's last_value with 2,147,483,647 and extrapolate from recent growth.

Is changing AutoField to BigAutoField safe?

The migration is correct but rewrites the table under a lock. On large tables use an online swap.

Do foreign keys need converting too?

Yes. Columns referencing the primary key must become bigint as well.

Can I just let the sequence go negative?

As a short-term stopgap, yes, but code assuming positive or increasing IDs may break.