DuckDB integration

DuckDB provides catalog integrity checks, SQL inspection, simulation matching, and the file-output interval selection backend. Scientific strain processing does not depend on it. The Python dependency requires duckdb>=1.5.6; compatibility is checked against 1.5.6 and 2.0.0. This requirement is a compatibility floor, not a reproducible environment lock: release images should record the exact installed version.

Module responsibilities

The production integration has the following owners. Paths are relative to the repository root.

Module

Responsibility and input

Output and resource ownership

modules/catalog/_duckdb.py

Private merge bookkeeping: completed job/trial/lag membership, fragment counts, duplicate trigger IDs, simulation truth coverage, and exact job descriptor comparisons.

A disposable scratch database shared by all merge checks. It stores narrow keys and descriptor offsets; descriptor bytes live in a separate temporary file. The caller publishes the Arrow payloads.

modules/catalog/catalog.py

Catalog.query(sql) exposes the deduplicated Arrow trigger table as triggers for interactive SQL, including nested injection fields.

A materialized Arrow result. This convenience API first loads the trigger table and is not the bounded merge/selection path.

modules/catalog/catalog_json.py

JSONCatalog.query(sql) provides the corresponding SQL interface for legacy JSON catalogs.

A materialized Arrow result. The JSON catalog is already loaded in memory; SQL does not make JSON reading out of core.

modules/catalog/matching.py

match_simulations_parquet joins on trial, optional job, and overlapping trigger/simulation time windows. Two deterministic ranking passes choose at most one match per trigger and simulation.

A materialized Arrow table and optional Parquet output. Arrow assembles unmatched rows for left/right/outer results. This is independent of interval-based background splitting.

modules/postprocess/selection_duckdb.py

Eligibility checks, completed-progress filtering, compact interval metadata, and seeded file-output splits for trigger_selection.

Per-partition jobs, progress, interval summaries, trigger catalogs and livetime metadata. Owns a private connection and scratch directory.

modules/postprocess/selection_duckdb_membership.py

Duplicate-safe membership join on job/lag and trial when available; streams ordered source row ordinals and partition IDs.

Arrow rereads original trigger batches and writes selected payloads, retaining source order, nested values, field metadata and provenance. Borrows the selection connection and leaves it open.

These files live below pycwb/. They have distinct responsibilities; a SQL query result or simulation match should not be substituted for merge validation or interval exposure accounting.

Orchestration and public interfaces

pycwb.workflow.merge owns merge snapshot selection, completion rules, consistency warnings, two-pass Arrow validation/publication and the final merge manifest. It calls _CompletionIndex, _FragmentValidator and descriptor helpers in catalog._duckdb. The database is an internal scratch index, not the persistent catalog format. A valid resumed snapshot reuses its published outputs before opening the database. See Catalog format and provenance and Batch Injection Runs.

postprocess.selection.trigger_selection tries the DuckDB backend only for split.by: interval or interval_livetime with supported file outputs. Plain progress schemas, signed integer membership keys and unambiguous columns are required. Unsupported custom job filters, complex schemas, conflicting destinations or in-memory progress requests retain the existing selection path. Eligibility checks finish before output writes; failures after writing starts propagate rather than replaying the action through another backend.

postprocess.matching.match_simulations exposes the Parquet simulation matcher as a resumable YAML action. The YAML runner in pycwb.post_production.workflow also dispatches merge and selection actions. The native CLI entry points are pycwb merge, pycwb match-simulations and pycwb post-process WORKFLOW.yaml.

Use the installed PycWB environment and the native commands when measuring a real run. For example, after activating that environment:

source /path/to/pycwb-env/bin/activate
cd /path/to/pycwb-checkout
pycwb merge --work-dir /path/to/run --mlabel duckdb-benchmark \
    --duckdb-memory-limit 128MB --no-resume
pycwb post-process /path/to/interval-selection.yaml --no-diagram --no-resume

Replace the environment, checkout, run and workflow paths with the actual paths; use a fresh merge label and fresh selection output paths for every repetition. Existing merge labels are immutable, and resume must be disabled when timing the work itself. Keep the split fractions, seed, projections, batch size and output settings in the workflow YAML. See Post-Production Workflow Guide for the action configuration.

Memory, threads and scientific invariants

Path

Database settings

Additional memory and I/O

Merge validation

One DuckDB thread; default 128MB budget, configurable through --duckdb-memory-limit or duckdb_memory_limit.

Database, spill files and descriptor bytes use disposable system temporary storage (TMPDIR when set). Completion-key batches are bounded by 65,536 rows; ID/fragment buffers also target 8 MiB, with a single oversized scalar handled alone. Python job metadata and Arrow decoding/publication add memory.

Interval selection

One DuckDB thread; 2GB budget; private spill directory on the trigger output filesystem.

Compact numeric metadata still scales with progress rows. Trigger membership and payload output stream in batch_size batches, default 65,536 rows. Scratch storage is removed on success and failure.

Interactive queries and simulation matching

No merge/selection memory or thread budget is applied by these APIs.

Results are materialized. Input/output size and query operators determine memory demand; do not infer bounded behavior from the other paths.

DuckDB’s memory setting is not a cap on process RSS or a scheduler memory request. merge --threads N controls Arrow decoding, while the shared DuckDB merge connection stays single-threaded. It does not parallelize the fragment traversal or publication.

Interval exposure sums retain source row order and the existing pandas grouped sum kernel. The seeded partition algorithm also remains in selection.py. DuckDB determines membership; it does not recalculate scientific livetimes or round-trip trigger payloads through SQL. Tests therefore compare partition membership, exact livetime summaries, original trigger order, payload types and metadata, empty outputs, and external job-manifest provenance.

DuckDB 1.5.6 and 2.0 compatibility

DuckDB 2 rejects operations such as Arrow registration when a connection has an unfinished result. Reading one row with fetchone() can leave even a one-row query active. Small count/validation queries must consume their result fully before registering or unregistering Arrow objects or reusing the connection. Large query results must remain streamed and release readers and cursors on normal completion, early exit and errors.

Streaming uses to_arrow_reader where available. The selection membership adapter retains a fallback for the older fetch_record_batch spelling; DuckDB 2 removes that spelling, so code and tests must avoid requiring it. Membership streams a con.sql(...) relation directly, avoiding the extra DB-API result conversion that DuckDB 2 warns about for execute plus Arrow. Materializing convenience queries should return an Arrow table, not an active reader whose lifetime depends on a reused connection.

Catalog.query, JSONCatalog.query and simulation matching own short-lived connections and materialize connection.sql(...).to_arrow_table() before closing them. Catalog query methods explicitly register an Arrow table as triggers, including conversion of legacy JSON records, instead of relying on local-variable replacement scans or the global default connection.

Catalog tests cover normal merges, exact legacy ID semantics, job descriptors larger than the database budget, validation errors, temporary resource cleanup and resumed snapshots. Selection tests cover the supported backend and parity with the existing pandas/Arrow path. Simulation matching and catalog SQL tests cover the remaining public DuckDB consumers. Passing a newer dependency in one environment does not demonstrate the minimum version still works; install and test each version explicitly.

Local validation on 21 October 2026 ran the full non-slow catalog and postprocess suites separately under both versions: each completed with 1,368 passed and 6 skipped tests. Each broader run reported 2,561 warnings from the existing postprocess stack; the focused DuckDB compatibility suites reported none. Ruff quality checks and mypy also passed. These are local validation results for the two explicitly installed versions.

Reproduce the version benchmark

benchmark/parquet_perf/benchmark_duckdb_versions.py generates deterministic synthetic fixtures, measures the native CLI commands, and compares decoded outputs. The native CLI does not provide fixture generation, repeated timing, resource sampling or cross-environment output comparison; this development driver supplies those measurements while leaving analysis settings in public CLI options and generated workflow YAML.

The measured fixture has 1,048,576 triggers, 4,096 jobs, 16 lags and 16 fragments: 65,536 completed tasks, each containing 16 events. Each event has a 16-element float64 payload. Merge is measured with both 128MB and 32MB database budgets. Interval selection writes train/FAR partitions with fractions 0.25/0.75, seed 20261021 and 16,384-row batches. Zero lag is excluded for the unshifted jobs, using the normal selection behavior.

For the 21 October 2026 run, two Python environments with the same PycWB checkout and runtime dependencies were installed at the paths below. The driver checks their exact DuckDB versions and the presence of the native pycwb executable. Recreate the environments from the verified scientific base environment as follows:

cd /Users/yumengxu/Project/Physics/cwb/pycwb-patch
/Users/yumengxu/miniforge3/envs/pycwb-dev-py13/bin/python \
    -m venv --system-site-packages /private/tmp/pycwb-duckdb-compat/duckdb-1.5.6
/Users/yumengxu/miniforge3/envs/pycwb-dev-py13/bin/python \
    -m venv --system-site-packages /private/tmp/pycwb-duckdb-compat/duckdb-2.0.0
PYCWB_DISABLE_WAT=1 /private/tmp/pycwb-duckdb-compat/duckdb-1.5.6/bin/python \
    -m pip install --no-deps --no-build-isolation -e . 'duckdb==1.5.6'
PYCWB_DISABLE_WAT=1 /private/tmp/pycwb-duckdb-compat/duckdb-2.0.0/bin/python \
    -m pip install --no-deps --no-build-isolation -e . 'duckdb==2.0.0'

Both environments inherit the same scientific packages from the base environment, with their own exact DuckDB installation and editable PycWB entry point. These commands do not modify the base environment. --no-deps avoids resolving the inherited scientific packages again. The result JSON records the Python version and the DuckDB, Arrow, pandas, NumPy, PycWB and psutil versions. On another machine, substitute an existing PycWB development environment with the required runtime/build packages, the checkout path and fresh virtual-environment paths.

Run the benchmark with explicit cache and thread settings. These commands select the environment through absolute interpreter paths:

cd /Users/yumengxu/Project/Physics/cwb/pycwb-patch
export NUMBA_CACHE_DIR=/private/tmp/pycwb-duckdb-compat/numba-cache
export MPLCONFIGDIR=/private/tmp/pycwb-duckdb-compat/matplotlib
export NUMBA_NUM_THREADS=2
export OMP_NUM_THREADS=1
export OPENBLAS_NUM_THREADS=1
export MKL_NUM_THREADS=1
export ARROW_NUM_THREADS=1
/private/tmp/pycwb-duckdb-compat/duckdb-1.5.6/bin/python \
    benchmark/parquet_perf/benchmark_duckdb_versions.py \
    --python-156 /private/tmp/pycwb-duckdb-compat/duckdb-1.5.6/bin/python \
    --python-200 /private/tmp/pycwb-duckdb-compat/duckdb-2.0.0/bin/python \
    --directory /private/tmp/pycwb-duckdb-benchmark-20261021 \
    --output /private/tmp/pycwb-duckdb-benchmark-20261021/results.json \
    --rows 1048576 --jobs 4096 --lags 16 --fragments 16 --repeats 5 \
    --merge-memory 128MB 32MB

Substitute the checkout and interpreter paths for another machine, and choose a new --directory for every run, updating --output to match. Fixture generation rejects an existing directory. The local result JSON records hardware, Python and dependency versions, individual measurements, exact native commands and output comparisons. Generated results stay outside the repository. The temporary run directory retains input fixtures, workflow YAML and command logs; verified result catalogs are removed between repetitions to limit disk use.

Output checks hash ordered decoded Arrow values, schemas including field metadata, root metadata and resolved job-manifest provenance. Only the fresh per-run merge snapshot UUID and the manifest’s equivalent relative-path spelling are normalized. Merge counts, completeness, exposure and job descriptions must also agree, as must each selection partition’s trigger, progress, interval and job-ID outputs.

Each timed operation is a fresh native process. The merge command is:

/private/tmp/pycwb-duckdb-compat/duckdb-1.5.6/bin/pycwb merge \
    --work-dir /private/tmp/pycwb-duckdb-benchmark-20261021 \
    --mlabel bench-merge-128MB-1.5.6-1 \
    --duckdb-memory-limit 128MB --no-resume

For interval selection the driver writes selection.yaml in that repetition’s output directory and invokes:

/private/tmp/pycwb-duckdb-compat/duckdb-1.5.6/bin/pycwb post-process \
    /private/tmp/pycwb-duckdb-benchmark-20261021/bench-interval_selection-default-1.5.6-1/selection.yaml \
    --no-diagram --no-resume --progress plain \
    --log-file /private/tmp/pycwb-duckdb-benchmark-20261021/bench-interval_selection-default-1.5.6-1/selection-detail.log

The 2.0.0 command uses that environment’s bin/pycwb and distinct labels, workflow paths and outputs. The generated YAML and JSON contain the exact values for every repetition. The driver checks the selection log to confirm the DuckDB backend actually ran.

Measured results, 21 October 2026

The local benchmark completed 36 successful runs: 30 measured repetitions and 6 warmups. Every merge and selection output comparison agreed across versions, repetitions and merge memory budgets under the normalization described above.

The host reported macOS 26.6.2 on arm64, 11 logical CPUs and 18 GiB RAM. Both environments used Python 3.13.12, Arrow 23.0.1, pandas 3.0.3 and NumPy 2.3.5. Wall times below include native CLI startup and imports. Parentheses show the minimum and maximum of five measured runs; RSS is the median of the sampled per-run peaks.

Operation

1.5.6 time, seconds

2.0.0 time, seconds

Median time decrease

RSS, MiB: 1.5.6 → 2.0.0

Merge, 128MB

2.898 (2.875–3.034)

2.849 (2.809–3.106)

1.7%

396.2 → 383.5

Merge, 32MB

2.875 (2.862–3.504)

2.751 (2.731–2.849)

4.3%

395.5 → 378.5

Interval selection, 2GB

3.158 (3.048–3.241)

3.057 (2.992–3.144)

3.2%

491.0 → 480.6

DuckDB 2.0.0 had modestly lower median time and sampled peak memory in this fixture. The timing ranges overlap for the default merge and interval split; five local repetitions do not establish a universal speedup. No spill files were observed by the sampler, including at 32MB, so these measurements do not compare disk-spilling performance.

The imported PycWB checkout identified itself as 1.1.0b6.dev38+gd57f9c50d.d20261021 with the compatibility changes in the working tree. Its imported source path and source version identify the tested code. Installed distribution metadata still reported the older 1.1.0b4.dev19+g25d1c5ca0.d20261012 because of stale editable-install metadata; the local JSON records both rather than substituting the package metadata version for the imported source.

Benchmark interpretation

Measure complete merge and interval selection operations, including Arrow decoding and output writing, with identical inputs under both versions. Verify semantic outputs as well as elapsed time and peak RSS. Track spill-directory usage when studying constrained-memory workloads. Byte hashes of Parquet files alone are not an output-equivalence test because writer metadata can differ.

The version benchmark includes interpreter startup, imports, SQL, Arrow I/O and publication in wall time. Fixture generation and exact decoded-value checks are outside the timed interval. There is one warmup per version/workload followed by five measured repetitions with alternating version order and warm operating system caches; no cache purge is performed. Report medians and ranges rather than selecting a single fast run. RSS and scratch/spill files are sampled every 20 ms, so reported peaks are lower bounds. The monitored disk footprint covers per-run scratch files, logs and selection outputs; published merge outputs lie outside that directory. The database’s configured memory budget is separate from measured RSS.

Synthetic local tests cannot establish an S3 or production-catalog speedup. The merge’s Python validation and Arrow I/O, and selection’s pandas exposure sums and Arrow output, can dominate runtime even when DuckDB query operators improve.

benchmark/parquet_perf/benchmark_queries.py is older exploratory SQL/Arrow query tooling; it does not benchmark the full merge or interval selection workflow. Scripts under reports/ are analysis-specific evidence tooling, not package interfaces or the supported way to run postproduction.