How to diff two large SQL dumps

You dumped a database before and after a migration and want to know what changed. Plain diff reads both files into memory. Rift does not.

1. Count the differences

Start with --summary. It writes no diff and tells you how much differs:

a.sql, b.sql: a 64 MiB pair

$ rift --summary a.sql b.sql
a: 843633 lines, 67108879 bytes
b: 845034 lines, 67182379 bytes
stratum 0, 830263 anchors
equal    436 segments, 835632 lines in a, 835632 in b
differs  435 segments, 8001 lines in a, 9402 in b
first differing ranges (0-based, end exclusive):
  a 381..383  b 381..382
  a 1005..1011  b 1004..1010
  a 1280..1281  b 1279..1281
  a 8109..8119  b 8109..8110
  a 9618..9619  b 9609..9618
  a 11782..11783  b 11781..11790
  a 17463..17784  b 17470..17471
  a 21556..21560  b 21243..21244
  a 22749..22750  b 22433..22437
  a 25037..25048  b 24724..24725
  ... and 425 more
moved    62 blocks, 2737 lines
  a 21557..21560  b 22434..22437
  a 25038..25048  b 26389..26399
  a 27627..27952  b 28206..28531
  a 36864..36874  b 36680..36690
  a 48226..48235  b 49457..49466
  a 50776..50780  b 50896..50900
  a 59441..59445  b 61396..61400
  a 65493..65499  b 65178..65184
  a 99590..99595  b 99830..99835
  a 110203..110209  b 111213..111219
  ... and 52 more
peak heap 78.6 MiB of 256.0 MiB (82397510 bytes)
time 0.13 s

Here 435 segments differ out of 871, and 8,001 lines of the first file are involved. The peak heap was 78.6 MiB against the default limit of 256 MiB.

2. Write the diff

rift a.sql b.sql > changes.patch

the first and the third hunk

$ rift a.sql b.sql
--- a.sql+++ b.sql@@ -380,7 +380,6 @@ INSERT INTO public.orders (id, customer_id, status, amount, note) VA… 381	178722	cancelled	2930.54	wmojdqnzik INSERT INTO public.orders (id, customer_id, status, amount, note) VA…-383	668107	shipped	5325.74	zu INSERT INTO public.orders (id, customer_id, status, amount, note) VA… 385	359182	cancelled	475.15	ztbyhdlmxk INSERT INTO public.orders (id, customer_id, status, amount, note) VA…@@ -1279,6 +1278,7 @@ 1277	990810	cancelled	3466.36	qppoqa INSERT INTO public.orders (id, customer_id, status, amount, note) VA… 1279	877409	new	9427.22	ivpjtbhdno+383	668107	shipped	5325.74	zu INSERT INTO public.orders (id, customer_id, status, amount, note) VA… 1281	232076	cancelled	3547.41	lkkqfp INSERT INTO public.orders (id, customer_id, status, amount, note) VA…

Timestamps dropped, lines cut at 70 columns.

The patch is a unified diff. patch a.sql changes.patch turns the first dump into the second, byte for byte.

3. If the dump is a pg_dump with COPY blocks

Row order in a pg_dump can change between runs without any row changing. If it does, compare by key instead of by line: key mode reads the COPY blocks with --key copy:id.

Memory

The default limit is 256 MiB. On a 1 GiB pair the heap peaked at 83.6 MiB, and the resident set at 113 MiB, in the benchmarks. Raise the limit with --mem if you want a finer diff: see the memory guide.