Learning center

Which PostgreSQL migration needs a rehearsal before the next deploy

Most migrations can ship after an ordinary review. Rehearse the ones that hold a strong lock for longer than a moment, depend on rows already in the table, or change what a running release reads or writes. This page sorts common PostgreSQL changes into those groups and links a lab for each.

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:

  1. 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.
  2. What do existing rows do to it? A constraint, a type conversion or a default meets data that was written under older rules.
  3. 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#

ChangeLabWhat to rehearse
ALTER COLUMN ... TYPEWhich column type changes rewrite the tableWhether the table file changes, and how long the rewrite takes at production size
ADD COLUMN ... DEFAULTWhat a column default does to existing rowsWhether the default is volatile, and which value existing rows receive
SET NOT NULLMake SET NOT NULL skip the table scanWhether a validated CHECK proves the column has no nulls
ADD FOREIGN KEYAdd a foreign key to a table with orphaned rowsOrphaned rows, and the lock on the referenced table
REFRESH MATERIALIZED VIEWWhen a materialized view refresh blocks readersWhether readers wait, and whether CONCURRENTLY is available
Any ALTER TABLEWhy a fast migration blocked every queryThe lock queue behind a long transaction, and a lock_timeout with a retry

Changes that depend on existing rows#

ChangeLabWhat to rehearse
Type change with USINGFind the rows a column type change rejects or misreadsValues that fail the cast, and values that convert to the wrong thing
UNIQUE constraint or indexFind the duplicates a unique constraint lets throughDuplicates by the application's meaning, and NULL handling
Integer key growthHow much room is left in an integer IDSequence headroom, and every column that stores the key
CHECK constraintWhat NOT VALID means for PostgreSQL CHECK constraintsLegacy rows that block updates before validation

Changes that break the release that is still running#

ChangeLabWhat to rehearse
Any column added or droppedWhy adding a column breaks cached plansThe previous release with a warm connection pool during the migration
Column added, dropped or reorderedHow a column change misroutes positional INSERT and COPYLoaders, fixtures and scripts that omit the column list
Unique index replacedChanging the unique index behind an ON CONFLICT upsertThe previous release's upsert after each step
Enum value addedAdding an enum value while the old release still reads itThe previous release reading the new value
Table renamedRename a table while the old release still uses the nameThe previous release writing through a view
Column renamedHow to test a migration before rolling back the appThe first write the previous release can no longer read
New index for a slow queryCheck a new index against the query the app sendsThe 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.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading