Database Schema Migrations Without Downtime

Table of Contents:

• Why Schema Migrations Cause Downtime
• The Expand-Contract Pattern (Core Principle)
• Safe vs Unsafe Schema Changes
• Adding Columns Without Downtime
• Renaming Columns (3-Step Process)
• Changing Column Types
• Dropping Columns and Tables
• Adding Indexes Without Locking
• Data Backfill Strategies
• Migration Automation and Tooling
• Rollback Strategies
• Production Checklist
• Frequently Asked Questions

Why Schema Migrations Cause Downtime

Short answer: Database schema migrations cause downtime when they acquire table locks (ALTER TABLE TYPE, ADD NOT NULL constraint), perform full table scans, or break running application code — solved with expand-contract pattern, dual-write, and backward-compatible changes.

If you searched "database schema migrations without downtime", you're deploying schema changes to production PostgreSQL/MySQL with 99.99% uptime SLAs, coordinating database and application releases, or replacing manual migration processes.

Key Takeaways:

• Locking operations block reads/writes (ALTER COLUMN TYPE, ADD CONSTRAINT NOT NULL without DEFAULT)
• Expand-contract pattern ensures old and new code work simultaneously (3 deployments: expand → migrate → contract)
• Add columns as nullable with DEFAULT → backfill → add NOT NULL in separate migration
• Rename columns in 3 steps: add new column, dual-write, drop old column (not atomic rename)
• Online DDL (PostgreSQL CREATE INDEX CONCURRENTLY, MySQL ALGORITHM=INPLACE) avoids locking
• Data backfills run as async jobs (not in migration) to avoid blocking deployment

This guide covers database schema migrations without downtime with production PostgreSQL/MySQL patterns, Alembic/Flyway automation, and the validation framework we deploy at HinterBuild for backend systems.

The Expand-Contract Pattern (Core Principle)

Never make breaking changes in a single deployment. The expand-contract pattern ensures database and application compatibility during migrations.

Three-Phase Process

┌─────────────────────────────────────────────────────────────────┐
│ Phase 1: EXPAND (Backward Compatible Change)                   │
│ - Add new column/table (old code ignores it)                   │
│ - Deploy application v2 (dual-write to old + new schema)       │
├─────────────────────────────────────────────────────────────────┤
│ Phase 2: MIGRATE (Data Transformation)                          │
│ - Backfill data to new schema                                  │
│ - Verify data consistency                                       │
│ - Deploy application v3 (read from new schema, fallback to old)│
├─────────────────────────────────────────────────────────────────┤
│ Phase 3: CONTRACT (Remove Old Schema)                           │
│ - Deploy application v4 (only uses new schema)                 │
│ - Drop old column/table                                        │
└─────────────────────────────────────────────────────────────────┘

Key principle: At every stage, old and new code must work simultaneously.

Example: Rename users.name → users.full_name

-- ❌ WRONG: Atomic rename breaks running code
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Old code crashes: column "name" does not exist

-- ✅ CORRECT: 3-step process

-- Step 1 (Deployment 1): EXPAND — Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Application v1: Write to BOTH columns (dual-write)
-- INSERT INTO users (name, full_name) VALUES ('Alice Smith', 'Alice Smith');

-- Step 2 (Deployment 2): MIGRATE — Backfill data
UPDATE users SET full_name = name WHERE full_name IS NULL;
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

-- Application v2: Read from full_name, fallback to name
-- SELECT COALESCE(full_name, name) AS name FROM users;

-- Step 3 (Deployment 3): CONTRACT — Drop old column
-- Application v3: Only read/write full_name
ALTER TABLE users DROP COLUMN name;

Timeline: 3 deployments over 1–2 weeks (allows testing and rollback between phases).

For deployment strategies, see our zero downtime deployment guide.

Safe vs Unsafe Schema Changes

Safe Changes (No Downtime Risk)

✅ Add nullable column (no default)

-- PostgreSQL: Instant (metadata-only change)
ALTER TABLE users ADD COLUMN email VARCHAR(255);

✅ Add column with DEFAULT (PostgreSQL 11+)

-- PostgreSQL 11+: Instant (stores default in metadata, doesn't rewrite table)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

✅ Drop column (if application no longer reads it)

-- Safe if code already deployed without referencing column
ALTER TABLE users DROP COLUMN deprecated_field;

✅ Create index concurrently

-- PostgreSQL: Non-blocking
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL 5.7+: Online DDL
ALTER TABLE users ADD INDEX idx_users_email (email), ALGORITHM=INPLACE, LOCK=NONE;

✅ Increase VARCHAR length

-- PostgreSQL: Instant (no rewrite)
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(500);  -- From VARCHAR(255)

Unsafe Changes (Cause Downtime Without Mitigation)

❌ Add NOT NULL constraint without DEFAULT

-- ❌ Full table scan + validation + blocks writes
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

Safe alternative:

-- Step 1: Add nullable column with default
ALTER TABLE users ADD COLUMN email VARCHAR(255) DEFAULT 'unknown@example.com';

-- Step 2: Backfill real values (async job)
-- UPDATE users SET email = fetch_email_from_auth_service(id);

-- Step 3: Add NOT NULL constraint (fast because all rows have values)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

❌ Change column type (incompatible types)

-- ❌ Full table rewrite (locks table)
ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(10);  -- From INTEGER

Safe alternative: Expand-contract with new column.

❌ Add foreign key constraint

-- ❌ Full table scan to validate constraint
ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);

Safe alternative:

-- Step 1: Add constraint NOT VALID (skips existing rows)
ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: Validate constraint (locks rows being checked, not whole table)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user_id;

❌ Rename table

-- ❌ Breaks all queries immediately
ALTER TABLE users RENAME TO customers;

Safe alternative: Create view as alias, migrate code gradually.

Adding Columns Without Downtime

Pattern 1: Nullable Column (Instant)

-- migration_001_add_email.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Application code handles NULL
-- SELECT id, name, email FROM users WHERE email IS NOT NULL;

Application compatibility:

user = db.execute("SELECT id, name FROM users WHERE id = ?", user_id)

# New code (handles email)
user = db.execute("SELECT id, name, email FROM users WHERE id = ?", user_id)
# email can be NULL — app handles gracefully

Pattern 2: Column with Default (PostgreSQL 11+)

-- migration_002_add_status.sql
-- PostgreSQL 11+: Instant (default stored in pg_attrdef, not in rows)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

PostgreSQL 10 and earlier:

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- Step 2: Set default for NEW rows only
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Step 3: Backfill existing rows (async, in batches)
-- See backfill section below

Pattern 3: Required Column (3-Step Process)

-- Step 1 (Deployment 1): Add nullable column with default
ALTER TABLE users ADD COLUMN email VARCHAR(255) DEFAULT 'temp@example.com';

-- Application v1: Start writing to new column
-- INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Step 2 (Deployment 2): Backfill real values
-- Run async job to populate email from external auth service

-- Step 3 (Deployment 3): Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email DROP DEFAULT;  -- Remove temp default
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

For PostgreSQL performance patterns, see our database optimization guide.

Renaming Columns (3-Step Process)

Never use ALTER TABLE RENAME COLUMN in production. It breaks old code immediately.

Rename name → full_name

Step 1: Add New Column (Deployment 1)

-- migration_003_add_full_name.sql
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

# application/models.py — Dual-write to both columns
class User:
    def save(self):
        db.execute("""
            INSERT INTO users (name, full_name, email)
            VALUES (?, ?, ?)
            ON CONFLICT (id) DO UPDATE SET
                name = EXCLUDED.name,
                full_name = EXCLUDED.name  -- Copy name to full_name
        """, self.name, self.name, self.email)

Step 2: Backfill Data (Deployment 2)

-- migration_004_backfill_full_name.sql
-- Copy data from old column to new column
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

# application/models.py — Dual-read (prefer new column, fallback to old)
class User:
    @property
    def display_name(self):
        return self.full_name or self.name  # Prefer full_name

Step 3: Drop Old Column (Deployment 3)

-- migration_005_drop_name.sql
ALTER TABLE users DROP COLUMN name;

# application/models.py — Only use new column
class User:
    @property
    def display_name(self):
        return self.full_name  # Only new column

Timeline: 1–2 weeks between deployments (allows rollback if issues detected).

Changing Column Types

Pattern 1: Compatible Type Change (Safe)

Increasing VARCHAR length or numeric precision:

-- PostgreSQL: Instant (no rewrite)
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(500);  -- From VARCHAR(255)

-- PostgreSQL: Instant for numeric precision increase
ALTER TABLE orders ALTER COLUMN total TYPE NUMERIC(12,2);  -- From NUMERIC(10,2)

Pattern 2: Incompatible Type Change (Expand-Contract)

Change users.age from INTEGER to VARCHAR:

-- ❌ WRONG: Full table rewrite
ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(10);

-- ✅ CORRECT: 3-step process

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN age_str VARCHAR(10);

-- Application v1: Dual-write
-- INSERT INTO users (age, age_str) VALUES (30, '30');

-- Step 2: Backfill
UPDATE users SET age_str = age::TEXT WHERE age_str IS NULL;
ALTER TABLE users ALTER COLUMN age_str SET NOT NULL;

-- Application v2: Read from age_str, fallback to age
-- SELECT COALESCE(age_str, age::TEXT) AS age FROM users;

-- Step 3: Drop old column
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN age_str TO age;

Pattern 3: Change created_at from TIMESTAMP to TIMESTAMPTZ

-- Step 1: Add new column with timezone
ALTER TABLE users ADD COLUMN created_at_tz TIMESTAMPTZ;

-- Step 2: Backfill (convert existing timestamps to UTC)
UPDATE users SET created_at_tz = created_at AT TIME ZONE 'UTC' WHERE created_at_tz IS NULL;

-- Step 3: Swap columns
BEGIN;
ALTER TABLE users DROP COLUMN created_at;
ALTER TABLE users RENAME COLUMN created_at_tz TO created_at;
COMMIT;

Dropping Columns and Tables

Safe Column Drop

Rule: Only drop column after code deployed that no longer references it.

-- Step 1 (Deployment 1): Deploy application v2 (doesn't read deprecated_field)
-- No SQL changes yet

-- Step 2 (Deployment 2): Drop column
ALTER TABLE users DROP COLUMN deprecated_field;

Timeline:

• Deploy v2 (removes column from queries)
• Wait 7 days (ensure no old code running, check logs)
• Deploy migration to drop column

Safe Table Drop

-- Step 1: Deploy application that doesn't query table
-- Step 2: Rename table (soft delete)
ALTER TABLE old_table RENAME TO old_table_deprecated_2026_09_11;

-- Step 3: Wait 30 days, monitor logs for accidental queries
-- Step 4: Drop table
DROP TABLE old_table_deprecated_2026_09_11;

Why rename first? Easy rollback if accidental queries discovered.

Adding Indexes Without Locking

PostgreSQL: CREATE INDEX CONCURRENTLY

-- ❌ Standard index creation (locks writes)
CREATE INDEX idx_users_email ON users(email);

-- ✅ Concurrent index creation (no locks)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Caveats:

• Can't run inside transaction (commit each DDL separately)
• Takes longer than standard index creation
• If interrupted, leaves INVALID index (must DROP and recreate)

Check for invalid indexes:

SELECT indexrelid::regclass AS index_name,
       indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

MySQL: Online DDL

-- MySQL 5.7+ Online DDL
ALTER TABLE users
ADD INDEX idx_users_email (email),
ALGORITHM=INPLACE,
LOCK=NONE;

LOCK=NONE: Allows reads and writes during index creation.

Check progress:

SHOW PROCESSLIST;
-- Look for "Waiting for table metadata lock" or "copy to tmp table"

Partial Index for Performance

-- Index only active users (smaller index, faster queries)
CREATE INDEX CONCURRENTLY idx_users_active_email
ON users(email)
WHERE status = 'active';

For index strategies, see our PostgreSQL performance guide.

Data Backfill Strategies

Never backfill in migration. Migrations should complete in <5 seconds. Run backfills as async jobs.

Pattern 1: Batch Update (Small Tables)

# scripts/backfill_email.py — Backfill in batches
import psycopg2
import time

conn = psycopg2.connect("postgresql://localhost/mydb")
cur = conn.cursor()

BATCH_SIZE = 1000
offset = 0

while True:
    # Update batch
    cur.execute("""
        UPDATE users
        SET full_name = name
        WHERE full_name IS NULL
        LIMIT %s
    """, (BATCH_SIZE,))
    
    updated = cur.rowcount
    conn.commit()
    
    print(f"Updated {updated} rows")
    
    if updated == 0:
        break  # No more rows to update
    
    time.sleep(0.5)  # Throttle to avoid overloading database

print("Backfill complete")

Run as Kubernetes Job:

# kubernetes/backfill-job.yaml
apiVersion: batch/v1
kind: Job
metadata:
  name: backfill-user-full-name
spec:
  template:
    spec:
      containers:
      - name: backfill
        image: myapp:v2.1.0
        command: ["python", "scripts/backfill_email.py"]
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: db-credentials
              key: url
      restartPolicy: OnFailure
  backoffLimit: 3

Pattern 2: Progressive Backfill (Large Tables)

# scripts/progressive_backfill.py — Backfill with rate limiting
import psycopg2
import time

conn = psycopg2.connect("postgresql://localhost/mydb")
cur = conn.cursor()

# Backfill users created before migration
cur.execute("""
    SELECT id FROM users
    WHERE full_name IS NULL
    ORDER BY id
    LIMIT 1000000
""")

user_ids = [row[0] for row in cur.fetchall()]
print(f"Found {len(user_ids)} users to backfill")

BATCH_SIZE = 100
for i in range(0, len(user_ids), BATCH_SIZE):
    batch = user_ids[i:i+BATCH_SIZE]
    
    cur.execute("""
        UPDATE users
        SET full_name = name
        WHERE id = ANY(%s)
    """, (batch,))
    
    conn.commit()
    print(f"Progress: {i+len(batch)}/{len(user_ids)}")
    
    time.sleep(1)  # 1-second delay between batches (100 updates/sec)

Pattern 3: Lazy Backfill (On-Read)

# application/models.py — Backfill lazily when user accessed
class User:
    @classmethod
    def get(cls, user_id: int):
        user = db.fetchone("SELECT * FROM users WHERE id = ?", user_id)
        
        # Lazy backfill: If full_name is NULL, populate it
        if user.full_name is None:
            db.execute(
                "UPDATE users SET full_name = name WHERE id = ?",
                user_id
            )
            user.full_name = user.name
        
        return user

When to use: Low-traffic columns where immediate backfill isn't critical.

Migration Automation and Tooling

Alembic (Python)

# alembic/versions/001_add_email.py — Alembic migration
from alembic import op
import sqlalchemy as sa

def upgrade():
    """Add email column (nullable)"""
    op.add_column('users', sa.Column('email', sa.String(255), nullable=True))

def downgrade():
    """Remove email column"""
    op.drop_column('users', 'email')

Run migration:

# Apply migration
alembic upgrade head

# Rollback one version
alembic downgrade -1

# Show current version
alembic current

Flyway (Java)

-- V001__add_email.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255);

# Run migrations
flyway migrate

# Show migration status
flyway info

Django Migrations

# migrations/0001_add_email.py
from django.db import migrations, models

class Migration(migrations.Migration):
    dependencies = [
        ('users', '0000_initial'),
    ]

    operations = [
        migrations.AddField(
            model_name='user',
            name='email',
            field=models.EmailField(max_length=255, null=True),
        ),
    ]

# Create migration
python manage.py makemigrations

# Apply migration
python manage.py migrate

Pre-Flight Safety Checks

# scripts/check_migration_safety.py — Validate migrations before running
import re
import sys

UNSAFE_PATTERNS = [
    r'ALTER\s+TABLE\s+\w+\s+ALTER\s+COLUMN\s+\w+\s+TYPE',  # Type change
    r'ALTER\s+TABLE\s+\w+\s+ADD\s+COLUMN\s+\w+.*NOT\s+NULL',  # NOT NULL without default
    r'DROP\s+TABLE',  # Table drop
    r'TRUNCATE',  # Data loss
]

def check_migration(sql: str) -> bool:
    """Check if migration contains unsafe operations"""
    for pattern in UNSAFE_PATTERNS:
        if re.search(pattern, sql, re.IGNORECASE):
            print(f"❌ Unsafe operation detected: {pattern}")
            return False
    return True

if __name__ == '__main__':
    with open(sys.argv[1]) as f:
        sql = f.read()
    
    if not check_migration(sql):
        sys.exit(1)
    
    print("✅ Migration passed safety checks")

Integrate into CI/CD pipeline with DevOps services.

Rollback Strategies

Pattern 1: Backward-Compatible Schema (Best)

Design migrations to be rollback-safe:

-- Migration: Add email column
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Rollback: Drop email column
ALTER TABLE users DROP COLUMN email;

Application compatibility:

• Old code (doesn't query email) → still works
• New code (queries email) → still works

Pattern 2: Two-Way Migrations

# alembic migration with proper rollback
def upgrade():
    op.add_column('users', sa.Column('email', sa.String(255)))

def downgrade():
    op.drop_column('users', 'email')

Test rollback:

# Apply migration
alembic upgrade head

# Rollback
alembic downgrade -1

# Re-apply
alembic upgrade head

Pattern 3: Database Backup Before Migration

#!/bin/bash
# backup_before_migration.sh

# Backup database
pg_dump -Fc mydb > backups/mydb_$(date +%Y%m%d_%H%M%S).dump

# Run migration
alembic upgrade head

# If migration fails, restore
if [ $? -ne 0 ]; then
    echo "Migration failed, restoring backup..."
    pg_restore -d mydb backups/mydb_latest.dump
fi

Restore time: 10–60 minutes for multi-GB databases (not true zero-downtime).

Production Checklist

Pre-Migration

• Migration tested on production-sized dataset (not just dev data)
• Estimated migration time <5 seconds (or run async for backfills)
• Rollback plan documented (exact SQL commands)
• Application code backward-compatible (dual-read/dual-write)
• Database backup completed (< 1 hour old)
• No unsafe operations (ALTER TYPE, ADD NOT NULL without default)
• Indexes created with CONCURRENTLY (PostgreSQL) or ALGORITHM=INPLACE (MySQL)

During Migration

• Monitor database connection count (migrations shouldn't spike connections)
• Watch query performance (pg_stat_activity for long-running queries)
• Check for lock contention (SELECT * FROM pg_locks WHERE NOT granted)
• Monitor error rates in application logs
• Keep database backup snapshot (cloud snapshots, not full dump)

Post-Migration

• Verify schema changes applied (\d users in psql)
• Check for INVALID indexes (SELECT * FROM pg_index WHERE NOT indisvalid)
• Spot-check data consistency (sample queries)
• Monitor application error rates (15-minute window)
• Schedule backfill job (if required)
• Update schema documentation

Related implementation guides:

• Database Schema Ai Applications

Primary references: official documentation, official documentation, official documentation, official documentation.

Database Schema Migrations Without Downtime Decision Table

Operating Database Schema Migrations Without Downtime as a System

The implementation is only one part of Database Schema Migrations Without Downtime. A production design also needs an explicit contract for inputs, outputs, ownership, and failure behavior. Write that contract before selecting a library. It should identify which component validates input, where state lives, what may be retried, and which result is authoritative when two components disagree. This prevents a convenient prototype boundary from silently becoming the long-term architecture.

Start with a representative baseline. Capture request shape, traffic distribution, dependency latency, error classes, and the quality signal users actually care about. Averages hide the cases that cause incidents, so keep percentiles and segment measurements by workload type. Record the configuration and dataset version beside every result. Without that context, a faster or more accurate run cannot be reproduced and should not be used to approve a rollout.

Define the failure model

List failures by where they originate: invalid input, capacity exhaustion, dependency timeout, partial state change, malformed output, and semantically wrong output. Each class needs a different response. Validation errors should fail immediately. Transient dependency failures may be retried with a budget and jitter. An operation that may have committed must use an idempotency key or reconciliation step before retrying. A syntactically valid but incorrect result belongs in evaluation and review, not a blind retry loop.

Set a deadline for the complete operation and derive smaller budgets for each dependency. Local timeouts that add up to more than the caller's deadline merely create abandoned work. Propagate cancellation where the protocol supports it. Bound every queue, retry loop, context buffer, and concurrency pool; an unbounded safety mechanism becomes a second outage during overload.

Design a degraded mode before it is needed. Depending on the workload, that can mean returning a cached answer, selecting a simpler path, placing work in a durable queue, or asking for human review. The degraded response must be visible in telemetry and, where it changes meaning, visible to the caller. Silent fallback makes quality regressions almost impossible to diagnose.

Measure the decision, not just the component

Use three layers of signals. System metrics cover latency, throughput, saturation, and errors. Correctness metrics measure whether the result satisfies its contract. Business or user metrics show whether the system solved the intended problem. Improving only one layer can move the others backward, so release criteria should name acceptable movement for all three.

Attach a reason code to every route, rejection, fallback, and retry. Include version identifiers for configuration, code, model, schema, and data when relevant. Logs should let an engineer reconstruct a decision without storing secrets or raw personal data. Traces should cross process boundaries, while metrics should remain low-cardinality enough to operate reliably.

Alert on symptoms that require action, not every internal anomaly. A useful alert names the affected service objective, links to a runbook, and distinguishes a customer-visible incident from exhausted headroom. Dashboards serve a different purpose: they support diagnosis and capacity planning. Treating a dashboard as an alerting strategy leaves failures undiscovered until someone happens to look.

Roll out with reversible steps

Ship Database Schema Migrations Without Downtime behind a versioned interface and a kill switch. Begin with offline replay using production-shaped, privacy-safe samples. Then use shadow execution when duplicate work has acceptable cost and side effects can be suppressed. A small canary should exercise the real dependency graph before traffic expands. Compare the canary with the baseline by cohort rather than mixing both populations into one aggregate.

Promotion gates should be written before the rollout. Include a minimum sample size or observation window, maximum regression in tail latency and error rate, and a correctness threshold. Roll back automatically when a hard safety boundary is crossed; use manual review for ambiguous quality movement. Preserve enough evidence from both paths to explain why the gate passed or failed.

Configuration deserves the same discipline as code. Review changes, validate them before activation, keep an immutable history, and make rollback a single operation. If a deployment changes code and configuration together, record both versions. Otherwise an incident responder may roll back the binary while leaving the triggering configuration active.

Capacity and cost controls

Model capacity in units the bottleneck understands: concurrent connections, tokens, queue jobs, database transactions, GPU memory, or bytes in flight. Convert the expected traffic distribution into those units and include burst behavior. Then load-test the first constrained dependency, not merely the public endpoint. A system that accepts more work than it can finish within its deadline is overloaded even if CPU utilization looks comfortable.

Cost is also a reliability limit. Add per-request attribution, tenant or workflow budgets, and a global circuit breaker for unexpectedly expensive paths. Review unit economics at the same granularity as performance; a cheap median can conceal a small class of requests responsible for most spend. Optimize only after measuring, because reducing context, replicas, validation, or redundancy can trade visible cost for less visible risk.

Production readiness review

Before launch, ask an engineer who did not build the feature to follow the runbook through one simulated failure. Verify backups or checkpoints by restoring them, not by checking that a job reported success. Exercise credential rotation, dependency unavailability, bad configuration, and rollback. Assign an owner for each alarm and a date for reviewing thresholds after real traffic arrives.

The final architecture document should be short enough to remain current. Keep the decision, rejected alternatives, invariants, dependency contracts, dashboards, and rollback procedure. Link detailed experiments rather than pasting them into the document. Teams that need help turning this review into an operable service can use our Database Schema Migrations Without Downtime engineering support.

Frequently Asked Questions

Can I rename a column without downtime?

Not with a single ALTER TABLE RENAME COLUMN — it breaks old code immediately.

Use 3-step process: Add new column → dual-write → drop old column. Takes 1–2 weeks but ensures zero downtime.

How do I add a NOT NULL column to a large table?

3-step process:

1. Add nullable column with DEFAULT
2. Backfill data (async job)
3. Add NOT NULL constraint

Never add NOT NULL in step 1 — causes full table scan and locks.

What is the fastest way to backfill millions of rows?

Batch updates with sleep between batches:

UPDATE users SET full_name = name WHERE full_name IS NULL LIMIT 1000;
# Sleep 500ms
# Repeat until 0 rows updated

Speed: 100–1000 updates/second (depends on indexes, CPU).

For 10M rows: 3–30 hours. Run as background job, not in migration.

Can I drop a column immediately after deploying code?

No. Wait 7 days after deploying code that stops reading the column.

Reason: Old code might still be running (mobile apps, cached instances, scheduled jobs).

How do I test migrations on production data safely?

Use anonymized production snapshot:

# Dump production database
pg_dump -Fc production_db > prod_snapshot.dump

# Restore to staging
pg_restore -d staging_db prod_snapshot.dump

# Run migration on staging
alembic upgrade head

# Measure migration time, check locks
# If safe, run on production

What if migration takes longer than expected?

Abort and redesign:

1. Cancel migration (Ctrl+C or pg_cancel_backend)
2. Rollback transaction (if in transaction)
3. Analyze why it's slow (missing index, type conversion, full table scan)
4. Redesign as async job or multi-step migration

Never let migration run for >1 minute — indicates locking issue.

Conclusion

Database schema migrations without downtime require discipline: expand-contract pattern, backward compatibility, and async backfills. Shortcuts like ALTER TABLE RENAME COLUMN or ADD NOT NULL cause production outages.

Key Recommendations:

• Expand-contract pattern for breaking changes (3 deployments, 1–2 weeks)
• Add columns as nullable first, backfill async, add NOT NULL later
• CREATE INDEX CONCURRENTLY (PostgreSQL) or ALGORITHM=INPLACE (MySQL)
• Dual-write and dual-read during transition periods
• Never run backfills in migrations — async jobs only
• Test on production-sized data before running on production

Build safe database migration pipelines with our backend engineering services. We design expand-contract patterns, backfill strategies, and zero-downtime schema evolution for production databases.

Services

• Backend & API Engineering — schema design, migration strategies, backward compatibility
• Cloud Infrastructure & DevOps — database deployment, backup automation, migration pipelines
• Data Pipelines & Integrations — async backfill jobs, ETL patterns