harsh040506/migrations

Database Migrations

Safely evolve database schemas in production with zero or minimal downtime.

---

The Expand-Contract Pattern

For any migration that would break the running application, use the three-phase expand-contract pattern:

Phase 1: EXPAND  — Add new structure without removing old
Phase 2: MIGRATE — Backfill data; update app to use new structure
Phase 3: CONTRACT — Remove old structure after app fully migrated

Each phase is a separate deployment. This eliminates the window where the migration breaks the live application.

---

Operation Risk Reference

| Operation | Lock? | Risk | Strategy | |-----------|-------|------|----------| | CREATE TABLE | No | Safe | Apply directly | | ADD COLUMN ... DEFAULT NULL | Brief | Safe | Apply directly | | ADD COLUMN ... NOT NULL DEFAULT x | Table lock (small tables only) | Risky | Add nullable, backfill, add constraint | | ADD COLUMN ... NOT NULL (no default) | Table lock | High | Expand-contract | | DROP COLUMN | Brief | App risk | Remove code first, then drop | | RENAME COLUMN | Brief | App breaks | Expand-contract | | ALTER COLUMN TYPE | Table lock | High | Expand-contract | | CREATE INDEX (naive) | Table lock | High | CREATE INDEX CONCURRENTLY | | CREATE UNIQUE INDEX (naive) | Table lock | High | CREATE UNIQUE INDEX CONCURRENTLY | | DROP INDEX | Brief | Safe | Apply directly | | ADD FOREIGN KEY | Lock, full scan | Risky | NOT VALID, then VALIDATE separately | | ADD CHECK CONSTRAINT | Table lock | High | NOT VALID, then VALIDATE | | DROP TABLE | Brief | Data loss! | Remove code references first |

---

Specific Migration Recipes

Add nullable column (safe)

-- Safe: adds column with no default or a constant default
-- PostgreSQL 11+: instant even with DEFAULT for non-volatile defaults
-- Earlier versions: instant only for NULL default

BEGIN;
ALTER TABLE orders ADD COLUMN notes TEXT;
COMMIT;

-- Rollback:
ALTER TABLE orders DROP COLUMN notes;

Add NOT NULL column to large table (zero-downtime)

-- WRONG: locks table for duration of backfill (dangerous on large tables)
ALTER TABLE orders ADD COLUMN tier TEXT NOT NULL DEFAULT 'standard';

-- RIGHT: Three steps

-- Step 1: Add nullable column (fast, no lock)
ALTER TABLE orders ADD COLUMN tier TEXT;

-- Step 2: Backfill in batches (no locks held, doesn't block reads/writes)
-- Run this from application code or a migration script
DO $$
DECLARE
  batch_size INT := 1000;
  last_id UUID;
  rows_updated INT;
BEGIN
  SELECT id INTO last_id FROM orders ORDER BY id LIMIT 1;
  
  LOOP
    UPDATE orders
    SET tier = 'standard'
    WHERE id IN (
      SELECT id FROM orders
      WHERE tier IS NULL
      ORDER BY id
      LIMIT batch_size
    );
    
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    
    PERFORM pg_sleep(0.01);  -- Small pause between batches
  END LOOP;
END $$;

-- Step 3: Add NOT NULL constraint (PostgreSQL validates without full scan if you do this right)
-- First, add a NOT VALID check to avoid table lock during validation
ALTER TABLE orders ADD CONSTRAINT orders_tier_not_null CHECK (tier IS NOT NULL) NOT VALID;

-- Then validate in a separate transaction (uses a lower lock level)
ALTER TABLE orders VALIDATE CONSTRAINT orders_tier_not_null;

-- Finally (optional), drop the check and add the real NOT NULL — this is a quick catalog update
-- in PostgreSQL 12+ after validation proves all rows satisfy it

Add index without locking (PostgreSQL)

-- WRONG: locks table for entire index build
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- RIGHT: CONCURRENTLY (no locks, can take longer)
-- Must be run OUTSIDE a transaction
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);

-- If CONCURRENT build fails (e.g., connection dropped), clean up invalid index first:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer;
-- Then retry

Add foreign key without locking

-- WRONG: Validates entire table with share lock
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- RIGHT: Two steps (PostgreSQL)
-- Step 1: Add constraint, skip validation (extremely fast)
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  NOT VALID;

-- Step 2: Validate in a separate transaction (lower lock level, can proceed with reads)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

Rename column (zero-downtime)

This is one of the most dangerous operations because renaming immediately breaks running code.

Full expand-contract for column rename:

-- Step 1 (Migration A): Add new column
ALTER TABLE orders ADD COLUMN customer_reference_id UUID;
CREATE INDEX CONCURRENTLY idx_orders_customer_ref ON orders(customer_reference_id);

-- Step 2 (Code change A): App dual-writes to both columns
-- Write to: order.customer_id AND order.customer_reference_id
-- Read from: order.customer_id (still using old one)

-- Step 3 (Migration B): Backfill old → new
UPDATE orders SET customer_reference_id = customer_id
WHERE customer_reference_id IS NULL;

-- Step 4 (Code change B): Switch reads to new column
-- Read from: order.customer_reference_id
-- Write to: both

-- Step 5 (Code change C): Stop writing to old column
-- Read from: order.customer_reference_id
-- Write to: order.customer_reference_id only

-- Step 6 (Migration C): Drop old column
-- Verify no code references customer_id first!
ALTER TABLE orders DROP COLUMN customer_id;

Change column type

-- WRONG: locks table, doesn't work if existing data can't cast
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- RIGHT: Expand-contract
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN age_v2 BIGINT;

-- Step 2: Populate via trigger (keep in sync during backfill)
CREATE OR REPLACE FUNCTION sync_age_v2()
RETURNS TRIGGER AS $$
BEGIN
  NEW.age_v2 := NEW.age::BIGINT;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_age_v2
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_age_v2();

-- Step 3: Backfill
UPDATE users SET age_v2 = age::BIGINT WHERE age_v2 IS NULL;

-- Step 4: Add NOT NULL constraint after confirming backfill complete
ALTER TABLE users ALTER COLUMN age_v2 SET NOT NULL;

-- Step 5: Switch app to use age_v2, remove trigger, drop age
DROP TRIGGER trg_sync_age_v2 ON users;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN age_v2 TO age;

---

Migration File Best Practices

Naming

20240115_143000_add_customer_tier_to_orders.sql
YYYYMMDD_HHMMSS_short_description.sql

Structure

-- Migration: Add customer_tier column to orders
-- Author: eng-team
-- Created: 2024-01-15
-- Risk: LOW — nullable column addition
-- Estimated time: <1s (instant DDL)
-- Rollback: See rollback section below

-- === FORWARD MIGRATION ===
BEGIN;

ALTER TABLE orders ADD COLUMN customer_tier TEXT
  CHECK (customer_tier IN ('standard', 'premium', 'enterprise'));

COMMENT ON COLUMN orders.customer_tier IS 'Customer tier at time of order. NULL = pre-tier-feature orders.';

COMMIT;

-- === ROLLBACK ===
-- BEGIN;
-- ALTER TABLE orders DROP COLUMN customer_tier;
-- COMMIT;

Idempotency

Migrations should be safe to apply twice:

-- Idempotent index creation
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_tier ON orders(customer_tier);

-- Idempotent column addition
ALTER TABLE orders ADD COLUMN IF NOT EXISTS customer_tier TEXT;

-- Idempotent constraint
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'orders_tier_valid'
  ) THEN
    ALTER TABLE orders ADD CONSTRAINT orders_tier_valid
      CHECK (customer_tier IN ('standard', 'premium', 'enterprise'));
  END IF;
END $$;

---

Pre-Production Checklist

Before running a migration in production:

• [ ] Test on a production-size database (not development data)
• [ ] Measure how long it takes on realistic data volume
• [ ] Confirm CONCURRENTLY is used for all new indexes
• [ ] Confirm NOT VALID is used for new FK constraints
• [ ] Backfill script tested for idempotency (run twice, correct result)
• [ ] Rollback tested end-to-end
• [ ] Application code change (if needed) is deployed BEFORE the migration
• [ ] Monitoring is watching error rate and query latency during migration window
• [ ] On-call engineer available during migration
• [ ] Maintenance window scheduled for high-risk operations

---

Lock Monitoring During Migrations

-- Monitor for blocking locks during migration
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query,
  blocked_activity.wait_event_type,
  blocked_activity.wait_event
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON (
  blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
)
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

If the migration is holding locks longer than expected:

1. Kill the migration: SELECT pg_cancel_backend(pid)
2. Kill blocking transactions if safe: SELECT pg_terminate_backend(pid)
3. Never kill without understanding what the blocking transaction was doing

Deeper Reference

For zero-downtime migration playbooks and lock mitigation SQL, see:

• references/lock-mitigation.md — lock type reference table, lock monitoring queries, and step-by-step procedures for common high-lock operations (adding NOT NULL, renaming columns)
• references/zero-downtime-patterns.md — expand-contract pattern, online index builds, table rewrites with pg_repack, and blue-green schema migration strategy