How a common Postgres migration command can silently lock your production table

Certain Postgres migration commands, such as a plain ALTER TABLE to set a column NOT NULL, acquire an ACCESS EXCLUSIVE lock that blocks all reads and writes until the operation completes — potentially causing outages lasting several minutes on large tables. The core danger is that a safe-looking migration diff and a dangerous one can appear identical to a reviewer, since both express the same intent but behave very differently at the lock level. Safer alternatives exist for each risky pattern: for example, adding a NOT VALID check constraint first and validating it separately avoids holding a strong lock during a full table scan. Similarly, CREATE INDEX CONCURRENTLY builds an index without blocking writes, though it cannot run inside a transaction block — a problem since tools like Flyway and Liquibase wrap migrations in transactions by default, requiring per-migration configuration overrides. Developers are advised to treat lock behavior, not just logical intent, as the primary safety criterion when reviewing database migrations.