Learning center

Find every backfill mismatch with IS DISTINCT FROM

A reconciliation query written with <> skips every row where one side is null, and a backfill's typical failure is a null. On a table with four wrong rows, <> found one. IS DISTINCT FROM found all four. Run the comparison more than once and report only rows that stay wrong, because the running release can be halfway through a write when the query reads.

The comparison#

The orders table has 100,000 rows, and state should equal upper(status) on every one. We planted four faults that a backfill and a sync trigger can leave behind, and ran this on PostgreSQL 16.15:

UPDATE orders SET state = NULL WHERE id IN (17, 4242);    -- the backfill skipped them
UPDATE orders SET status = NULL WHERE id = 9001;          -- the old release cleared it and nothing synced
UPDATE orders SET state = 'SHIPPED' WHERE id = 31337;     -- a wrong value

SELECT count(*) FROM orders WHERE state <> upper(status);                 -- 1
SELECT count(*) FROM orders WHERE state IS DISTINCT FROM upper(status);   -- 4
idstatusstate
17paid
4242paid
9001PAID
31337paidSHIPPED

state <> upper(status) is null, not true, when either side is null, and WHERE keeps only true rows. IS DISTINCT FROM treats null as a value: two nulls are not distinct, and a null and a value are. The comparison operators documentation defines both. A reconciliation that reports one mismatch on this table would have let three rows through, two of them the exact rows the backfill missed.

The same applies to any join that compares an old table with a new one: ON o.id = n.id AND o.amount IS DISTINCT FROM n.amount, never <>.

In-flight writes look like mismatches#

While the check runs, the running release keeps writing. A release that writes the two columns in two separate statements leaves a row mismatched between them. To separate those rows from real faults, record what each run sees and report only rows that a later run sees again:

CREATE TABLE mismatch_seen (
    id bigint PRIMARY KEY,
    first_run int NOT NULL,
    last_run int NOT NULL
);

-- run N
INSERT INTO mismatch_seen (id, first_run, last_run)
SELECT id, $run, $run FROM orders WHERE state IS DISTINCT FROM upper(status)
ON CONFLICT (id) DO UPDATE SET last_run = EXCLUDED.last_run;

SELECT id FROM mismatch_seen WHERE last_run = $run AND first_run < $run;

We left only order 17 wrong and ran a writer that updated 200 orders, status first and state 10 ms later. Three runs 0.7 s apart:

RunRows mismatched nowMismatched in an earlier run too
11none
2217
3217

Runs 2 and 3 each caught one in-flight order, a different one each time. Only order 17 persisted. Space the runs further apart than your longest write transaction takes.

A row seen for the first time in the latest run is not cleared by that query: it could be in flight, or it could have gone wrong a moment ago and stay wrong. Check each such row again after the same interval, by id, and treat any that still differs as persistent.

When to run it#

Run the count after the backfill finishes, during the whole period both releases write, and again before you stop writing the old column. Pass when the latest run reports no row that an earlier run also reported, and every row first seen in the latest run matches when you check it again after the interval. On a large table, add an id range to each run and walk the table in batches, or compare checksums first as compare two tables in chunks with a checksum shows. The next decision is in decide when to stop writing the old column.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading