A migration that takes a second on your laptop can hold a lock on a busy production table for minutes. While it waits or runs, every request that touches the table waits too, and the application looks down even though nothing has crashed.

This article shows how we split a risky change into small Alembic migrations that keep a PostgreSQL-backed application running. The example adds a required email_normalised column to a users table so the app can look people up by email without worrying about capital letters.

Which changes are risky

Most schema changes need a lock on the table. Some only hold it for a moment. Others hold it while PostgreSQL reads or rewrites every row:

  • creating an ordinary index, which blocks writes until it finishes
  • making an existing column NOT NULL, which scans the whole table under an exclusive lock
  • adding a check or foreign key constraint that has to be validated
  • changing a column’s type in a way that rewrites the table

Even a quick change can hurt. A migration waiting for its lock sits behind any long-running query, and every new query queues behind the migration. That is why each migration below starts with a lock timeout.

Step 1: expand

Add the new column as nullable. That only changes the table’s definition, so it is fast. The lock_timeout makes the migration fail after five seconds instead of queueing all traffic behind it. If it times out, you run it again at a quieter moment.

0002_add_email_normalised.py
import sqlalchemy as sa
from alembic import op

revision = "0002_add_email_normalised"
down_revision = "0001_create_users"


def upgrade() -> None:
    op.execute("SET lock_timeout = '5s'")
    op.add_column(
        "users", sa.Column("email_normalised", sa.String(254), nullable=True)
    )


def downgrade() -> None:
    op.drop_column("users", "email_normalised")

Then deploy application code that writes the new column whenever it creates or updates a user. Without this, rows written after the backfill will still be empty and the final step will fail.

Step 2: backfill in batches

Filling the column with one UPDATE would lock every row in a single long transaction. Instead, update a few thousand rows at a time. Alembic’s autocommit_block commits each batch on its own, so locks are held only briefly and other queries keep flowing.

0003_backfill_email_normalised.py
import sqlalchemy as sa
from alembic import op

revision = "0003_backfill_email_normalised"
down_revision = "0002_add_email_normalised"

BATCH_SIZE = 5_000

BACKFILL = sa.text(
    """
    UPDATE users SET email_normalised = lower(email)
    WHERE id IN (
        SELECT id FROM users
        WHERE email_normalised IS NULL
        LIMIT :batch_size
    )
    """
)


def upgrade() -> None:
    with op.get_context().autocommit_block():
        connection = op.get_bind()
        while connection.execute(BACKFILL, {"batch_size": BATCH_SIZE}).rowcount:
            pass


def downgrade() -> None:
    pass

Step 3: build the index concurrently

CREATE INDEX CONCURRENTLY builds the index without blocking writes. It can’t run inside a transaction, so it also goes in an autocommit block. If a concurrent build fails, it leaves an invalid index behind, so drop it before you retry.

0004_index_email_normalised.py
from alembic import op

revision = "0004_index_email_normalised"
down_revision = "0003_backfill_email_normalised"


def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_users_email_normalised",
            "users",
            ["email_normalised"],
            postgresql_concurrently=True,
            if_not_exists=True,
        )


def downgrade() -> None:
    with op.get_context().autocommit_block():
        op.drop_index(
            "ix_users_email_normalised",
            table_name="users",
            postgresql_concurrently=True,
            if_exists=True,
        )

Step 4: contract

Setting NOT NULL directly would scan the table while holding an exclusive lock. Instead, add a check constraint marked NOT VALID, which is instant, then validate it in a separate transaction. Validation still reads every row, but reads and writes carry on while it runs.

From PostgreSQL 12, SET NOT NULL skips the full scan when a valid check constraint already proves there are no nulls. After that, the check is redundant and can go.

0005_require_email_normalised.py
from alembic import op

revision = "0005_require_email_normalised"
down_revision = "0004_index_email_normalised"

CHECK = "users_email_normalised_not_null"


def upgrade() -> None:
    op.execute("SET lock_timeout = '5s'")
    op.execute(
        f"ALTER TABLE users ADD CONSTRAINT {CHECK} "
        "CHECK (email_normalised IS NOT NULL) NOT VALID"
    )
    with op.get_context().autocommit_block():
        op.execute(f"ALTER TABLE users VALIDATE CONSTRAINT {CHECK}")
    op.alter_column("users", "email_normalised", nullable=False)
    op.drop_constraint(CHECK, "users", type_="check")


def downgrade() -> None:
    op.alter_column("users", "email_normalised", nullable=True)

Deploy in order

  • Run the expand migration.
  • Deploy code that writes the new column for new and changed rows.
  • Run the backfill, then the concurrent index.
  • Run the contract migration.
  • Deploy code that reads the new column.

Each step is safe to stop after, which makes the whole change easier to roll out and easier to reverse.

A short checklist

  • Set a lock timeout at the top of every migration that alters a table.
  • Add columns as nullable and fill them in batches.
  • Build indexes concurrently, outside a transaction.
  • Use a NOT VALID check and validate it before SET NOT NULL.
  • Deploy code and migrations in an order where each step still works.

We ran these migrations against PostgreSQL 17 with 120,000 rows, including a downgrade and a second upgrade. Safe schema changes are part of our Python and FastAPI development work, and they matter most when the table is busy and the product can’t stop.