Database migration during operation: compatibility, locks and rollback

Fast DDL can still break a running application

A deployment renames customers.legacy_code to external_code and starts the new application version. An old worker is still finishing an export and still reads the original name. Its SQL fails even if the rename itself was quick. A rolling deployment also serves requests from old and new processes for a while. Measuring migration duration alone does not establish compatibility.

The following example covers an illustrative rename of a text field in PostgreSQL. It assumes legacy_code is populated and can be copied without changing its meaning. It is not a universal recipe for every schema. Type changes, partitioning or moving to another database require additional decisions and tests. Uninterrupted operation is a verification goal, not a property guaranteed by a pattern’s name.

Separate expansion and removal across releases

With expand–contract, you first introduce the new element while keeping the old one available. Update the application to work with the expanded schema. Remove the old element only after every reader and writer has switched. Your inventory must include web processes, workers, exports, reports, integrations and infrequently scheduled jobs.

In this example, the first compatible version still reads legacy_code but atomically writes both columns in the same transaction. During its rollout, external_code may not yet be correct everywhere. Start the backfill and new reads only after all original writers have been retired. Otherwise an old process could change only legacy_code after the backfill, creating a discrepancy that a one-off check would miss.

Before DDL, find which lock it will wait for

Many PostgreSQL ALTER TABLE operations use ACCESS EXCLUSIVE, which conflicts even with ordinary reads. A short statement can wait behind a long transaction. Waiting DDL may then increase waiting for further requests. Before deployment, inspect active transactions and locks on the actual table, rather than only reviewing the planned SQL file.

You can set lock_timeout and statement_timeout for the migration session itself. The former limits waiting for a lock; the latter limits statement duration. Values must follow the operational budget; the example’s two seconds are illustrative. Exceeding a limit interrupts this step, and another attempt is scheduled separately. When a statement fails inside a transaction, roll it back instead of continuing to the next step.

Adding a nullable column without a default still requires a lock, even though it needs no bulk value population. Other changes may rewrite the table or scan existing data. Check the precise behaviour for your database version and statement. An ORM label such as “add field” does not express these operational differences.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15s';
ALTER TABLE customers ADD COLUMN external_code text;
COMMIT;

Backfill must be resumable work

One UPDATE over a large table creates a long transaction and considerable load. In this illustrative approach, process a bounded number of rows, commit the batch and then continue. Choose the batch size by measuring representative data. Watch transaction duration, write volume, available space and any replica lag. The example’s five hundred rows are an initial test parameter, not a recommendation for every database.

The batch selects only missing values and locks those rows. Writers in the compatible version must update both columns in one transaction. Under concurrency, the database serialises writes to each selected row; the external_code IS NULL condition also prevents overwriting a value already filled in. If a transformation changes data meaning, replace this simple copy with a separately verified rule.

After a crash, the task must resume without repeatedly damaging data. Record progress and safely retry incomplete batches. SKIP LOCKED can omit currently busy rows, so run further passes and a final verification. Advancing a cursor or receiving zero rows from one batch does not by itself prove completion.

WITH batch AS (
  SELECT id
  FROM customers
  WHERE external_code IS NULL
  ORDER BY id
  LIMIT 500
  FOR UPDATE SKIP LOCKED
)
UPDATE customers AS customer
SET external_code = customer.legacy_code
FROM batch
WHERE customer.id = batch.id
  AND customer.external_code IS NULL;

Plan indexes and constraints separately

The new read path may need an index. CREATE INDEX CONCURRENTLY limits blocking of ordinary writes, but follows different rules from ordinary CREATE INDEX. It cannot run inside a transaction block, and failure can leave an invalid index. The operating procedure must verify the resulting state and clean up before another attempt; the index name’s existence is insufficient.

Add constraints only after understanding existing data and new writes. For an appropriate CHECK or foreign key, adding NOT VALID can be separated from a later VALIDATE CONSTRAINT. New writes must already obey the rule even while old rows remain unverified. This mechanism is not a universal shortcut for every constraint type; UNIQUE, for example, has a different procedure.

For the example rename, first verify that values are populated and match. If the new field must be required or unique, introduce that rule in a separate migration step with its own test. Avoid combining copying data, creating an index and removing the old column simply to make the deployment look shorter on paper.

Rolling back application code differs from rolling back data

After switching reads, this model permits rollback to the compatible version that still reads legacy_code and writes both columns. Returning to the original version, which writes only the old column, has different consequences: synchronisation would need to be restored before enabling new reads again. Identify an allowed rollback target by its actual version rather than a generic “we will restore the previous release”.

After dual writes stop, old data may stop updating. Removing the column also closes the straightforward rollback path. A backup is necessary, but restoring it can return other data to an earlier point and requires reconciliation of new writes. Before a destructive step, test recovery, assign responsibility and close the supported rollback window.

Measure consistency before and during the switch

Row counts do not reveal incorrect values in the correct places. In this example, track missing external_code values and differences between old and new columns whose contents should match. Evaluate them using a consistent database view. For transformations, also check meaning, references and representative application outputs.

Observe error rates and response times during the switch. PostgreSQL provides activity and waiting information; combine it with application metrics to distinguish lock waits from a slow query plan. Completing the backfill does not establish that the new read path has an appropriate index or that an old report uses the correct schema.

Rehearse stopping the migration too

A representative test needs realistic data volume and distribution, concurrent writes and a long transaction held during DDL. A small empty database checks syntax but says little about waiting or write load. During rehearsal, stop the backfill, resume it and restore the compatible application version.

Define continuation and interruption conditions for every step. If waiting grows or values diverge, stop further transitions while retaining the compatible schema. For a small application, a short agreed maintenance window may be cheaper and easier to manage than several releases with dual writes. Choose based on operational requirements and rehearsal results.

• The original application works after schema expansion, and every writer is accounted for.
• DDL stops according to its limit under a blocking transaction, and the next attempt is controlled.
• Backfill resumes after a crash and does not overwrite a newer concurrently updated value.
• Final verification finds rows omitted through SKIP LOCKED too.
• New reads meet the agreed performance requirements, and rollback to the compatible version works.
• The old column is removed only after all readers are checked and the rollback window ends.

Put the topic into practice.

Related project: FaxCopy a.s.