Accelerating Tabular Reconciliation

A benchmark of execution strategies for row-level comparison of large datasets

0.56 sfastest: Polars (Vectorized)
12×faster than the slowest (Pandas, 6.53 s)
311 MBlowest peak memory: Row-group fingerprint
4.9 sleast CPU time: Polars (Vectorized)

Summary

An analyst has been asked to reconcile two extracts of 7M rows and 12 columns, and the book closes soon. Ten ways of comparing them were measured: seven from the original study and three added here. On this machine the slowest took 12× as long as the fastest, with every method given the same data, the same machine and the same answer to reach.

Of the three new methods: PyArrow (Parallel row groups) took 1.92 s (3.4× the time of the best original method, Polars (Vectorized)); Row-group fingerprint took 1.32 s (2.4× the time of the best original method, Polars (Vectorized)); Numba kernel took 1.24 s (2.2× the time of the best original method, Polars (Vectorized)). The numbers alone do not tell the whole story, because each method trades something away; see the scorecard and the decision guide.

Every method returned the correct answer on every run: each result was checked against the set of changed rows recorded when the data was generated.

Key findings

1. Scenario and research question

A data analyst owns a month-end reconciliation. Two systems each produce a file of the same shape, and the question is how many rows agree. The files have grown from thousands of rows to millions, the spreadsheet no longer opens, and the deadline has not moved. The analyst can reach for a different library, change how the data is read, use more of the machine, or avoid doing part of the work.

Research question. For a row-by-row comparison of two large tables, which execution strategy gives the shortest time to an answer, and what does each one cost in memory, CPU and complexity?

2. How the study was run

Machine
Operating systemWindows-10-10.0.26200-SP0
CPU6 physical cores, 12 threads
Memory13.9 GB RAM
Python3.11.9
Librariespandas 2.3.3, polars 1.34.0, duckdb 1.4.1, pyarrow 21.0.0, numba 0.68.0, numpy 1.26.2

3. The methods

The first seven come from the original study; the last three are new. “Exactness” says whether the answer can ever be wrong in principle; “nulls” records how a missing value in both files is treated, which differs between methods even though this workload contains no nulls.

PandasOriginal study

Loads both files into DataFrames and tests every cell with (df1 == df2).all(axis=1). The approach most analysts would write first.

Trade-off. Shortest and most familiar code. String columns are held as Python objects, which costs memory, and comparing them cell by cell is single-threaded.

Exactness
Exact
Nulls
NaN != NaN (null rows count as mismatches)
Code size
5 lines
Result at 7M rows
6.53 s · 1.9 GB · 14.3 s CPU

PolarsOriginal study

Reads both files eagerly into Polars frames, compares them cell by cell, then counts the all-true rows by iterating Python tuples.

Trade-off. Fast reader and comparison, but turning every row into a Python tuple hands back much of the gain.

Exactness
Exact
Nulls
null propagates (null rows are not counted as matches)
Code size
5 lines
Result at 7M rows
4.18 s · 3.4 GB · 8.6 s CPU

DuckDBOriginal study

Expresses the comparison as SQL: number the rows of each file, join on the row number and average a null-safe equality across every column.

Trade-off. Declarative, multi-threaded and null-safe. The row-number join costs memory, and matching by position relies on the engine returning rows in file order.

Exactness
Exact
Nulls
null == null counts as a match (IS NOT DISTINCT FROM)
Code size
27 lines
Result at 7M rows
2.57 s · 1.4 GB · 5.5 s CPU

Polars (Streaming)Original study

Lazily scans both files, reduces each row to a 64-bit hash and compares the two hash streams with a join on row position, using Polars' streaming engine.

Trade-off. Built for bounded memory and larger-than-RAM inputs. Equality of hashes is probabilistic (a collision could hide a break) and the join adds overhead.

Exactness
Probabilistic (64-bit row hash)
Nulls
nulls hash to a fixed value, so null == null matches
Code size
18 lines
Result at 7M rows
1.97 s · 2.1 GB · 17.8 s CPU

PyArrowOriginal study

Walks the two files in lock-step record batches and combines per-column Arrow equality kernels. The batch size is the whole table, so each file is read in a single batch.

Trade-off. Few moving parts and no dataframe layer, but a single huge batch means peak memory scales with the file.

Exactness
Exact
Nulls
null == null is not a match
Code size
36 lines
Result at 7M rows
1.23 s · 1.4 GB · 8.9 s CPU

PyArrow (Tuned batch size)Original study

The same comparison with the batch size chosen by a sweep, trading per-batch overhead against cache locality and memory.

Trade-off. Smaller batches keep memory flat and data cache-resident. The best size depends on the machine and the schema, so it has to be re-measured.

Exactness
Exact
Nulls
null == null is not a match
Code size
36 lines
Result at 7M rows
1.20 s · 516 MB · 7.8 s CPU

Polars (Vectorized)Original study

Reads eagerly and compares the frames purely with Polars expressions (==, all_horizontal, mean), so no Python-level loop runs at all.

Trade-off. Very short code with multi-threaded, SIMD-friendly execution. It materialises both tables plus a boolean frame of the same shape.

Exactness
Exact
Nulls
null propagates (null rows are not counted as matches)
Code size
10 lines
Result at 7M rows
0.56 s · 2.4 GB · 4.9 s CPU

PyArrow (Parallel row groups)New in this study

Splits the work by Parquet row group and compares groups concurrently in a pool of worker processes, each using single-threaded PyArrow kernels.

Trade-off. Uses every core and bounds memory by row-group size. Each worker pays process start-up and its own library imports, and speed-up flattens once workers exceed physical cores.

Exactness
Exact
Nulls
null == null is not a match
Code size
28 lines
Result at 7M rows
1.92 s · 943 MB · 16.2 s CPU

Row-group fingerprintNew in this study

Compares the raw compressed bytes of each Parquet row group across the two files and only decodes the groups whose bytes differ; identical groups are counted without being read as data.

Trade-off. Potentially orders of magnitude faster when few row groups differ, but it needs both files written with the same layout and settings, and when differences touch every group it only adds a byte-comparison pass.

Exactness
Exact (identical bytes imply identical data)
Nulls
identical groups match including nulls; differing groups follow PyArrow rules
Code size
38 lines
Result at 7M rows
1.32 s · 311 MB · 8.0 s CPU

Numba kernelNew in this study

Reads both files into Arrow buffers and runs a hand-written, JIT-compiled loop over the integer arrays and raw string offsets, parallelised across rows.

Trade-off. Closest to the hardware, with no intermediate arrays. In exchange the author owns correctness: only int64 and string columns, no null handling, a one-off JIT compile and the most code to maintain.

Exactness
Exact (for supported types)
Nulls
not supported (raises)
Code size
67 lines
Result at 7M rows
1.24 s · 2.0 GB · 10.9 s CPU

4. Results at 7M rows

4.1 Speed

Median time to reconcile 7M rows over up to 7 runs per method.
Median time to reconcile 7M rows over up to 7 runs per method.
View data as a table
MethodMedianFastestSlowestRuns
Polars (Vectorized)0.56 s0.54 s0.57 s7
PyArrow (Tuned batch size)1.20 s1.15 s1.22 s7
PyArrow1.23 s1.18 s1.29 s7
Numba kernel1.24 s1.22 s1.26 s7
Row-group fingerprint1.32 s1.27 s1.34 s7
PyArrow (Parallel row groups)1.92 s1.83 s1.95 s7
Polars (Streaming)1.97 s1.95 s2.01 s7
DuckDB2.57 s2.48 s2.66 s7
Polars4.18 s4.05 s4.38 s7
Pandas6.53 s6.45 s7.39 s7

Polars (Vectorized) was fastest, followed by PyArrow (Tuned batch size) and PyArrow. The slowest method, Pandas, took 12× longer.

4.2 Speed against memory

Each point is one method. The dashed line joins the methods that no other method beats on both axes.
Each point is one method. The dashed line joins the methods that no other method beats on both axes.
View data as a table
MethodPeak memoryMedian time
Polars (Vectorized)2.4 GB0.56 s
PyArrow (Tuned batch size)516 MB1.20 s
PyArrow1.4 GB1.23 s
Numba kernel2.0 GB1.24 s
Row-group fingerprint311 MB1.32 s
PyArrow (Parallel row groups)943 MB1.92 s
Polars (Streaming)2.1 GB1.97 s
DuckDB1.4 GB2.57 s
Polars3.4 GB4.18 s
Pandas1.9 GB6.53 s

Memory ranges from 311 MB (Row-group fingerprint) to 3.4 GB (Polars). Methods near the origin are both fast and light; a method that is fast only because it loads everything into memory will stop being an option when the data outgrows the machine (see section 5).

4.3 CPU cost

Wall-clock time (open circle) against total CPU time (filled circle) per method.
Wall-clock time (open circle) against total CPU time (filled circle) per method.
View data as a table
MethodWallCPU timeCores busy
Polars (Vectorized)0.56 s4.88 s8.7
PyArrow (Tuned batch size)1.20 s7.80 s6.5
PyArrow1.23 s8.86 s7.2
Numba kernel1.24 s10.94 s8.8
Row-group fingerprint1.32 s8.00 s6.0
PyArrow (Parallel row groups)1.92 s16.19 s8.4
Polars (Streaming)1.97 s17.77 s9.0
DuckDB2.57 s5.47 s2.1
Polars4.18 s8.64 s2.1
Pandas6.53 s14.27 s2.2

CPU time matters when the machine is shared or billed by the core-second. Wall-clock time hides how much work was done: among the five fastest methods, total CPU time differs by 2.2×. The parallel row-group method kept 8.4 cores busy on average and used 16.2 CPU-seconds, against 4.9 for Polars (Vectorized).

4.4 Scorecard

Each cell shows the value and how many times worse it is than the best method in that column.
Each cell shows the value and how many times worse it is than the best method in that column.
View data as a table
MethodTimePeak memoryCPU timeCode size
Polars (Vectorized)0.56 s2.4 GB4.9 s10 lines
PyArrow (Tuned batch size)1.20 s516 MB7.8 s36 lines
PyArrow1.23 s1.4 GB8.9 s36 lines
Numba kernel1.24 s2.0 GB10.9 s67 lines
Row-group fingerprint1.32 s311 MB8.0 s38 lines
PyArrow (Parallel row groups)1.92 s943 MB16.2 s28 lines
Polars (Streaming)1.97 s2.1 GB17.8 s18 lines
DuckDB2.57 s1.4 GB5.5 s27 lines
Polars4.18 s3.4 GB8.6 s5 lines
Pandas6.53 s1.9 GB14.3 s5 lines

5. Scaling with dataset size

The same comparison was repeated at 1M, 7M, 14M, 28M, 56M rows per file, with the changed-row proportion held constant.

Median time against rows per file, both axes logarithmic.
Median time against rows per file, both axes logarithmic.
View data as a table
Method1M7M14M28M56M
Pandas0.96 s6.53 s13.70 sstoppednot run
Polars0.63 s4.27 sstoppednot runnot run
DuckDB0.39 s2.55 s5.18 s11.01 sstopped
Polars (Streaming)0.26 s2.02 s4.15 s8.21 s16.35 s
PyArrow0.18 s1.21 s2.42 s5.33 sstopped
PyArrow (Tuned batch size)0.17 s1.16 s2.26 s4.71 s9.56 s
Polars (Vectorized)85 ms0.56 s1.10 sstoppednot run
PyArrow (Parallel row groups)0.69 s1.89 s2.88 s4.52 s7.81 s
Row-group fingerprint0.18 s1.30 s2.59 s4.99 s10.08 s
Numba kernel0.21 s1.27 s2.50 sstoppednot run
Peak memory against rows per file.
Peak memory against rows per file.
View data as a table
Method1M7M14M28M56M
Pandas390 MB1.9 GB3.8 GBstoppednot run
Polars539 MB3.4 GBstoppednot runnot run
DuckDB240 MB1.4 GB2.8 GB5.6 GBstopped
Polars (Streaming)456 MB2.0 GB2.4 GB3.3 GB4.9 GB
PyArrow247 MB1.4 GB3.1 GB5.6 GBstopped
PyArrow (Tuned batch size)254 MB497 MB539 MB560 MB549 MB
Polars (Vectorized)392 MB2.4 GB4.8 GBstoppednot run
PyArrow (Parallel row groups)327 MB928 MB937 MB948 MB981 MB
Row-group fingerprint179 MB329 MB364 MB389 MB399 MB
Numba kernel394 MB2.0 GB3.9 GBstoppednot run

On this machine the following methods were stopped by the memory guard before the largest size: Pandas (largest completed: 14M), Polars (largest completed: 7M), DuckDB (largest completed: 28M), PyArrow (largest completed: 28M), Polars (Vectorized) (largest completed: 14M), Numba kernel (largest completed: 14M). The limit is the memory actually usable on this machine at the time (13.9 GB installed, part of it taken by the operating system and background processes), not a fixed property of each method; on a larger machine the cut-off moves, but the order in which methods fail would not. A method that cannot finish is the slowest possible method, whatever it scored on a smaller file.

At 56M rows the fastest completed method was PyArrow (Parallel row groups) (7.81 s) and the lightest was Row-group fingerprint (399 MB).

6. Sensitivity studies

Three of the methods depend on a setting or on the shape of the data. These studies show how much.

6.1 PyArrow batch size

Runtime of the PyArrow method against the number of rows read per batch.
Runtime of the PyArrow method against the number of rows read per batch.
View data as a table
Batch sizeMedian time
10,0001.76 s
20,0001.50 s
40,0001.37 s
80,0001.26 s
131,0721.23 s
250,0001.24 s
500,0001.21 s
1,000,0001.29 s
2,000,0001.24 s
3,500,0001.31 s
7,000,0001.27 s

The fastest batch size was 500,000 rows (1.21 s); reading each file as one batch took 1.27 s and the smallest batch size took 1.76 s. Above roughly 500,000 rows the curve is flat, so tuning changes the runtime by only 4%. The larger effect is on memory: the single-batch method peaked at 1.4 GB, the tuned one at 516 MB (2.7× less), because only one batch is held at a time. Very small batches pay per-batch overhead. The best size is specific to this machine and data, so it should be measured rather than assumed. The sweep uses repeats and medians; the original version took the minimum of single runs, which favours noise.

6.2 Parallel row groups: how many workers?

Runtime of the parallel method against the number of worker processes.
Runtime of the parallel method against the number of worker processes.
View data as a table
WorkersMedian timeSpeed-up vs 1 worker
13.79 s1.0×
22.56 s1.5×
41.78 s2.1×
61.80 s2.1×
81.71 s2.2×
121.96 s1.9×

Best was 8 workers at 1.71 s, 2.2× faster than one worker, which is well short of the ideal (28% parallel efficiency). Every worker is a separate process that must start, import its libraries and read its share of the file, and the machine has 6 physical cores (12 threads). The number of row groups (28) also caps how finely the work can be divided.

6.3 Fingerprinting: how widely are the differences spread?

Runtime against the number of row groups (of 28) that contain a difference. The total number of changed rows is the same in every case.
Runtime against the number of row groups (of 28) that contain a difference. The total number of changed rows is the same in every case.
View data as a table
Row groups differingRow-group fingerprintPyArrow (Tuned batch size)Polars (Vectorized)
068 ms1.17 s0.56 s
10.11 s1.19 s0.55 s
20.16 s1.19 s0.58 s
40.25 s1.16 s0.56 s
70.37 s1.20 s0.55 s
140.69 s1.26 s0.58 s
281.28 s1.18 s0.57 s

With identical files the fingerprint method finished in 68 ms, against 1.17 s for PyArrow (Tuned batch size). It was clearly faster than PyArrow (Tuned batch size) (by more than 5%) when up to 14 of 28 row groups differed, and had no meaningful advantage from 28 differing groups onwards. The speed-up comes from not decoding unchanged data, so it depends entirely on how the differences are distributed: in the main benchmark the 200 changes are scattered across 28 row groups, so nearly every group is touched and there is little to skip.

6.4 The Numba kernel's first-call cost

A compiled kernel has a one-off cost that the benchmark excludes from the timed region: with an empty cache the first call took 3.3 s to compile; with the cache populated it took 0.7 s to load. That is irrelevant for a job that runs daily and decisive for a one-off, so it belongs in the decision.

7. Decision guide

If you need…ChooseEvidence
The shortest time on one machine with enough memoryPolars (Vectorized)0.56 s at 7M rows; 12× faster than Pandas
The smallest memory footprintRow-group fingerprint311 MB peak at 7M rows
The least total CPU work (shared or billed machine)Polars (Vectorized)4.9 CPU-seconds
The least code to write and maintainPandas5 lines
The largest file that still finishes (56M rows tested)PyArrow (Parallel row groups)7.81 s, 981 MB; 4 of 10 methods completed this size
Re-checking files that are mostly unchanged and were written the same wayRow-group fingerprint68 ms for identical files; 0.11 s when one row group differs
A fixed schema, run repeatedly, where every second countsNumba kernel1.24 s per run once compiled; supports only int64 and string columns, and no nulls

8. Limitations

9. Reproducing this study

pip install -e .[dev]
python -m pytest                     # every method agrees with ground truth
python -m tabrecon all               # generate data, run every study, build this report
# or step by step:
python -m tabrecon batch-sweep       # tunes the PyArrow batch size first
python -m tabrecon benchmark --rows 7M --repeats 7
python -m tabrecon scaling --sizes 1M 7M 14M 28M 56M
python -m tabrecon report