# Safe Schema Migrations — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/m-migrate

> Change live databases without downtime.

## 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.

```sql
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.

**Quiz:** Why use CREATE INDEX CONCURRENTLY on a busy table?

- [ ] It creates two indexes
- [ ] It is always faster to build
- [x] It builds the index without blocking writes to the table
- [ ] It skips validation

*Answer:* It builds the index without blocking writes to the table. Avoid long write locks.