Database Migration Best Practices from 100+ Migrations

Every production incident teaches a lesson. After 100+ database migrations—some smooth, some painful—patterns emerge. These database migration steps best practices come from real experience. Start with our database migration steps checklist for the fundamentals.

Database Migration Steps Best Practices

1. Always Test Rollback First

The rollback script is more important than the migration script:

## Rollback Testing Procedure

1. Apply migration to staging
2. Verify migration worked
3. Apply rollback
4. Verify rollback restored original state
5. Apply migration again
6. Verify migration still works

Only then proceed to production.

Why: You’ll never test rollback as carefully during an incident as you will beforehand.

2. Time Your Migrations

Know how long migrations take before production:

# Time the migration on production-sized data
time psql -h $STAGING_HOST -d staging -f migration.sql

# Compare table sizes
echo "Production rows: $(psql -t -c 'SELECT COUNT(*) FROM users')"
echo "Staging rows: $(psql -h $STAGING_HOST -t -c 'SELECT COUNT(*) FROM users')"

Why: “It took 2 minutes in staging” doesn’t mean 2 minutes in production.

3. Never Migrate and Deploy Simultaneously

Separate schema changes from code changes:

## Safe Deployment Order

✅ Correct:
1. Deploy migration (schema supports old AND new code)
2. Verify migration success
3. Deploy new application code
4. Verify application works

❌ Dangerous:
1. Deploy code + migration together
2. Hope everything works

Why: When something fails, you need to know if it’s schema or code.

4. Use Migration Frameworks Properly

Don’t bypass your migration framework:

# ❌ Running raw SQL in production
psql -f random_fix.sql

# ✅ Using migration framework
rails generate migration AddStatusToOrders status:string
rails db:migrate

Why: Migration frameworks track state. Manual changes create drift.

5. Lock Tables Deliberately

Understand what locks your migration takes:

-- Check lock requirements before running
EXPLAIN (ANALYZE, BUFFERS) 
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(255);

-- For PostgreSQL, monitor locks during migration
SELECT locktype, relation::regclass, mode, granted
FROM pg_locks 
WHERE relation = 'your_table'::regclass;

Why: Unexpected locks cause unexpected downtime.

6. Communicate More Than You Think Necessary

## Migration Communication Checklist

- [ ] 24 hours before: Announce maintenance window
- [ ] 1 hour before: Remind stakeholders
- [ ] Start: "Migration starting now"
- [ ] Progress: Updates every 5 minutes for long migrations
- [ ] Complete: "Migration complete, monitoring for issues"
- [ ] +1 hour: "No issues detected, migration successful"

Why: Silence during migrations causes anxiety and interruptions.

7. Keep Migrations Small

Break large migrations into smaller steps:

## Large Migration Breakdown

❌ One migration:
- Add 5 columns
- Create 3 indexes
- Migrate 10M rows
- Add constraints

✅ Multiple migrations:
1. Add columns (fast, low risk)
2. Create indexes concurrently (slow, no locks)
3. Migrate data in batches (slow, reversible)
4. Add constraints (fast, validates data)

Why: Smaller migrations are easier to test, faster to rollback, and simpler to debug.

8. Document Decisions

Record why you made choices:

-- Migration: 2025_11_05_add_user_preferences.sql
-- 
-- Decision: Using JSONB instead of separate table
-- Reason: Low query frequency, high flexibility needs
-- Alternative considered: user_preferences table
-- Tradeoff: Harder to query, easier to evolve schema
--

ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';

Why: Future you will thank present you.

9. Automate Verification

Don’t rely on manual checks:

#!/bin/bash
# post_migration_verify.sh

echo "Checking table exists..."
psql -c "\dt preferences" || exit 1

echo "Checking column type..."
psql -c "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'preferences'" || exit 1

echo "Checking row counts..."
EXPECTED=1000000
ACTUAL=$(psql -t -c "SELECT COUNT(*) FROM users WHERE preferences IS NOT NULL")
[ "$ACTUAL" -ge "$EXPECTED" ] || exit 1

echo "All checks passed!"

Why: Humans skip steps under pressure. Scripts don’t.

10. Learn from Incidents

After every migration incident, document what happened:

## Incident Report: Migration 2025-11-05

**What happened:** Migration locked users table for 8 minutes

**Why:** Concurrent index on 50M row table took longer than expected

**How we fixed it:** Killed migration, ran during lower traffic

**Prevention:** Test migration timing on production-sized dataset

**Action items:**
- [ ] Add timing check to pre-migration checklist
- [ ] Set up staging with production-sized data

Why: The same mistake twice is a process failure.

Making Best Practices Automatic

Knowing database migration steps best practices is one thing. Following them under pressure is another. For specific scenarios, see our guides on zero downtime migrations and PostgreSQL migrations.

Stew embeds best practices into executable runbooks. Your pre-migration checklist runs automatically. Your verification scripts execute with a click. Your rollback procedure is always ready.

Join the waitlist and make database migration best practices your default.