Learning center

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.

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;
chunkold_rowsnew_rows
8100009999
731000010000

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:

idold_amountnew_amount
8123492.24
734512851.24851.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;
equalab
t10.5010.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 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.

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