Online Migrations: Why ALTER TABLE Locks
Understand which ALTER TABLE forms take an ACCESS EXCLUSIVE lock and rewrite the table, and which are metadata-only.
The Production Migration Problem
On a small DB, ALTER TABLE is instant. On a 500GB live table, the same command can lock writes for 20 minutes. Knowing which ALTERs are safe and which aren't is essential.
Lock Levels
PostgreSQL locks come in levels:
- ACCESS SHARE — selects
- ROW EXCLUSIVE — writes
- SHARE / SHARE ROW EXCLUSIVE — DDL coexisting with reads
- EXCLUSIVE — blocks selects
- ACCESS EXCLUSIVE — blocks EVERYTHING
All lessons in this course
- Online Migrations: Why ALTER TABLE Locks
- Concurrent Indexes (CREATE INDEX CONCURRENTLY)
- Zero-Downtime Column Renames
- Tools: Flyway, Liquibase, Sqitch