Renaming a column without downtime using expand and contract

Do not rename in place during a rolling deploy — add the new column, keep both in sync with a trigger, backfill in batches, switch the model to the new column, and drop the old one in a later release; or, if only Python needs the new name, map it with full_name: Mapped[str] = mapped_column("name") and skip the migration entirely. This guide belongs to zero-downtime schema migration strategies.

Quick Answer

An in-place rename is a single fast statement, and it breaks every running instance of the previous release the moment it commits.

Before — renaming in one migration:

# alembic/versions/a1c0_rename_customer_name.py
from alembic import op


def upgrade() -> None:
    op.alter_column("customers", "name", new_column_name="full_name")

# Old pods, still running during the rollout:
# sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError)
# <class 'asyncpg.exceptions.UndefinedColumnError'>: column customers.name does not exist

After — release 1, expand: add the column and keep it in sync:

# alembic/versions/b7d2_expand_customer_full_name.py
import sqlalchemy as sa
from alembic import op


def upgrade() -> None:
    op.add_column("customers", sa.Column("full_name", sa.String(200), nullable=True))
    op.execute("""
        CREATE FUNCTION customers_sync_full_name() RETURNS trigger AS $$
        BEGIN
            IF TG_OP = 'INSERT' THEN
                NEW.full_name := COALESCE(NEW.full_name, NEW.name);
                NEW.name := COALESCE(NEW.name, NEW.full_name);
            ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
                NEW.name := NEW.full_name;
            ELSIF NEW.name IS DISTINCT FROM OLD.name THEN
                NEW.full_name := NEW.name;
            END IF;
            RETURN NEW;
        END $$ LANGUAGE plpgsql;
    """)
    op.execute("""
        CREATE TRIGGER customers_sync_full_name
        BEFORE INSERT OR UPDATE ON customers
        FOR EACH ROW EXECUTE FUNCTION customers_sync_full_name();
    """)


def downgrade() -> None:
    op.execute("DROP TRIGGER customers_sync_full_name ON customers")
    op.execute("DROP FUNCTION customers_sync_full_name()")
    op.drop_column("customers", "full_name")

The model in this release is unchanged — it still maps name. Old and new pods both work, and every write from either keeps full_name current.

Execution Context & Async Workflow Integration

A rolling deploy runs two versions of the application at once for several minutes, and both talk to one database. Any schema change has to be compatible with both versions for that window. A rename is not: the old version's SQL names a column the new schema does not have, and the new version's SQL names a column the old schema does not have.

The ORM makes this sharper than it would be with hand-written SQL. SQLAlchemy never emits SELECT *; it lists every mapped column explicitly, so a select(Customer) in the old release names customers.name even in code paths that never read the attribute. Every query touching the table fails, not just the ones that use the renamed field. Under asyncpg the error arrives as ProgrammingError wrapping UndefinedColumnError, and on connections with cached prepared statements it can also surface as InvalidCachedStatementError for statements prepared before the migration.

Expand and contract splits the change so that every intermediate state is compatible with the releases on either side of it:

1. Expand. Add full_name, nullable, with a trigger syncing the two columns. The release that ships this migration still maps name. Old and new pods are identical.
2. Backfill. Copy name into full_name for existing rows, in batches, from a job rather than the migration.
3. Switch. Release a model that maps full_name. During this rollout, old pods write name and new pods write full_name, and the trigger keeps both current.
4. Contract. Once no running code references name, drop the trigger and the column.

Each migration is run before the application release that depends on it, the same ordering rule as in adding a NOT NULL column without locking in Postgres. Adding a nullable column without a default is catalog-only, creating a trigger takes a brief lock, and dropping a column is catalog-only too — PostgreSQL marks it dropped and reclaims space later — so none of the migrations holds a long lock. The slow part, the backfill, runs outside any migration.

Resolving Warnings, Errors & Common Mistakes

The last two rows share a lesson: the application is rarely the only reader. Views, materialised views, reports in a BI tool, a data pipeline and a second service may all select the old column, and none of them are in your repository. Before the contract migration, check dependencies in the catalog and look at what actually queries the column:

from sqlalchemy import text


async def column_dependents(session, table: str, column: str) -> list[str]:
    rows = await session.execute(text("""
        SELECT DISTINCT dependent.relname
        FROM pg_depend d
        JOIN pg_rewrite r ON r.oid = d.objid
        JOIN pg_class dependent ON dependent.oid = r.ev_class
        JOIN pg_class source ON source.oid = d.refobjid
        JOIN pg_attribute a ON a.attrelid = source.oid AND a.attnum = d.refobjsubid
        WHERE source.relname = :table AND a.attname = :column
          AND dependent.oid <> source.oid
    """), {"table": table, "column": column})
    return [row[0] for row in rows]

With pg_stat_statements enabled, searching its query texts for the old column name over a few days of traffic finds readers that the catalog cannot, such as an external reporting job.

Advanced: The Backfill, the Switch and the Python-Only Alternative

The backfill copies existing values in bounded batches, each in its own transaction, so no statement holds row locks on a large share of the table and replicas keep up:

import asyncio

from sqlalchemy import text
from sqlalchemy.ext.asyncio import async_sessionmaker

BATCH = text("""
    WITH batch AS (
        SELECT id FROM customers
        WHERE full_name IS NULL AND name IS NOT NULL
        ORDER BY id
        LIMIT :size
        FOR UPDATE SKIP LOCKED
    )
    UPDATE customers c SET full_name = c.name
    FROM batch WHERE c.id = batch.id
""")


async def backfill_full_name(Session: async_sessionmaker, size: int = 2_000) -> int:
    total = 0
    while True:
        async with Session() as session:
            result = await session.execute(BATCH, {"size": size})
            await session.commit()
        total += result.rowcount
        if result.rowcount < size:
            return total
        await asyncio.sleep(0.05)   # let replication and autovacuum breathe

Because the trigger already handles every write, the backfill only has to cover rows nobody has touched since the expand release, and it is safe to stop and restart.

The switch release changes the model to map the new column, and once the backfill is complete a follow-up migration can make it NOT NULL, using the CHECK ... NOT VALID approach for large tables:

from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    full_name: Mapped[str] = mapped_column(String(200))
    # `name` is no longer mapped: this release never selects it.

Removing name from the model before the contract migration is essential for the same reason renaming broke things: the ORM lists every mapped column, so a model that still mapped name would fail the moment it was dropped.

Finally, the question to ask before starting: does the database column need a new name, or only the Python attribute? SQLAlchemy separates the two. full_name: Mapped[str] = mapped_column("name", String(200)) gives Python code Customer.full_name while every query still uses the name column. No migration, no deploy ordering, no trigger. If the only people who ever see the column name are Python developers, that single line is the whole change, and using mapped_column instead of Column covers the mapping options.

Renaming a Table: One Release With an Updatable View

Tables are easier to rename safely than columns, because PostgreSQL can make the old name keep working at no cost. A simple view — one table, no aggregates, no joins — is automatically updatable: inserts, updates and deletes against the view are applied to the underlying table. Rename the table and create a view under the old name in the same migration, and old and new releases both work immediately.

from alembic import op


def upgrade() -> None:
    op.rename_table("customer", "customers")
    op.execute("CREATE VIEW customer AS SELECT * FROM customers")


def downgrade() -> None:
    op.execute("DROP VIEW customer")
    op.rename_table("customers", "customer")

The old release keeps selecting from and writing to customer, which is now the view; the new release's models use __tablename__ = "customers". Both migrations are catalog-only, so there is no lock of any duration worth measuring. Once no code references the old name, a later migration drops the view.

Three caveats keep this from being entirely free. SELECT * in a view is expanded when the view is created, so a column added to customers afterwards does not appear in customer — which is fine as long as only old code uses the view, and old code does not know about the new column either. INSERT ... RETURNING through the view works, but sequence-backed defaults are evaluated by the underlying table, so the ORM's primary-key fetch through RETURNING behaves normally. And foreign keys, indexes and triggers belong to the table and move with the rename, while their names do not change — customer_pkey stays customer_pkey until you rename it, which a metadata naming convention will immediately notice and propose to fix.

The same trick does not extend to columns in a useful way. A view can alias full_name AS name, but the table itself cannot have two names for one column, and the ORM of each release addresses the table, not a view. For columns, the trigger-based sequence above remains the general answer — or the Python-only rename, when the database name was never the problem.

Frequently Asked Questions

Related

• Zero-Downtime Schema Migration Strategies — The parent guide: compatibility windows and lock-safe DDL.
• Adding a NOT NULL column without locking in Postgres — Tightening the new column once the backfill is complete.
• Writing data migrations safely in Alembic — Where backfills belong, and how to batch them.
• Detecting column type and server default changes in autogenerate — Type changes that need the same expand and contract treatment.