Concept
Schema Evolution & Versioning
As applications evolve, database structures must change. Schema Migrations are version-controlled script files (usually SQL) that update database tables.
Migrations are applied sequentially:
- Up Migrations: Apply changes (e.g.
CREATE TABLE,ADD COLUMN). - Down Migrations (Optional): Rollback changes (e.g.
DROP TABLE,DROP COLUMN).
The Zero-Downtime Challenge
In production, code deployments and database migrations cannot happen at the exact same millisecond. If you run a migration that deletes or renames a column while production servers are running old code, the application will crash.
To achieve zero-downtime deployments, all database changes must be backward-compatible with the currently running application code.
The Expand-and-Contract Pattern (Three-Step Deploy)
To execute breaking schema changes (like renaming a column from username to login), implement the Expand-and-Contract pattern across three phases:
Step 1: EXPAND
- Database: Add the new column 'login' (keep 'username').
- Code: Write to BOTH columns; read from 'username'.
Step 2: DATA MIGRATE & TRANSITION
- Script: Copy all historical data from 'username' to 'login'.
- Code: Read/write from 'login'; fallback/sync 'username'.
Step 3: CONTRACT
- Code: Remove all reference code to 'username'.
- Database: Safely drop the old column 'username'.This ensures that the database always supports both the old code and the new code during deployment windows.
Common Mistakes
1. Renaming columns directly in a single migration
Running ALTER TABLE users RENAME COLUMN username TO login instantly crashes running application instances that are querying username. Always use Expand-and-Contract.
2. Running index creation on large tables blocking writes
Running CREATE INDEX idx ON orders (user_id) locks the table for writes on large tables, causing backend requests to time out. In production PostgreSQL, always use CONCURRENTLY:
CREATE INDEX CONCURRENTLY idx_user_id ON orders (user_id);Best Practices
- Never write default values that trigger full table writes: Adding a column with a default value (e.g.
DEFAULT 'active') in older database engines rewrites every single row, locking the table. Use nullable columns with application defaults instead. - Run migrations before code deploys: Always ensure the database expands to support the new schema before rolling out the new server instances.
- Automate migration locks: Ensure your migration runner acquires transactional locks (or uses tools like Prisma Migrate lock records) to prevent concurrent runners from applying the same scripts.
