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 |
|---|---|---|
|
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. |
|
|
A materialized Arrow result. This convenience API first loads the trigger table and is not the bounded merge/selection path. |
|
|
A materialized Arrow result. The JSON catalog is already loaded in memory; SQL does not make JSON reading out of core. |
|
|
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. |
|
Eligibility checks, completed-progress filtering, compact interval
metadata, and seeded file-output splits for |
Per-partition jobs, progress, interval summaries, trigger catalogs and livetime metadata. Owns a private connection and scratch directory. |
|
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 |
Database, spill files and descriptor bytes use disposable system
temporary storage ( |
Interval selection |
One DuckDB thread; |
Compact numeric metadata still scales with progress rows. Trigger
membership and payload output stream in |
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, |
2.898 (2.875–3.034) |
2.849 (2.809–3.106) |
1.7% |
396.2 → 383.5 |
Merge, |
2.875 (2.862–3.504) |
2.751 (2.731–2.849) |
4.3% |
395.5 → 378.5 |
Interval selection, |
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.