SQLite-Columnar Benchmarks

sqlite-columnar Benchmark

Date: 2026-05-10

Single-run command:

make
make benchmarks
build/columnar-analytics-bench ./columnar 10000000 256

For variance-aware runs, use the repeatable benchmark suite:

make variance-bench VARIANCE_REPEATS=9 \
  VARIANCE_DATASETS="small:10000:64 medium:50000:128 wide:50000:512"

The variance suite emits machine-readable lines:

• dataset,... records load/analyze time, storage size, and zone-map coverage.
• query,... records row-store and columnar median/p95 timings, median/p95 speedups, result row count, and normalized result hash.

Each query is warmed once before sampling and every sample verifies that the columnar result hash matches the row-store result hash.

Variance Benchmark Results

Command:

make variance-bench VARIANCE_REPEATS=3 \
  VARIANCE_DATASETS='large100k:100000:128 large1m:1000000:128 large10m:10000000:256'

With three repeats, p95 is effectively the slowest sampled run. Use more repeats for publishable tail-latency claims.

Load and analyze:

10M query medians:

Median speedup by dataset size:

Single-Run Analytical Benchmark

Dataset:

• Rows: 10,000,000
• Wide fact table with dimensions ts, id1..id6, measures v1..v3, and cold unused payload columns.
• Cold text payload: 2 columns x 256 bytes.
• ts is clustered/monotonic, which lets chunk zone maps prune rowid ranges.
• Chunk size: 65,536 rowids.

Load and analyze:

Query results:

Interpretation:

• Global sum/avg/count are accelerated by precomputed stats.
• Generic virtual-table scans benefit from reading fewer columns but still pay SQLite row materialization and generic aggregation costs.
• Specialized grouped functions avoid generic row materialization.
• Range-filtered functions use chunk zone maps. They help modestly on uniformly distributed filters such as id2, and dramatically on clustered filters such as ts, where only 3 of 153 chunks are scanned.
• Scalar range-filtered aggregates use precomputed chunk sum/count for full-cover chunks. In this run, 1 of the 3 selected ts chunks is served from chunk stats instead of row scans.
• columnar_analyze() is incremental after the initial bootstrap. Persistent metadata tracks row_count, chunk_count, dirty_count, and stats_valid. A no-op analyze on this 10M-row table now returns from metadata in 2.114 ms, and reanalyzing the dirty metadata after a single inserted row took 82.998 ms.
• The current weak spots remain load time, columnar_analyze() cost, and larger on-disk size due to one SQLite B-tree per column plus zone-map metadata. The expensive analyze cost now applies primarily to the first bootstrap or to large dirty ranges.