Why reviewed migrations still break deployments#
A migration and the code that uses it can both pass tests against the new schema. The deployment can still fail while old and new application versions share one database.
Database migration review asks which schema each running version can read and write at every step. Include background workers, scheduled jobs, and the version you would restore during a rollback.
Adding a constraint has its own validation and locking questions. See what NOT VALID means for PostgreSQL CHECK constraints. If a rollout also changes replica counts or connection pools, check the PostgreSQL connection budget during deployment alongside schema compatibility.
A column rename that breaks the running version#
This illustrative example uses PostgreSQL. An orders API stores a non-null text value in orders.status. A pull request renames the column to state and updates the application queries:
ALTER TABLE orders RENAME COLUMN status TO state;
The tests pass because they run the new application against the new schema. During deployment, however, the workflow runs migrations before replacing the API tasks. The running version still executes:
SELECT status FROM orders WHERE id = $1;
As soon as the rename commits, that query fails because status no longer exists. A rolling deployment extends the overlap: old tasks keep serving requests while their replacements become healthy.
Reversing the order does not solve this example. If the new application queries state before the migration, that column does not exist yet. The rollout needs a schema that both versions can use.
PostgreSQL documents column renames and other table changes. A valid SQL statement does not establish compatibility with the applications that use the table.
Expand and contract across releases#
An expand-and-contract migration adds the new structure, moves its users, and removes the old structure in a later release. For this example, the two columns carry the same value throughout the transition.
Expand the schema#
Add a nullable column and keep the original column:
ALTER TABLE orders ADD COLUMN state text;
Existing rows initially have a null state. Existing application versions continue to read and write status.
Adding a column still needs a lock. Review lock acquisition and transaction duration for your PostgreSQL version and workload. The ALTER TABLE reference documents the required locks. Even this additive change can make application queries wait.
Deploy compatible writers, then backfill#
Deploy an application version that still reads status and writes both columns in the same database transaction. Update every insert and update path, including workers and scheduled jobs.
For an order update, the write can take this form:
UPDATE orders
SET status = $1, state = $1
WHERE id = $2;
Wait until every writer that updates only status has stopped before relying on the backfill. An old writer can otherwise change status after its row was copied, leaving state stale.
Backfill existing rows from status in bounded transactions. Keep status authoritative during this phase. Design the backfill to coordinate with concurrent writes and to resume without overwriting newer values with an older copy.
Verify that the two columns agree before switching readers. Checking only for nulls misses stale values left by an older writer. In this example, a reconciliation query is:
SELECT count(*) AS mismatched_rows
FROM orders
WHERE state IS DISTINCT FROM status;
This query can scan the table. Choose its execution window and the backfill batch size using your database's observed load. A zero count is useful only while every active writer preserves equality between the columns.
The original column disallows nulls. After reconciliation, preserve that requirement on state with a separately reviewed constraint change:
ALTER TABLE orders ALTER COLUMN state SET NOT NULL;
Plan its validation and lock requirements before running it.
Switch readers and retain rollback compatibility#
Deploy a version that reads state and continues to write both columns. The previous compatible version can still read status if the new release needs to roll back.
Test both application versions against the expanded schema. Include inserts, updates, background jobs, and records created during the rollout. A successful migration command alone does not test these paths.
Contract after old users are retired#
First retire every reader and supported rollback version that depends on status. Before deploying writers that omit it, remove its old NOT NULL requirement:
ALTER TABLE orders ALTER COLUMN status DROP NOT NULL;
Without that step, inserts that supply only state fail even though both columns still exist. Deploy code that no longer reads or writes status. Wait until all running jobs and supported rollback versions also meet that condition. Then remove the old column in a separate migration:
ALTER TABLE orders DROP COLUMN status;
Once writes to status stop, a rollback to code that reads it can return stale values. Once the column is dropped, that code cannot run against the schema. Choose the rollback boundary before cleanup, and retain a recovery plan for the data you remove.
A database migration review checklist#
Review the deployment sequence alongside the SQL and application diff:
- Identify every reader and writer, including other services, workers, scheduled jobs, and rollback candidates.
- Record whether migrations run before, during, or after the application rollout.
- Check the old application against the expanded schema and the new application against the schema it first encounters.
- Keep writes consistent during coexistence. Define the authoritative value and when older writers must stop.
- Plan backfill batches, progress checks, restart behavior, and reconciliation before reader cutover.
- Review lock requirements, long transactions, table rewrites, and constraint validation against the database version and observed workload.
- Separate destructive cleanup from the reader cutover. State when rollback to an older version stops being supported.
How Proofline reviews the migration#
Proofline reviews a database migration against the version still running, as well as the code in the pull request. Migration paths, SQL files, schema declarations, and executable code can bring datastore compatibility into a review.
For the rename example, the finding would explain why the old query fails after the column rename. To support that explanation, it would cite the rename, the query, and evidence that the old application runs when the migration takes effect. The recommended response would separate the schema transition from destructive cleanup.
The example describes a possible investigation, not a measured result. Proofline publishes findings when the recorded evidence supports them. See how a review works.
What the review relies on#
The proposed code comes from the exact commit under review. Recorded deployment evidence can identify the running revision and its source commit. Rollout facts come from connected sources such as ECS or Cloud Run.
Proofline reads database configuration from the cloud control plane, such as RDS and Cloud SQL instance settings and versions. It does not connect to your database or read its rows.
These settings do not establish row counts, current lock contention, or how long a backfill will take. Verify those with your database tools. Missing required environment evidence makes the assessment incomplete and names the gap; a code-only finding does not complete it.
For your team's review process, start with the checklist above and compare Proofline's code-review workflow. The free-review evaluation guide explains how to assess findings on your own pull requests.
Questions#
Does Proofline connect to my database?
No. Proofline reads database configuration from the cloud control plane, such as RDS and Cloud SQL settings. It never reads your rows.
Which migration tools does Proofline support?
Proofline reviews migration code in the pull request. The migration tool's name alone does not establish coverage. The review needs the files, deployment order, and relevant environment evidence to investigate compatibility.
What is an expand and contract migration?
An expand and contract migration splits a breaking schema change across releases. First add the new structure and move the code to it. Remove the old structure only after no running version uses it.
Can I roll back the application after a database migration?
Only if the earlier application remains compatible with the current schema and data. Retaining a column is insufficient when new writes stop updating it. Test the rollback version during the transition and retire incompatible rollback targets before cleanup.