writing-safe-migrationslisted

# Writing Safe Migrations ## Overview Two questions before any migration runs against a live table: 1. **Does this rewrite the table?** A rewrite holds `ACCESS EXCLUSIVE` for the whole rewrite — every read and write blocks. 2. **Can it wait for its lock without taking the table down?** A statement waiting for `ACCESS EXCLUSIVE` queues *ahead of* every query that arrives after it. One long-running `SELECT` plus one unguarded `ALTER TABLE` stalls the entire table, even for statements the migration itself would never have blocked. Question 2 causes more outages than question 1, and `lock_timeout` is the whole fix. ## When to Use - Adding an index to an existing large table. - `ALTER TABLE`, adding or dropping columns, changing types, adding constraints. - Backfilling a column across many rows. - Any migration on a table with meaningful traffic. ## Always set a lock timeout Never issue DDL against a live table without bounding the wait: ```sql SET lock_timeout = '5s'; -- fail instead of building a lock queue SET statement_timeout = '0'; -- but let a long index build finish ALTER TABLE tasks ADD COLUMN priority int; ``` If the lock isn't acquired in 5s the statement errors — retry it later. That is the correct outcome: a failed migration is recoverable, a stalled table is an incident. `lock_timeout` only bounds *acquiring* a lock, not holding one. It will not save you from a rewrite that takes ten minutes once it starts ��� check the lock table for that. Find