The Postgres migration that locks your table at 3am, and how to catch it in review

Short summary

Dangerous Postgres migrations hold strong locks (ACCESS EXCLUSIVE or SHARE) while scanning or rewriting large tables, causing production outages. The article provides safe rewrites for common hazardous migrations: use CREATE INDEX CONCURRENTLY, add CHECK constraints as NOT VALID then validate separately, and backfill columns in batches before setting defaults. It also covers transaction-wrapping pitfalls in Flyway and Liquibase that can break concurrent index creation.

• •ACCESS EXCLUSIVE and SHARE locks on large tables cause silent production outages during migrations
• •Safe patterns: NOT VALID constraints, CONCURRENTLY indexes, batch backfills for volatile defaults
• •Flyway and Liquibase wrap migrations in transactions by default, breaking concurrent index creation

Generated with AI, which can make mistakes.