Database migrations

Database Migration Patterns

Flyway (Java/SQL)

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

-- V2__add_users_status.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active' NOT NULL;

-- V2.1__backfill_users_status.sql (repeatable: R__)
-- R__views.sql — re-run when checksum changes
CREATE OR REPLACE VIEW active_users AS
  SELECT * FROM users WHERE status = 'active';

# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=app
flyway.password=${DB_PASS}
flyway.locations=classpath:db/migration
flyway.baselineOnMigrate=true
flyway.validateOnMigrate=true
flyway.outOfOrder=false

Alembic (Python/SQLAlchemy)

# alembic/env.py — autogenerate config
from app.models import Base
target_metadata = Base.metadata

# Generate migration
# alembic revision --autogenerate -m "add status to users"

# Migration file: versions/abc123_add_status_to_users.py
def upgrade() -> None:
    op.add_column('users', sa.Column('status', sa.String(50),
                  nullable=False, server_default='active'))
    op.create_index('ix_users_status', 'users', ['status'])

def downgrade() -> None:
    op.drop_index('ix_users_status', table_name='users')
    op.drop_column('users', 'status')

# Run migrations
# alembic upgrade head
# alembic downgrade -1
# alembic history --verbose

Zero-Downtime Migration Patterns

-- PATTERN 1: Add nullable column (safe — no lock on Postgres 11+)
ALTER TABLE orders ADD COLUMN new_col TEXT;

-- PATTERN 2: Add NOT NULL with default (use DEFAULT in pg 11+, avoids rewrite)
ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;

-- PATTERN 3: Rename column safely (expand-contract)
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Step 2: Deploy code that writes both columns
-- Step 3: Backfill
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Step 4: Add NOT NULL constraint (low-overhead if backfilled)
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
-- Step 5: Deploy code using only full_name
-- Step 6: Drop old column
ALTER TABLE users DROP COLUMN name;

-- PATTERN 4: Add index without locking
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- PATTERN 5: Add foreign key without full-table lock
ALTER TABLE orders ADD CONSTRAINT fk_orders_users
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;  -- skip historical rows
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_users;  -- validates in background

Large Table Migration

-- Batch backfill to avoid lock contention
DO $$
DECLARE
  batch_size INT := 10000;
  last_id    BIGINT := 0;
  max_id     BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM orders;
  WHILE last_id <= max_id LOOP
    UPDATE orders
    SET status = 'legacy'
    WHERE id > last_id AND id <= last_id + batch_size
      AND status IS NULL;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.1);  -- breathing room
  END LOOP;
END $$;

Migration Checklist

• Migration is idempotent (can re-run safely)
• Has downgrade() / rollback path
• Tested on production-size data snapshot
• CREATE INDEX CONCURRENTLY for new indexes on large tables
• No ALTER TABLE ... ADD COLUMN NOT NULL without DEFAULT on Postgres < 11
• Long-running backfills done in batches with sleep intervals
• Monitoring in place during deployment (row counts, lock waits, replication lag)