Implementing Zero-Downtime PostgreSQL Schema Migrations with the Expand-Contract Pattern

A practical guide to deploying column renames, table splits, and foreign key additions in 3 rollback-safe steps

Many of you have seen a message like this pop up in your team channel the night before a deployment: "DB migration tomorrow — let's do it when traffic is low in the early morning." The entire team watching Slack at 2 AM just because of a single RENAME COLUMN, service maintenance notice and all.

Applying the Expand-Contract pattern lets you deploy breaking schema changes — column renames, table splits, foreign key additions — with zero service downtime, fully rollback-safe at every stage. This article starts with why a direct ALTER TABLE is dangerous, explains how the 3-stage structure eliminates that risk, and covers integrating it into a CI/CD pipeline with real-world scenarios.

Core Concepts

Why Direct ALTER TABLE is Dangerous

Say you want to rename the name column to full_name in the users table. Intuitively, you'd want to do this:

-- We explain the problem with this approach below
ALTER TABLE users RENAME COLUMN name TO full_name;

The DDL itself is atomic and fast. The problem lies in the deploy gap. In a Kubernetes rolling-update environment, if this command runs while old app instances are still referencing name, the old code immediately throws a column "name" does not exist error.

Large tables also have locking problems. Before PostgreSQL 11, running ADD COLUMN NOT NULL DEFAULT 'value' on a table with hundreds of millions of rows holds an ACCESS EXCLUSIVE lock for the entire table rewrite. PostgreSQL 11+ handles constant defaults with only a metadata change, but touching a large table with the wrong pattern is still risky.

The 3-Stage Structure of the Expand-Contract Pattern

The core idea of Expand-Contract (also known as the Parallel Change pattern) is to never make a breaking change all at once. It allows an intermediate state where the old and new schemas coexist briefly, silently transitioning data and code in between.

flowchart TD
    S[Existing Schema] --> E1[Stage 1 Expand]
    E1 --> E2[Stage 2 Migrate]
    E2 --> E3[Stage 3 Contract]
    E3 --> F[New Schema]
 
    style E1 fill:#e8f5e9
    style E2 fill:#fff3e0
    style E3 fill:#fce4ec

In this structure, the system is always in a working state at the end of each stage. Because the Expand stage only adds, any problem can be completely rolled back with DROP COLUMN.

Why New Columns in the Expand Stage Must Allow NULL

Old code is unaware of newly added columns. If you add a column as NOT NULL, the moment old code fires an INSERT that doesn't include that column, you'll get an error immediately. New columns in the Expand stage must be added as nullable so that old and new code can safely coexist on the same database.

Practical Application

Scenario 1 — Column Rename: name → full_name

Phase 1: Expand — Add New Column

-- Migration file: V001__expand_add_full_name.sql
SET lock_timeout = '5s';
SET statement_timeout = '30s';
 
ALTER TABLE users ADD COLUMN full_name TEXT; -- must be nullable

After applying this migration, update the app code to dual-write and deploy.

# App code: write to both old and new columns
def create_user(name: str):
    db.execute(
        "INSERT INTO users (name, full_name) VALUES (%s, %s)",
        [name, name]
    )
 
def update_user_name(user_id: int, name: str):
    db.execute(
        "UPDATE users SET name = %s, full_name = %s WHERE id = %s",
        [name, name, user_id]
    )

Phase 2: Migrate — Batch Backfill + Switch Reads

Existing rows have no full_name value and need to be filled in. The fixed-range sliding approach (id > last_id AND id <= last_id + BATCH_SIZE) has a bug where the loop terminates immediately upon hitting the first empty range in tables with ID gaps. Cursor-based pagination is the safe approach.

# scripts/backfill_full_name.py
import time
import psycopg2
 
BATCH_SIZE = 10_000
SLEEP_SECONDS = 0.1
 
conn = psycopg2.connect(DATABASE_URL)
last_id = 0
 
while True:
    with conn.cursor() as cur:
        cur.execute("""
            UPDATE users
            SET full_name = name
            WHERE id IN (
                SELECT id FROM users
                WHERE full_name IS NULL AND id > %s
                ORDER BY id
                LIMIT %s
            )
            RETURNING id
        """, [last_id, BATCH_SIZE])
        rows = cur.fetchall()
        conn.commit()
 
    if not rows:
        break
 
    last_id = max(row[0] for row in rows)
    time.sleep(SLEEP_SECONDS)  # prevent replication lag
 
print("Backfill complete")

Once the backfill is done, switch reads in the app code to full_name and verify stability after deployment. Then stop dual-writing to name, deploy code that writes only to full_name, and proceed to Phase 3.

Phase 3: Contract — Remove Old Column

Proceed only after confirming all app instances are using full_name exclusively.

-- Migration file: V003__contract_drop_name.sql
SET lock_timeout = '5s';
 
ALTER TABLE users DROP COLUMN name;

Scenario 2 — Table Split: orders → order_headers + order_items

Splitting a single orders table into order_headers and order_items is more complex. Dual-write via triggers is the key, and the ordering and deduplication approach matters.

Phase 1: Expand — Create New Tables + Trigger

CREATE TABLE order_headers (
    id          BIGINT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE order_items (
    id               BIGSERIAL PRIMARY KEY,
    order_id         BIGINT NOT NULL REFERENCES order_headers(id),
    product_id       BIGINT NOT NULL,
    quantity         INT NOT NULL,
    price            NUMERIC(10,2) NOT NULL,
    source_order_id  BIGINT UNIQUE  -- ensures idempotency between backfill and trigger
);
 
CREATE OR REPLACE FUNCTION sync_orders_to_new_tables()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_headers (id, user_id, created_at)
        VALUES (NEW.id, NEW.user_id, NEW.created_at)
        ON CONFLICT (id) DO NOTHING;
 
        INSERT INTO order_items (order_id, product_id, quantity, price, source_order_id)
        VALUES (NEW.id, NEW.product_id, NEW.quantity, NEW.price, NEW.id)
        ON CONFLICT (source_order_id) DO NOTHING;
 
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_headers (id, user_id, created_at)
        VALUES (NEW.id, NEW.user_id, NEW.created_at)
        ON CONFLICT (id) DO UPDATE
            SET user_id    = EXCLUDED.user_id,
                created_at = EXCLUDED.created_at;
 
        UPDATE order_items
        SET product_id = NEW.product_id,
            quantity   = NEW.quantity,
            price      = NEW.price
        WHERE source_order_id = NEW.id;
    END IF;
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_orders
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_orders_to_new_tables();

The TG_OP branching is critical. Executing INSERT INTO order_items on UPDATE as well would add a row on every call, producing duplicate data.

Phase 2: Migrate — Batch Copy Existing Data + Expose View

Since the trigger only syncs new data, backfill the existing data separately. Thanks to the UNIQUE constraint on source_order_id, if the backfill re-inserts a row already processed by the trigger, it is handled idempotently without duplication.

INSERT INTO order_headers (id, user_id, created_at)
SELECT id, user_id, created_at FROM orders
ON CONFLICT (id) DO NOTHING;
 
INSERT INTO order_items (order_id, product_id, quantity, price, source_order_id)
SELECT id, product_id, quantity, price, id FROM orders
ON CONFLICT (source_order_id) DO NOTHING;
 
-- expose a backward-compatible view until app code migration is complete
CREATE VIEW orders_view AS
SELECT
    h.id,
    h.user_id,
    h.created_at,
    i.product_id,
    i.quantity,
    i.price
FROM order_headers h
JOIN order_items i ON i.order_id = h.id;

At this stage, switch the app code to query order_headers and order_items directly and deploy. orders_view is kept as a backward-compatible path until the migration is fully complete.

Phase 3: Contract — Clean Up Old Table

Proceed only after confirming that the app code has fully transitioned to querying order_headers and order_items directly.

DROP TRIGGER trg_sync_orders ON orders;
DROP FUNCTION sync_orders_to_new_tables();
DROP VIEW orders_view;
DROP TABLE orders;

Scenario 3 — Adding a Foreign Key: The NOT VALID Pattern

Adding a foreign key in one shot holds a SHARE ROW EXCLUSIVE lock for the duration of the full-table validation scan. This lock doesn't conflict with reads (SELECT), but it blocks write operations like INSERT, UPDATE, and DELETE. On large tables where the scan can take minutes, all writes wait for that entire time.

Splitting it into two stages minimizes write-blocking time.

sequenceDiagram
    participant App as App Server
    participant PG as PostgreSQL
 
    Note over App,PG: Phase 1 - ADD CONSTRAINT NOT VALID
    App->>PG: ALTER TABLE orders ADD CONSTRAINT fk_user NOT VALID
    PG-->>App: Done - SHARE ROW EXCLUSIVE lock acquired and released instantly
    Note over PG: Skips validation of existing rows. FK check applies to new INSERT/UPDATE
 
    Note over App,PG: Phase 2 - VALIDATE CONSTRAINT
    App->>PG: ALTER TABLE orders VALIDATE CONSTRAINT fk_user
    PG-->>App: Done - validates all existing rows with SHARE UPDATE EXCLUSIVE lock
    Note over PG: Does not block reads or writes

-- Phase 1: add the constraint without validating existing rows
-- SHARE ROW EXCLUSIVE lock, but completes instantly with no table scan
SET lock_timeout = '5s';
ALTER TABLE orders
    ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
 
-- Phase 2: validate existing rows in a separate transaction
-- SHARE UPDATE EXCLUSIVE lock — does not block reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

The difference between ADD CONSTRAINT NOT VALID and a plain ADD CONSTRAINT is not the strength of the lock but the duration it is held. Both acquire the same SHARE ROW EXCLUSIVE lock, but NOT VALID skips the table scan, so the lock is held for only a brief instant. VALIDATE CONSTRAINT then performs the long scan under the weaker SHARE UPDATE EXCLUSIVE lock, which does not conflict with normal reads or writes.

A constraint in NOT VALID state is still enforced on new INSERT and UPDATE operations. It merely defers the validation of existing data to later, so it is not actually risky.

Integrating into a CI/CD Pipeline

The key is managing each of the three stages as separate PRs and deployments. One important point is that DB migrations and app code deployments must happen in the correct order. A CI/CD YAML file alone cannot fully express the app deployment steps, but the flow can be structured like this.

# .github/workflows/db-migration.yml
jobs:
  phase1-expand:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Phase 1 - Apply Expand migration
        run: flyway migrate -locations=filesystem:migrations/phase1
      # After this stage: deploy dual-write app code via a separate deployment pipeline
 
  phase2-backfill:
    needs: phase1-expand
    runs-on: ubuntu-latest
    steps:
      - name: Phase 2 - Run batch backfill
        run: python scripts/backfill_full_name.py
        # Prerequisite: dual-write app deployment is already complete
      # After this stage: deploy app code that writes only to the new column via a separate deployment pipeline
 
  phase3-contract:
    needs: phase2-backfill
    environment: production  # manual approval gate
    runs-on: ubuntu-latest
    steps:
      - name: Phase 3 - Apply Contract migration
        run: flyway migrate -locations=filesystem:migrations/phase3
        # Prerequisite: all app instances have switched to code that writes only to the new column

The actual deployment order is: apply Phase 1 DB migration → deploy dual-write app → run backfill → deploy app with reads switched and dual-write stopped → apply Phase 3 DB migration. It is recommended to place a manual approval gate on Phase 3 via environment: production so a human can verify data integrity and confirm the app transition is complete.

Automating All of This with pgroll

pgroll, an open-source CLI tool developed by Xata (currently v0.11+), automates the entire Expand-Contract lifecycle. Its multi-version schema approach — exposing both the old and new schema simultaneously via PostgreSQL views — is impressive. It allows old-version and new-version apps on the same PostgreSQL instance to see different schema views, naturally supporting both blue-green deployments and rolling updates.

# Example pgroll migration file
version: "1"
operations:
  - rename_column:
      table: users
      from: name
      to: full_name

With just this one file, pgroll handles the entire Expand-Contract process internally. It automates writing triggers manually, creating batch scripts, and generating views.

Pros and Cons

Advantages

Disadvantages and Common Mistakes in Practice

The most common mistake in practice is not setting lock_timeout. Without lock_timeout, DDL waits indefinitely behind long-running queries while also blocking new connections, exhausting the connection pool. GoCardless's article on migrating large-scale payment records (Zero-downtime Postgres migrations: the hard parts) covers this issue in detail.

-- Add to the beginning of every migration script
SET lock_timeout = '5s';
SET statement_timeout = '30s';

One caveat: SET applies at the session level and the setting may persist after the connection is returned to the pool. If running inside a transaction, SET LOCAL is safer. Flyway and Liquibase typically run each migration in a separate transaction, so SET LOCAL is appropriate.

lock_timeout set too short causes DDL to fail repeatedly; set too long, it can exhaust the connection pool. It is best to set an appropriate value based on your team's p99 query latency.

The second most common mistake is delaying the Contract stage until it is forgotten. It is very common to execute Phase 2 deployment perfectly and then find months later that the old column is still sitting there. Building an explicit tracking mechanism into PRs or tickets — something like "run Phase 3 within N days of merging this PR" — helps prevent this.

Conclusion

To summarize the key points:

• Never make breaking schema changes all at once — deploy in three stages: Expand → Migrate → Contract.
• New columns must be added as nullable to coexist with old code.
• The habit of setting lock_timeout at the start of every DDL script is the quickest improvement for preventing connection pool exhaustion.
• Adding a foreign key should be split into two stages: NOT VALID + VALIDATE CONSTRAINT to minimize write-blocking time.
• When implementing dual-write via triggers, always branch on TG_OP to distinguish INSERT from UPDATE, and use a natural key that guarantees idempotency.

Here are three things you can start doing right away: apply Expand-Contract to your next column addition task, add lock_timeout to all migration scripts, and try out pgroll in your local environment. Seeing firsthand how the tool automates the pattern is a great help for understanding the manual implementation as well.

When first introducing this pattern to a team, the reaction is often "We split a deployment into three separate steps?" But once you experience the complexity distributed across the team and the risk reduced, that question changes direction.

References

• pgroll Official Documentation — Zero-downtime reversible schema migrations for Postgres
• Xata Blog — Schema changes and the power of expand-contract with pgroll
• GitHub — xataio/pgroll
• GoCardless — Zero-downtime Postgres migrations: the hard parts
• Fabian Lindfors — Zero-downtime schema migrations in Postgres using views
• postgres.ai — lock_timeout and retries for zero-downtime migrations
• Michal Drozd — Zero-Downtime PostgreSQL Migrations: Expand/Contract, Backfill and Rollback Strategies
• Stormatics — Zero-Pain PostgreSQL DDL Migrations: Avoiding Locks & Long-running Queries
• DEV Community — Zero-Downtime PostgreSQL Schema Migrations: Expand/Contract vs Blue-Green Deployment
• Nexis Ltd — Zero-Downtime Database Migrations: Expand-and-Contract Pattern Explained
• datasops Blog — Database Migrations Without Downtime: Expand-Contract, Shadow Tables, and Feature Flags
• Thomas Skowron — Migrating Foreign Keys in PostgreSQL
• Bytebase — Flyway vs. Liquibase: The Definitive Comparison in 2026
• Reliable Penguin — Database Migrations Without Drama: Expand/Contract in Practice
• Hacker News Discussion — pgroll: zero-downtime, reversible schema migrations for Postgres