Prove a PostgreSQL migration is reversible before release

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A migration with an up file and a down file is not necessarily reversible. The down file may recreate a dropped column but cannot recreate the values it once held. It may restore a type that cannot represent values accepted after the forward migration. It may also succeed while the old application still fails against changed constraints or row meanings.

A useful rollback claim states a boundary: which database states can be returned to which application version, with which data preserved? Without that boundary, “reversible” often means only that a SQL file parses.

Three different kinds of reversal

Schema reversal restores object definitions. Data reversal restores information and meaning. Application reversal restores compatibility with the previous binary. These can diverge. Adding a nullable column is often easy to reverse structurally; dropping it after new clients have written valuable information is not data-preserving.

For PostgreSQL 18, use a disposable database and record the initial schema, fixtures, application version, and migration version. Include values chosen to expose conversion loss, not just a single ordinary row. The following exercise uses a deliberately lossy type change.

CREATE TABLE reversible_price (
  sku text PRIMARY KEY,
  amount numeric(12,2) NOT NULL
);
INSERT INTO reversible_price VALUES
  ('whole', 20.00), ('fraction', 19.95), ('negative', -4.25);
CREATE TABLE price_before AS TABLE reversible_price;

ALTER TABLE reversible_price
ALTER COLUMN amount TYPE integer USING amount::integer;

ALTER TABLE reversible_price
ALTER COLUMN amount TYPE numeric(12,2)
USING amount::numeric(12,2);

SELECT b.sku, b.amount AS before_amount, p.amount AS after_amount
FROM price_before b
JOIN reversible_price p USING (sku)
WHERE b.amount IS DISTINCT FROM p.amount;

The inverse cast restores the declared type, not fractional information discarded by the forward conversion. On PostgreSQL 18.6 the comparison returns two rows: fraction comes back as 20.00 instead of 19.95, and negative as -4.00 instead of -4.25. Only whole survives the round trip. CREATE TABLE AS is a convenient fixture snapshot here; it does not copy every original constraint, permission, or dependency and is not a production backup strategy.

Test the overlap states, not just the endpoints

A rolling deployment passes through more states than “old” and “new.” At minimum test old application with old schema, old application with expanded schema, new application with expanded schema, and the rollback application against data written by the new release. The last combination is frequently omitted.

Reference review graph. Restoring object definitions is a different edge from restoring lost information or application compatibility.

Build a matrix of actual operations: insert, update, read, delete, export, and background processing. Use the same identifiers across forward and backward phases so the test verifies continuity rather than creating fresh data after each change. A rollback that works only on an empty database is not representative of an incident response.

For an additive migration, a compatibility assertion might be straightforward:

ALTER TABLE reversible_price ADD COLUMN currency text;
UPDATE reversible_price SET currency = 'USD';

-- The old read contract still projects the original columns.
SELECT sku, amount FROM reversible_price ORDER BY sku;

-- The new contract must define null handling during backfill.
SELECT sku, amount, COALESCE(currency, 'USD') AS currency
FROM reversible_price ORDER BY sku;

The COALESCE is safe only if USD truly is the approved interpretation of missing values. It is not a generic way to make incomplete migrations look complete. If old rows have multiple currencies, a default would conceal ambiguity rather than resolve it.

Establish the irreversible boundary

Common boundaries include the first write that uses a new enum value, the first value outside an old type's range, deleting a source column, and accepting new uniqueness semantics. Record the boundary before release. A rollback procedure might say: application rollback is allowed until the writer flag is enabled; after that, use a forward repair unless a data translation has been reviewed.

A backup changes recovery options but does not make all migrations reversible. Restoring a backup can lose writes made after the recovery point. Point-in-time recovery can preserve a chosen historical state, but it does not merge later valid writes into that state automatically. The restoration process also needs tested timing, storage capacity, and a decision about external side effects.

Keep destructive cleanup in a later release after the rollback window closes. That can mean keeping a source column synchronized, retaining an old table behind a compatibility layer, or temporarily rejecting values that older code cannot handle. These are explicit costs paid to preserve rollback capability.

Rehearse operational failure

Interrupt the migration after each committed phase. A batch backfill needs a trustworthy resume cursor and idempotent update semantics. A DDL operation may fail while waiting for a lock. A client connection can disappear after the server commits, leaving the runner uncertain whether the step completed. Recovery must inspect actual schema state rather than blindly rerunning every statement.

PostgreSQL supports transactional behavior for many DDL operations, but not every operational command can run inside a transaction block. Concurrent index creation is a notable example. Separate transaction boundaries in the runbook and explain how to recognize partial artifacts, such as an invalid index left after a failed build.

Finally, distinguish a successful rehearsal from a promise about production duration. Preserve the tested dataset size, concurrency, WAL volume, and recovery steps. The strongest migration review is a short, precise statement: which states are supported, which information is retained, where rollback stops being safe, and which forward repair remains available afterward.

Sources and review scope

• ALTER TABLE
• Continuous archiving and recovery
• CREATE INDEX