---
title: "Find every backfill mismatch with IS DISTINCT FROM | Proofline"
description: "A reconciliation query written with <> skips every row where one side is null, and a backfill's typical failure is a null."
url: https://proofline.sh/learn/postgres-backfill-reconciliation-is-distinct-from/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 22, 2026 3 min read

## 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
```

| id | status | state |
| --- | --- | --- |
| 17 | paid |  |
| 4242 | paid |  |
| 9001 |  | PAID |
| 31337 | paid | SHIPPED |

`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](https://www.postgresql.org/docs/16/functions-comparison.html) 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:

| Run | Rows mismatched now | Mismatched in an earlier run too |
| --- | --- | --- |
| 1 | 1 | none |
| 2 | 2 | 17 |
| 3 | 2 | 17 |

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](/learn/postgres-chunked-table-checksum/) shows. The next decision is in [decide when to stop writing the old column](/learn/when-to-stop-dual-writing/).

## 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Compare two PostgreSQL tables in chunks with a checksum](/learn/postgres-chunked-table-checksum/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Decide when to stop writing the old column](/learn/when-to-stop-dual-writing/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
