---
title: "Compare two PostgreSQL tables in chunks with a checksum | Proofline"
description: "When data moves to a new table, a row-by-row comparison of millions of rows tells you only how many differ. A checksum per range of keys tells you where."
url: https://proofline.sh/learn/postgres-chunked-table-checksum/
---

[Learning center](/learn/)

# Compare two PostgreSQL tables in chunks with a checksum

When data moves to a new table, a row-by-row comparison of millions of rows tells you only how many differ. A checksum per range of keys tells you where. On two one-million-row tables, a hash per 10,000 ids took 2.5 s and pointed at two ranges; comparing rows inside those ranges found a deleted row and an amount that was one cent off. Build the hashed text from each column's value, not its display form, or equal values hash differently.

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

## The lab

`invoices` is the old table and `invoices_v2` the copy the next release will read. We copied all 1,000,000 rows, then planted two faults, and ran this on PostgreSQL 16.15:

```
UPDATE invoices_v2 SET amount = amount + 0.01 WHERE id = 734512;
DELETE FROM invoices_v2 WHERE id = 81234;
```

The comparison builds one hash per chunk on each side and joins the chunks:

```
WITH a AS (
  SELECT id / 10000 AS chunk, count(*) AS n,
         md5(string_agg(format('%s|%s|%s|%s', id, customer_id, trim_scale(amount),
                               extract(epoch FROM issued_at)), ',' ORDER BY id)) AS h
  FROM invoices GROUP BY 1),
b AS (
  SELECT id / 10000 AS chunk, count(*) AS n,
         md5(string_agg(format('%s|%s|%s|%s', id, customer_id, trim_scale(amount),
                               extract(epoch FROM issued_at)), ',' ORDER BY id)) AS h
  FROM invoices_v2 GROUP BY 1)
SELECT chunk, a.n AS old_rows, b.n AS new_rows
FROM a FULL JOIN b USING (chunk)
WHERE a.h IS DISTINCT FROM b.h
ORDER BY chunk;
```

| chunk | old\_rows | new\_rows |
| --- | --- | --- |
| 8 | 10000 | 9999 |
| 73 | 10000 | 10000 |

Chunk 8 is ids 80,000 to 89,999 and has lost a row. Chunk 73 has the same count and a different hash, so a value changed. A full join of the two tables inside those ranges, with `IS DISTINCT FROM` on each column, named the rows:

| id | old\_amount | new\_amount |
| --- | --- | --- |
| 81234 | 92.24 |  |
| 734512 | 851.24 | 851.25 |

The `FULL JOIN` between the chunk lists matters: a chunk that exists on only one side has a null hash, and `IS DISTINCT FROM` reports it.

## Hash values, not their display

`format('%s', x)` uses each value's text output, and two outputs that differ can represent equal values. In our copy, `invoices.amount` is `numeric(12,2)` and `invoices_v2.amount` is unconstrained `numeric`:

```
SELECT 10.50::numeric(12,2) = 10.5::numeric AS equal,
       10.50::numeric(12,2)::text AS a, 10.5::numeric::text AS b;
```

| equal | a | b |
| --- | --- | --- |
| t | 10.50 | 10.5 |

A new release that writes `10.5` into `invoices_v2` would make its chunk differ from the old table although no amount changed. Normalize such columns with `trim_scale(amount)` on both sides, which drops trailing zeros without rounding: `trim_scale(10.50)` is `10.5`, and `trim_scale(10.504)` stays `10.504`. Casting to `numeric(12,2)` instead would round `10.504` to `10.50` and hide a real difference. Timestamps have the same problem: the text of a `timestamptz` follows the session's `TimeZone`, so `2026-03-01 12:00:00+00` in one session is `2026-03-01 13:00:00+01` in another. We hashed `extract(epoch FROM issued_at)` to remove the session from the comparison.

## Size the chunks and the run

The 2.5 s figure is for both tables in one pass on our machine. On a larger table, run one chunk range per statement so each statement reads a bounded number of rows, and pick a chunk size whose drill-down you can read by eye. The comparison sees each table at a single moment, so rows the running release writes during the run show up as mismatched chunks; rerun those chunks and report only the ones that stay different, as [find every backfill mismatch with IS DISTINCT FROM](/learn/postgres-backfill-reconciliation-is-distinct-from/) does for rows.

## The cutover test

Pass when every chunk hash matches in two runs taken longer apart than your longest write transaction, with the running release writing to both tables through its sync path. Then the readers can move. The other steps are in [moving data while two application releases share one database](/learn/data-migration-across-service-versions/).

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

-   ### [Find every backfill mismatch with IS DISTINCT FROM](/learn/postgres-backfill-reconciliation-is-distinct-from/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Why a watermark copy misses a row that commits late](/learn/postgres-backfill-watermark-late-commit/)
    
    Breaking migrations The Proofline team November 22, 2026 4 min read
