The Midnight Deployment Fear

In many software organizations, running database migrations remains an anxiety-inducing ordeal. The typical routine: declare a scheduled maintenance downtime window, take the web service offline, run alembic upgrade head, and pray that a heavy column lock or index creation doesn’t freeze the cluster.

In modern continuous delivery (CD) workflows powering 24/7 web platforms, “maintenance windows” are unacceptable. Migrations must execute under peak traffic without locking production tables or causing HTTP 504 gateway timeouts.

Here is how to achieve zero-downtime migrations in PostgreSQL using Alembic.

---

1. Index Creation: Escaping the Exclusive Lock Trap

Executing a standard index creation on a table containing millions of rows acquires a restrictive SHARE lock on PostgreSQL:

-- DANGEROUS: Blocks all INSERT, UPDATE, and DELETE writes until completion!
CREATE INDEX idx_users_email ON users(email);

If indexing takes two minutes on a high-traffic table, write queues back up, application worker threads saturate, and API gateways drop connections.

The Zero-Downtime Solution: CONCURRENTLY in Alembic

PostgreSQL supports CREATE INDEX CONCURRENTLY, which scans and builds the index in the background without blocking concurrent writes. Because CONCURRENTLY cannot execute inside a single transactional block, Alembic requires specific transaction handling:

"""add index concurrently to users

Revision ID: 4a2b91c0e
"""
from alembic import op

# Instruct Alembic not to wrap this migration in a single transaction
commit_after_each_step = True

def upgrade() -> None:
    # Explicitly commit any pending transaction
    op.execute("COMMIT")
    op.create_index(
        "idx_users_email",
        "users",
        ["email"],
        postgresql_concurrently=True,
    )

def downgrade() -> None:
    op.execute("COMMIT")
    op.drop_index(
        "idx_users_email",
        table_name="users",
        postgresql_concurrently=True,
    )

With this pattern, the database creates the index concurrently while web requests proceed without interruption.

---

2. The Expand and Contract Pattern

Never rename or modify a database column in a single step (e.g. renaming full_name to display_name). If you rename the column before the new application containers boot, existing live pods crash immediately because their SQL queries reference full_name.

Instead, execute the change across three progressive phases:

Phase 1: Expand

• Add the new column display_name as nullable.
• Deploy an updated application version that writes data to both columns simultaneously, while continuing to read from full_name.

Phase 2: Backfill

• Run a background script to copy historical data from full_name to display_name in small, throttled batches to avoid I/O spikes.

Phase 3: Contract

• Deploy the application version that reads and writes exclusively from display_name.
• Once verified in production, drop the legacy full_name column in a follow-up migration.

---

3. Safe Defaults: Constants vs Functions

In modern PostgreSQL versions (11+), adding a column with a constant default:

ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT FALSE;

Executes in sub-milliseconds because PostgreSQL updates only table metadata without rewriting disk blocks.

However, beware of volatile function defaults (such as DEFAULT clock_timestamp() or custom functions): these force an immediate physical rewrite of every table row, creating long-lived locks. Always backfill dynamic defaults asynchronously via worker scripts.

---

Takeaways

• Build all production indexes using postgresql_concurrently=True.
• Decouple schema changes from code deployments using the Expand and Contract pattern.
• Audit migrations for restrictive locks before running them in staging and production CI pipelines.