Schema migration without downtime: a production field guide

By Vikram Pillai · 22 July 20260 views
#databases#migration#devops#backend#postgresql

A backend engineer runs a migration on a Friday afternoon. The migration adds a NOT NULL constraint to the email column of the users table — a table with 8 million rows. The migration acquires an exclusive lock to validate the constraint. For the 4 minutes and 23 seconds the validation runs, all writes to the users table queue behind the lock. Connections accumulate. The connection pool fills. The application starts returning 500 errors to users. The engineer reverts the migration. The constraint is not added. The production incident is logged. The Friday afternoon is ruined.

The migration was correct. The execution was wrong. Adding a NOT NULL constraint by locking the table is one of several migration patterns that look straightforward and are not.

Why migrations cause downtime

Database migrations that require an exclusive lock on a table cause downtime when the table is large and the migration takes longer than the application's connection timeout. The lock prevents reads and writes for the duration of the operation. Applications waiting to read or write queue their connections. When the queue grows past the connection pool limit, the application stops accepting requests.

The operations that require exclusive locks on most databases include:

  • Adding a NOT NULL constraint without a default (requires full table scan to validate)
  • Adding a non-nullable column (implies a NOT NULL constraint)
  • Changing a column type (may require rewriting every row)
  • Adding a constraint that requires a full table validation
  • Rebuilding an index (blocks writes on the table being indexed)

The operations that do not require exclusive locks (on Postgres, with NOT VALID):

  • Adding a nullable column with or without a default
  • Adding a CHECK constraint marked NOT VALID (validates new rows, skips existing)
  • Creating an index CONCURRENTLY (builds in the background, does not block writes)
  • Adding a foreign key NOT VALID (deferred constraint validation)

The pattern for safe migrations is: use the lock-free operations to get the database to the desired end state in multiple steps, rather than using the locking operation in a single step.

The expand-contract pattern

The expand-contract pattern makes any migration safe by separating it into three phases.

Phase 1 — Expand: Add the new structure in a backward-compatible way. New columns are nullable. New tables are added. Application code is updated to write to both old and new structures. Existing functionality is unchanged.

Phase 2 — Migrate: Move existing data to the new structure. This can be done in batches, without locks, while the application is running. Application code may now prefer the new structure for reads but still reads from the old structure as a fallback.

Phase 3 — Contract: Remove the old structure. This phase only runs after the old structure is no longer needed by any application code in production. Drop the old column, remove the old code paths.

Making a NOT NULL constraint safe

The Friday migration could have been done safely in three phases.

Phase 1 — Expand (no downtime): Add the column as nullable with a default:

-- Runs instantly — adding a nullable column with a default requires no table rewrite
-- Postgres stores the default in the catalog; existing rows appear to have the default
-- without rewriting them
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

Update the application to write email_verified on new user creates. No existing rows are touched.

Phase 2 — Migrate (no downtime, batched): Backfill existing rows in batches:

-- Batch update — update 10,000 rows at a time with a sleep between batches
-- Prevents lock contention on the users table
DO $$
DECLARE
    batch_size INT := 10000;
    rows_updated INT;
BEGIN
    LOOP
        UPDATE users
        SET email_verified = FALSE
        WHERE email_verified IS NULL
          AND id IN (
            SELECT id FROM users
            WHERE email_verified IS NULL
            LIMIT batch_size
          );

        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;

        PERFORM pg_sleep(0.1);  -- Brief pause between batches
    END LOOP;
END $$;

This runs in the background, in batches small enough that they do not hold locks long enough to affect the application. Progress can be monitored:

-- Check progress
SELECT
    COUNT(*) FILTER (WHERE email_verified IS NULL) AS pending,
    COUNT(*) FILTER (WHERE email_verified IS NOT NULL) AS completed,
    COUNT(*) AS total
FROM users;

Phase 3 — Contract (minimal-impact locking): Once all rows are backfilled, add the NOT NULL constraint using NOT VALID followed by VALIDATE CONSTRAINT:

-- Step 1: Add the constraint but skip validation of existing rows
-- This is fast because it only needs to update the constraint catalog,
-- not scan the table
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
-- Wait — this still locks. Better approach for Postgres:

-- For Postgres 12+, the safer approach uses a check constraint:
-- Add the check constraint without validating existing rows
ALTER TABLE users
    ADD CONSTRAINT users_email_verified_not_null
    CHECK (email_verified IS NOT NULL) NOT VALID;

-- Validate using ShareUpdateExclusiveLock (does not block reads or writes)
-- This may take time on a large table, but does not block the application
ALTER TABLE users VALIDATE CONSTRAINT users_email_verified_not_null;

NOT VALID adds the constraint for new rows immediately. VALIDATE CONSTRAINT applies the ShareUpdateExclusiveLock which allows concurrent reads and writes while it scans the table. Validation may take several minutes on a large table, but the application continues to serve traffic.

After validation, the NOT NULL constraint is enforced for all rows. The phase 3 migration is complete.

Safe index creation

Adding an index on a large table blocks reads and writes for the duration of index creation. The safe alternative is CREATE INDEX CONCURRENTLY:

-- Unsafe: blocks reads and writes while building
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Safe: builds in the background, allows concurrent reads and writes
-- Takes 2-3x longer but never blocks
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

CREATE INDEX CONCURRENTLY requires two passes over the table: one to build the initial index, one to catch changes made during the first pass. It cannot be run inside a transaction (no BEGIN ... COMMIT). It is the correct approach for production index creation on tables with any meaningful traffic.

If CREATE INDEX CONCURRENTLY fails partway through, it leaves an invalid index behind:

-- Check for invalid indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'
  AND indexname LIKE 'idx_orders_user_id%';

-- The pg_index catalog shows invalid indexes
SELECT i.indexrelid::regclass AS index_name, i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname LIKE 'idx_orders_user_id%';

-- Drop the invalid index and retry
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Renaming a column safely

Renaming a column — ALTER TABLE users RENAME COLUMN name TO full_name — is instant in Postgres. The problem is the application, not the database.

If the application refers to name and the column is renamed to full_name, the application breaks at the moment of the rename. The migration and the deployment cannot be atomic across a distributed system with rolling deploys.

The safe approach is expand-contract:

-- Phase 1: Add the new column
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Application reads full_name, falls back to name if full_name is null
-- Application writes both full_name and name

-- Phase 2: Backfill existing rows
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Phase 3: After old column reads removed from all deployed instances:
ALTER TABLE users DROP COLUMN name;

Between phase 1 and phase 3, all application instances — old and new — must work correctly. Old instances read name. New instances read full_name. Both must work during the deployment window.

Coordinating migrations with deployments

The migration order determines what application code can safely read and write during each phase.

For expand-contract:

  1. Deploy application code that writes both old and new columns (if appropriate) and reads from new column with fallback to old
  2. Run phase 1 migration (add new column/structure)
  3. Run phase 2 migration (backfill)
  4. Deploy application code that reads only from new column, stops writing old column
  5. Run phase 3 migration (remove old column/structure)

The migration and the deployment are decoupled. Each step can be validated independently. Rollback at any step is straightforward: the previous state is always a valid state.

This approach requires careful attention to what each version of the application code expects from the database — and ensuring that during any deployment window, the database state is compatible with all versions of the application code that may be running simultaneously.

Common mistakes teams make with production migrations

Running migrations inside deployment scripts without a rollback plan. Migrations that run as part of the deployment process, in the same pipeline step, are difficult to decouple when a problem appears. If the migration runs but the deployment fails, the schema is ahead of the application. If the migration is destructive, rollback is not possible. The safer model runs migrations as a separate step, with the previous application version verified to work against the migrated schema before the new version is deployed.

Using ADD COLUMN ... NOT NULL without a default. In Postgres, adding a NOT NULL column without a default requires a full table rewrite before Postgres 11, and a table lock for constraint validation afterward. In Postgres 11+, adding a column with a DEFAULT literal value is instant (the default is stored in the catalog rather than written to every row). Adding a column without a default and then adding the NOT NULL constraint requires the expand-contract approach described above — there is no shortcut.

Migrating large tables during peak traffic. The batched backfill approach is designed to run during off-peak hours. A batch that holds a row-level lock for 200ms is acceptable at 3 AM when traffic is low. During peak hours, the same lock accumulates queries behind it and causes latency spikes. Schedule migrations for the lowest-traffic window, even when the migration itself is non-locking.

Treating CREATE INDEX as equivalent to CREATE INDEX CONCURRENTLY. The non-concurrent form acquires an exclusive lock. The concurrent form takes two to three times longer but allows reads and writes throughout. In any table with meaningful traffic, the non-concurrent form will cause connection accumulation. The concurrent form is always the correct choice in production.

Dropping columns before verifying all application versions are removed. In a rolling deployment where multiple application versions run simultaneously, a column that is read by the previous version cannot be dropped until the previous version is no longer running. Migrations that drop columns should include verification that no application instance in production is reading that column. A deployment history showing 100% of instances on the new version is the prerequisite for the column drop.

Measuring migration safety before running in production

Before running any migration on a production table with more than 1 million rows, answer these questions:

-- How many rows are in the table?
SELECT COUNT(*) FROM users;

-- What is the table size on disk?
SELECT pg_size_pretty(pg_total_relation_size('users')) AS total_size;

-- Are there any existing locks on the table?
SELECT pid, mode, granted, query_start
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE relation = 'users'::regclass;

-- What is the current write throughput on the table?
SELECT n_tup_ins + n_tup_upd + n_tup_del AS writes_per_interval
FROM pg_stat_user_tables
WHERE relname = 'users';

A table with 8 million rows, 50 writes per second, and a migration that requires a full table scan is a migration that will hold a lock for several minutes. The calculation: at 8 million rows and typical Postgres throughput of 500,000 rows per second for a sequential scan, the validation takes approximately 16 seconds. During those 16 seconds, all writes queue behind the lock. At 50 writes per second, that is 800 queued writes — likely enough to fill the connection pool on most configurations.

The engineer who understands expand-contract, batched backfill, and CONCURRENTLY index creation has a toolkit for migrating any database change safely. The Friday afternoon incident is preventable. The NOT NULL constraint gets added. The users table stays available. The production incident is someone else's story.

Testing migrations before production

Even well-designed migrations benefit from dry runs on production-sized data. The batched backfill script that takes 3 minutes on a staging database with 100,000 rows may take 8 hours on production with 50 million rows. Testing on realistic data sizes is the only way to know.

A practical approach: maintain a staging database that is restored from a recent production snapshot. Run the migration on staging first and measure the actual time for each phase. The measurement informs the migration plan:

-- Time the batch update on staging with a LIMIT that matches the production batch size
EXPLAIN (ANALYZE, BUFFERS)
UPDATE users
SET email_verified = FALSE
WHERE email_verified IS NULL
  AND id IN (
    SELECT id FROM users
    WHERE email_verified IS NULL
    LIMIT 10000
  );

If a single batch takes 200ms on staging and there are 50 million rows to backfill with a batch size of 10,000, the full backfill takes approximately 1,000 batches at 200ms each — 200 seconds plus sleep time between batches. That is a reasonable backfill duration. A batch that takes 5 seconds on staging indicates lock contention or a missing index on the backfill condition, and should be investigated before running on production.

Automating migration safety checks

Migration frameworks like Flyway, Liquibase, and Django migrations execute SQL automatically, which creates risk if the SQL contains a locking operation on a large table. A pre-migration lint check can catch the most dangerous patterns:

import re
from typing import List

DANGEROUS_PATTERNS = [
    (r"ALTER TABLE \w+ ADD COLUMN \w+ \w+ NOT NULL(?! DEFAULT)", "NOT NULL column without default requires table rewrite"),
    (r"ALTER TABLE \w+ RENAME COLUMN", "Column rename breaks previous application version"),
    (r"ALTER TABLE \w+ DROP COLUMN", "Column drop breaks previous application version if still in use"),
    (r"CREATE INDEX(?! CONCURRENTLY)", "Non-concurrent index creation blocks reads and writes"),
    (r"ALTER TABLE \w+ ALTER COLUMN \w+ TYPE", "Column type change may require full table rewrite"),
]

def lint_migration(sql: str) -> List[dict]:
    """
    Checks a migration SQL string for patterns that commonly cause production outages.
    Returns a list of findings. An empty list means the migration passed all checks.
    """
    findings = []
    for pattern, description in DANGEROUS_PATTERNS:
        if re.search(pattern, sql, re.IGNORECASE):
            findings.append({
                "pattern": pattern,
                "description": description,
                "recommendation": "Use the expand-contract pattern or CONCURRENTLY option"
            })
    return findings

Running this lint check in CI on every migration file catches the patterns that cause incidents before they reach the deployment pipeline. A migration that fails the lint check is returned to the author for revision, not merged and scheduled.

The evolving landscape of online schema changes

Postgres 11 and later have made several previously dangerous migration patterns safe. Adding a column with a constant DEFAULT value no longer rewrites the table — the default is stored in the catalog and applied at read time. This eliminated one of the most common table-rewrite migrations.

Tools like pg_repack and pglogical enable more aggressive online schema changes — including full table rewrites — by maintaining a synchronized copy of the table during the rewrite and atomically swapping to the new version. These tools are appropriate for migrations that cannot be expressed as expand-contract sequences, though they add operational complexity.

The general direction is toward fewer migration operations requiring table locks, with each major Postgres release reducing the set of operations that block. Staying current on the database version's migration capabilities is worthwhile — a migration that required expand-contract in Postgres 10 may be safe as a single statement in Postgres 14. The safe migration toolkit evolves alongside the database.

Comments

No comments yet. Be the first!

Sign in to leave a comment.

Schema migration without downtime: a production field guide — ANN Tech