postgres-database-migration

# PostgreSQL Database Migrations A schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely. ## DDL Lock Reference Every schema change acquires a lock. The critical question is: **does it block reads and writes, and for how long?** ### Fast, Non-Blocking Operations These complete in milliseconds regardless of table size. They only hold a brief `AccessExclusiveLock` for the catalog update, not for data rewriting. | Operation | Lock Level | Notes | |-----------|-----------|-------| | `ADD COLUMN` (nullable, no default) | `AccessExclusiveLock` (brief) | **Fast.** No table rewrite. Metadata-only change. | | `ADD COLUMN ... DEFAULT x` (PG 11+) | `AccessExclusiveLock` (brief) | **Fast.** Non-volatile defaults stored in catalog, not backfilled. | | `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. | | `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. | | `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. | | `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. | | `RENAME CO...