postgres-database-migration

What it does

Describes risk assessment, testing against real data, and safe execution patterns for PostgreSQL migrations. Provides concrete guidance on lock behavior, safe vs. unsafe DDL patterns, backfill, validation, rollback planning, and transactional strategies.

How it works

Outlines fast/non-blocking and slow/blocking DDL operations with explicit lock levels for each change (e.g., ADD COLUMN, DROP COLUMN, CREATE INDEX CONCURRENTLY, etc.). Provides safe migration patterns for adding/dropping/renaming columns, changing types via backfill and column swaps, adding NOT NULL constraints, adding foreign keys and indexes, redefining primary keys, and dropping constraints. Includes sample SQL snippets demonstrating safe and unsafe approaches, backfill strategies, and validation steps. Describes two execution approaches: wrapping all DDL in a single transaction or running separate transactions, along with their tradeoffs. Emphasizes rollback planning, validation queries, and the option to fork databases for testing. Mentions dealing with long-running queries and the use of lock_timeout and statement_timeout.

When to use it

Triggered when planning or executing migrations, testing migrations against real data, or for zero-downtime deployment plans. Also applicable when forking a database to test migrations safely, or when rollback and validation steps are required.

What it can touch

Contains references to commands and scripts such as:

• SQL statements like ALTER TABLE, CREATE INDEX CONCURRENTLY, DROP CONSTRAINT, VALIDATE CONSTRAINT, ADD CONSTRAINT, BACKFILL patterns, and various column/type changes.
• Validation and backfill guidance references (no external tools listed beyond standard PostgreSQL operations). It specifies use of SQL snippets and catalog-level notes.

Caveats

Notes that some operations lock tables for extended periods and can block reads/writes, with explicit lock-level guidance. Includes warnings about unsafe patterns (e.g., full table rewrites on large tables) and the need for application coordination when dropping columns. Recommends forks for testing and outlines irreversible operations requiring restoration from backup or fork. License not stated within the section.