The migration passed, but the constraint still was not safe

The backfill job printed “completed,” yet a direct query still found rows with no value in the new column. The next deployment tried to enforce NOT NULL and waited behind normal traffic before failing. A green worker exit had been mistaken for valid data and a safe schema change.

This article uses PostgreSQL behavior deliberately. The phase boundaries apply to other databases, but commands, locks, catalogs, and online migration features do not transfer by analogy. Verify the equivalent behavior in the engine and version you operate.

Why a successful job can leave unsafe data

A live backfill is not one migration statement. It is a temporary system in which old rows, new rows, old application versions, new application versions, a repair worker, and the database rule can all disagree. Testing only the worker’s happy path ignores most of the states where a release fails.

Imagine an orders table gaining currency_code. Historical rows contain market_code but no currency. New code knows how to write both fields. Old code still writes only market_code during a rolling deployment. The final design requires currency_code to be NOT NULL.

Adding the column as nullable keeps the old writer compatible. That is the expand phase. The application then deploys a new write path, and possibly a read fallback while old rows remain. A worker repairs existing rows in bounded transactions. Once every active writer supplies a valid currency and every stored row satisfies the rule, the database can enforce it. A later release removes the fallback and temporary migration machinery.

Each boundary has a different oracle. Expand succeeds when both supported application versions can read and write. Backfill succeeds when an independent invariant query finds no unresolved target rows, including rows created during the job. Enforcement succeeds when catalog state shows the constraint is validated and a deliberately invalid write is rejected. Contract cleanup succeeds only after no supported code or operational tool depends on the transitional shape.

Worker counters are evidence, but not the final oracle. “Selected 500, updated 500” can still be misleading if the selection excludes a market with no mapping. “No next batch” can mean the pagination cursor skipped a row. A process can return zero after updating everything visible to its database role while another writer commits a new null immediately afterward.

Database state is stronger than process state, but the validation query can also be wrong. A predicate such as currency_code = NULL never finds nulls in SQL because null is not compared with equality. A join from orders to the mapping table can silently omit unmapped orders. A query against a delayed replica may not describe the primary at the enforcement point. Test the query with a known violating row before trusting its zero.

The migration also carries an application compatibility contract. A database-level rule may be correct in the final design and still be deployed too early. PostgreSQL permits a CHECK constraint to be added as NOT VALID, skipping the initial scan, but it enforces that constraint for subsequent inserts and updates. An old writer that omits the new value will start failing as soon as that CHECK is added. NOT VALID does not mean “observe only.” On PostgreSQL 18 the same warning applies to a not-null constraint added as NOT VALID: the table scan is skipped, and an insert that omits the value is still rejected with SQLSTATE 23502 immediately.

This distinction is the source of many unsafe runbooks. Teams add a nullable column and a NOT VALID non-null check in one release, believing old rows are the only exception. Existing rows are exempt from the initial scan, but old application instances are not exempt from enforcement on new writes. The gate before that step must prove writer adoption, not merely backfill progress.

PostgreSQL’s ALTER TABLE documentation also distinguishes validation from final NOT NULL enforcement. A named CHECK such as CHECK (currency_code IS NOT NULL) can be added NOT VALID and later validated. A valid CHECK that proves the absence of nulls can allow SET NOT NULL to skip its usual verification scan. That does not make ALTER TABLE lock-free. The operation still needs its documented locks, so rehearse acquisition and set an operational wait budget.

Which version you run changes the shape of that sequence, so pin it before designing the runbook. Through PostgreSQL 17, NOT VALID is accepted only for foreign-key and CHECK constraints, which is why the transitional CHECK exists at all: it is the only way to defer the scan that a not-null rule would otherwise force. PostgreSQL 18 adds not-null constraints to that list, so ALTER TABLE accepts a named table-level not-null constraint written as NOT NULL column_name with NOT VALID, and VALIDATE CONSTRAINT scans for all three kinds. The transitional CHECK is then unnecessary work: the constraint you actually want can be added unvalidated and validated later, without a second constraint to add and drop.

Rollback has two meanings that should never be collapsed. Rolling application code back during the expand phase may be safe because the new column is optional. Rolling the schema back after removing a column can restore a column definition, but it cannot reconstruct discarded values. A down script that executes successfully proves syntax and structure, not data recovery.

Prove every phase with a database-level oracle

Start the test from the oldest schema version you promise to upgrade. Applying only the newest migration to a handcrafted approximation can miss a renamed constraint, an old default, or data shape created by an earlier release. Seed rows through both direct fixtures and the supported old writer when that writer contributes important normalization or defaults.

Include at least four data categories. A straightforward legacy row should map cleanly. A row at the first and last batch boundaries should expose pagination mistakes. An already-correct row should remain unchanged. An unmappable row should stop validation and produce an actionable identifier instead of receiving a convenient but false default.

The following runnable shell test creates an isolated PostgreSQL schema, refuses to run unless the database name ends in _test, and exercises the expand contract. The values are fixture data, not production measurements. The old-style insert omits currency_code and must succeed while the column is nullable; the new-style insert supplies it.

#!/usr/bin/env bash
set -euo pipefail

: "${TEST_DATABASE_URL:?Use a disposable PostgreSQL database ending in _test}"

psql "$TEST_DATABASE_URL" -X --set=ON_ERROR_STOP=1 <<'SQL'
DO $$
BEGIN
  IF current_database() !~ '_test$' THEN
    RAISE EXCEPTION 'refusing migration test in database %', current_database();
  END IF;
END
$$;

DROP SCHEMA IF EXISTS qa_backfill CASCADE;
CREATE SCHEMA qa_backfill;

CREATE TABLE qa_backfill.orders (
  id bigint PRIMARY KEY,
  market_code text NOT NULL
);

INSERT INTO qa_backfill.orders (id, market_code) VALUES
  (101, 'US'),
  (205, 'GB'),
  (990, 'IN');

ALTER TABLE qa_backfill.orders
  ADD COLUMN currency_code text;

INSERT INTO qa_backfill.orders (id, market_code)
VALUES (1200, 'US');

INSERT INTO qa_backfill.orders (id, market_code, currency_code)
VALUES (1201, 'GB', 'GBP');

DO $$
DECLARE
  legacy_nulls integer;
  new_value text;
BEGIN
  SELECT count(*) INTO legacy_nulls
  FROM qa_backfill.orders
  WHERE currency_code IS NULL;

  SELECT currency_code INTO new_value
  FROM qa_backfill.orders
  WHERE id = 1201;

  IF legacy_nulls <> 4 THEN
    RAISE EXCEPTION 'expand fixture expected 4 legacy nulls, found %', legacy_nulls;
  END IF;
  IF new_value IS DISTINCT FROM 'GBP' THEN
    RAISE EXCEPTION 'new writer value was not stored: %', new_value;
  END IF;
END
$$;
SQL

The count assertion is tied to product state, not a list that guarantees its own truth. Premature NOT NULL enforcement makes the old-style insert fail. A migration that fills every old row with an undisclosed default changes the null count. A writer that drops the supplied currency breaks the value assertion. Each result points to a different contract violation.

Do not promote the literal count into a production expectation. It is correct only for this five-row fixture. Production validation asks whether the violation count is zero and retains samples of the rows that prevent zero. Operational dashboards can show counts over time, but those counts become measurements only when read from the running job and labeled with their query and time.

For the backfill, derive values from an approved source. The example uses a one-to-one market mapping maintained inside the fixture. A catch-all value such as UNKNOWN might satisfy NOT NULL while corrupting meaning. If UNKNOWN is a legitimate domain value, define its semantics and test downstream behavior. If it is only a way to make the constraint pass, leave the row unresolved and stop the rollout.

PostgreSQL does not provide LIMIT directly on UPDATE, but its UPDATE documentation shows the common-table-expression pattern for bounded changes. The next script adds an unambiguous mapping table, repairs rows in batches, and checks that a second pass is a no-op. The selected batch uses row locks and SKIP LOCKED so multiple workers can avoid waiting on the same rows. PostgreSQL explicitly warns that SKIP LOCKED gives an inconsistent view and is suitable for queue-like consumption, not general-purpose reads. That is why the final invariant query is mandatory.

#!/usr/bin/env bash
set -euo pipefail

: "${TEST_DATABASE_URL:?Run the expand fixture first}"
batch_size=2

psql "$TEST_DATABASE_URL" -X --set=ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE qa_backfill.market_currency (
  market_code text PRIMARY KEY,
  currency_code text NOT NULL
);

INSERT INTO qa_backfill.market_currency (market_code, currency_code) VALUES
  ('US', 'USD'),
  ('GB', 'GBP'),
  ('IN', 'INR');
SQL

require_count() {
  local label="$1"
  local value="$2"
  [[ "$value" =~ ^[0-9]+$ ]] || {
    echo "$label produced a non-numeric result: '$value'" >&2
    exit 1
  }
}

backfill_batch() {
  psql "$TEST_DATABASE_URL" -X --tuples-only --no-align \
    --set=ON_ERROR_STOP=1 --set=batch_size="$batch_size" <<'SQL'
WITH batch AS (
  SELECT orders.id
  FROM qa_backfill.orders AS orders
  JOIN qa_backfill.market_currency AS mapping
    ON mapping.market_code = orders.market_code
  WHERE orders.currency_code IS NULL
  ORDER BY orders.id
  FOR UPDATE OF orders SKIP LOCKED
  LIMIT :batch_size
),
changed AS (
  UPDATE qa_backfill.orders AS orders
  SET currency_code = mapping.currency_code
  FROM batch
  JOIN qa_backfill.market_currency AS mapping
    ON true
  WHERE orders.id = batch.id
    AND mapping.market_code = orders.market_code
    AND orders.currency_code IS NULL
  RETURNING orders.id
)
SELECT count(*) FROM changed;
SQL
}

run_whole_backfill() {
  local updated
  while true; do
    updated="$(backfill_batch)"
    require_count "the batch statement" "$updated"
    [[ "$updated" -eq 0 ]] && break
  done
}

row_snapshot() {
  psql "$TEST_DATABASE_URL" -X --tuples-only --no-align \
    --set=ON_ERROR_STOP=1 \
    --command="SELECT id, currency_code, xmin::text
               FROM qa_backfill.orders
               ORDER BY id"
}

run_whole_backfill

remaining="$(
  psql "$TEST_DATABASE_URL" -X --tuples-only --no-align \
    --set=ON_ERROR_STOP=1 \
    --command="SELECT count(*) FROM qa_backfill.orders WHERE currency_code IS NULL"
)"
require_count "the remaining-null query" "$remaining"
[[ "$remaining" -eq 0 ]] || {
  echo "$remaining orders still have no currency_code" >&2
  exit 1
}

incorrect="$(
  psql "$TEST_DATABASE_URL" -X --tuples-only --no-align \
    --set=ON_ERROR_STOP=1 \
    --command="SELECT count(*)
               FROM qa_backfill.orders AS orders
               LEFT JOIN qa_backfill.market_currency AS mapping
                 ON mapping.market_code = orders.market_code
               WHERE orders.currency_code IS DISTINCT FROM mapping.currency_code"
)"
require_count "the mapping-comparison query" "$incorrect"
[[ "$incorrect" -eq 0 ]] || {
  echo "$incorrect orders have a currency_code that disagrees with the approved mapping" >&2
  exit 1
}

before_rerun="$(row_snapshot)"
[[ -n "$before_rerun" ]] || {
  echo "the row snapshot was empty, so the rerun comparison would prove nothing" >&2
  exit 1
}

run_whole_backfill

after_rerun="$(row_snapshot)"
[[ "$after_rerun" == "$before_rerun" ]] || {
  echo "a second complete backfill run rewrote stored rows" >&2
  diff <(printf '%s\n' "$before_rerun") <(printf '%s\n' "$after_rerun") >&2 || true
  exit 1
}

The completion gate uses two data oracles that read stored rows rather than the worker's counters. The direct invariant catches any remaining null, including one committed by a concurrent writer. The mapping comparison uses a LEFT JOIN with IS DISTINCT FROM and requires zero disagreements, so a non-null catch-all such as UNKNOWN, a value supplied without an approved mapping, or USD copied into every market fails even though NOT NULL would accept it. Be exact about what that second oracle is independent of. It reads the same qa_backfill.market_currency fixture the backfill writes from, so it is independent of the worker's SQL, its pagination, and its reported counts, and it is not independent of the source of truth. A wrong mapping row makes the repair and the check wrong in the same direction, and only a review of the mapping against the business definition catches that.

The idempotency oracle needs more care than it usually gets, because the obvious version of it proves nothing at all. Running the batch statement one more time and requiring zero changed rows re-tests the loop's own exit condition. The loop ends only when that identical statement returns zero, and nothing writes between the exit and the extra call, so the answer is zero by construction no matter what the worker does. This script instead snapshots every row, runs the entire backfill again from the top, and requires the snapshot to be unchanged. The snapshot includes the system column xmin, so a rerun that rewrites a row with a value identical to the one already stored still changes that row's transaction stamp and fails the comparison.

The difference is not academic. A worker that pages over the whole table by keyset and updates every row it reads, rather than restricting itself to unfilled rows, terminates normally and leaves the guarded batch statement reporting zero changes, while the snapshot shows every row rewritten. Assigning a value from a stable mapping is naturally idempotent, but a transformation that appends text, emits events, or moves money needs stronger side-effect assertions, and a rerun that silently rewrites rows is exactly the shape that turns those transformations into duplicates.

Every count crossing from psql into the shell passes through the same numeric guard first. In bash 3.2, still the /bin/bash shipped on macOS, a comparison such as [[ "" -eq 0 ]] evaluates to true, so a query that exits zero but prints nothing would satisfy a bare equality check and pass the gate in silence. The guard rejects anything that is not a run of digits before the comparison runs, and the snapshot has its own emptiness check for the same reason.

Review the UPDATE join carefully. PostgreSQL warns that if a target row joins to more than one source row, one source is chosen but not predictably. The primary key on market_currency prevents that failure in this fixture. Without it, a duplicate mapping could yield a valid non-null value that changes between runs. The database constraint would pass while the business data remained untrustworthy.

Catch skipped rows, retries, and writers that arrive late

Offset pagination is a common source of false completion. A worker selects null rows with OFFSET 0, updates them so they no longer match, then selects OFFSET one batch size from the now-smaller result. It skips the rows that shifted into the first page. The final batch is empty, so the worker announces completion.

Keyset selection or repeated selection of the first remaining rows avoids that exact movement. Row locking can coordinate multiple workers. Neither choice eliminates the need for the final invariant. New rows can appear, mappings can be absent, transactions can roll back, and permissions can hide data from a poorly chosen validation role.

Test first and last identifiers rather than only a smooth sequence. Sparse keys such as 101, 205, and 990 expose assumptions that IDs are contiguous. Insert a row while the worker is between batches. Update an eligible row concurrently. Kill the worker after it has selected a batch but before commit, then restart it from the recorded checkpoint. The expected state is defined in the database, not by how many loop iterations occurred.

Checkpoint timing deserves its own assertion. If a worker records last_id before the transaction that updates the batch commits, a crash can advance the cursor past unchanged rows. Keep the checkpoint and updates in one transaction where the design permits it, or make rescan behavior explicit. A test can pause the process at the boundary, terminate it, restart, and require the final zero-violation query to pass.

Retries must be safe for the transformation and its side effects. Setting currency_code from a stable mapping produces the same final value on a second run. Sending a “currency assigned” event after every UPDATE may duplicate events even when the row value is unchanged. Restrict the update to currency_code IS NULL, use an outbox or equivalent approved design, and assert event identity separately. The database column alone cannot prove downstream exactly-once behavior.

An old writer is more dangerous than an old row because it can undo progress. The backfill may reach zero at noon, then a lagging application instance inserts null before enforcement. Prove writer adoption from deployment inventory and exercise every supported write path: current API, older rolling version, imports, scheduled jobs, administrative scripts, and direct integrations. If any must remain active, keep the schema compatible or add a server-side transition that supplies a correct value.

Dual writing has its own failure modes. The old and new fields may disagree, one write may commit without the other, or a retry may update only one representation. Read fallback can hide these inconsistencies because the application chooses whichever field exists. Add a comparison query that identifies disagreement, not merely nulls. Decide which field is authoritative before repairing conflicts.

An unmappable row should create a visible exception. The next diagnostic inserts a fictional market ZZ without a mapping, proves that the naive joined count misses it, and then runs the correct invariant directly against orders. Both results are assertions raised inside the database rather than rows printed to a log. A query whose output is only displayed is not proof of anything, because nothing fails when the answer is wrong and nobody is reading the transcript of a green build.

#!/usr/bin/env bash
set -euo pipefail

: "${TEST_DATABASE_URL:?Run against the disposable migration database}"

psql "$TEST_DATABASE_URL" -X --set=ON_ERROR_STOP=1 <<'SQL'
INSERT INTO qa_backfill.orders (id, market_code, currency_code)
VALUES (2001, 'ZZ', NULL);

DO $$
DECLARE
  wrong_joined_violation_count integer;
  unresolved bigint[];
BEGIN
  SELECT count(*) INTO wrong_joined_violation_count
  FROM qa_backfill.orders AS orders
  JOIN qa_backfill.market_currency AS mapping
    ON mapping.market_code = orders.market_code
  WHERE orders.currency_code IS NULL;

  SELECT array_agg(orders.id ORDER BY orders.id) INTO unresolved
  FROM qa_backfill.orders AS orders
  WHERE orders.currency_code IS NULL;

  IF wrong_joined_violation_count <> 0 THEN
    RAISE EXCEPTION
      'the joined query was expected to miss the unmapped order, but found %',
      wrong_joined_violation_count;
  END IF;
  IF unresolved IS DISTINCT FROM ARRAY[2001]::bigint[] THEN
    RAISE EXCEPTION 'unresolved orders were expected to be {2001}, found %',
      unresolved;
  END IF;
END
$$;
SQL

remaining="$(
  psql "$TEST_DATABASE_URL" -X --tuples-only --no-align \
    --set=ON_ERROR_STOP=1 \
    --command="SELECT count(*) FROM qa_backfill.orders WHERE currency_code IS NULL"
)"
[[ "$remaining" =~ ^[0-9]+$ ]] || {
  echo "the remaining-null query produced a non-numeric result: '$remaining'" >&2
  exit 1
}
[[ "$remaining" -eq 1 ]] || {
  echo "diagnostic fixture expected one unresolved order, found $remaining" >&2
  exit 1
}

The joined query reports zero because ZZ has no mapping row, and the assertion requires that zero, so the fixture fails loudly if someone adds a ZZ mapping and quietly removes the adversarial case. The direct predicate reports order 2001, and the assertion compares the whole array of unresolved identifiers rather than a count, so a catch-all that fills the row with UNKNOWN fails with the identifiers printed in the exception. A validation query should be tested against this kind of adversarial row before it becomes a deployment gate.

Catalog evidence answers a separate question, and it has to be collected where the constraint exists. In pg_constraint, convalidated records whether a constraint has been validated. A row with convalidated false means enforcement may cover new changes but PostgreSQL has not accepted the rule for all existing rows. A true value proves database validation occurred; it does not prove the mapped currency is semantically correct. Querying that catalog from this diagnostic would be worse than useless, because the named constraint is created and dropped inside the enforcement script that runs afterward. The query would return no rows on every run, in every state of the system, and a reader who saw it in the transcript would take an empty result for evidence. The assertions belong in the enforcement script, between the statements whose effect they describe.

Distinguish bad data from an enforcement operation that is blocked

Constraint rollout can fail because rows violate the rule, because the statement cannot acquire its lock, because the session lacks permission, or because the DDL targets the wrong relation. Those failures may all appear to a deployment system as a nonzero exit. Treating them as one retryable error can turn a safe abort into a traffic incident.

Run the invariant query immediately before enforcement using the same primary database and an appropriately privileged migration connection. Retain the count, sample identifiers, migration version, and database identity. Then execute the DDL with ON_ERROR_STOP so psql cannot continue after a failed statement. A script that logs an error and proceeds to the cleanup phase is worse than no automation.

When ALTER TABLE waits, inspect pg_stat_activity and pg_locks from a separate administrative session. Identify the waiting migration backend, requested relation, blocking backend, transaction age, and application name. Do not terminate a blocker automatically unless the runbook gives that authority and identifies safe targets. A long-running business transaction may require coordination, not a kill command.

PostgreSQL lock_timeout aborts a statement that waits too long to acquire a lock. It applies to each lock acquisition attempt and is distinct from statement_timeout. The documentation discourages setting it globally in postgresql.conf because that would affect every session. Set a reviewed budget only for the migration session. The three-second value in the next example is illustrative; choose a value from your service’s operational tolerance and rehearsal evidence.

Data failure leaves different evidence. VALIDATE CONSTRAINT scans existing rows for a foreign key, CHECK or, from PostgreSQL 18, a not-null constraint that was added NOT VALID. If an old violation remains, validation fails, and the message names the column or constraint rather than the row count. Repair the identified data or correct the rule. Retrying the same scan without changing either cannot make the invariant true.

An invalid-write probe closes a gap that catalog inspection cannot. After final enforcement, deliberately attempt a null in a transaction or isolated fixture. Require a failure and the expected SQLSTATE class. PostgreSQL's error-code appendix assigns SQLSTATE 23502 to not-null violations. Matching only localized message text is less stable than checking the code.

Roll out enforcement without guessing

The safest sequence starts with a written compatibility matrix. List every deployed application version and writer. For each phase, state whether it can read rows with and without the new value, whether it writes the value, and whether it tolerates the new constraint. A diagram is optional. The matrix is the release contract.

First deploy the nullable column without removing or changing the old representation. Run old-version and new-version smoke tests against that schema. Next deploy code that writes currency_code for every new or changed order. If reads temporarily fall back to market_code, test both populated and legacy rows and preserve metrics that reveal fallback use.

Wait until deployment evidence shows that incompatible writers are gone. “The new deployment completed” is not enough if workers, cron jobs, or a second service still run the old code. Exercise those paths directly. Only then add the named CHECK as NOT VALID. From that moment, PostgreSQL rejects a new or updated row that does not satisfy it, even though existing rows have not yet been scanned.

Backfill legacy rows in bounded, committed batches. Record each batch boundary and changed count for diagnosis, but gate completion on the direct invariant. Resolve unmappable rows through an approved business decision. Run the full job again and require no product changes or duplicate side effects.

Validate the CHECK after the invariant reaches zero. PostgreSQL uses a SHARE UPDATE EXCLUSIVE lock for VALIDATE CONSTRAINT, which allows more concurrency than the lock normally used to add many constraints, but it still conflicts with documented operations. Rehearse on realistic schema and workload characteristics. Do not publish a duration estimate from a toy fixture as a production measurement.

Finally set the column NOT NULL while the valid CHECK remains in place, then drop the temporary CHECK if the final schema does not need both. PostgreSQL can use the valid CHECK to prove no null exists and skip the verification scan for SET NOT NULL. The final ALTER still needs to acquire a lock. A session-specific lock timeout makes an unexpected wait fail visibly so the team can reschedule or investigate.

On PostgreSQL 18 that four-statement dance collapses into two. Add the not-null constraint itself as NOT VALID before the backfill, so new writes are rejected immediately without a scan, then validate it once the invariant reaches zero. The catalog assertion is the same shape as the CHECK version, with contype n instead of c, and it is the assertion the go and stop conditions should name.

ALTER TABLE qa_backfill.orders
  ADD CONSTRAINT orders_currency_code_not_null
  NOT NULL currency_code
  NOT VALID;

ALTER TABLE qa_backfill.orders
  VALIDATE CONSTRAINT orders_currency_code_not_null;

DO $$
DECLARE
  validated boolean;
BEGIN
  SELECT convalidated INTO validated
  FROM pg_catalog.pg_constraint
  WHERE conrelid = 'qa_backfill.orders'::regclass
    AND conname = 'orders_currency_code_not_null'
    AND contype = 'n';

  IF NOT FOUND THEN
    RAISE EXCEPTION 'the named not-null constraint was not created';
  END IF;
  IF validated IS DISTINCT FROM true THEN
    RAISE EXCEPTION
      'the not-null constraint is still unvalidated, convalidated = %',
      validated;
  END IF;
END
$$;

One observation from running that sequence is worth carrying into your gate. Between the two statements, information_schema.columns already reports is_nullable as NO for the column, while pg_constraint still reports convalidated as false. The column catalog describes the rule that applies to new writes, not the state of the existing rows, so it cannot be the oracle for whether validation happened. Only the pg_constraint row separates the two states. The same query also returns the automatically named not-null constraints PostgreSQL 18 records for primary-key columns, so filter by constraint name rather than by contype alone.

The following enforcement script assumes the ZZ fixture has been repaired and all writers have been upgraded. It adds the check, requires the catalog to report it as not validated, validates it, requires convalidated to have flipped to true, sets NOT NULL, drops the transitional check, requires the column catalog to report the column as not nullable, and proves a null insert fails with SQLSTATE 23502. Each of those checks raises inside psql under ON_ERROR_STOP, so a failed DDL statement or a false catalog state cannot be turned into success by a later statement.

#!/usr/bin/env bash
set -euo pipefail

: "${TEST_DATABASE_URL:?Use the disposable migration database}"

psql "$TEST_DATABASE_URL" -X --set=ON_ERROR_STOP=1 <<'SQL'
UPDATE qa_backfill.orders
SET market_code = 'US', currency_code = 'USD'
WHERE id = 2001 AND market_code = 'ZZ' AND currency_code IS NULL;

SET lock_timeout = '3s';

ALTER TABLE qa_backfill.orders
  ADD CONSTRAINT orders_currency_code_present
  CHECK (currency_code IS NOT NULL)
  NOT VALID;

DO $$
DECLARE
  validated boolean;
BEGIN
  SELECT convalidated INTO validated
  FROM pg_catalog.pg_constraint
  WHERE conrelid = 'qa_backfill.orders'::regclass
    AND conname = 'orders_currency_code_present'
    AND contype = 'c';

  IF NOT FOUND THEN
    RAISE EXCEPTION 'the named CHECK constraint was not created';
  END IF;
  IF validated THEN
    RAISE EXCEPTION 'a NOT VALID CHECK reported convalidated = true';
  END IF;
END
$$;

ALTER TABLE qa_backfill.orders
  VALIDATE CONSTRAINT orders_currency_code_present;

DO $$
DECLARE
  validated boolean;
BEGIN
  SELECT convalidated INTO validated
  FROM pg_catalog.pg_constraint
  WHERE conrelid = 'qa_backfill.orders'::regclass
    AND conname = 'orders_currency_code_present'
    AND contype = 'c';

  IF validated IS DISTINCT FROM true THEN
    RAISE EXCEPTION
      'VALIDATE CONSTRAINT did not leave convalidated true, found %',
      validated;
  END IF;
END
$$;

ALTER TABLE qa_backfill.orders
  ALTER COLUMN currency_code SET NOT NULL;

ALTER TABLE qa_backfill.orders
  DROP CONSTRAINT orders_currency_code_present;

DO $$
DECLARE
  nullability text;
BEGIN
  SELECT is_nullable INTO nullability
  FROM information_schema.columns
  WHERE table_schema = 'qa_backfill'
    AND table_name = 'orders'
    AND column_name = 'currency_code';

  IF nullability IS DISTINCT FROM 'NO' THEN
    RAISE EXCEPTION 'currency_code is_nullable was expected to be NO, found %',
      nullability;
  END IF;
END
$$;
SQL

set +e
failure="$(
  psql "$TEST_DATABASE_URL" -X \
    --set=ON_ERROR_STOP=1 \
    --set=VERBOSITY=verbose \
    --command="INSERT INTO qa_backfill.orders
               (id, market_code, currency_code)
               VALUES (3001, 'US', NULL)" 2>&1
)"
status=$?
set -e

[[ "$status" -ne 0 ]] || {
  echo "invalid insert succeeded after NOT NULL enforcement" >&2
  exit 1
}
grep --quiet '23502' <<<"$failure" || {
  echo "insert failed for an unexpected reason: $failure" >&2
  exit 1
}

This probe can fail meaningfully in both directions. If NOT NULL is absent, the insert succeeds and the script stops. If a different constraint, permission problem, or missing table causes the error, the SQLSTATE check rejects that false green. The fixture does not merely assert that “an error happened.” The catalog assertions fail for their own reasons, which is what makes them worth writing: remove the ADD CONSTRAINT statement and the first block reports that the named constraint was never created, remove VALIDATE CONSTRAINT and the second reports convalidated still false, remove SET NOT NULL and the third reports the column as still nullable.

Wire the same harness into CI against a disposable PostgreSQL service and keep a larger rehearsal outside pull-request CI. The service version should match the production major and any relevant extensions. The workflow below uses the current checkout action version and the official PostgreSQL container. Replace the script path with the repository-owned harness assembled from the examples above.

name: migration-contract

on:
  pull_request:
    paths:
      - "db/migrations/**"
      - "db/tests/**"

jobs:
  postgres-migration:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:18
        env:
          POSTGRES_DB: migration_test
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd "pg_isready -U postgres -d migration_test"
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10
    steps:
      - uses: actions/checkout@v7
      - name: Verify expand, backfill, validation, and enforcement
        env:
          TEST_DATABASE_URL: postgresql://postgres:postgres@localhost:5432/migration_test
        run: bash db/tests/test-currency-migration.sh

The health-check intervals in that configuration are configuration choices, not measured recommendations for a production database. Pull-request CI proves deterministic schema and data contracts on small fixtures. It does not prove how long validation will run, how much replica lag a backfill may create, or which production transaction will block the DDL.

Stage rehearsal should use masked or generated data that preserves relevant distribution without exposing customer records. Observe locks, transaction duration, update rate, database load, table growth, and replication behavior with the production team’s approved tools. Report the measurements with timestamps and environment. Never transplant illustrative CI timings into a rollout forecast.

During production rollout, publish explicit go and stop conditions. Examples include no incompatible writer instances, a zero direct violation count on the primary, no unresolved mapping exceptions, a pg_constraint row with convalidated true for the named constraint (the transitional CHECK through PostgreSQL 17, the not-null constraint on 18), successful lock acquisition within the approved budget, and a rejected invalid-write probe in a safe test tenant or transaction. Every one of those signals must be asserted somewhere that fails, not printed somewhere that scrolls past. Assign an owner for each signal.

Cleanup should be a later change. Remove read fallback only after telemetry shows it is unused and support confirms no hidden writer remains. Delete the backfill job after retaining its migration evidence and recovery notes. Drop the old column only when its data is no longer needed for rollback or audit. A quiet period is not proof; dependency search and version inventory are.

When the staged pattern is the wrong tool

Do not build a multi-release expand-and-contract process for every small table. If a verified maintenance window permits a direct change, the table is genuinely small, and all writers can stop together, a single migration plus an invalid-write test may be safer than weeks of dual-state complexity. Record why the simpler path meets the availability requirement.

Avoid a CHECK constraint for a rule that depends on other rows or changing external data. PostgreSQL's constraint documentation explains why CHECK constraints cannot safely enforce conditions involving other table data. Use the database feature that represents the invariant, such as UNIQUE or FOREIGN KEY where appropriate, and test its actual enforcement and rollout semantics.

Do not backfill a value before the business meaning is settled. Turning missing currency into USD, an absent consent state into accepted, or an unknown owner into a system user can produce valid-looking but false data. A constraint protects shape. It cannot decide semantics.

Skip concurrent workers when one bounded worker meets the operational window. SKIP LOCKED adds coordination complexity and an intentionally inconsistent selection view. More workers can increase database pressure and make incident reconstruction harder. Add concurrency only after a rehearsal shows the need and the database team approves the load.

Do not claim rollback when the forward transformation discards information. Converting free text to an enum, merging columns, or dropping legacy data may not be reversible. Test restore from backup or a forward repair when that is the actual recovery plan. An up-down-up test is valuable only for migrations that promise structural reversibility.

Finally, do not copy the PostgreSQL sequence into MySQL, SQL Server, Oracle, or a managed migration service and assume the names imply the same behavior. Check that engine’s constraint validation, online DDL, locking, transaction, and catalog documentation. Keep the phase assertions, but replace every implementation-specific mechanism with one you have verified.