Safe Schema Migrations

Expand, migrate, contract

Run schema changes through versioned migrations (Flyway, Liquibase, Alembic, Prisma, Rails and others) reviewed like code. On busy tables, avoid long locks: create indexes with CREATE INDEX CONCURRENTLY, add constraints as NOT VALID and validate later, add nullable columns first and backfill in batches, and set a lock_timeout so a migration fails fast instead of queueing behind long transactions. Use expand and contract: add the new structure, deploy code that uses both, migrate data, then remove the old structure.

Low-lock migration steps (sketch)

Patterns for large, busy tables. Not run here.

SET lock_timeout = '5s';

-- add an index without blocking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY orders_ordered_on_idx ON orders (ordered_on);

-- add a foreign key without a long full-table check
ALTER TABLE order_items ADD CONSTRAINT fk_product FOREIGN KEY (product_id)
  REFERENCES products(id) NOT VALID;
ALTER TABLE order_items VALIDATE CONSTRAINT fk_product;   -- weaker lock, can run later

Rehearse on a production-sized copy

Migrations that take seconds on a dev database can lock a large production table for minutes.

Quick check: Why use CREATE INDEX CONCURRENTLY on a busy table?

• It creates two indexes
• It is always faster to build
• It builds the index without blocking writes to the table
• It skips validation