The read side of the stress test : catalog query latency is a function of file count, not episode count

[Sagar-024]

In #48 i measured 21-26s for a single curate query on a 200 episode corpus (around 1000 catalog files). Back then i wrote that DuckDB curation is fast and not the bottleneck. Re-reading that number against how the catalog actually writes, i dont think that holds anymore. 21s for 200 rows is the leading edge of a curve that grows with file count, not row count.

The mechanism:

Every append_episode publishes one Parquet file per table. 5 files per episode. So 10k episodes means around 50k files.

Every reader re-scans the whole directory on every read. The episodes_latest view does this. The quarantine gate runs a ROW_NUMBER() OVER (PARTITION BY episode_id ...) across all history. The server opens a fresh DuckDB connection per request and registers a VIEW over the glob. On bucket roots, sync_for_read lists and downloads every missing object, so a fresh worker bootstrap at 10k episodes is a 50k object sync.

The write side is deliberately content-named, create-if-absent, manifest-last. That buys idempotence and crash safety. But the same design makes reads O(corpus) with no relief valve anywhere.

I checked issues, PRs and discussions. Couldnt find this articulated anywhere. Closest is #180 which flags an EXISTS over check_runs_latest as possibly costly, but that was a single query micro-cost and was never followed up.

Before any code i want to confirm the shape with a real measurement. The plan:

• A 200 to 1k to 5k to 10k append benchmark, plotting curate latency against file count
• The per-request view cost in the server
• Row-level diff showing any fix stays byte-identical to current reads

If the curve is linear in file count as i expect, the natural relief valve is a compaction pass that merges small files into ordered segments behind an atomic manifest swap, keeping the append discipline intact. Plus a small append-time latest-run index to kill the per-query window function.

No code yet. Just want to confirm this is the right problem to measure, or if there is a read-side plan im missing.