Database Migration Strategies Cheat Sheet

-- Step 1 (expand): add the new column, nullable, deploy app that writes to BOTH columnsALTER TABLE users ADD COLUMN full_name TEXT;-- Step 2 (backfill): populate the new column from the old one in batchesUPDATE users SET full_name = first_name || ' ' || last_nameWHERE full_name IS NULL;-- Step 3: deploy app version that reads from full_name, still writes both-- Step 4 (contract): once all instances are on the new version, drop the old columnsALTER TABLE users DROP COLUMN first_name;ALTER TABLE users DROP COLUMN last_name;

exports.up = function (knex) {  return knex.schema.alterTable('users', (table) => {    table.string('full_name');  });};exports.down = function (knex) {  return knex.schema.alterTable('users', (table) => {    table.dropColumn('full_name');  });};// CLI usage:// npx knex migrate:make add_full_name_to_users// npx knex migrate:latest// npx knex migrate:rollback

-- On the source (blue) database: create a publication for changed tablesCREATE PUBLICATION migration_pub FOR TABLE users, orders;-- On the target (green) database: subscribe to stream ongoing writesCREATE SUBSCRIPTION migration_sub  CONNECTION 'host=blue-db dbname=app'  PUBLICATION migration_pub;-- Monitor replication lag until it reaches ~0 before cutoverSELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn()FROM pg_replication_slots;-- Cutover: stop writes to blue, wait for lag to drain, repoint app to greenDROP SUBSCRIPTION migration_sub;

-- 1. Add the column nullable (fast, metadata-only change)ALTER TABLE users ADD COLUMN status TEXT;-- 2. Backfill in batches (application code or a loop), not in one giant UPDATE-- UPDATE users SET status = 'active' WHERE id BETWEEN 1 AND 1000 AND status IS NULL;-- 3. Add the constraint as NOT VALID (instant, no scan, no lock)ALTER TABLE users ADD CONSTRAINT users_status_not_null CHECK (status IS NOT NULL) NOT VALID;-- 4. Validate separately (scans but only takes a SHARE UPDATE EXCLUSIVE lock, not exclusive)ALTER TABLE users VALIDATE CONSTRAINT users_status_not_null;-- 5. Once satisfied, promote to a real NOT NULL (fast now that the check exists)ALTER TABLE users ALTER COLUMN status SET NOT NULL;ALTER TABLE users DROP CONSTRAINT users_status_not_null;

"""add index concurrently to orders.status"""from alembic import opimport sqlalchemy as sa# Concurrent index builds can't run inside a transaction blockrevision = 'a1b2c3d4'down_revision = 'f9e8d7c6'def upgrade():    with op.get_context().autocommit_block():        op.create_index(            'ix_orders_status',            'orders',            ['status'],            postgresql_concurrently=True,        )def downgrade():    with op.get_context().autocommit_block():        op.drop_index(            'ix_orders_status',            table_name='orders',            postgresql_concurrently=True,        )