# Rehearse the migration while a long transaction is open

A fast command on a quiet database can wait behind live work. Test lock contention and cancellation before the release window.

By Cobnex editorial. Published 2026-09-10. Updated 2026-09-11.

## Use the actual migration command

Prepare a non-production database with representative table size, indexes and constraints. Run the same migration tool and database version intended for release.

Open a transaction that exercises a realistic application path and deliberately hold it open. Then start the migration. Observe whether it proceeds, waits or blocks later requests under the database's locking rules.

Keep the test controlled and isolated. The purpose is to understand the sequence, not to manufacture a production incident.

## Observe the application as well as the migration

Send ordinary reads and writes while the migration is waiting. Record latency, timeouts and queued connections. A migration that has not started changing data can still affect traffic through lock contention.

Inspect the blocker and waiter relationship using the database's supported activity tools. Save the relevant query identities and timing without exposing sensitive parameters.

The useful evidence is a timeline showing the open transaction, migration request and affected application operations. A single total-duration number cannot explain the failure mechanism.

## Exercise the stop conditions

Set the intended lock-wait and statement limits in the rehearsal. Confirm which condition ends the command and what the migration tool records afterwards.

Test cancellation during each meaningful phase. Some operations are transactional, while others have special execution and cleanup rules. Inspect the resulting schema instead of assuming cancellation restores the original state in every case.

For an index build, verify whether a failed attempt leaves an unusable object that needs a documented cleanup path. Use the database documentation for the exact operation and version.

## Retry from the observed state

Restart the migration after resolving the blocker. Confirm that the tool recognises completed steps and handles partial artefacts correctly.

Do not use a broad ignore-errors option to make the second attempt green. An existing object with the wrong definition is different from the intended object already being present.

Finish with application reads and writes from every supported release version. The rehearsal passes when traffic remains within the agreed limits, the stop procedure works and retry produces the intended schema without hidden leftovers.

## Sources

- [PostgreSQL: explicit locking](https://www.postgresql.org/docs/17/explicit-locking.html)
- [PostgreSQL: CREATE INDEX](https://www.postgresql.org/docs/17/sql-createindex.html)