---
title: "Which PostgreSQL migration needs a rehearsal before the next deploy | Proofline"
description: "Most migrations can ship after an ordinary review."
url: https://proofline.sh/learn/postgres-migration-rehearsal/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 20, 2026 4 min read

## 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](https://www.postgresql.org/docs/16/sql-altertable.html) 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

| Change | Lab | What to rehearse |
| --- | --- | --- |
| `ALTER COLUMN ... TYPE` | [Which column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/) | 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](/learn/postgres-add-column-default/) | Whether the default is volatile, and which value existing rows receive |
| `SET NOT NULL` | [Make SET NOT NULL skip the table scan](/learn/postgres-set-not-null-scan/) | Whether a validated `CHECK` proves the column has no nulls |
| `ADD FOREIGN KEY` | [Add a foreign key to a table with orphaned rows](/learn/postgres-foreign-key-existing-rows/) | Orphaned rows, and the lock on the referenced table |
| `REFRESH MATERIALIZED VIEW` | [When a materialized view refresh blocks readers](/learn/postgres-materialized-view-refresh/) | Whether readers wait, and whether `CONCURRENTLY` is available |
| Any `ALTER TABLE` | [Why a fast migration blocked every query](/learn/postgres-migration-lock-queue/) | 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](/learn/postgres-column-type-conversion-check/) | 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](/learn/postgres-unique-constraint-existing-duplicates/) | Duplicates by the application's meaning, and `NULL` handling |
| Integer key growth | [How much room is left in an integer ID](/learn/postgres-integer-id-headroom/) | Sequence headroom, and every column that stores the key |
| `CHECK` constraint | [What NOT VALID means for PostgreSQL CHECK constraints](/learn/postgres-not-valid-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](/learn/postgres-cached-plan-result-type/) | 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](/learn/postgres-positional-insert-column-changes/) | Loaders, fixtures and scripts that omit the column list |
| Unique index replaced | [Changing the unique index behind an ON CONFLICT upsert](/learn/postgres-on-conflict-unique-index-change/) | The previous release's upsert after each step |
| Enum value added | [Adding an enum value while the old release still reads it](/learn/postgres-enum-add-value/) | The previous release reading the new value |
| Table renamed | [Rename a table while the old release still uses the name](/learn/postgres-rename-table-view/) | The previous release writing through a view |
| Column renamed | [How to test a migration before rolling back the app](/learn/postgres-migration-rollback/) | 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](/learn/postgres-index-parameter-types/) | 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](/learn/postgres-migration-lock-queue/) 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](/learn/database-migration-review/) 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](/learn/data-migration-across-service-versions/) 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [PostgreSQL migration review for rolling deployments](/learn/database-migration-review/)
    
    Breaking migrations The Proofline team October 7, 2026 7 min read
    
-   ### [Why a fast PostgreSQL migration blocked every query](/learn/postgres-migration-lock-queue/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
