What a rehearsal is for#
A rehearsal runs the migration where it can fail cheaply. That place has a realistic amount of data, the application release that will still be running, and a second session that holds a lock the way production traffic does. Each lab in this collection reduces one change to a few SQL statements you can run in psql. We ran all of them on PostgreSQL 16.15. Check the PostgreSQL documentation for your own major version before relying on a lock mode or rewrite rule.
Ask three questions about each statement in the migration:
- How long does it hold its lock? Some statements finish in a millisecond. Others rewrite or scan the whole table while holding a lock that blocks reads.
- What do existing rows do to it? A constraint, a type conversion or a default meets data that was written under older rules.
- What does the release that is still running do? During a rolling deployment, the previous application release reads and writes the new schema. After a rollback, it does so again.
A change needs a rehearsal when any answer is "it depends on production". Rehearse it with a copy of production's row count and shape, the previous release and the next one.
Changes that hold a lock for the size of the table#
| Change | Lab | What to rehearse |
|---|---|---|
ALTER COLUMN ... TYPE | Which column type changes rewrite the table | Whether the table file changes, and how long the rewrite takes at production size |
ADD COLUMN ... DEFAULT | What a column default does to existing rows | Whether the default is volatile, and which value existing rows receive |
SET NOT NULL | Make SET NOT NULL skip the table scan | Whether a validated CHECK proves the column has no nulls |
ADD FOREIGN KEY | Add a foreign key to a table with orphaned rows | Orphaned rows, and the lock on the referenced table |
REFRESH MATERIALIZED VIEW | When a materialized view refresh blocks readers | Whether readers wait, and whether CONCURRENTLY is available |
Any ALTER TABLE | Why a fast migration blocked every query | The lock queue behind a long transaction, and a lock_timeout with a retry |
Changes that depend on existing rows#
| Change | Lab | What to rehearse |
|---|---|---|
Type change with USING | Find the rows a column type change rejects or misreads | Values that fail the cast, and values that convert to the wrong thing |
UNIQUE constraint or index | Find the duplicates a unique constraint lets through | Duplicates by the application's meaning, and NULL handling |
| Integer key growth | How much room is left in an integer ID | Sequence headroom, and every column that stores the key |
CHECK constraint | What NOT VALID means for PostgreSQL CHECK constraints | Legacy rows that block updates before validation |
Changes that break the release that is still running#
| Change | Lab | What to rehearse |
|---|---|---|
| Any column added or dropped | Why adding a column breaks cached plans | The previous release with a warm connection pool during the migration |
| Column added, dropped or reordered | How a column change misroutes positional INSERT and COPY | Loaders, fixtures and scripts that omit the column list |
| Unique index replaced | Changing the unique index behind an ON CONFLICT upsert | The previous release's upsert after each step |
| Enum value added | Adding an enum value while the old release still reads it | The previous release reading the new value |
| Table renamed | Rename a table while the old release still uses the name | The previous release writing through a view |
| Column renamed | How to test a migration before rolling back the app | The first write the previous release can no longer read |
| New index for a slow query | Check a new index against the query the app sends | The plan for the app's parameter types |
Changes that rarely need one#
A new table, a new nullable column without a default, a non-unique index built with CREATE INDEX CONCURRENTLY, and a DROP of an object no release references usually need review rather than rehearsal. They still take locks. Set a lock_timeout on them and read the lock queue lab once.
Record what the rehearsal showed#
Put the result in the pull request that carries the migration: the database version, the row count you rehearsed against, the lock each statement took and for how long, and the releases you ran beside it. A reviewer can then check the rehearsal against the deployment order. The migration review checklist covers that order, from backfill to cleanup. When the migration also moves data between columns or tables while both releases write, moving data while two application releases share one database covers the backfill and the checks that show the two shapes agree.