Back to content
    Backend and DatabasesDeveloperTechnical migration guide

    How to Perform Zero-Downtime Database Migrations: Core Principles

    Learn how to execute zero-downtime database migrations using expand-contract patterns, batch backfills, concurrent indexes, and constraint validations.

    Published: August 23, 2026Updated: August 23, 2026InoviqLab
    Zero-downtime database migration architecture showing expand, dual-write, backfill, cutover, and contract phases.
    Audience
    Developer
    Content type
    Technical migration guide
    Evergreen guide. Publication and update dates are tracked in article metadata.
    Zero DowntimeDatabase MigrationExpand and ContractPostgreSQLMySQLPrismaBackfillIndex

    Short answer

    Modifying database schemas in a production application serving active users carries risk. Executing destructive SQL commands (e.g., `ALTER TABLE DROP COLUMN`, changing data types, or locking large tables) during peak traffic can cause site outages, query timeouts, and data corruption.

    Achieving **Zero-Downtime Database Migration** requires separating schema changes from application code deployments using the **Expand-Contract Pattern (Parallel Change)**.

    Expand-Contract Migration Stages:

    1. Expand Schema (Add new column/table without breaking legacy app)
    2. Deploy Dual-Write Application Code (Write to old and new schemas)
    3. Backfill Historical Data (Sync past records asynchronously)
    4. Deploy Read-New Application Code (Switch application reads to new schema)
    5. Contract Schema (Safely remove legacy column/table)

    Zero-Downtime Deployment Matrix:

    Migration GoalDangerous Approach (Downtime)Safe Zero-Downtime Approach
    Rename Column`ALTER TABLE RENAME COLUMN old TO new;`Add new column -> Dual write -> Backfill -> Drop old column
    Change Data TypeDirect `ALTER TABLE ALTER COLUMN`Add new typed column -> Backfill -> Switch references
    Add NOT NULL`ALTER TABLE ADD COLUMN col NOT NULL;`Add NULLable column -> Backfill -> Add NOT NULL constraint
    Add Index`CREATE INDEX idx ON table (col);``CREATE INDEX CONCURRENTLY` (PostgreSQL)

    1. Safe Schema Expansion Rules

    • Never drop or rename a column in a single release.
    • Always use non-blocking index creation (`CREATE INDEX CONCURRENTLY` in PostgreSQL).
    • Run heavy data backfill scripts in small batches (e.g., 1,000 rows at a time) to prevent transaction log saturation and table locks.

    Zero-Downtime Migration Checklist

    • [ ] Use Expand-Contract pattern for all breaking schema changes
    • [ ] Create database indices concurrently to prevent table locks
    • [ ] Test dual-write application code on staging environment
    • [ ] Execute asynchronous backfill scripts in controlled micro-batches

    Sources

    • PostgreSQL Documentation — Non-blocking Index Creation and DDL Locks
    • Martin Fowler — Evolutionary Database Design and Parallel Change Pattern

    Share

    LinkedInX