Schema Migrations

-- V1__create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 000001_create_users_table.up.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 000001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

# Sequential (Flyway, golang-migrate with -seq):
V1__create_users.sql
V2__add_email_column.sql
V3__create_orders.sql

# Timestamped (Liquibase, golang-migrate without -seq):
20240115143000__create_users.sql
20240115144500__add_email_column.sql
20240116100000__create_orders.sql

-- Up: 002_add_phone_column.up.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Down: 002_add_phone_column.down.sql
ALTER TABLE users DROP COLUMN phone;

-- Up: drop a column (data loss on rollback)
ALTER TABLE users DROP COLUMN ssn;

-- Down: cannot restore dropped data
-- Option 1: explicitly fail
-- ERROR: cannot rollback migration 005_drop_ssn (data already lost)

-- Option 2: create an empty down migration and document the limitation
-- (no-op down migration)

-- Up: merge duplicate users
INSERT INTO users_merged (email, name)
SELECT email, MAX(name)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Down: cannot reliably reverse a merge
-- Document the irreversibility and require a backup before running

-- PostgreSQL 11+: metadata-only operation (instant, no table rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

-- PostgreSQL < 11: rewrites the entire table (locks for the duration)
-- Workaround: add nullable column, backfill, then add constraint
ALTER TABLE users ADD COLUMN is_active BOOLEAN;
-- Deploy code that handles NULL
UPDATE users SET is_active = TRUE WHERE is_active IS NULL;
-- In batches to avoid long locks
ALTER TABLE users ALTER COLUMN is_active SET NOT NULL DEFAULT TRUE;

Phase 1: Add new column
  ALTER TABLE users ADD COLUMN display_name TEXT;

Phase 2: Deploy code that reads from display_name, writes to both name and display_name
  Backfill: UPDATE users SET display_name = name;

Phase 3: Deploy code that only uses display_name
  ALTER TABLE users DROP COLUMN name;

-- Non-concurrent: blocks writes
CREATE INDEX idx_users_email ON users(email);

-- Concurrent: no blocking (takes longer, requires unique index name)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- If concurrent index creation fails, it leaves an INVALID index
-- Clean up manually:
DROP INDEX IF EXISTS idx_users_email;
-- Then retry

-- Phase 1: Stop using the column in application code
-- Phase 2: Verify no queries reference the column (check pg_stat_statements)
-- Phase 3: Drop the column
ALTER TABLE users DROP COLUMN legacy_field;

-- Process in batches to avoid long transactions and lock contention
DO $$
DECLARE
    batch_size INTEGER := 5000;
    processed INTEGER := 1;
BEGIN
    WHILE processed > 0 LOOP
        UPDATE orders
        SET status = 'archived'
        WHERE status = 'completed'
          AND created_at < CURRENT_DATE - INTERVAL '90 days'
        LIMIT batch_size;

        GET DIAGNOSTICS processed = ROW_COUNT;
        RAISE NOTICE 'Processed % rows', processed;
        COMMIT;
    END LOOP;
END $$;

# benefits: retries, progress tracking, rate limiting, error handling
def backfill_user_display_names(batch_size=1000):
    offset = 0
    while True:
        users = db.query("SELECT id, first_name, last_name FROM users "
                        "WHERE display_name IS NULL "
                        "ORDER BY id LIMIT %s OFFSET %s", batch_size, offset)
        if not users:
            break
        for user in users:
            display_name = f"{user.first_name} {user.last_name}"
            db.execute("UPDATE users SET display_name = %s WHERE id = %s",
                      display_name, user.id)
        offset += batch_size

-- Before migration: check table size and row count
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS size;
SELECT COUNT(*) FROM orders;

-- Time the migration
EXPLAIN ANALYZE CREATE INDEX CONCURRENTLY idx_orders_date ON orders(created_at);

-- After migration: verify
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS size;

[ ] Migration tested on staging with production-like data
[ ] Rollback migration tested and verified
[ ] Migration duration measured and acceptable
[ ] Lock timeout configured (statement_timeout, lock_timeout)
[ ] Backup taken before migration
[ ] Rollback plan documented
[ ] Monitoring in place (connection counts, query times)
[ ] Team notified of migration window
[ ] Application code deployed before or simultaneously (if expand-contract)

-- Set before running the migration
SET statement_timeout = '60s';
SET lock_timeout = '30s';

-- Or configure at the connection level
ALTER ROLE migration_user SET statement_timeout = '60s';
ALTER ROLE migration_user SET lock_timeout = '30s';

-- Add a column to the shared schema (affects all tenants)
ALTER TABLE documents ADD COLUMN encrypted BOOLEAN NOT NULL DEFAULT FALSE;

-- Backfill per tenant (to avoid locking the entire table at once)
UPDATE documents SET encrypted = TRUE WHERE tenant_id = 1;
UPDATE documents SET encrypted = TRUE WHERE tenant_id = 2;

-- Apply migration to all tenant schemas
DO $$
DECLARE
    schema_name TEXT;
BEGIN
    FOR schema_name IN
        SELECT schema_name FROM information_schema.schemata
        WHERE schema_name LIKE 'tenant_%'
    LOOP
        EXECUTE format('ALTER TABLE %I.documents ADD COLUMN encrypted BOOLEAN NOT NULL DEFAULT FALSE', schema_name);
    END LOOP;
END $$;

main branch:  V1, V2, V3, V4
feature branch: V1, V2, V3_feature

After merge: renumber V3_feature → V5 (or use timestamps)

-- BAD: irreversible change with no down migration
ALTER TABLE users DROP COLUMN email;

-- BETTER: use expand-contract pattern
-- Phase 1: Add new identity column
-- Phase 2: Deploy code using new column
-- Phase 3: Drop old column

-- BAD: migration assumes specific data exists
INSERT INTO roles (name) VALUES ('admin') ON CONFLICT DO NOTHING;
ALTER TABLE users ADD COLUMN role_id INTEGER REFERENCES roles(id);
-- If the roles table is empty or the admin role doesn't exist, the FK fails

-- BAD: updates millions of rows in one transaction
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';
-- Holds row locks for the entire duration, blocks all other access

-- GOOD: batch the update
-- See "Batched Data Migrations" section above

-- Check for invalid indexes
SELECT indexrelname::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

-- Drop and retry
DROP INDEX IF EXISTS idx_orders_date;
CREATE INDEX CONCURRENTLY idx_orders_date ON orders(created_at);