Database Migration Strategies: Changing Schemas Without Breaking Production

Database schema changes are inevitable in evolving systems, but every migration risks downtime, data corruption, or inconsistent behavior in production. This page details how to evolve a database schema incrementally—without halting the service—by applying proven strategies that keep the system live, consistent, and resilient during and after schema changes.

Incremental Schema Evolution

Change the database schema without rolling back, without locking tables for hours, and without requiring a full deployment cycle. The core idea: introduce new schema elements alongside existing ones, and manage the transition from old to new formats through application logic and migration scripts.

Add a Column Without Breaking Existing Code

When adding a column to an existing table, use ALTER TABLE ... ADD COLUMN with a default value and make the column nullable. The application can then write to and read from the column while the old code continues to function.

ALTER TABLE users ADD COLUMN preferred_language VARCHAR(10) DEFAULT 'en' NULL;

The application reads the column as optional, and for existing rows, preferred_language is NULL until updated. Use COALESCE(preferred_language, 'en') in queries to fall back to the default.

A common failure: forgetting to set NULL on the new column. If the column is NOT NULL, existing rows with NULL values fail to insert or update, causing silent failures. Always add NULL explicitly unless the default is sufficient.

Add a Column with a Full Migration

When the new column requires data population and cannot tolerate NULL, use a two-phase migration:

Add the column as NOT NULL with a default.
Run a background job to populate the column.
Update the application to use the new column.

ALTER TABLE users ADD COLUMN preferred_language VARCHAR(10) NOT NULL DEFAULT 'en';

Then, in the application code, write a job that iterates over all users and populates preferred_language using a rule: if the user’s locale is 'fr', set preferred_language to 'fr', else use 'en'.


import psycopg2

def populate_preferred_language():
    with psycopg2.connect("host=prod dbname=users user=app") as conn:
        with conn.cursor() as cur:
            cur.execute("""
                UPDATE users
                SET preferred_language = CASE
                    WHEN locale = 'fr' THEN 'fr'
                    ELSE 'en'
                END
                WHERE preferred_language IS NULL
            """)
            conn.commit()

After the job completes, the application can assume all rows have valid values. The migration is now complete, and the column can be optimized (e.g., indexed, made non-nullable, or used in queries).

Migrate Data Between Tables with a Shadow Table

When reorganizing data—e.g., splitting a users table into profiles and user_metadata—use a shadow table to coexist with the original.

CREATE TABLE profiles (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    name VARCHAR(255),
    bio TEXT,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);

Insert new data into profiles while reading from both users and profiles. Use SELECT * FROM users and SELECT * FROM profiles in the application, and merge results in code.

When ready, update the application to write to profiles and read from profiles only. Then, run a migration that copies all existing users rows to profiles.

INSERT INTO profiles (id, user_id, name, bio, created_at, updated_at)
SELECT id, id, name, bio, created_at, updated_at FROM users;

Once copied, run a job to update the user_id foreign key constraint:

ALTER TABLE profiles ADD CONSTRAINT fk_profiles_user_id
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

The shadow table strategy allows for zero-downtime schema evolution. While the new table is populated, the old one remains live. The application can gradually shift from reading from the old to the new, and the migration can be validated before final cutover.

Use a Two-Phase Migration with Feature Flags

When introducing a new schema that requires application logic changes, use feature flags to control the transition.

Add a new table (user_preferences) with a jsonb column for flexible configuration.
Deploy the application to write to and read from user_preferences.
Enable the feature flag use_new_preferences for a subset of users.

CREATE TABLE user_preferences (
    user_id UUID PRIMARY KEY,
    settings JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

In the application, when fetching user preferences:

def get_preferences(user_id: UUID) -> dict:
    if feature_flag_enabled("use_new_preferences"):
        with db.cursor() as cur:
            cur.execute("""
                SELECT settings FROM user_preferences
                WHERE user_id = %s
            """, (user_id,))
            result = cur.fetchone()
            if result:
                return result[0]
    # fallback to old method
    return get_old_preferences(user_id)

After validation, gradually enable the flag for all users. When stable, disable the old logic and remove the fallback code.

Handle Schema Drift with a Migration Tracker

Track applied migrations using a migrations table:

CREATE TABLE migrations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    version VARCHAR(20) NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    checksum VARCHAR(64) NOT NULL
);

Each migration script includes a name, version, and checksum. The application runs migrations in order, using the applied_at timestamp to detect missing or duplicated migrations.

Example migration script (002_add_preferred_language.sql):

-- name: add_preferred_language
-- version: 1.2.0
-- checksum: a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a1b2c3d4e5f6a7b8c9d0e1f2a3b4
ALTER TABLE users ADD COLUMN preferred_language VARCHAR(10) DEFAULT 'en' NULL;

The migration runner checks the migrations table and applies any missing scripts. If a migration script is run twice, the runner logs a warning: Migration already applied: add_preferred_language.

A silent failure: missing checksum column. Without it, the runner cannot detect changes in the migration script, leading to skipped or duplicated migrations.

Support Multiple Schema Versions in the Same Database

When rolling out schema changes across multiple services, use versioned schemas in a single database.

Create a schema_version table:

CREATE TABLE schema_version (
    version VARCHAR(20) NOT NULL PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    description TEXT
);

Each service reads and writes to its own schema, but shares the same database. The users service uses users_v1 and users_v2 schemas. The application can query:

SELECT u.id, u.name, p.status
FROM users_v1.users u
JOIN users_v2.preferences p ON u.id = p.user_id;

When a new version is ready, promote it to users_v2 and update the schema_version table:

INSERT INTO schema_version (version, description)
VALUES ('2.0.0', 'Added support for dark mode preferences');

The application can read from users_v1 while writing to users_v2, allowing for a smooth transition between schema versions.

Use Row-Level Versioning for Schema Evolution

For systems requiring historical tracking, use a versioned table pattern.

CREATE TABLE user_versions (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    version INT NOT NULL,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Each update to a user creates a new row in user_versions. The application reads the latest version (version = MAX(version)) and applies changes in a versioned manner.

A common mistake: forgetting to index user_id and version. Without it, queries become slow as the table grows. Always add:

CREATE INDEX idx_user_versions_user_id_version ON user_versions (user_id, version);

Additionally, when updating a user, use a UPSERT operation:

INSERT INTO user_versions (id, user_id, version, data)
VALUES (gen_random_uuid(), $1, $2, $3)
ON CONFLICT (user_id, version)
DO UPDATE SET data = EXCLUDED.data, updated_at = NOW();

This pattern allows for backward compatibility, schema drift, and audit trails.

Handle Schema Changes with Atomic Transactions

When multiple tables must be updated together, use transactions to ensure consistency.

BEGIN;

ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT FALSE;
ALTER TABLE user_logs ADD COLUMN verified_at TIMESTAMPTZ;

INSERT INTO user_logs (user_id, action, details)
SELECT id, 'verified', '{"reason": "migration"}'
FROM users
WHERE is_verified = TRUE;

COMMIT;

If any step fails, the entire migration rolls back. A failure mode: missing COMMIT or ROLLBACK—the transaction remains open, blocking other queries, and leaving the database in an inconsistent state.

Use SAVEPOINT for partial rollbacks:

SAVEPOINT add_columns;

ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

SAVEPOINT update_logs;

UPDATE users SET last_login = NOW() WHERE is_verified = TRUE;

-- if something fails after update_logs:
ROLLBACK TO SAVEPOINT update_logs;

This allows granular recovery and reduces the impact of failure.

Handle Schema Drift in Migrations

When multiple services use the same database, schema drift occurs: one service updates schema, but others are out of sync.

Detect drift using a schema_drift table:

CREATE TABLE schema_drift (
    service_name VARCHAR(100) NOT NULL,
    schema_version VARCHAR(20) NOT NULL,
    last_checked TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Each service runs a periodic check:

# check_schema_drift.sh
#!/bin/bash

psql -c "
    INSERT INTO schema_drift (service_name, schema_version)
    VALUES ('orders', '1.3.0')
    ON CONFLICT (service_name)
    DO UPDATE SET schema_version = EXCLUDED.schema_version, last_checked = NOW()
"

A failure: the service updates schema_version but forgets to update last_checked. The monitoring system detects drift but cannot determine when the check last ran.

Use SELECT * FROM schema_drift WHERE last_checked < NOW() - INTERVAL '1 hour' to detect stale checks.

Summary of Key Patterns

Add a column: ALTER TABLE ... ADD COLUMN ... DEFAULT ... NULL
Populate data: use background jobs to populate NOT NULL columns after schema change
Shadow table: coexist old and new tables during migration
Feature flag: control transition between old and new schema
Migration tracker: use migrations table with name, version, checksum
Versioned schemas: use schema_version and schema-qualified queries
Row-level versioning: user_versions with version and data fields
Atomic transactions: use BEGIN, COMMIT, ROLLBACK, SAVEPOINT
Schema drift detection: schema_drift table with service-specific checks

These patterns are not tied to a specific framework or product. They work in any environment with SQL and a process for running migrations. They are resilient, observable, and predictable. They form the foundation for evolving a database schema without breaking production.

This page was rewritten on 18 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.