Database Migrations Without Downtime: Expand/Contract, Shadow Tables, and Safe Deploys

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- See what locks are currently held and waiting
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- See lock conflicts explicitly
SELECT
  blocked.pid,
  blocked_activity.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_locks blocked
JOIN pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_locks blocking
  ON blocking.relation = blocked.relation AND blocking.granted AND NOT blocked.granted
JOIN pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid;

1
2
3

-- DO NOT DO THIS on a live database
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Old code: SELECT name FROM users → ERROR: column "name" does not exist

1
2

-- Migration: add new column (fast, no table rewrite)
ALTER TABLE users ADD COLUMN full_name TEXT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# Application code after phase 1: write to BOTH columns
def create_user(full_name: str) -> User:
    return db.execute(
        "INSERT INTO users (name, full_name) VALUES (%s, %s) RETURNING *",
        (full_name, full_name)
    ).fetchone()

def update_user_name(user_id: int, full_name: str):
    db.execute(
        "UPDATE users SET name = %s, full_name = %s WHERE id = %s",
        (full_name, full_name, user_id)
    )

def get_user(user_id: int) -> User:
    # Read from old column — new column may not be populated for old rows yet
    return db.execute("SELECT *, COALESCE(full_name, name) AS full_name FROM users WHERE id = %s", (user_id,)).fetchone()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- Backfill in batches (never one giant UPDATE on a large table)
DO $$
DECLARE
  batch_size INT := 1000;
  last_id BIGINT := 0;
  max_id BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM users;
  WHILE last_id < max_id LOOP
    UPDATE users
    SET full_name = name
    WHERE id > last_id
      AND id <= last_id + batch_size
      AND full_name IS NULL;

    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.01);  -- brief pause to avoid I/O saturation
  END LOOP;
END $$;

1
2
3
4

# Application code after backfill: read from new column
def get_user(user_id: int) -> User:
    return db.execute("SELECT * FROM users WHERE id = %s", (user_id,)).fetchone()
    # full_name is now the authoritative column

1
2

-- Only after verifying all application instances use full_name
ALTER TABLE users DROP COLUMN name;

1
2
3
4
5
6

# Application code: write only to new column
def create_user(full_name: str) -> User:
    return db.execute(
        "INSERT INTO users (full_name) VALUES (%s) RETURNING *",
        (full_name,)
    ).fetchone()

1
2
3
4

-- PostgreSQL 11+: adding a column with a constant default is instant
-- (stored as a table-level default, not written to each row)
ALTER TABLE orders ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';
-- No table rewrite — immediate ✓

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- Step 1: Add as nullable (instant)
ALTER TABLE events ADD COLUMN processed_at TIMESTAMPTZ;

-- Step 2: Backfill existing rows (batched)
UPDATE events SET processed_at = created_at WHERE processed_at IS NULL;

-- Step 3: Add NOT NULL constraint with a validation check
-- (validates without a full lock in PostgreSQL 12+)
ALTER TABLE events
  ADD CONSTRAINT events_processed_at_not_null
  CHECK (processed_at IS NOT NULL)
  NOT VALID;  -- marks existing rows as already validated (trusted)

-- Step 4: Validate the constraint (uses ShareUpdateExclusiveLock — allows reads/writes)
ALTER TABLE events VALIDATE CONSTRAINT events_processed_at_not_null;

-- Step 5: Now you can safely add NOT NULL (uses the validated constraint)
-- In a later migration after step 4 is deployed and running:
ALTER TABLE events ALTER COLUMN processed_at SET NOT NULL;
ALTER TABLE events DROP CONSTRAINT events_processed_at_not_null;

1
2
3
4
5
6

-- Blocks writes for potentially minutes on large tables — NEVER in prod
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- CONCURRENTLY: takes ShareUpdateExclusiveLock, allows reads AND writes
-- Takes 2-3x longer, but zero downtime
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

1
2
3
4
5
6
7
8
9

-- Check for invalid indexes (left behind by failed CONCURRENTLY)
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

-- Clean up an invalid index
DROP INDEX CONCURRENTLY idx_orders_user_id;
-- Then recreate
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

1
2
3
4

-- Set a lock timeout — fail if we can't acquire the lock within 5 seconds
SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;
-- If this hangs, it fails after 5s rather than blocking all queries

1
2
3
4
5

# In your migration framework:
def run_migration_safely(conn, sql: str, lock_timeout: str = "5s"):
    conn.execute(f"SET lock_timeout = '{lock_timeout}'")
    conn.execute(f"SET statement_timeout = '60s'")
    conn.execute(sql)

1
2
3
4
5
6
7
8

-- integer → bigint: instant in PostgreSQL (widens in place)
ALTER TABLE events ALTER COLUMN user_id TYPE BIGINT;

-- varchar(50) → varchar(255): instant (just changes metadata)
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(255);

-- varchar(255) → text: instant
ALTER TABLE users ALTER COLUMN description TYPE TEXT;

1
2

-- text → integer: requires data validation and a full table rewrite
-- DO NOT run directly — use the expand/contract approach

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Phase 1: Add new typed column
ALTER TABLE products ADD COLUMN price_cents INTEGER;

-- Phase 2: Backfill (validate data in application code first)
UPDATE products
SET price_cents = (price::NUMERIC * 100)::INTEGER
WHERE price_cents IS NULL;

-- Phase 3: Switch application writes, verify, then drop old column
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;

1
2
3
4

-- Safe timing: only after ALL running application instances
-- have been deployed with code that no longer references this column

ALTER TABLE users DROP COLUMN legacy_token;

1
2
3
4
5
6
7

# Django: ignore a column before dropping it
class User(models.Model):
    # Mark as deferred — won't be included in default SELECT *
    legacy_token = models.TextField(null=True, db_column='legacy_token')

    class Meta:
        managed = False  # or use a custom migration

1
2
3
4
5

# Rails: use ignored_columns before dropping
class User < ApplicationRecord
  self.ignored_columns = ["legacy_token"]
end
# Deploy this, then run the DROP COLUMN migration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Batched backfill with pauses — run this as a background job, not a migration
DO $$
DECLARE
  batch_size  INT     := 5000;
  updated     INT     := 0;
  total       INT     := 0;
  last_id     BIGINT  := 0;
BEGIN
  LOOP
    UPDATE orders
    SET    tax_amount = subtotal * 0.1
    WHERE  id > last_id
      AND  id <= last_id + batch_size
      AND  tax_amount IS NULL;

    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;

    total   := total + updated;
    last_id := last_id + batch_size;

    RAISE NOTICE 'Backfilled % rows (total: %)', updated, total;
    PERFORM pg_sleep(0.05);  -- 50ms pause between batches
  END LOOP;
END $$;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# A Celery / RQ / Sidekiq job that runs the backfill incrementally
def backfill_tax_amount(batch_size: int = 1000, max_batches: int = 100):
    """Run as a recurring job until backfill completes."""
    with db.transaction():
        rows = db.execute("""
            UPDATE orders
            SET tax_amount = subtotal * 0.1
            WHERE id IN (
                SELECT id FROM orders
                WHERE tax_amount IS NULL
                LIMIT %s
                FOR UPDATE SKIP LOCKED   ← avoids deadlocks with concurrent runs
            )
            RETURNING id
        """, (batch_size,)).fetchall()

    if not rows:
        logger.info("Backfill complete")
        return

    logger.info(f"Backfilled {len(rows)} rows")
    # Reschedule if there may be more
    backfill_tax_amount.apply_async(countdown=1)  # 1 second delay

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- Phase 1: Create new structure
CREATE TABLE user_profiles (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id) UNIQUE,
  bio        TEXT,
  avatar_url TEXT,
  location   TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Phase 2: Create sync trigger (new writes to users also write to user_profiles)
CREATE OR REPLACE FUNCTION sync_user_to_profile()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO user_profiles (user_id, bio, avatar_url, location)
    VALUES (NEW.id, NEW.bio, NEW.avatar_url, NEW.location)
    ON CONFLICT (user_id) DO UPDATE SET
      bio        = EXCLUDED.bio,
      avatar_url = EXCLUDED.avatar_url,
      location   = EXCLUDED.location,
      updated_at = NOW();
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE user_profiles SET
      bio        = NEW.bio,
      avatar_url = NEW.avatar_url,
      location   = NEW.location,
      updated_at = NOW()
    WHERE user_id = NEW.id;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM user_profiles WHERE user_id = OLD.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_user_profile_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_to_profile();

-- Phase 3: Backfill existing rows (batched)
INSERT INTO user_profiles (user_id, bio, avatar_url, location, created_at)
SELECT id, bio, avatar_url, location, created_at
FROM users
ON CONFLICT (user_id) DO NOTHING;

-- Phase 4 & 5: Application switches to reading/writing user_profiles
-- (Deploy new application code — the trigger keeps both in sync during cutover)

-- Phase 6: Remove trigger and old columns
DROP TRIGGER sync_user_profile_trigger ON users;
DROP FUNCTION sync_user_to_profile();
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users DROP COLUMN avatar_url;
ALTER TABLE users DROP COLUMN location;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Check for rows in users that are missing from user_profiles
SELECT u.id
FROM users u
LEFT JOIN user_profiles up ON up.user_id = u.id
WHERE up.user_id IS NULL;
-- Should return 0 rows

-- Check for diverged data
SELECT u.id, u.bio, up.bio AS profile_bio
FROM users u
JOIN user_profiles up ON up.user_id = u.id
WHERE u.bio IS DISTINCT FROM up.bio
LIMIT 20;
-- Should return 0 rows

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- V1__Create_users.sql
CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT NOT NULL
);

-- V2__Add_full_name.sql
ALTER TABLE users ADD COLUMN full_name TEXT;
UPDATE users SET full_name = name;

-- V3__Drop_name.sql
ALTER TABLE users DROP COLUMN name;
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# docker-compose.yml — run Flyway before starting the app
services:
  migrate:
    image: flyway/flyway:10
    command: migrate
    environment:
      FLYWAY_URL: jdbc:postgresql://db:5432/app
      FLYWAY_USER: app
      FLYWAY_PASSWORD: secret
      FLYWAY_LOCATIONS: filesystem:/migrations
    volumes:
      - ./migrations:/migrations
    depends_on:
      db:
        condition: service_healthy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# changelog.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: jmoon
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: full_name
                  type: TEXT
      rollback:
        - dropColumn:
            tableName: users
            columnName: full_name

1
2
3
4
5
6
7
8
9

# Install
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Create migration files
migrate create -ext sql -dir migrations -seq add_full_name_to_users

# Generates:
# migrations/000002_add_full_name_to_users.up.sql
# migrations/000002_add_full_name_to_users.down.sql

1
2
3
4
5

-- 000002_add_full_name_to_users.up.sql
ALTER TABLE users ADD COLUMN full_name TEXT;

-- 000002_add_full_name_to_users.down.sql
ALTER TABLE users DROP COLUMN full_name;

1
2
3
4
5

# Run migrations
migrate -path migrations -database "postgres://user:pass@localhost/app?sslmode=disable" up

# Roll back one step
migrate -path migrations -database "..." down 1

1
2
3
4
5
6

# pgroll migration definition
operations:
  - rename_column:
      table: users
      from: name
      to: full_name

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# Gemfile
gem 'strong_migrations'

# A migration that would cause downtime is caught at development time:
class AddIndexToOrders < ActiveRecord::Migration[7.1]
  def change
    add_index :orders, :user_id  # ← strong_migrations raises:
    # StrongMigrations::UnsafeMigration:
    # Adding an index non-concurrently blocks reads and writes.
    # Use: add_index :orders, :user_id, algorithm: :concurrently
  end
end

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django_pg_zero_downtime_migrations.backends.postgresql',
        ...
    }
}

# Now Django migrations automatically use CONCURRENTLY for indexes,
# NOT VALID for constraints, and other safe patterns

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# GitHub Actions: safe migration workflow
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Run migrations with timeout and rollback on failure
        env:
          DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}
        run: |
          # Dry-run first: validate migration SQL is syntactically correct
          migrate -path ./migrations -database "$DATABASE_URL" \
            -dry-run up 2>&1 | tee /tmp/migration-plan.txt

          echo "=== Migration plan ==="
          cat /tmp/migration-plan.txt

          # Apply with a timeout
          timeout 300 migrate -path ./migrations -database "$DATABASE_URL" up \
            || { echo "Migration failed or timed out"; exit 1; }

      - name: Verify schema version
        run: |
          # Confirm expected version is applied
          migrate -path ./migrations -database "$DATABASE_URL" version