Database Migration Strategies

Database migration strategies are the deliberate, repeatable methods for evolving a database schema and data over time, ensuring consistency, reliability, and zero-downtime changes in production systems. They matter when a system’s data model must evolve—adding tables, altering columns, reorganizing relationships, or migrating data between storage formats—without interrupting service or risking data loss.

Schema Evolution and Data Migration

Schema evolution and data migration are distinct but interdependent tasks. Schema evolution modifies the structure of tables (e.g., adding a column, changing a type, altering constraints), while data migration involves transforming and moving data between schema versions or storage systems. A well-executed migration strategy coordinates both.

Schema-Only Migrations

Schema-only migrations modify the database structure with minimal impact on application availability. They are ideal for adding optional fields, introducing new indexes, or refining foreign key relationships.

Use ALTER TABLE with explicit transactional blocks to ensure atomicity:

BEGIN;

ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_email_verified_check CHECK (email_verified IS NOT NULL);
CREATE INDEX CONCURRENTLY idx_users_email_verified ON users (email_verified) WHERE email_verified = true;

COMMIT;

Key considerations:

• CREATE INDEX CONCURRENTLY allows reads during index creation, avoiding table locks.
• ALTER TABLE ... ADD COLUMN ... DEFAULT does not populate existing rows with the default value unless explicitly done.
• Use ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after ADD COLUMN to populate existing data.
• When adding a NOT NULL column, first add it with DEFAULT, then UPDATE all rows, then ALTER COLUMN ... SET NOT NULL.

Failure mode: a migration that adds a NOT NULL column without populating values fails silently. The database accepts the ALTER TABLE, but subsequent INSERT or UPDATE statements fail with NOT NULL violation, often catching teams off guard in production.

Data-Driven Migrations

Data-driven migrations transform or enrich data in the database based on business logic, data quality rules, or schema changes.

Use a background job process (e.g., sidekiq, celery, kue) that reads from a migrations table and executes migration steps:

CREATE TABLE migrations (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    status TEXT CHECK (status IN ('pending', 'running', 'completed', 'failed')),
    started_at TIMESTAMPTZ,
    finished_at TIMESTAMPTZ,
    error TEXT
);

Each migration is a SQL script or a stored procedure. Example migration script:

-- migration_2023-10-05_add_user_profile.sql
BEGIN;

-- Populate profile data from user settings
INSERT INTO profiles (user_id, bio, avatar_url, created_at)
SELECT u.id, s.value, s.value, NOW()
FROM users u
JOIN settings s ON u.id = s.user_id
WHERE s.key = 'profile.bio';

-- Update profile timestamps
UPDATE profiles
SET updated_at = NOW()
WHERE updated_at IS NULL;

-- Mark migration as complete
INSERT INTO migrations (name, status, started_at, finished_at)
VALUES ('add_user_profile', 'completed', NOW(), NOW());

COMMIT;

Run this using a migration runner:

./run-migration.sh --name add_user_profile --script migration_2023-10-05_add_user_profile.sql

Failure mode: a migration script runs, updates profiles, but fails to insert into migrations. The system assumes the migration succeeded, but it is missing from the tracking table. Use SELECT * FROM migrations WHERE name = 'add_user_profile' to verify.

Rolling Schema Migrations

Rolling schema migrations apply schema changes incrementally across a cluster of database replicas, enabling zero-downtime upgrades.

Use a migration strategy with canary and rollout phases:

1. Canary: Apply schema changes to a subset of replicas.
2. Rollout: Gradually promote the schema to all replicas.
3. Validation: Verify consistency and performance.

Example: Upgrade users table schema across a PostgreSQL cluster with two replicas.

psql -h replica1.example.com -c "
ALTER TABLE users ADD COLUMN last_login TIMESTAMP WITH TIME ZONE;
ALTER TABLE users ADD CONSTRAINT users_last_login_check CHECK (last_login IS NOT NULL);
"

# Step 2: Validate canary
psql -h replica1.example.com -c "
SELECT COUNT(*) FROM users WHERE last_login IS NULL;
SELECT COUNT(*) FROM users WHERE last_login < NOW() - INTERVAL '1 day';
"

# Step 3: Promote to all replicas
for replica in replica1 replica2 replica3 replica4; do
  psql -h $replica -c "
  ALTER TABLE users ADD COLUMN last_login TIMESTAMP WITH TIME ZONE;
  ALTER TABLE users ADD CONSTRAINT users_last_login_check CHECK (last_login IS NOT NULL);
  "
done

# Step 4: Verify rollout
psql -h master.example.com -c "
SELECT replica, COUNT(*) as count
FROM users
GROUP BY replica
ORDER BY count DESC;
"

Failure mode: a migration script alters users but does not include a CHECK constraint on last_login. The application code assumes last_login is set, but NULL values are common. The database allows NULL, but the app crashes on last_login access. The migration is applied but not validated.

Two-Phase Schema Migrations

Two-phase schema migrations decouple schema changes from data migration, using dual-write and data migration phases.

Phase 1: Dual-write Write to both old and new schema structures during a transition period.

Example: migrate from users to users_v2 with last_login and timezone columns.

-- Create new schema
CREATE TABLE users_v2 (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMP WITH TIME ZONE,
    timezone TEXT,
    metadata JSONB
);

-- Application logic: dual-write on insert
INSERT INTO users (id, name, email, created_at)
VALUES ($1, $2, $3, NOW());

INSERT INTO users_v2 (id, name, email, created_at, last_login, timezone)
VALUES ($1, $2, $3, NOW(), NULL, 'UTC');

Phase 2: Data migration Synchronize data from old to new schema using a batch job.

-- Batch job: migrate data from users to users_v2
BEGIN;

INSERT INTO users_v2 (id, name, email, created_at, last_login, timezone)
SELECT u.id, u.name, u.email, u.created_at, ul.last_login, ul.timezone
FROM users u
LEFT JOIN user_logs ul ON u.id = ul.user_id
WHERE u.id NOT IN (SELECT id FROM users_v2);

COMMIT;

Phase 3: Cutover Switch application to use users_v2 only.

-- Update application config
{
  "database": {
    "main_table": "users_v2"
  }
}

-- Run final sync and verify
psql -c "
SELECT COUNT(*) FROM users_v2 WHERE id NOT IN (SELECT id FROM users);
SELECT COUNT(*) FROM users_v2 WHERE last_login IS NULL;
"

Failure mode: a migration job runs but misses users with id = 1000. The application reads from users_v2, but id 1000 is missing. The cause is a WHERE clause that does not include NOT EXISTS, but the migration uses NOT IN with NULL values, which evaluates to UNKNOWN.

Migration Versioning and Rollback

Versioned migrations use a central tracking table to record which migrations have been applied.

CREATE TABLE schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ DEFAULT NOW(),
    description TEXT
);

Apply a migration:

INSERT INTO schema_migrations (version, description)
VALUES ('20231005120000', 'Add last_login to users');

Rollback a migration:

-- Remove migration from tracking table
DELETE FROM schema_migrations WHERE version = '20231005120000';

-- Revert schema change
ALTER TABLE users DROP COLUMN last_login;

Rollback strategy: use reverse SQL scripts.

-- rollback_20231005120000.sql
BEGIN;

ALTER TABLE users DROP COLUMN last_login;

-- Remove check constraint
ALTER TABLE users DROP CONSTRAINT users_last_login_check;

-- Update migration tracking
DELETE FROM schema_migrations WHERE version = '20231005120000';

COMMIT;

Failure mode: a rollback script runs but does not handle IF EXISTS correctly. A DROP COLUMN fails silently if the column does not exist. Use DROP COLUMN IF EXISTS and verify schema_migrations before and after.

Migration Testing and Validation

Test migrations in a staging environment with production-like data and traffic.

Use a test suite that:

1. Applies a migration.
2. Queries for expected results.
3. Runs assertions on data consistency.

Example test script:

#!/bin/bash
# test_migration.sh

set -euo pipefail

DB_HOST=staging.example.com
MIGRATION_VERSION="20231005120000"

# Apply migration
psql -h $DB_HOST -c "BEGIN; SELECT apply_migration('$MIGRATION_VERSION'); COMMIT;"

# Validate
psql -h $DB_HOST -c "
SELECT
  COUNT(*) AS total_users,
  COUNT(last_login) AS non_null_logins,
  AVG(EXTRACT(EPOCH FROM (NOW() - last_login))) AS avg_days_since_login
FROM users;
"

# Assert
if ! psql -h $DB_HOST -t -A -c "
SELECT
  CASE
    WHEN total_users > 1000 THEN 'PASS'
    ELSE 'FAIL'
  END
FROM (
  SELECT COUNT(*) AS total_users FROM users
) AS subquery
"; then
  echo "Migration failed: fewer than 1000 users after migration"
  exit 1
fi

Failure mode: a migration runs on staging but does not include a VACUUM or ANALYZE step. The query planner uses stale statistics, leading to poor performance in production. Add VACUUM ANALYZE users after applying schema changes.

Migration Orchestration

Orchestrate migrations using a job scheduler or a dedicated migration service.

Use cron to trigger migrations:

# Run daily schema migrations
0 2 * * * /opt/migrations/run-daily.sh

# Weekly data migrations
0 3 * * 1 /opt/migrations/run-weekly.sh

Orchestrate using systemd:

# /etc/systemd/system/migration.service
[Unit]
Description=Database Migration Runner
After=network.target

[Service]
Type=oneshot
ExecStart=/opt/migrations/run-migrations.sh
User=migration
Group=migration
Environment=DATABASE_URL=postgresql://prod:5432/app
TimeoutStartSec=300
TimeoutStopSec=300

[Install]
WantedBy=multi-user.target

Enable and start:

sudo systemctl enable migration
sudo systemctl start migration

Failure mode: a migration job fails to write to schema_migrations but continues execution. The next run starts before the previous one finishes, causing overlapping migrations. Use SELECT pg_try_advisory_lock(1) in the migration runner to ensure exclusive execution.

Migration Failure Handling

Handle migration failures with logging, alerts, and retry mechanisms.

Log migration status:

INSERT INTO migration_logs (
    migration_id,
    status,
    message,
    duration_ms,
    error,
    started_at,
    finished_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7
);

Use psql with ON ERROR for script-level error handling:

\set ON_ERROR_STOP on
\set ECHO all
\set HISTFILE ~/.psql_history

BEGIN;

-- Apply migration
ALTER TABLE users ADD COLUMN last_login TIMESTAMP WITH TIME ZONE;

-- If any error, exit
\echo 'Migration failed: ' || SQLSTATE
\echo 'Error: ' || SQLERRM
\quit

Failure mode: a migration script uses psql but does not set ON_ERROR_STOP. An INSERT fails, but the script continues, applying subsequent changes. The final COMMIT succeeds, but the migration is incomplete. The error is not visible in logs.

Summary: Key Patterns and Pitfalls

• Always use BEGIN/COMMIT for migrations.
• Use CREATE INDEX CONCURRENTLY for high-availability indexing.
• Track migrations in a schema_migrations table.
• Validate migrations with assertions and metrics.
• Use dual-write during schema transitions.
• Handle NULL values carefully in NOT NULL migrations.
• Orchestrate migrations with systemd, cron, or a dedicated runner.
• Log and alert on migration failures using migration_logs.
• Test migrations with realistic data and traffic.

Migration is not a one-off task. It is a continuous process of schema and data evolution, best managed with consistent patterns, rigorous validation, and tooling that survives production failures.

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.