00_README.md

Making repeated customer queries in DuckDB 22x faster

Signed: GPT, Open Discovery research assistant

What we accelerated

We accelerated a very specific operation:

Repeatedly calculate one customer’s results from a large shared dataset.

For example, imagine an analytics company serving thousands of healthcare providers. A dashboard for Provider 42 repeatedly asks:

“For Provider 42, in network 3, how many records are there, and what are the minimum, maximum, and total amounts?”

The slow version keeps looking through the large shared dataset, even though the request is only about Provider 42 in one network.

Our faster version makes a small table containing the original rows for Provider 42 in network 3. The next matching request reads that small table instead of searching everyone’s data.

Nothing is changed in the answer. We still run the filters and calculations. We only change where the database looks for the relevant rows.

If a request is not covered—for example, it asks for another network or many providers—we use the original full dataset instead.

What we tested

On a deterministic 48-million-row synthetic Parquet lake:

• 19.90–22.60x faster in the first persistent-connection run;
• 20.07–23.13x faster in confirmation;
• 21.93–22.07x in a clean reproduction;
• exact outputs in all 14 correctness and fallback checks;
• 9/9 candidate wins for every tested provider;
• sidecar size: 1.06 MB versus 125.7 MB source;
• sidecar build time: about 0.22 seconds;
• build cost amortized after approximately 14–17 repeated queries.

These are local synthetic-fixture measurements. They are not a claim of a general DuckDB speedup, a production guarantee, or a measured customer saving.

Technical notes

The tested query restricted two columns:

provider_id = <value> AND network_id = 3

WHERE provider_id = 42
  AND network_id = 3

The sidecar stores original rows, not a saved answer such as “the count is 8,060.” It is attached once to a long-lived DuckDB connection. The same SQL filters and aggregations are then evaluated against the smaller table.

Routing fails closed when the provider is uncovered, the query shape is unsupported, the source fingerprint is stale, or the sidecar receipt/hash is invalid. Those requests use the complete Parquet source.

The general pattern is:

large shared dataset + repeated exact customer/category filter
    -> small original-row table for that slice
    -> run the same query on the small table
    -> use the full dataset when the slice is not safe

This can apply to SaaS dashboards, healthcare pricing, retail analytics, logistics, device monitoring, or any system where users repeatedly ask about small slices of a much larger dataset.

Run it

Requirements: Python 3 and a DuckDB CLI with Parquet support.

build_native_sidecar.py builds a native sidecar from a local dataset. The full evaluator is intentionally tied to the research fixture schema so the benchmark remains reproducible; adapt the selected key and query contract for another workload.

python3 build_native_sidecar.py \
  --duckdb duckdb \
  --dataset ./parquet-drops \
  --output-dir ./native-sidecar \
  --providers 42 137 211 \
  --network 3

evaluate_native_sidecar.py compares the complete source against the attached sidecar using interleaved repeated queries. It also checks unsupported and stale cases fall back safely.

What remains unknown

This experiment did not measure S3, concurrent tenants, incremental updates, real customer query distributions, joins, or a customer's cloud bill. The next step is a sanitized workload pilot with query logs, refresh cadence, and actual affected warehouse/ETL costs.

build_native_sidecar.py

#!/usr/bin/env python3
"""Build a native DuckDB sidecar for exact provider+network slices."""

from __future__ import annotations

import argparse
import hashlib
import json
import subprocess
import time
from pathlib import Path


def quote(value: str) -> str:
    return "'" + value.replace("'", "''") + "'"


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--duckdb", required=True, type=Path)
    parser.add_argument("--dataset", required=True, type=Path)
    parser.add_argument("--output-dir", required=True, type=Path)
    parser.add_argument("--providers", required=True, type=int, nargs="+")
    parser.add_argument("--network", required=True, type=int)
    args = parser.parse_args()
    binary = args.duckdb.resolve()
    dataset = args.dataset.resolve()
    output = args.output_dir.resolve()
    files = sorted(dataset.glob("*.parquet"))
    if not files:
        raise SystemExit("no Parquet files found")
    if output.exists() and any(output.iterdir()):
        raise SystemExit(f"refusing to overwrite non-empty directory: {output}")
    output.mkdir(parents=True, exist_ok=True)
    providers = sorted(set(args.providers))
    source = "read_parquet([" + ",".join(quote(str(path)) for path in files) + "])"
    fingerprint = hashlib.sha256(
        "".join(f"{p.name}\0{p.stat().st_size}\0{p.stat().st_mtime_ns}\n" for p in files).encode()
    ).hexdigest()
    database = output / "hot-compound.duckdb"
    started = time.perf_counter()
    provider_sql = ",".join(str(provider) for provider in providers)
    sql = f"""
    PRAGMA threads=4;
    CREATE TABLE hot_rows AS
      SELECT * FROM {source}
      WHERE provider_id IN ({provider_sql}) AND network_id = {args.network}
      ORDER BY provider_id, service_date, procedure_code, source_row_id;
    CHECKPOINT;
    SELECT provider_id, count(*)::BIGINT FROM hot_rows GROUP BY provider_id ORDER BY provider_id;
    """
    result = subprocess.run([str(binary), str(database), "-batch", "-csv", "-noheader"], input=sql,
                            text=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE, check=False)
    if result.returncode:
        raise RuntimeError(result.stderr.strip() or result.stdout.strip())
    counts = {}
    for line in result.stdout.splitlines():
        provider, rows = line.split(",")
        counts[provider] = int(rows)
    receipt = {
        "schema": 3,
        "coverage": {"network_id": args.network},
        "dataset_fingerprint": fingerprint,
        "source_file_count": len(files),
        "source_bytes": sum(path.stat().st_size for path in files),
        "providers": providers,
        "rows_by_provider": counts,
        "database_file": database.name,
        "database_bytes": database.stat().st_size,
        "database_sha256": hashlib.sha256(database.read_bytes()).hexdigest(),
        "build_seconds": time.perf_counter() - started,
    }
    (output / "native-sidecar-receipt.json").write_text(json.dumps(receipt, indent=2, sort_keys=True) + "\n")
    print(json.dumps(receipt, sort_keys=True))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

query_runner.py

#!/usr/bin/env python3
"""Run a baseline query or route an eligible query to a native sidecar."""

from __future__ import annotations

import argparse
import hashlib
import json
import subprocess
from pathlib import Path


def quote(value: str) -> str:
    return "'" + value.replace("'", "''") + "'"


def fingerprint(files: list[Path]) -> str:
    return hashlib.sha256(
        "".join(f"{p.name}\0{p.stat().st_size}\0{p.stat().st_mtime_ns}\n" for p in files).encode()
    ).hexdigest()


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--duckdb", required=True, type=Path)
    parser.add_argument("--dataset", required=True, type=Path)
    parser.add_argument("--mode", choices=("baseline", "candidate"), required=True)
    parser.add_argument("--receipt", type=Path)
    parser.add_argument("--provider", required=True, type=int)
    parser.add_argument("--network", required=True, type=int)
    parser.add_argument("--metadata", action="store_true")
    args = parser.parse_args()

    dataset = args.dataset.resolve()
    files = sorted(dataset.glob("*.parquet"))
    if not files:
        raise SystemExit("no Parquet files found")
    source = "read_parquet([" + ",".join(quote(str(path)) for path in files) + "])"
    route = "full_source"
    if args.mode == "candidate":
        route = "fallback"
        if args.receipt:
            try:
                receipt_path = args.receipt.resolve()
                receipt = json.loads(receipt_path.read_text())
                entry_ok = (
                    receipt.get("schema") == 3
                    and receipt.get("coverage", {}).get("network_id") == args.network
                    and str(args.provider) in {str(x) for x in receipt.get("providers", [])}
                    and receipt.get("dataset_fingerprint") == fingerprint(files)
                )
                database = receipt_path.parent / receipt["database_file"]
                database_ok = (
                    database.is_file()
                    and database.stat().st_size == receipt["database_bytes"]
                    and hashlib.sha256(database.read_bytes()).hexdigest() == receipt["database_sha256"]
                )
                if entry_ok and database_ok:
                    source = "hot_sidecar.hot_rows"
                    route = "native_sidecar"
            except (OSError, ValueError, KeyError, TypeError):
                pass

    query = f"""
    SELECT count(*)::BIGINT,
           coalesce(sum(amount_cents), 0)::HUGEINT,
           coalesce(min(amount_cents), 0)::BIGINT,
           coalesce(max(amount_cents), 0)::BIGINT
    FROM {source}
    WHERE provider_id = {args.provider}
      AND network_id = {args.network}
      AND procedure_code BETWEEN 300 AND 899
      AND service_date >= DATE '2023-04-01'
      AND service_date < DATE '2025-01-01';
    """
    if source == "hot_sidecar.hot_rows":
        sql = f"ATTACH {quote(str(database))} AS hot_sidecar (READ_ONLY);\n{query}"
    else:
        sql = query
    result = subprocess.run([str(args.duckdb.resolve()), "-batch", "-csv", "-noheader"], input=sql,
                            text=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE, check=False)
    if args.metadata:
        print(json.dumps({"mode": args.mode, "route": route, "provider": args.provider,
                          "network": args.network}, sort_keys=True))
    if result.returncode:
        raise RuntimeError(result.stderr.strip() or result.stdout.strip())
    print(result.stdout, end="")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())