Dropping a column safely during a rolling deploy

Split the change into two releases: the first stops mapping the column while it stays in the database, the second drops it once nothing deployed refers to it — because SQLAlchemy lists every mapped column in every SELECT, so dropping a column the previous release still maps fails all of its queries for that table. This guide belongs to zero-downtime schema migration strategies.

Quick Answer

The drop itself is fast. What breaks is the release that is still running, and it breaks completely rather than partially.

Before — the model change and the drop in one release:

# models.py: the fax attribute is deleted from Order.
# alembic/versions/c81f_drop_fax.py
from alembic import op


def upgrade() -> None:
    op.drop_column("orders", "fax")

# Pods from the previous release, still serving traffic:
# sqlalchemy.exc.ProgrammingError: (asyncpg.exceptions.UndefinedColumnError)
# column orders.fax does not exist
# — on every query that loads an Order, not just the ones that read fax.

After — release one unmaps it, release two drops it:

# Release 1 — models.py only. No migration at all.
class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    # fax is intentionally not mapped: the column still exists in the database and
    # is dropped in release 2. Do not re-add it.

# Release 2 — a revision containing only the drop.
"""drop orders.fax

Revision ID: d90a4b1c6e22
Revises: c81f2a7d5b03
"""
from alembic import op
import sqlalchemy as sa

revision = "d90a4b1c6e22"
down_revision = "c81f2a7d5b03"

# destructive: orders.fax unmapped in release 41, archived to orders_fax_archive 2026-09-10


def upgrade() -> None:
    op.drop_column("orders", "fax")


def downgrade() -> None:
    # The column can be recreated; its values cannot. Restore from the archive if needed.
    op.add_column("orders", sa.Column("fax", sa.String(length=32), nullable=True))

Between the two releases the column exists and nothing uses it, which is a perfectly stable state to sit in for a week — and the state that makes a rollback possible at every point.

Execution Context & Async Workflow Integration

SQLAlchemy never emits SELECT *. A mapped class produces a SELECT listing each of its mapped columns by name, which has two consequences that together define this whole procedure.

An unmapped column is invisible. Removing an attribute from the model means no query mentions that column, so the column can sit in the database indefinitely with no effect on the application. That is what makes the two-release split free: there is no cost to leaving the column in place.

A mapped column is mandatory. If the model lists it and the database does not have it, every query for that entity fails — including queries that never touch the attribute. This is why dropping a column is not a partial outage: it takes out all reads of the table for the previous release.

The release ordering follows from those two facts. Migrations run before the code that depends on them, which is right for additive changes and exactly backwards for destructive ones. A drop has to run after the last release that maps the column has been replaced, which means it cannot travel with the model change.

Three other dependencies are worth checking before the second release, and only the first is in your repository.

Other services. A shared database usually has more than one reader. pg_stat_statements is the place to look, because it records the query texts the database has actually seen:

from sqlalchemy import text

USES_COLUMN = text("""
    SELECT calls, query
    FROM pg_stat_statements
    WHERE query ILIKE '%' || :column_name || '%'
    ORDER BY calls DESC
    LIMIT 20
""")

Database objects. A view, index, constraint or trigger referring to the column makes the drop fail with cannot drop column ... because other objects depend on it, or — with CASCADE — silently drop the view too. The dependency query in renaming a column without downtime lists them.

Constraints that could reject writes. While the column still exists and is unmapped, inserts from the new release will not supply it — so it must be nullable or have a default. A NOT NULL column that is no longer written is the one case where the intermediate state is not stable, and it needs the constraint dropped in release one.

On PostgreSQL the drop itself is a catalogue change: the column is marked dropped, the space is reclaimed as rows are later updated, and the lock is brief. That is worth knowing because it removes the temptation to bundle the drop with other work "since we have a maintenance window anyway".

Resolving Warnings, Errors & Common Mistakes

The autogenerate row is the one that determines whether this procedure is followed in practice. Removing an attribute produces a migration with a drop_column in it, and a developer following the normal workflow will commit exactly the change this guide warns against. A process_revision_directives hook that refuses destructive operations unless explicitly acknowledged is what turns the procedure into the default, and it is described in blocking destructive operations in autogenerated migrations.

Archiving deserves a decision rather than an assumption. DROP COLUMN discards the values, and no downgrade restores them; if there is any chance the data will be wanted, copy it first:

from alembic import op


def upgrade() -> None:
    op.execute("""
        CREATE TABLE orders_fax_archive AS
        SELECT id AS order_id, fax, now() AS archived_at
        FROM orders
        WHERE fax IS NOT NULL
    """)
    op.drop_column("orders", "fax")

Doing the archive and the drop in one revision is deliberate: they are atomic together, so there is no state in which the column is gone and the archive was not written. For a large table, CREATE TABLE AS reads every row, so it belongs in a batched job before the migration rather than inside it.

Advanced: Dropping Columns With Constraints, Indexes and NOT NULL

A bare nullable column is the easy case. Four variations need an extra step in release one, and all of them are about making the intermediate state stable.

A NOT NULL column cannot survive a release that stops writing it: inserts will fail. Drop the constraint first, which is a catalogue change:

# Release 1's migration — only the constraint, not the column.
from alembic import op
import sqlalchemy as sa


def upgrade() -> None:
    op.alter_column("orders", "fax", existing_type=sa.String(32), nullable=True)

A column with a unique constraint or index should have it dropped in release one too. It costs nothing to keep, but an index on a column nobody writes is pure overhead on every insert, and dropping it early shortens the second release to the column alone.

A column referenced by a view needs the view changed before the column can go. Whether that is a release of its own depends on who reads the view: if another service does, the view's shape is an interface, and changing it needs the same two-release treatment.

A column that is part of a composite index means the index has to be rebuilt without it. CREATE INDEX CONCURRENTLY for the replacement, then drop the old one — the sequence in creating indexes concurrently in Alembic migrations.

A useful way to organise all of this is to treat release one as "make the column irrelevant" and release two as "remove it". Release one may therefore contain a migration — dropping constraints and indexes — even though it does not touch the column itself:

# Release 1: the column becomes irrelevant.
def upgrade() -> None:
    op.drop_index("ix_orders_fax", table_name="orders")
    op.alter_column("orders", "fax", existing_type=sa.String(32), nullable=True)
    op.execute("ALTER TABLE orders ALTER COLUMN fax DROP DEFAULT")

Dropping the default matters for the same reason as the NOT NULL: a default is only applied on insert, so it is harmless — but leaving it means the column keeps accumulating values nobody reads, which is confusing to whoever finds the table later.

For the reverse direction, note that adding a column back is not symmetric. A nullable column with no default is instant; a NOT NULL column with a default is instant on PostgreSQL 11 and later but needs a backfill for existing rows on older versions, as adding a NOT NULL column without locking describes. That asymmetry is another reason to be sure before dropping.

Verifying Nothing Uses the Column

The check that decides whether the second release is safe is "does anything still reference this column", and it has three parts, only one of which is a grep.

Your repositories. Search for the attribute name and the column name, across every repository that talks to this database — the application, the workers, the admin tool, the data pipeline, the notebook someone runs monthly:

rg -n --hidden -g '!.git' '\bfax\b' services/ jobs/ tools/

The database catalogue. Views, constraints, indexes and generated columns that depend on the column will block the drop or be silently cascaded:

from sqlalchemy import text

DEPENDENTS = text("""
    SELECT DISTINCT dependent.relname AS depends_on_it, dependent.relkind
    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
""")

The queries the database has actually seen. This is the part no repository can answer, and the one that catches a BI tool or another team's service. With pg_stat_statements enabled, the column name appears in the recorded query texts of anything that selected it:

from sqlalchemy import text

RECENT_READERS = text("""
    SELECT calls, rows, left(query, 200) AS query
    FROM pg_stat_statements
    WHERE query ILIKE '%fax%' AND query NOT ILIKE '%pg_stat_statements%'
    ORDER BY calls DESC
    LIMIT 20
""")

pg_stat_statements accumulates since its statistics were last reset, so check how long that window is — a monthly report will not appear in a window that was reset last week. Where the window is short, resetting it deliberately after release one and checking again a month later is the thorough version.

A final safeguard costs nothing and catches the case where all three checks were done and something was still missed: revoke access before dropping. Between release one and release two, REVOKE SELECT (fax) ON orders FROM app_user makes any remaining reader fail with a permission error rather than reading a column that is about to disappear — and a permission error is trivially reversible, while a drop is not.

Keeping the acknowledgement comment in the revision is what makes the decision auditable afterwards: which release stopped using the column, when it was archived, and who checked. That is the information the next person needs when a report turns out to have wanted it after all.

Frequently Asked Questions

Related

• Zero-Downtime Schema Migration Strategies — The parent guide: compatibility windows and lock-safe DDL.
• Renaming a column without downtime using expand and contract — The additive half of the same procedure.
• Blocking destructive operations in autogenerated migrations — Stopping the one-release version from being committed.
• Adding a NOT NULL column without locking in Postgres — Why adding a column back is not symmetric.