Migrating an integer primary key to bigint

On a small table, ALTER COLUMN TYPE bigint with a lock timeout is the whole migration; on a large one it rewrites every row and index under an exclusive lock, so add a nullable bigint column, backfill it in batches, build a unique index concurrently, and swap the key in a short window. This guide belongs to zero-downtime schema migration strategies.

Quick Answer

An integer primary key stops at 2,147,483,647. The first insert past it fails, and so does every one after.

The symptom, and the migration that is only viable while the table is small:

# The failure, once the sequence runs out:
# sqlalchemy.exc.DataError: (asyncpg.exceptions.NumericValueOutOfRangeError)
# integer out of range

# alembic/versions/e41a_orders_bigint.py — fine on a small table, an outage on a large one.
from alembic import op
import sqlalchemy as sa


def upgrade() -> None:
    op.execute("SET lock_timeout = '5s'")
    op.alter_column("orders", "id", existing_type=sa.Integer(), type_=sa.BigInteger())
    op.alter_column("order_lines", "order_id", existing_type=sa.Integer(),
                    type_=sa.BigInteger())
    op.execute("ALTER SEQUENCE orders_id_seq AS bigint")

The staged version, for a table that cannot be locked:

# Release 1 — add the column and keep it current for new rows.
from alembic import op
import sqlalchemy as sa


def upgrade() -> None:
    op.add_column("orders", sa.Column("new_id", sa.BigInteger(), nullable=True))
    op.execute("CREATE SEQUENCE IF NOT EXISTS orders_new_id_seq AS bigint")
    op.execute("""
        CREATE FUNCTION orders_fill_new_id() RETURNS trigger AS $$
        BEGIN
            NEW.new_id := COALESCE(NEW.new_id, nextval('orders_new_id_seq'));
            RETURN NEW;
        END $$ LANGUAGE plpgsql;
    """)
    op.execute("""
        CREATE TRIGGER orders_fill_new_id BEFORE INSERT ON orders
        FOR EACH ROW EXECUTE FUNCTION orders_fill_new_id();
    """)
    # Start the new sequence above every existing id.
    op.execute("SELECT setval('orders_new_id_seq', (SELECT COALESCE(max(id), 0) + 1 FROM orders))")

The backfill then runs as a batched job, the unique index is built concurrently, and only the final swap needs exclusive locks — seconds rather than hours. Each stage is described below.

Execution Context & Async Workflow Integration

ALTER TABLE ... ALTER COLUMN ... TYPE bigint is a rewrite: PostgreSQL writes a new copy of every row, because the column's storage width changes from four bytes to eight. Every index on the table is rebuilt as part of it, and the whole operation holds an ACCESS EXCLUSIVE lock, which blocks reads as well as writes. On a table of a hundred million rows that is measured in hours.

Worse, it does not stop at one table. Every foreign key column referencing the key must become bigint too — a reference from an integer column cannot hold the new values — so each child table is its own rewrite, and the foreign keys are revalidated afterwards.

That is why the decision is driven by table size rather than by preference. Up to a few million rows, the in-place change with a lock_timeout is minutes and is by far the simplest thing to do. Past that, the staged approach is the only option that keeps the table serving traffic:

1. Add a nullable bigint column, which is a catalogue change, plus a trigger that fills it for new inserts and a sequence started above the current maximum id.
2. Backfill existing rows in batches, committing each — the restartable loop from processing large tables in batches with partitions.
3. Build a unique index concurrently on the new column, then check pg_index.indisvalid.
4. Swap in a short window: promote the new unique index to the primary key, repoint the foreign keys, and move the sequence ownership.
5. Drop the old column in a later release, following the unmap-then-drop rule from dropping a column safely during a rolling deploy.

The application side is smaller than it looks. Mapped[int] covers both types in Python — there is no separate integer width — so the model change is mapped_column(BigInteger, primary_key=True) and nothing else. asyncpg returns both as Python int, which has arbitrary precision.

Where the width does leak out is at serialisation boundaries. JSON numbers are doubles in most consumers, and JavaScript loses integer precision above 2^53, which is far below the bigint maximum but far above the integer one. An API that will eventually issue ids past 2^53 should serialise them as strings — a decision worth making during this migration rather than after it, because changing an id's JSON type later is a breaking API change.

Resolving Warnings, Errors & Common Mistakes

The sequence itself is the detail most often forgotten, because it is a separate object from the column. In PostgreSQL 10 and later, an identity or serial column owns a sequence whose own type must also be widened:

from alembic import op


def upgrade() -> None:
    # Widen the sequence as well as the column, or it keeps issuing 32-bit values.
    op.execute("ALTER SEQUENCE orders_id_seq AS bigint")
    op.execute("ALTER SEQUENCE orders_id_seq MAXVALUE 9223372036854775807")

Finding every referencing column is a catalogue query rather than a memory exercise, and worth running before writing the migration:

from sqlalchemy import text

REFERENCING_COLUMNS = text("""
    SELECT c.conrelid::regclass AS child_table,
           a.attname            AS child_column,
           format_type(a.atttypid, a.atttypmod) AS child_type
    FROM pg_constraint c
    JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
    WHERE c.contype = 'f' AND c.confrelid = 'orders'::regclass
    ORDER BY 1, 2
""")

Any child column reported as integer is part of this migration. On a schema with several levels of references, that query is what turns "the orders table" into the actual list of tables to change.

Advanced: The Swap Window and Its Rollback

Stages one to three touch nothing exclusively; the swap is the only part that needs a window, and it should be short enough to fit inside a lock timeout. The work is all catalogue changes, provided the index already exists:

from alembic import op


def upgrade() -> None:
    op.execute("SET lock_timeout = '5s'")

    # 1. The old primary key becomes an ordinary unique constraint for now.
    op.execute("ALTER TABLE orders DROP CONSTRAINT orders_pkey")

    # 2. Promote the concurrently built unique index to the primary key.
    op.execute("ALTER TABLE orders ADD CONSTRAINT orders_pkey "
               "PRIMARY KEY USING INDEX orders_new_id_key")

    # 3. Point the sequence at the new column and make it the default.
    op.execute("ALTER TABLE orders ALTER COLUMN new_id SET DEFAULT "
               "nextval('orders_new_id_seq')")
    op.execute("ALTER SEQUENCE orders_new_id_seq OWNED BY orders.new_id")
    op.execute("ALTER TABLE orders ALTER COLUMN new_id SET NOT NULL")

    # 4. The trigger is no longer needed: the default does the work.
    op.execute("DROP TRIGGER orders_fill_new_id ON orders")
    op.execute("DROP FUNCTION orders_fill_new_id()")

PRIMARY KEY USING INDEX is what keeps this fast: the index was built concurrently in stage three, so promoting it validates nothing and rewrites nothing. SET NOT NULL does scan the table on PostgreSQL versions before 12; on 12 and later it can use a pre-existing CHECK (new_id IS NOT NULL) NOT VALID that was already validated, which is worth adding in stage two if the table is very large.

Child tables are the other half of the swap, and they need their own bigint columns backfilled from the parent's mapping before their foreign keys can be repointed. For a schema with several child tables this is where most of the work is, and it is the reason the whole procedure is worth avoiding by acting early.

Rollback is worth planning explicitly, because the swap is the one irreversible-feeling step. In fact it is reversible while the old column still exists: the old unique constraint can be promoted back to primary key, and the default and trigger restored. That is precisely why stage five — dropping the old column — waits for a later release. Until it runs, the migration can be undone in seconds.

Two operational notes for the window. Announce it and run it at a quiet time, because although the locks are brief, they are exclusive, and every query arriving during them queues. And run it as a single migration with lock_timeout set, so a failure to acquire a lock aborts cleanly rather than queueing traffic behind it — the lock-budget argument from managing enums, constraints and indexes in migrations.

Monitoring Sequence Headroom

Every part of this migration is easier before the sequence runs out, and the only thing standing between a planned change and an outage is a query that nobody is running. It costs nothing and belongs in whatever checks your database already has.

from sqlalchemy import text

SEQUENCE_HEADROOM = text("""
    SELECT schemaname || '.' || sequencename AS sequence,
           last_value,
           max_value,
           round(100.0 * last_value / max_value, 2) AS pct_used
    FROM pg_sequences
    WHERE last_value IS NOT NULL
      AND 100.0 * last_value / max_value > :threshold
    ORDER BY pct_used DESC
""")


async def sequences_running_low(session, threshold: float = 50.0) -> list[dict]:
    rows = await session.execute(SEQUENCE_HEADROOM, {"threshold": threshold})
    return [dict(row._mapping) for row in rows]

pg_sequences reports max_value per sequence, so the percentage is meaningful whether the sequence is integer-bounded or bigint. Alerting at seventy percent gives room to plan; alerting at ninety-five does not, because the staged migration on a large table takes days of elapsed time even though almost none of it is downtime.

Two related checks are worth having alongside it.

Column types on new tables. A bigint primary key costs four extra bytes per row and removes this problem permanently, so new tables should simply start there. A test over the metadata enforces it:

import pytest
from sqlalchemy import BigInteger, Integer

from shop.models import Base

ALLOWED_INTEGER_KEYS = {"calendar_days", "currencies"}     # small, bounded lookup tables


def test_primary_keys_are_bigint():
    for table in Base.metadata.sorted_tables:
        if table.name in ALLOWED_INTEGER_KEYS:
            continue
        for column in table.primary_key.columns:
            if isinstance(column.type, Integer) and not isinstance(column.type, BigInteger):
                pytest.fail(f"{table.name}.{column.name} is a 32-bit integer primary key")

Consumption rate, not just percentage. A sequence at forty percent that consumes ten percent a month needs attention sooner than one at eighty percent that has not moved in a year. Recording last_value daily and looking at the slope answers "when", which is the number that decides whether this is next quarter's work or next week's.

One last consideration: sequences are consumed by failed inserts too. An ON CONFLICT DO NOTHING upsert loop, or a retry storm, can burn through sequence values far faster than the row count suggests — a table with ten million rows can have a sequence at two billion. That is worth knowing before assuming the headroom matches the data volume, and it is another argument for bigint from the start.

Frequently Asked Questions

Related

• Zero-Downtime Schema Migration Strategies — The parent guide: compatibility windows and lock-safe DDL.
• Dropping a column safely during a rolling deploy — Removing the old column after the swap.
• Creating indexes concurrently in Alembic migrations — Building the unique index the swap promotes.
• Processing large tables in batches with partitions — The restartable backfill loop.