Splink 5/DuckDB/Chunking performance notes

[Robin Linacre (RobinL)]

In our testing for the forthcoming Splink 5 release against the current DuckDB 2.0 prereleases (duckdb==1.6.0.dev365), we've been looking at performance on large ec2 instances with many threads.

We've found that it's possible to get extremely good performance, but there are also some DuckDB settings that can cause performance to drop dramatically.

We'll eventually add a section to the docs about this, but in the meantime I'll write up some notes here.

The trigger that led us to investigate these performance issues was workloads with a large number of input nodes - in our case we created a fake dataset with 1bn rows.

The most important issue is running Splink from an on disk connection:

con = duckdb.connect("myfile.db")

In machines with large CPU counts, in some situations, this causes the runtime to be dominated with period of close to 0% CPU ultilisation and slow disk writes.

The solution is simple - create an in-memory connection:

con = duckdb.connect()

One reason for this is that large CPU counts interact very poorly with the write_buffer_row_group_count=5 the default setting, and setting write_buffer_row_group_count=1 is better. But this doesn't fully explain the performance gap: the in-memory write is still much faster. The following stats are to produce 100m prediction rows:

Configuration	predict_chunk	Final prediction CTAS
On-disk DB, write_buffer_row_group_count=5	214.58 s	125.08 s
On-disk DB, write_buffer_row_group_count=1	90.12 s	50.67 s
In-memory table path*	20.72 s	4.28 s

The reason is that on-disk DuckDB tables are more expensive than simply writing a file. With an in-memory database DuckDB knows the data is not supposed to survive a crash or process exit. With on-disk databases, DuckDB has to maintain a consistent, recoverable database, and some of that bookkeeping becomes a bottleneck on machines with very large CPU counts. write_buffer_row_group_count=1 reduces the worst of this overhead, but an in-memory connection avoids it altogether.

On a run with looser blocking rules on the same 192-thread 48x instance:

In-memory path workload	predict_chunk	Final prediction CTAS	Parquet export	Predictions
10% chunk	20.72 s	4.28 s	—	100.4m
One-third chunk	97.39 s	37.31 s	25.18 s	1.115bn

Splink generated over 1.1 billion predictions in 97 seconds. Writing all of those predictions out afterwards as 47 GB of compressed Parquet took only another 25 seconds, giving about 123 seconds total for computation plus output.

Large output tables and spill to tmp

At still larger scales, however, we found another performance cliff: the prediction table itself can become too large to remain entirely in memory, even when using an in-memory DuckDB connection.

For example, a full run on the same r8id.48xlarge produced approximately 10.04 billion prediction rows. Immediately after predict() completed, DuckDB reported approximately:

• 556 GiB of the materialised table resident in memory
• 1.37 TiB of data in DuckDB temporary storage across 67 spill files

This had a very large effect on Parquet export performance. The 1.115bn-row result above, which fitted much more comfortably in memory, exported at around 1.87 GB/s. During the badly affected 10bn-row export, final Parquet files were growing at only around 1.5 GB/min (0.025 GB/s) — roughly 75× slower by output throughput — despite running on the same machine and writing to the same local NVMe RAID0 filesystem.

I also found that export settings matter considerably once the source table is heavily spilled. The initial export used 192 threads, one-million-row Parquet row groups and PER_THREAD_OUTPUT, resulting in around 192 concurrent writers. Reducing the export to 32 DuckDB threads, disabling insertion-order preservation, and using smaller bounded row groups:

con.execute("SET threads = 32")
con.execute("SET preserve_insertion_order = false")

predictions.as_duckdbpyrelation().write_parquet(
    output_directory,
    compression="zstd",
    per_thread_output=True,
    row_group_size=262_144,
    row_group_size_bytes="64MB",
    file_size_bytes="1GB",
    overwrite=True,
)

increased completed-row export throughput by roughly 4×

Even after that improvement, the very large export remained around 8–9× slower per row than the 1.1bn-row export

[Robin Linacre (RobinL)]

Full prediction and Parquet export experiments

Workload

These experiments ran Splink PR 3255 on an r8id.48xlarge with 192 vCPUs, approximately 1.5 TiB RAM, and three 3.5 TB local NVMe devices combined as XFS on RAID0. DuckDB used an in-memory main database and /mnt/ssd for temporary spill and Parquet output.

The input was 1,000,000,000 Parquet rows. Splink predict() used six blocking rules and materialized exactly 10,037,267,664 prediction rows, as recorded by the root operator cardinality in all three DuckDB prediction profiles. Prediction itself always used 192 DuckDB threads.

The prediction result did not fit in memory. Immediately after predict(), DuckDB reported approximately 556.5 GiB of IN_MEMORY_TABLE data and 1.49-1.50 TB of temporary storage across 67 spill files. The largest individual spill files were approximately 258 GiB and 231 GiB.

Export settings tested

Run	Export threads	Row group rows	Row group byte target	File target	Other settings
Initial	192	1,000,000	Not set	512 MB	Zstandard, per-thread output
Tuned	32	262,144	64 MB	1 GB	Zstandard, per-thread output, preserve_insertion_order=false
Tuned	64	262,144	64 MB	1 GB	Zstandard, per-thread output, preserve_insertion_order=false

For the tuned runs, the export pragmas were deliberately applied only after predict() had completed. This kept prediction at 192 threads while changing only export concurrency.

Results

Export threads	Prediction time	Post-predict spill	Export observation	State at cancellation
192	494.37 s	Approximately 1.5 TB	About 193 files were active concurrently. Late throughput fell to roughly 1.5-1.7 GB/min, with only 7-8% host CPU utilization.	Canceled; partial metrics, logs, and profiles preserved.
32	478.77 s	1,503,544,541,184 bytes (1.37 TiB)	Initially about 13.1 GB/min. At roughly 13-14 minutes it had written about 171.5 GB and completed 4.073B rows.	217 files and 213,368,264,454 output bytes. Spill had grown to 1,858,665,938,944 bytes.
64	550.54 s	1,491,548,504,064 bytes (1.36 TiB)	Initially 34-40 GB/min, but later fell to 5.55 GB/min and then about 3.5 GB/min. At 10.4 minutes it had 3.340B rows in completed files.	After about 14 minutes: 204 files and 170,690,263,813 output bytes. Spill had grown to 1,709,133,725,696 bytes.

The 64-thread run began much faster, but the advantage did not persist. At comparable elapsed time near 14 minutes it had written 170.7 GB, essentially the same as the 171.5 GB observed for 32 threads. Increasing export concurrency therefore improved startup throughput but not sustained throughput.

Export also caused additional spill growth. In the 32-thread run, temporary storage increased by approximately 355.1 GB between the post-predict snapshot and cancellation. In the 64-thread run it increased by approximately 217.6 GB before cancellation.

Machine-level evidence

A live snapshot during the 64-thread export showed that the instance hardware was not saturated:

• RAID0 utilization was only 9-18%, with approximately 0.15 ms read latency.
• RAID reads were 113-168 MB/s during the sampled intervals.
• Approximately 75% of the 192 CPUs were idle.
• About 53 threads were runnable; most of the process's threads were sleeping and only two were in uninterruptible I/O wait.
• CPU, I/O, and memory PSI pressure were effectively zero.
• The process held approximately 1.21 TiB RSS, but about 265 GiB remained available and no swap was in use.

This rules out raw NVMe bandwidth, memory exhaustion, and whole-machine CPU saturation as the main causes. The evidence instead points to limited parallelism while DuckDB reads and reconstructs the heavily spilled in-memory table and feeds compression and Parquet writing. Once that pipeline exposes work for fewer than 64 workers, raising the thread count cannot improve sustained export throughput.

Outcome

All three exports were canceled before completion because throughput degraded too far. No large Parquet output or DuckDB spill files were downloaded. Logs, machine metrics, cancellation metadata, storage snapshots, and DuckDB query profiles were preserved. The final 64-thread instance was terminated after its small evidence artifacts were uploaded and synced locally.

is it plausible that it writes the first part that's still in mem fast and then once it has to write out the data that's in .tmp/ it then goes slow, does our data supoprt that?

The timing is strongly consistent with that hypothesis, but the data cannot prove DuckDB scans all resident blocks before spilled blocks. The decisive alignment is that only 28.6% of the materialized table was resident after prediction, corresponding to about 2.87B rows; around three minutes into the 64-thread export, completed plus active-file output represented roughly 3.0B rows, exactly when throughput collapsed from 34–40 GB/min to the later 5.55 GB/min range.

Yes, the data strongly supports that interpretation, with one caveat.

Resident table: 597 GB, or 28.6% of total materialized storage.
Equivalent resident rows: approximately 2.87B of 10.037B.
At three minutes, completed plus active output represented approximately 3.0B rows.
That is almost exactly when throughput dropped from 34–40 GB/min to around 5.55 GB/min.
Similar degradation appeared in all three exports.
However, DuckDB may interleave resident and spilled blocks rather than process them in two strict phases. Available memory also fell during the first four minutes rather than being steadily released.

The most defensible conclusion is:

Export is initially fast while predominantly consuming resident or cached blocks. Performance collapses once it becomes dominated by fetching and reconstructing spilled blocks.

[Robin Linacre (RobinL)]

Things to try:

Instead of:

prediction query
↓
CREATE TABLE 10B rows
↓
1.5 TB spills to .tmp
↓
scan it again
↓
Parquet

do:

prediction query
↓
Parquet COPY sink

using

COPY (
SELECT ...
)
TO '/mnt/ssd/predictions'
(
FORMAT PARQUET,
...
);

[Robin Linacre (RobinL)]

Failed 384-vCPU, 3-TiB-memory run

We repeated the full 10.037-billion-prediction workload on an r8id.metal-96xl, which has 384 vCPUs and approximately 3 TiB RAM. This run used an ordinary in-memory DuckDB connection, 384 DuckDB threads, and 384 concurrent Parquet writers. It used the same tuned Parquet row-group and file-size settings, but did not reduce the DuckDB thread count before export.

The run was canceled after export throughput deteriorated, so it did not produce a complete Parquet result. The last confirmed sample before cancellation contained 259.45 GB across 391 Parquet files. The instance terminated before its normal final artifact upload completed, so the figures below come from timestamped live process, log, filesystem, and Parquet-footer observations collected during the run.

Prediction did not improve with twice as many vCPUs

Doubling the available vCPUs did not make predict() faster:

Instance	vCPUs	Blocking	Post-blocking prediction	Combined logged work
r8id.48xlarge	192	108.99 s	494.37 s	603.36 s
r8id.metal-96xl	384	139.42 s	517.83 s	657.25 s

The like-for-like post-blocking prediction segment was 4.7% slower on the 384-vCPU machine. Including blocking, the combined logged work was 8.9% slower. The corresponding end-to-end predict() phase timings were approximately 622 seconds and 659 seconds, respectively, so the same conclusion holds using wall-clock phase time.

This was not because DuckDB remained capped at 192 threads. Live process samples during predict() showed approximately 253 to 298 CPU core-equivalents in use, proving that the larger machine did use substantially more than 192 cores. However, it did not use all 384 cores continuously, and the additional parallel CPU work did not reduce elapsed time. At this scale, the workload appears limited by parallel efficiency, memory traffic, spill/materialisation work, or another shared bottleneck rather than the number of available logical CPUs.

More memory delayed, but did not remove, the export cliff

The larger machine got materially further through the fast part of the export:

• Approximately 40.08 GB had been written 53 seconds after export began.
• Output reached 142.22 GB after approximately 2 minutes 17 seconds, averaging roughly 62 GB/min overall at that point.
• The next measured interval still reached 230.84 GB, but its rate had fallen to approximately 32 GB/min.
• Throughput then fell to approximately 8.6 GB/min and finally 2.9 GB/min.
• At the last confirmed sample, 259.45 GB had been written and allocated temporary storage was approximately 841 GB.

Completed Parquet footers in this run averaged approximately 38.8 bytes per prediction row. On that basis, 142.22 GB represents about 3.67 billion rows and the expected complete output was approximately 389 GB. The high-throughput region therefore extended further than in the smaller-machine tests before the same qualitative collapse appeared.

The memory and spill observations are consistent with the extra RAM being responsible. During prediction, process RSS rose to approximately 2.31 TiB while allocated spill reached approximately 781 GiB. That is much less spill than the approximately 1.37 TiB seen after prediction on the 1.5-TiB r8id.48xlarge, allowing more of the prediction table to remain resident or cached. It is therefore plausible that the exporter consumed more fast resident data before spill reconstruction became dominant.

This run strengthens the resident-first or resident-dominated interpretation, but still does not prove DuckDB's physical scan order. It also shows that adding RAM and writers moves the cliff rather than eliminating it: once export became spill-dominated, throughput again declined by more than an order of magnitude. Meanwhile, doubling the vCPU count provided no prediction speedup despite measurable use of the extra cores.

[Robin Linacre (RobinL)]

Known-good EC2 configuration for large Splink jobs

This is the best configuration we have tested for Splink 5 on an r8id.48xlarge (192 vCPUs, approximately 1.5 TiB RAM) when each prediction chunk fits in memory.

Recommended strategy

1. Use Splink PR 3255 and DuckDB 1.6.0.dev365.
2. Use an ordinary, uncompressed in-memory DuckDB connection.
3. Read the input Parquet files directly from local NVMe.
4. Use the largest predict_chunk() size that produces no DuckDB spill.
5. Export each completed chunk to Parquet, then release it before computing the next chunk. Do not accumulate all chunks in one DuckDB table.

The largest chunk we have tested successfully on r8id.48xlarge is one of three physical chunks on each side (chunk_count=3). It produced 1,115,182,886 prediction rows, completing predict_chunk() in 97.392 seconds and writing 47.19 GB of Zstandard-compressed Parquet in 25.175 seconds. All six DuckDB query profiles recorded peak_temp_dir_size: 0, confirming that this run did not spill.

Treat one third as a measured starting point, not a universal limit: blocking rules and output width change memory use. Increase the chunk size only if duckdb_temporary_files() remains empty throughout prediction and export. If it spills, use more chunks.

Software

requires-python = ">=3.12"
dependencies = [
  "duckdb==1.6.0.dev365",
  "splink @ git+https://github.com/moj-analytical-services/splink.git@7269e36cb510dcdb350846d55ab3b3b4da7250ba",
]

The pinned Splink commit is from PR 3255. The original one-third benchmark used an earlier commit from the same PR (852ebad546025498ea60619a77a7baaa3775c2e1).

EC2 and storage

• Instance: r8id.48xlarge (192 logical CPUs, approximately 1.5 TiB RAM).
• AMI: Amazon Linux 2023, x86-64; the benchmark resolves /aws/service/ami-amazon-linux-latest/al2023-ami-kernel-6.18-x86_64.
• Root disk: 100 GiB gp3, 6,000 IOPS, 1,000 MB/s; use it for the OS and logs, not benchmark data.
• Data disks: combine all three 3.5 TB local instance-store NVMe devices as RAID0 at /dev/md0.
• RAID chunk: 1024K, metadata format 1.2.
• Filesystem: XFS with stripe geometry matching the RAID: su=1024k,sw=3.
• Mount: /mnt/ssd with noatime,nodiscard (nofail in /etc/fstab).
• Put the input dataset, DuckDB temporary directory, and Parquet output on /mnt/ssd.

Equivalent setup:

mdadm --create /dev/md0 \
  --level=0 --raid-devices=3 --chunk=1024K --metadata=1.2 --force \
  /dev/nvme1n1 /dev/nvme2n1 /dev/nvme3n1
mkfs.xfs -f -K -d su=1024k,sw=3 /dev/md0
mount -o noatime,nodiscard /dev/md0 /mnt/ssd

Discover the instance-store device names rather than assuming the three paths above.

S3 transfers

Keep the bucket in the same AWS region as the instance. Configure AWS CLI v2 to use the CRT transfer client and advertise the instance's available bandwidth:

[default]
region = eu-central-1
s3 =
  preferred_transfer_client = crt
  target_bandwidth = 50Gb/s

Then stage the dataset onto local NVMe before running Splink:

aws s3 sync --no-progress \
  s3://BUCKET/datasets/fake_people_for_splink_1bn/ \
  /mnt/ssd/fake-people-project/dataset/fake_people_for_splink_1bn/

Do not query the billion-row source remotely from S3 for this benchmark.

DuckDB and Splink

from pathlib import Path
import duckdb

temp_dir = Path("/mnt/ssd/fake-people-project/duckdb_tmp")
temp_dir.mkdir(parents=True, exist_ok=True)

con = duckdb.connect(":memory:")
con.execute("SET threads = 192")
con.execute("SET preserve_insertion_order = false")
con.execute(f"SET temp_directory = '{temp_dir}'")

Important details:

• Do not attach a compressed in-memory catalog. Compression made full prediction approximately 3.5-4 times slower and did not prevent the export cliff.
• Do not use a persistent DuckDB database for intermediate prediction tables. Recoverability and WAL/storage bookkeeping performed very poorly at high thread counts.
• Keep the Splink input as a direct Parquet view rather than first materialising the one-billion-row input into a DuckDB table.
• Use all 192 threads while prediction chunks fit in memory.
• write_buffer_row_group_count=1 helped persistent-table writes and was present in the successful one-third experiment, but it is not the reason the ordinary in-memory path is fast. It can remain 1; do not expect it to rescue a spilled workload.
• DuckDB chose an observed memory_limit of approximately 1.1 TiB on this host. Leave headroom for the process and OS rather than forcing the limit to all 1.5 TiB.

Parquet output

The exact measured fast export used 192 writers, Zstandard compression, one-million-row groups, and a 512 MB file target:

predictions.as_duckdbpyrelation().write_parquet(
    output_directory,
    compression="zstd",
    per_thread_output=True,
    row_group_size=1_000_000,
    file_size_bytes="512MB",
    overwrite=True,
)

This wrote 1.115 billion rows in 25.175 seconds because the source chunk was not spill-bound. Reducing export concurrency to 32-64 threads only helped once a source table had already spilled; it is not the preferred configuration for a chunk that fits in memory.

Operational guardrails

• Record duckdb_temporary_files() and duckdb_memory() after every prediction chunk.
• Verify the exported row count before deleting the in-memory result.
• Keep logs, profiles, metrics, and metadata on the root volume; keep large transient data on instance-store NVMe.
• Upload small evidence and manifests to S3 before instance shutdown. Do not upload spill files unless explicitly required.
• If a chunk spills, stop tuning Parquet writers and reduce the chunk size first. Avoiding spill is the dominant optimization.

In short: PR 3255, ordinary in-memory DuckDB, direct local Parquet input, 192 threads, and the largest chunk that creates zero temporary files. On the tested one-billion-row workload, chunk_count=3 is the largest demonstrated fast configuration.