Migrations & Schema Evolution

what a migration actually is

A migration is a small, ordered, version-controlled script that transforms the schema from one known state to the next — and, ideally, an equally well-defined way to undo it. The alternative (hand-running ALTER TABLE statements against production, unrecorded) is how schemas drift out of sync with what's in source control, and why a fresh developer's local database never quite matches staging.

common ALTER TABLE patterns

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users RENAME COLUMN uname TO username;
ALTER TABLE users DROP COLUMN legacy_flag;
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Each of these is fast and safe on a small table. On a large table under real production traffic, several of them are not — see the zero-downtime section below.

migration tools

Tool	Ecosystem
Alembic	Python, usually paired with SQLAlchemy
Flyway	JVM-ecosystem-agnostic, plain versioned `.sql` files
golang-migrate	Go, also usable standalone via CLI regardless of language
Django migrations / Rails Active Record migrations	built into those specific frameworks

They all solve the same three problems: track which migrations have already run (usually a schema_migrations table), apply pending ones in order, and provide a rollback path. Pick whatever matches your application stack rather than fighting it.

zero-downtime migration practices

On a table with real production traffic, some seemingly simple changes hold a lock long enough to matter:

Risky	Safer alternative
Adding a column with a non-null `DEFAULT` on an old Postgres version	in PostgreSQL 11+, this is actually fast (metadata-only) — but a `CHECK` or `NOT NULL` added afterward with `VALIDATE` still needs care
`ADD COLUMN ... NOT NULL` without a default, on a non-empty table	this doesn't run slowly — it fails outright ("column contains null values"). Add the column nullable, backfill in batches, then promote it: `ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID`, `VALIDATE CONSTRAINT` (which takes only a weak lock), then `SET NOT NULL`, which reuses the validated check and skips the table scan on PostgreSQL 12+. The `CHECK` can then be dropped
Renaming a column the application still queries by old name	expand/contract: add the new column, dual-write from the app, backfill, switch reads, then drop the old column in a later deploy
Adding an index with default `CREATE INDEX`	`CREATE INDEX CONCURRENTLY` — slower, but doesn't hold a write lock on the table for the duration

The common thread: separate a migration that changes the shape of data from one that changes a constraint, and never require the application code and the schema to update in the exact same instant — deploys and migrations don't happen atomically together.

where to go from here

Indexing Strategies — CREATE INDEX CONCURRENTLY in more depth.
Locking & Concurrency — exactly which DDL statements take which locks.