Skip to main content
Safe, reversible database schema changes for production systems.

​
When to Activate

  • Creating or altering database tables
  • Adding/removing columns or indexes
  • Running data migrations (backfill, transform)
  • Planning zero-downtime schema changes
  • Setting up migration tooling for a new project

​
Core Principles

  1. Every change is a migration — never alter production databases manually
  2. Migrations are forward-only in production — rollbacks use new forward migrations
  3. Schema and data migrations are separate — never mix DDL and DML in one migration
  4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
  5. Migrations are immutable once deployed — never edit a migration that has run in production

​
Migration Safety Checklist

Before applying any migration:
  • Migration has both UP and DOWN (or is explicitly marked irreversible)
  • No full table locks on large tables (use concurrent operations)
  • New columns have defaults or are nullable (never add NOT NULL without default)
  • Indexes created concurrently (not inline with CREATE TABLE for existing tables)
  • Data backfill is a separate migration from schema change
  • Tested against a copy of production data
  • Rollback plan documented

​
PostgreSQL Patterns

​
Adding a Column Safely

-- Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- Column with default (Postgres 11+ is instant)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

​
Adding an Index Without Downtime

-- Non-blocking, allows concurrent writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

​
Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:
1
Add new column (migration 001)
2
ALTER TABLE users ADD COLUMN display_name TEXT;
3
Backfill data (migration 002)
4
UPDATE users SET display_name = username WHERE display_name IS NULL;
5
Update application code
6
Deploy application changes to read/write both columns
7
Drop old column (migration 003)
8
ALTER TABLE users DROP COLUMN username;

​
Large Data Migrations

-- Batch update with progress
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', rows_updated;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

​
Prisma (TypeScript/Node.js)

​
Workflow

# Create migration from schema changes
npx prisma migrate dev --name add_user_avatar

# Apply pending migrations in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Generate client after schema changes
npx prisma generate

​
Custom SQL Migration

For operations Prisma cannot express (concurrent indexes, data backfills): 
# Create empty migration, then edit the SQL manually
npx prisma migrate dev --create-only --name add_email_index

-- migrations/20240115_add_email_index/migration.sql
-- Prisma cannot generate CONCURRENTLY, so we write it manually
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

​
Django (Python)

​
Data Migration

from django.db import migrations

def backfill_display_names(apps, schema_editor):
    User = apps.get_model("accounts", "User")
    batch_size = 5000
    users = User.objects.filter(display_name="")
    while users.exists():
        batch = list(users[:batch_size])
        for user in batch:
            user.display_name = user.username
        User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)

def reverse_backfill(apps, schema_editor):
    pass  # Data migration, no reverse needed

class Migration(migrations.Migration):
    dependencies = [("accounts", "0015_add_display_name")]

    operations = [
        migrations.RunPython(backfill_display_names, reverse_backfill),
    ]

​
Zero-Downtime Migration Strategy

For critical production changes, follow the expand-contract pattern: 
Phase 1: EXPAND
  - Add new column/table (nullable or with default)
  - Deploy: app writes to BOTH old and new
  - Backfill existing data

Phase 2: MIGRATE
  - Deploy: app reads from NEW, writes to BOTH
  - Verify data consistency

Phase 3: CONTRACT
  - Deploy: app only uses NEW
  - Drop old column/table in separate migration

​
Timeline Example

Day 1: Migration adds new_status column (nullable)
Day 1: Deploy app v2 — writes to both status and new_status
Day 2: Run backfill migration for existing rows
Day 3: Deploy app v3 — reads from new_status only
Day 7: Migration drops old status column

​
Anti-Patterns

Anti-PatternWhy It FailsBetter Approach
Manual SQL in productionNo audit trail, unrepeatableAlways use migration files
Editing deployed migrationsCauses drift between environmentsCreate new migration instead
NOT NULL without defaultLocks table, rewrites all rowsAdd nullable, backfill, then add constraint
Inline index on large tableBlocks writes during buildCREATE INDEX CONCURRENTLY
Schema + data in one migrationHard to rollback, long transactionsSeparate migrations
Dropping column before removing codeApplication errors on missing columnRemove code first, drop column next deploy