Database Migration Guide: Zero-Downtime Strategies

In this tutorial, you'll learn about Database Migration Guide: Zero. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database Migration is the controlled Process of evolving a database schema over time using version-controlled scripts -- enabling teams to apply, track, and roll back changes consistently across environments.

What You'll Learn

You will understand how to use Flyway and Liquibase for schema versioning, design backward-compatible migrations, execute zero-downtime schema changes, handle data backfills, and create rollback plans.

Why Database Migrations Matter

Schema changes without versioning cause production outages. DodaZIP archives millions of files daily; a Migration that renames a column without a transition period breaks every running Process that references the old name.

Migration Learning Path

flowchart LR
  A[Database Design] --> B[Schema Versioning]
  B --> C[Database Migrations]
  C --> D[Zero-Downtime Deploy]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Migration Tools Overview

Flyway Migrations

Flyway uses versioned SQL files applied in order. It tracks applied migrations in a flyway_schema_history table.

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- V2__add_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

# Apply migrations
flyway -url=jdbc:postgresql://localhost:5432/mydb \
       -user=app_user \
       -password=secret \
       migrate

# Check status
flyway info

# Repair checksums if migration files changed
flyway repair

Expected output:

Successfully applied 2 migrations (execution time 00:00.234s)

Liquibase Migrations

Liquibase uses a changelog file (XML, YAML, or JSON) that defines changesets.

<!-- db/changelog/db.changelog-master.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<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.20.xsd">

    <changeSet id="1" author="dodatech">
        <createTable tableName="users">
            <column name="id" type="bigint" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="varchar(100)">
                <constraints nullable="false"/>
            </column>
            <column name="email" type="varchar(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="created_at" type="timestamp"/>
        </createTable>
    </changeSet>

    <changeSet id="2" author="dodatech">
        <addColumn tableName="users">
            <column name="last_login" type="timestamp"/>
        </addColumn>
        <rollback>
            <dropColumn tableName="users" columnName="last_login"/>
        </rollback>
    </changeSet>
</databaseChangeLog>

# Apply migrations
liquibase --changeLogFile=db/changelog/db.changelog-master.xml \
          --url=jdbc:postgresql://localhost:5432/mydb \
          --username=app_user \
          --password=secret \
          update

# Rollback last changeset
liquibase rollbackCount 1

Alembic Migrations

Alembic is the Migration tool for SQLAlchemy-based Python projects.

# alembic/versions/0001_create_users.py
"""create users table

Revision ID: 0001
Revises:
Create Date: 2026-06-22
"""
from alembic import op
import sqlalchemy as sa

revision = '0001'
down_revision = None

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.BigInteger(), primary_key=True),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )

def downgrade():
    op.drop_table('users')

# Generate a new migration
alembic revision --autogenerate -m "add orders table"

# Apply pending migrations
alembic upgrade head

# Roll back one step
alembic downgrade -1

Zero-Downtime Migration Patterns

Pattern 1: Expand-Contract (Add Column)

-- Phase 1: Add column as nullable (old code ignores it)
ALTER TABLE users ADD COLUMN display_name VARCHAR(100);

-- Deploy code that writes to both name and display_name

-- Phase 2: Backfill display_name from name
UPDATE users SET display_name = name WHERE display_name IS NULL;

-- Phase 3: Make display_name NOT NULL
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

-- Deploy code that reads only display_name

-- Phase 4: Drop old column
ALTER TABLE users DROP COLUMN name;

Pattern 2: Rename Column Safely

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Step 2: Dual-write both columns in application code
-- UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Step 3: Stop writing to old column

-- Step 4: Drop old column
ALTER TABLE users DROP COLUMN email;

Pattern 3: Large Table Backfill

-- BAD: Single UPDATE locks the table for minutes
UPDATE orders SET user_id_new = user_id WHERE user_id_new IS NULL;

-- GOOD: Backfill in batches
DO $$
DECLARE
    batch_size INT := 10000;
    updated INT;
BEGIN
    LOOP
        UPDATE orders
        SET user_id_new = user_id
        WHERE user_id_new IS NULL
        AND id IN (
            SELECT id FROM orders WHERE user_id_new IS NULL
            LIMIT batch_size FOR UPDATE SKIP LOCKED
        );
        GET DIAGNOSTICS updated = ROW_COUNT;
        EXIT WHEN updated = 0;
        COMMIT;
    END LOOP;
END $$;

Handling Rollbacks

Flyway Undo (Paid Feature)

-- V3__add_last_login.sql
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Undo V3__add_last_login.sql (paid Flyway only)
ALTER TABLE users DROP COLUMN last_login;

Manual Rollback Strategy

# app/migrations/v003_add_last_login.py
def migrate_up(conn):
    conn.execute("ALTER TABLE users ADD COLUMN last_login TIMESTAMP;")

def migrate_down(conn):
    conn.execute("ALTER TABLE users DROP COLUMN last_login;")
    conn.execute("UPDATE schema_version SET applied = false WHERE version = 3;")

Rollback readiness checklist:

• Every Migration must have a rollback script
• Test rollbacks in staging before production
• Document rollback steps in runbook
• Practice rollbacks quarterly
• Never roll back after 48 hours -- create a fix-forward Migration instead

Common Migration Errors

1. Long-Running Locks on Large Tables

ALTER TABLE ADD COLUMN with a DEFAULT value on PostgreSQL locks the table. Use ADD COLUMN without DEFAULT, then UPDATE in batches.

2. Not Testing Migrations on Production-Sized Data

A Migration that takes 2 seconds on staging (10K rows) might take 2 hours on production (100M rows). Always test with production-scale data.

3. Deploying Code and Schema Simultaneously

New code that reads a new column fails if the Migration has not run. Always deploy schema changes before code changes that depend on them.

4. Ignoring Foreign Key Constraints

Adding a foreign key on a table with existing data fails if any row violates the constraint. Validate data before adding constraints.

5. No Rollback Plan

If a Migration fails halfway, can you recover? Every Migration must have a tested rollback script.

6. Running Migrations Automatically on App Startup

Auto-migrating on startup causes conflicts when multiple instances run simultaneously. Use a dedicated Migration step in CI/CD.

7. Not Versioning Seed Data

Seed data (lookup tables, default settings) must be versioned alongside schema changes. Missing seed data causes application errors.

Practice Questions

1. What is the difference between Flyway and Liquibase?

Flyway uses plain SQL files with version-number naming. Liquibase uses a changelog format (XML, YAML, JSON) that supports conditional logic and rollbacks natively.

2. How do you rename a column without downtime?

Add the new column, dual-write both columns in application code, backfill data, stop writing to the old column, then drop the old column.

3. What is the expand-contract pattern?

Add new schema elements while keeping old ones (expand), run code that supports both, then remove old elements (contract). Enables zero-downtime schema changes.

4. How do you backfill a column on a large table without locking?

Use batched UPDATE statements with LIMIT ... FOR UPDATE SKIP LOCKED to Process rows in small transactions.

5. Challenge: Plan a zero-downtime Migration.

You need to split a users.name column into first_name and last_name on a table with 50 million rows. Design the Migration steps. Answer: (1) Add first_name and last_name columns as nullable. (2) Deploy code that reads first_name/last_name and falls back to name. (3) Write writes to both old name and new fields. (4) Backfill in batches of 10K. (5) Deploy code that reads only new fields. (6) Drop old name column.

FAQ

Try It Yourself

Set up a Migration pipeline:

1. Create a local PostgreSQL database
2. Install Flyway and create a Migration V1__create_users.sql
3. Run flyway migrate and verify the table was created
4. Create V2__add_orders.sql and apply it
5. Add a rollback script for V2
6. Run flyway undo (or your rollback script) to revert

What's Next

You have learned how to version database schemas with Flyway, Liquibase, and Alembic, execute zero-downtime migrations, and plan safe rollbacks. Start by adding Flyway to your project and creating a V1 Migration for your current schema.