Zero-Downtime Schema Migrations: The Expand-Contract Pattern for Live Production DBs

Scenario context

Altering database schemas in a high-traffic production system without downtime requires the Expand-Contract pattern. Instead of changing a column in place, we add a new column (Expand), double-write to both columns via the application (Transition), backfill historical data, and finally remove the old column (Contract). This guide details this lifecycle with full Spring Boot and Liquibase snippets.

This walkthrough focuses on **Concept: Zero-Downtime Schema Migrations**.

Before making a design choice, clarify:

- In modern distributed systems, deploying updates to application servers is routinely managed using rolling updates or blue-green deployments.
- However, databases present a unique challenge because state cannot be easily duplicated or discarded.
- Running a standard ALTER TABLE RENAME COLUMN statement on a table containing millions of rows requires acquiring an exclusive table lock, causing active queries to block, requests to time out, and leading to service out…

**Concepts in play:** caching, jvm-internals-and-garbage-collection, consistency-replication, api-gateway

## Stage 1 — Mechanics: The Three-Phase Migration Lifecycle

- Let us trace the evolution of a schema during a column rename operation.
- Suppose we want to rename the column phone number to contact phone in our users table.
- We execute the change through these structured phases: 1.
- Phase 1: Expand (Database): We add the new column contact phone to the database schema as a nullable field.

*Applies [**caching**](/concept/caching) to the decision above.*

**Observable outcome:** Phase 1: Expand (Database): We add the new column contact phone to the database schema as a nullable field.

## Stage 2 — Flow: High-Level Migration Sequence

- The diagram below visualizes the transition states of the database and the active application versions during the migration: The table below summarizes the operational compatibility matrices at each stage:

*Applies [**jvm-internals-and-garbage-collection**](/concept/jvm-internals-and-garbage-collection) to the decision above.*

```architecture
{
  "version": 1,
  "title": "Flow: High-Level Migration Sequence - Subsystem Overview",
  "description": "Decoupled system architecture isolating ingress, stateless logic, and persistent datastores.",
  "view": "high-level",
  "pattern": "microservices",
  "direction": "LR",
  "groups": [
    { "id": "edge", "label": "Edge Traffic Ingress", "tone": "slate" },
    { "id": "services", "label": "Stateless Microservices", "tone": "blue" },
    { "id": "data", "label": "Datastore Infrastructure", "tone": "green" }
  ],
  "nodes": [
    { "id": "gw", "component": "api-gateway", "label": "API Gateway", "group": "edge", "badge": "TLS & Auth" },
    { "id": "app", "component": "workflow-service", "label": "Domain Service", "group": "services", "badge": "Stateless" },
    { "id": "cache", "component": "redis", "label": "Redis Cache", "group": "data", "badge": "Sub-ms Reads" },
    { "id": "db", "component": "postgres", "label": "Transactional DB", "group": "data", "badge": "ACID Log" }
  ],
  "edges": [
    { "from": "gw", "to": "app", "kind": "request", "label": "gRPC Request" },
    { "from": "app", "to": "cache", "kind": "read", "label": "Cache Lookups" },
    { "from": "app", "to": "db", "kind": "write", "label": "Durable Write" }
  ],
  "takeaway": {
    "title": "Architecture Boundary Invariant",
    "body": "Stateless compute nodes scale horizontally while shielding underlying database clusters behind fast cache layers."
  }
}

graph TD
    Start["State 0: Legacy Schema
App V0 reads/writes 'phone_number'"] -->|Expand: Add Column| Phase1["State 1: Expanded Schema
App V1 double-writes to both, reads 'phone_number'"]
    Phase1 -->|Data Backfill| Backfill["State 2: Backfilled Schema
All historical values copied to 'contact_phone'"]
    Backfill -->|Read Switch| Phase2["State 3: Transition Schema
App V2 double-writes to both, reads 'contact_phone'"]
    Phase2 -->|Contract: Drop Old Column| Phase3["State 4: Contracted Schema
App V3 reads/writes 'contact_phone' only"]

Observable outcome: The diagram below visualizes the transition states of the database and the active application versions during the migration: The table below summarizes the operational compatibility matrices at each stage:

Stage 3 — Deep Dive: Structural Evolution & Performance Profiling

• Understanding how the database engine executes these operations reveals why the Expand-Contract pattern is necessary.
• Database Engine Lock Internals In relational database engines like PostgreSQL or MySQL (InnoDB), schema changes require obtaining a Metadata Lock (MDL).
• In older database versions, any ALTER TABLE operation required rebuilding the entire table on disk, acquiring an Exclusive (Access Exclusive) lock.
• This blocks all read and write queries on the target table.

Observable outcome: This blocks all read and write queries on the target table.

Stage 4 — Advanced Concepts: Handling Large-Scale Table Alterations

• When working with tables that exceed hundreds of gigabytes, even adding a nullable column can sometimes hit system boundaries (e.g., maximum row size limits or disk space limits during page splits).
• To handle these scenarios at scale, we use toolkits like GitHub's gh-ost or Percona's pt-online-schema-change .
• These tools create a shadow copy of the table, apply migrations to the shadow table, set up database triggers or parse binary logs to stream incoming writes from the live table to the shadow table, and then execute a fa…

Observable outcome: These tools create a shadow copy of the table, apply migrations to the shadow table, set up database triggers or parse binary logs to stream incoming writes from the live table to the shadow table, and then execute a fa…

Stage 5 — Applications: Real-World SaaS Migrations

• Stripe Ledger Evolutions: Stripe uses a multi-phase column-mapping layer to move fields between database servers without dropping API requests.
• E-Commerce Account Merges: Moving customer shipping profiles from separate legacy databases into a unified schema during corporate mergers.
• High-Frequency Audits: Adding tracking metadata or audit hashes to core transaction records under live load.

Observable outcome: High-Frequency Audits: Adding tracking metadata or audit hashes to core transaction records under live load.

Stage 6 — Practical Implementation: Liquibase & Spring Boot Code

• Let us implement the database migrations and application code for Phase 2 (Transition Phase) of our column rename operation.
• Database Migration Scripts (Liquibase XML) Step 1: Expand Phase (Add Column) Step 2: Contract Phase (Drop Column) 2.
• Spring Boot JPA Transition Code During the transition phase, the application entity double-writes to both columns.
• We use JPA lifecycle callbacks ( @PrePersist and @PreUpdate ) to ensure that any change to the model updates both fields automatically.

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">

    <changeSet id="20260618-add-contact-phone-column" author="abstract-algorithms">
        <addColumn tableName="users">
            <column name="contact_phone" type="varchar(255)">
                <constraints nullable="true"/>
            </column>
        </addColumn>
    </changeSet>
</databaseChangeLog>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">

    <changeSet id="20260618-drop-old-phone-number-column" author="abstract-algorithms">
        <dropColumn tableName="users" columnName="phone_number"/>
    </changeSet>
</databaseChangeLog>

Observable outcome: We use JPA lifecycle callbacks ( @PrePersist and @PreUpdate ) to ensure that any change to the model updates both fields automatically.

End-to-end outcome

After working through the stages, you should be able to explain:

• Pattern Core: Expand (add new database field), Transition (application double-writes & copies history), Contract (drop old database field).
• Backfill Safety: Never run a single global UPDATE query on large tables.

Alternatives considered

Decision Guide: Expand-Contract vs. Locking Maintenance

Decision Guide: Expand-Contract vs. Locking Maintenance

Failure and degraded modes

Trade-offs and Failure Modes

• Detection: Operational Overhead: What would have been a single SQL script is split into multiple code deployments, data backfills, and database cleanup scripts.
• User or operator impact: Operational Overhead: What would have been a single SQL script is split into multiple code deployments, data backfills, and database cleanup scripts.

Lessons Learned: Common Migration Pitfalls

• Detection: Running Backfill Before Deploying Double-Writes: If the backfill job runs while application instances are still writing only to the legacy column, any database records modified after they are backfil…
• User or operator impact: Running Backfill Before Deploying Double-Writes: If the backfill job runs while application instances are still writing only to the legacy column, any database records modified after they are backfil…