Schema migrations

Why it matters

Applications change, so schemas change: new columns, renamed fields, new indexes, split tables. On a small database you can change the schema and restart the app. On a busy production system, the wrong ALTER TABLE can lock a table for minutes, queue every request behind it and take the site down. Deploying new code while old code is still running adds a second problem: both versions must work against the same schema at the same time. Planning safe migrations is a core DBA skill.

Migrations as code

A migration is a small, versioned script that moves the schema from one version to the next. Tools record which migrations have run in a table in the database and apply the rest in order: Flyway and Liquibase (any stack), Alembic (Python), Rails and Django migrations, Prisma Migrate, Knex and others.

Good habits:

• Every change is a migration in version control, reviewed like code. No hand edits in production.
• Migrations are small and do one thing.
• Each one is tested on a copy of production-sized data before release, so you know how long it takes and what it locks.
• You know how to roll back, or how to roll forward with a fix if a rollback would lose data.

Why some changes hurt

In PostgreSQL most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which blocks reads and writes on that table. For many changes the lock is brief; the danger is changes that rewrite or scan the whole table while holding it, and the fact that a waiting ALTER also blocks every query queued behind it.

MySQL has its own rules (ALGORITHM=INSTANT/INPLACE, and tools such as gh-ost or pt-online-schema-change), so always check your engine's documentation.

Set a short lock_timeout in migrations (SET lock_timeout = '5s';) so a migration that cannot get its lock fails fast and can be retried, instead of freezing traffic.

Expand, migrate, contract

Zero-downtime changes are split across several deploys so that old and new code both work at every step. Renaming customers.fullname to customers.display_name:

1. Expand: add the new column (nullable, fast). Deploy code that writes both columns and still reads the old one.
2. Backfill: copy existing data in small batches to avoid long locks and huge transactions:

UPDATE customers SET display_name = fullname
WHERE id IN (
  SELECT id FROM customers
  WHERE display_name IS NULL AND fullname IS NOT NULL
  LIMIT 5000
);
-- repeat until 0 rows are updated

3. Switch reads: deploy code that reads the new column. Add constraints safely:

ALTER TABLE customers
  ADD CONSTRAINT display_name_not_null CHECK (display_name IS NOT NULL) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT display_name_not_null;
ALTER TABLE customers ALTER COLUMN display_name SET NOT NULL;  -- uses the valid CHECK, no long scan

4. Contract: once no running code uses the old column (check logs and every service), stop writing it, then drop it in a later release.

The same pattern handles splitting tables, changing types (new column, backfill, switch) and moving data between services.

Index changes

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

CONCURRENTLY cannot run inside a transaction block, and if it fails it leaves an INVALID index that must be dropped and retried. Most migration tools need a flag to run a migration outside their usual transaction.

A release checklist

• Fresh backup (or a confirmed PITR window) before the change.
• Migration timed on realistic data; locks understood.
• lock_timeout and statement_timeout set.
• Run at a low-traffic time, with someone watching locks and errors.
• Rollback or roll-forward plan written down.

Common mistakes

• Renaming or dropping a column in the same release as the code change.
• One giant UPDATE backfill that bloats the table and holds locks for an hour.
• Creating indexes without CONCURRENTLY on a busy table.
• Testing migrations only on an empty development database.

Quick recap

• Migrations are versioned, reviewed, small scripts run by a tool.
• Know which changes lock, scan or rewrite; use lock_timeout, NOT VALID plus VALIDATE, and CREATE INDEX CONCURRENTLY.
• Expand, backfill, switch, contract: old and new code must both work at every step.

Try it

In the Do tab, carry out a full expand/backfill/contract column rename on your practice server.