Database Schema Evolution

Overview

Database schemas must evolve as applications grow, but schema changes are a leading cause of production outages. The expand-contract pattern, online DDL, and backward-compatible migrations allow teams to add capabilities without downtime. This resource covers practical techniques for evolving schemas in PostgreSQL, MySQL, and distributed databases while maintaining data integrity and application availability.

When to Use

Use this resource when:

• Adding columns, indexes, or constraints to tables with millions of rows
• You need to rename columns or split tables without breaking running applications
• Running migrations in a CI/CD pipeline that deploys multiple times daily
• Working with distributed databases where schema changes propagate asynchronously

Solution

Expand-Contract Pattern (PostgreSQL)

-- PHASE 1: EXPAND - Add new column without breaking existing code
ALTER TABLE users ADD COLUMN email_normalized VARCHAR(255);
CREATE INDEX CONCURRENTLY idx_users_email_normalized ON users(email_normalized);

-- Backfill in batches to avoid locking
UPDATE users 
SET email_normalized = LOWER(email)
WHERE id BETWEEN 1 AND 10000;

-- PHASE 2: DUAL WRITE - Application writes to both columns
-- (Deploy code that writes to email and email_normalized)

-- PHASE 3: CONTRACT - Remove old column after verification
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_normalized TO email;

Online DDL with pt-online-schema-change (MySQL)

# Add an index without locking the table
pt-online-schema-change \
  --alter "ADD INDEX idx_created_at (created_at)" \
  --execute \
  --max-load Threads_running=25 \
  --critical-load Threads_running=50 \
  D=mydb,t=orders

Flyway Migration (Java/Spring)

// V1.2__Add_user_preferences.sql
CREATE TABLE user_preferences (
    user_id UUID PRIMARY KEY REFERENCES users(id),
    theme VARCHAR(20) DEFAULT 'light',
    notifications_enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_user_preferences_theme ON user_preferences(theme);

Explanation

The expand-contract pattern:

1. Expand: Add new schema elements (columns, tables) without removing old ones
2. Migrate: Backfill data; run dual-write during transition
3. Verify: Ensure new and old paths produce identical results
4. Contract: Remove deprecated elements once all code uses the new schema

Online vs. offline DDL:

Database	Online DDL	Lock Level
PostgreSQL	CREATE INDEX CONCURRENTLY	None
MySQL	ALGORITHM=INPLACE	Brief metadata
MySQL (large tables)	pt-online-schema-change	Row-level copy
SQL Server	ONLINE=ON	Schema stability

Variants

Approach	Best For	Tooling
Expand-contract	Zero-downtime renames	Manual + application changes
Online DDL	Large table index changes	pt-online-schema-change, gh-ost
Blue-green schema	Major restructuring	Two databases + dual-write
Logical replication	Cross-version migration	pglogical, Debezium

What Works

• Never drop before adding: Always add the replacement before removing the original
• Use IF EXISTS and IF NOT EXISTS: Prevents migration failures on partial runs
• Batch backfills: Update 1,000-10,000 rows per transaction to avoid long locks
• Test migrations on production-sized data: pg_dump + restore to staging isn’t enough
• Version your migrations: Flyway, Liquibase, or Atlas for tracking and rollback

Common Mistakes

1. Big-bang migrations: Running ALTER TABLE on a 100M-row table without CONCURRENTLY
2. Not testing rollback: If the deploy fails, can you revert the schema change? Test deployment strategies.
3. Missing application compatibility: New schema breaks old code during rolling deployments
4. Ignoring lock timeouts: PostgreSQL statement_timeout aborts long migrations unpredictably. See connection pooling.
5. No dry runs: Running migrations directly in production without EXPLAIN or staging validation

Troubleshooting

• Query is slow after an index change: check execution plans and cardinality estimates. Rebuild statistics and verify the index is being used.
• Replication lag grows: monitor network, disk I/O, and long transactions. Split large writes and consider parallel replication.
• Connections exhausted: review connection pool size, idle timeouts, and leaked connections.
• Backup takes too long: enable compression, incremental backups, and off-peak scheduling.
• Deadlocks in high concurrency: access tables and rows in a consistent order.

Key Takeaways

• Apply database schema evolution when you need a practical solution for your use case.
• Monitor performance after implementation; measure latency, errors, and resource usage before and after.
• Check the Troubleshooting section for common failures; most have documented root causes with fixes.
• Keep dependencies updated and run tests in CI to prevent production regressions.

Additional Best Practices

6. Use CREATE INDEX CONCURRENTLY in PostgreSQL. This avoids blocking writes but cannot run inside a transaction. Plan your migration scripts accordingly.
7. Set lock_timeout for DDL operations. This prevents a migration from waiting indefinitely for a lock:

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN status VARCHAR(20);

8. Use NOT VALID for check constraints. Add constraints as NOT VALID to skip scanning existing rows, then validate in a separate step:

ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount;

9. Document each migration. Include the reason, expected duration, rollback plan, and verification steps in your migration tool’s comments or changelog.

10. Run migrations in staging first. Measure timing, lock behavior, and resource usage. Use production-sized data for accurate estimates.

Additional Common Mistakes

6. Adding a column with a volatile default. In PostgreSQL versions before 11, ADD COLUMN ... DEFAULT random() rewrites the entire table. Use a nullable column and backfill instead.
7. Not handling NULL values during type changes. When changing from VARCHAR to INTEGER, NULLs and non-numeric strings will cause errors. Clean the data first.
8. Forgetting to update statistics. After large backfills, run ANALYZE so the query planner has accurate statistics:

ANALYZE users;

9. Running migrations during peak traffic. Even zero-downtime migrations add load. Schedule backfills during off-peak hours to minimize impact.
10. Not having a rollback plan for each migration. Every migration should have a documented rollback procedure. Test it in staging before deploying.

Performance Tips

1. Batch backfills with LIMIT and sleep. Process 1,000-10,000 rows per batch with a short pause to minimize replication lag and lock contention.

2. Use CREATE INDEX CONCURRENTLY for all production indexes. This takes longer but does not block writes. Monitor progress via pg_stat_progress_create_index.

3. Run ANALYZE after large data changes. The query planner needs up-to-date statistics to choose optimal plans:

ANALYZE VERBOSE users;

4. Set statement_timeout for migration sessions. Prevent runaway DDL from blocking the database:

SET statement_timeout = '60s';

5. Monitor replication lag during backfills. Pause backfilling if replica lag exceeds your threshold:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Common Production Pitfalls

• Copying the example without adapting it to real data volumes and failure modes.
• Skipping load and error-injection tests before the first production deployment.
• Hard-coding values that should be configurable per environment.
• Forgetting to add logging and monitoring at each step.
• Deploying without a rollback plan or a tested backup strategy.
• Assuming the minimal example will scale without adding caching or batching.
• Not documenting the version and configuration used in production.
• Letting the recipe sit unchanged when dependencies or scale evolve.