Database Migration Zero Downtime

Production-grade guide to database migration zero downtime covering architecture patterns, implementation strategies, testing approaches, and operational best practices for enterprise engineering teams.

Database migrations that require downtime are a bottleneck in modern service delivery. When a database schema change, data transformation, or version upgrade requires the application to stop serving requests, every minute of unavailability becomes a cost. Zero-downtime database migrations eliminate this risk: the application continues serving reads and writes throughout the migration, with no interruption to users. This is essential for systems with high availability requirements—online marketplaces, financial transaction systems, real-time analytics, and any service where downtime is a direct loss of revenue.

Preparing the Migration

Before beginning the migration, ensure the database supports concurrent schema changes and has a consistent way to track migration state. Use a migration tracking table with columns version, started_at, finished_at, and applied_by. Each migration script is a self-contained SQL file in a versioned directory, with filenames like 2024-03-15-01-add-user-roles.sql.

Ensure the application connects to the database via a connection pool that supports connection lifetime tracking and server-side connection state. Use pgbouncer or HikariCP with maxLifetime and idleTimeout configured. In HikariCP, set:

{
  "maximumPoolSize": 50,
  "minimumIdle": 10,
  "connectionInitSql": "SET client_encoding TO 'UTF8'; SET search_path TO public, pg_catalog;",
  "validationTimeout": 5000,
  "idleTimeout": 600000,
  "maxLifetime": 1800000
}

The database must be accessible in a read-write state during migration. Use pg_ctl to manage PostgreSQL instances:

sudo systemctl start postgresql
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Executing the Migration

Begin the migration by creating a new database schema version in a separate schema. Use CREATE SCHEMA IF NOT EXISTS new_schema; and apply all migration scripts in order. Ensure all data transformations are idempotent and can be run multiple times without side effects.

Use psql with a script to apply migrations:

psql -h db.example.com -U app_user -d app_db -f migrations/2024-03-15-01-add-user-roles.sql
psql -h db.example.com -U app_user -d app_db -f migrations/2024-03-15-02-enforce-unique-emails.sql

Each migration script must include a BEGIN, COMMIT, and ROLLBACK block to ensure atomicity. For example:

-- migrations/2024-03-15-01-add-user-roles.sql
BEGIN;

CREATE TABLE IF NOT EXISTS users_roles (
    user_id UUID NOT NULL,
    role_name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (user_id, role_name)
);

INSERT INTO users_roles (user_id, role_name)
SELECT u.id, 'user'
FROM users u
WHERE u.role IS NULL;

-- Ensure all user roles are unique
ALTER TABLE users_roles
ADD CONSTRAINT unique_user_role UNIQUE (user_id, role_name);

COMMIT;

Dual-Write Phase

After schema changes are applied, activate a dual-write strategy: the application writes to both the old and new schemas simultaneously. This ensures data consistency during the transition. The application must be updated to write to both schemas. For example, in a Node.js service using pg:

const { Client } = require('pg');

async function writeUserData(userId, email, role) {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();

  try {
    await client.query(
      'INSERT INTO users (id, email, role) VALUES ($1, $2, $3)',
      [userId, email, role]
    );

    await client.query(
      'INSERT INTO new_schema.users (id, email, role) VALUES ($1, $2, $3)',
      [userId, email, role]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    await client.end();
  }
}

The dual-write phase must be monitored. Log every write to both schemas and verify that all data has been duplicated. Use pg_stat_activity to monitor active queries:

SELECT 
    pid, 
    query, 
    state, 
    now() - query_start AS duration,
    wait_event_type, 
    wait_event
FROM pg_stat_activity
WHERE query LIKE '%INSERT INTO%users%'
ORDER BY duration DESC;

Read-Only Migration

While writes continue to both schemas, promote the new schema to read-only mode. This allows the application to read from the new schema while data is being synchronized. Use SET statement_timeout to prevent long-running queries from blocking the migration.

In PostgreSQL, set:

ALTER TABLE new_schema.users SET (autovacuum_enabled = false);
ALTER TABLE new_schema.users_roles SET (autovacuum_enabled = false);

-- Prevent new writes
ALTER TABLE new_schema.users SET (toast.autovacuum_enabled = false);

-- Enable read-only access
UPDATE schema_migrations 
SET status = 'read-only' 
WHERE version = '2024-03-15-02-enforce-unique-emails';

Verify that all data is consistent by comparing row counts and primary key sets between old and new schemas:

SELECT 
    'old' as source, 
    COUNT(*) as count 
FROM users
UNION ALL
SELECT 
    'new' as source, 
    COUNT(*) as count 
FROM new_schema.users;

Shadow Copy and Data Synchronization

Before switching the main read path, perform a shadow copy of the entire database to the new schema. Use pg_dump with --schema-only and --data-only:

pg_dump -h db.example.com -U app_user -d app_db \
    --schema-only \
    --file=sql/schema-only.sql \
    --schema=new_schema

pg_dump -h db.example.com -U app_user -d app_db \
    --data-only \
    --file=sql/data-only.sql \
    --table=new_schema.users \
    --table=new_schema.users_roles

Synchronize data from the old to the new schema using a rsync-style process with a change log. Maintain a change_log table:

CREATE TABLE change_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
    row_id UUID NOT NULL,
    old_data JSONB,
    new_data JSONB,
    processed BOOLEAN DEFAULT false,
    processed_at TIMESTAMP WITH TIME ZONE
);

A background job reads from change_log and applies changes to the new schema:

import psycopg2
from datetime import datetime

def sync_change_log():
    conn = psycopg2.connect(DATABASE_URL)
    cur = conn.cursor()

    cur.execute("""
        SELECT id, table_name, operation, row_id, old_data, new_data
        FROM change_log
        WHERE processed = false
        ORDER BY id
        LIMIT 1000
    """)

    for row in cur.fetchall():
        change_id, table_name, op, row_id, old_data, new_data = row

        if op == 'INSERT':
            cur.execute(
                f"INSERT INTO new_schema.{table_name} (id, data) VALUES (%s, %s)",
                (row_id, new_data)
            )
        elif op == 'UPDATE':
            cur.execute(
                f"UPDATE new_schema.{table_name} SET data = %s WHERE id = %s",
                (new_data, row_id)
            )
        elif op == 'DELETE':
            cur.execute(
                f"DELETE FROM new_schema.{table_name} WHERE id = %s",
                (row_id,)
            )

        cur.execute("UPDATE change_log SET processed = true, processed_at = %s WHERE id = %s",
                    (datetime.utcnow(), change_id))

    conn.commit()
    cur.close()
    conn.close()

Cutover to New Schema

Once data is synchronized and read-only, initiate the cutover. The application must now read from the new schema and write to both schemas, but the read path is exclusively the new schema.

Use a database-level lock to ensure no data is lost during the cutover:

-- Lock the entire database during cutover
LOCK TABLE users, users_roles IN ACCESS EXCLUSIVE MODE;

-- Update the primary schema to point to new schema
ALTER TABLE users SET (schema = 'new_schema');
ALTER TABLE users_roles SET (schema = 'new_schema');

-- Update application configuration
UPDATE app_config SET value = 'new_schema' WHERE key = 'read_schema';

After the cutover, verify that all applications are reading from the new schema and that no writes are missing.

Post-Cutover Cleanup

After the migration is complete, decommission the old schema. Archive or drop it:

-- Archive old schema
ALTER SCHEMA public RENAME TO old_public;
CREATE SCHEMA public;

-- Copy data from new schema to public
INSERT INTO public.users
SELECT * FROM new_schema.users
WHERE id NOT IN (SELECT id FROM public.users);

-- Drop new_schema
DROP SCHEMA new_schema CASCADE;

Update application configuration to use the new schema exclusively:

{
  "database": {
    "schema": "public",
    "read_only": false,
    "migration_status": "completed"
  }
}

Monitor the system for at least 24 hours. Use pg_stat_statements to track query performance:

SELECT 
    query, 
    calls, 
    total_time, 
    mean_time, 
    rows
FROM pg_stat_statements
WHERE query LIKE '%SELECT%FROM%users%'
ORDER BY total_time DESC;

Common Failure Modes

Final Verification

After the migration, verify the state:

-- Verify all migrations are applied
SELECT 
    version, 
    started_at, 
    finished_at, 
    status 
FROM schema_migrations 
ORDER BY started_at DESC;

-- Check row counts
SELECT 
    'users' as table, 
    COUNT(*) as count 
FROM public.users
UNION ALL
SELECT 
    'users_roles' as table, 
    COUNT(*) as count 
FROM public.users_roles;

-- Check that all new schema tables exist
SELECT 
    schemaname, 
    tablename 
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY tablename;

A zero-downtime database migration is complete when the application reads and writes to the new schema, all data is synchronized, and the old schema is either archived or dropped. The migration has succeeded not when the script runs, but when the database continues to serve requests without interruption.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.