perf(cache): query cache returns stale data after writes (read-your-writes)

[EricAndrechek]

Update (2026-05-18): Resolved by PR #119 (feat(cache): implement per-table tagging and raw SQL bypass), which combines Option B + Option C below per the architecture review. Closing once #119 merges. The original problem statement and option analysis are kept below for historical context.

Any post-merge follow-up (e.g. tail-chopping for high-write tables; tracked in #86) belongs in its own issue, not as a re-open here.

---

Problem

internal/api/query.go:QueryHandler caches /v1/query responses keyed on sha256(sql + params) with a 5s default TTL. The cache has no invalidation signal from ingest writes — reads can return stale data for up to TTL after a write to the same table(s).

Surfaced in #71's new E2E Tests job as a cache-key collision between two tests:

• admin.test.ts:182 runs wh.sql('SELECT count() as cnt FROM default.clicks') against an empty clicks table at the top of the run → caches {cnt: 0}.
• query.test.ts:97 runs the byte-identical query after beforeAll seeds 10 rows → hits the cached {cnt: 0} and fails toBeGreaterThan(0).

The test collision is trivially fixable, but it exposes the broader read-your-writes hole: in production, an admin that inserts a row and immediately runs sql(count()) sees the stale count for up to 5s. Structured queries (/v1/query-builder-style) have the same risk whenever they hit the same cache layer.

Related

• Surfaced by: ci: bundle CI hygiene fixes for #70 #71 (bundled CI hygiene PR)
• Code: internal/api/query.go — queryCacheKey(), h.Cache.Get / h.Cache.Set, h.sf.Do singleflight
• Cache layer: internal/cache (TieredCache — L1 local + optional L2 shared)
• Ingest path (the WAL): Bento → NATS JetStream → ClickHouse, 5s batch window

Options to decide in next standup

Option A — Read-your-writes via WAL buffer (Eric's initial proposal)

On read, augment the CH-cached result with rows still in the NATS WAL buffer for the tables the query references.

Pros: zero stale-read window by construction; users always see their own writes instantly.

Pushback (and why this is harder than it looks):

• Trivial for simple aggregates (count, sum, avg) — you layer the in-memory buffer count on top of the cached result.
• For anything richer — GROUP BY, projections, joins, WHERE filters on fields not in the cache envelope — you need to actually execute the query against the in-memory buffer. That's a mini query engine.
• Call this out explicitly in the issue's design: don't drift toward 'let's write a query engine'. Scope the v1.

Option B — Per-table last-write-timestamp invalidation

Ingest writes to table X bump a last_write_ts[X] monotonic counter. Cache entries capture the list of tables they query (parse FROM at cache-set time). On cache Get, revalidate: miss if max(last_write_ts[tables-in-query]) > entry.written_at.

Pros: simple, works for every query shape (aggregates, GROUP BY, joins), no query engine needed.

Cons / tradeoffs:

• Cache thrash in hot-ingest scenarios — every insert to clicks invalidates every clicks-touching cache entry. Tunable with coarser granularity (e.g. second-bucketed timestamps, per-tenant+table keys).
• Requires a FROM-clause parser. Can be naive (regex-based) for v1; lift to a real SQL AST parser later.
• Still has a visibility window between the NATS publish and the cache invalidation — reads in that window still see stale data. Acceptable if the ingest → invalidate step is fast.

Option C — Bypass cache entirely for raw SQL

One-line change: in QueryHandler.Handle, skip the cache lookup when the query hits the /v1/query path (raw SQL, admin-gated).

[EricAndrechek]

@taitelee thoughts?

[taitelee]

Update: Post-Standup Decision & Architecture Path

Following our discussion, we've decided on an architecture that accepts eventual consistency for structured reads while providing explicit escape hatches for real-time and administrative workflows.

We are dropping option A (the NATS buffer merge / mini query engine) and option B (timestamp-based cache invalidation) in favor of a time-bucketed caching strategy combined with option C.

Here are some things we've agreed on:

1. The "acceptable delay" paradigm for standard reads
We are accepting the fact that standard API reads will have an inherent delay of up to $X$ seconds (the Bento buffer flush interval). We will rely entirely on ClickHouse as the source of truth for these queries, rather than trying to stich in-flight NATS data into the response. To ensure users can read writes once they land, we will trigger a full cache invalidation for a given table whenever Bento flushes a write batch to it.

2. The real-time escape hatch (streams)
If a client requires strict, sub-second real-time data, they will have to consume it using a direct stream rather than the API.

• The tradeoff: Streaming data is raw. We (for now) cannot apply structured query filters (WHERE, LIMIT, aggregations) to the in-flight stream. Filtering must be handled client-side.

3. Time-Bucketed caching and "tail chopping"
Instead of naive cache invalidation, we will leverage ClickHouse to bucket data based on timestamps, and we will cache those discrete buckets.

• When a query asks for a relative range (e.g., now() - 30s), the cache layer will serve the pre-computed, fully closed time buckets that fit within that window.
• Fractional Buckets (tail chopping): Because now() - X will rarely align perfectly with our bucket edges, the system will take the cached full buckets and "chop off the tail" of the interval. This will dynamically query or slice the fraction of the most recent/boundary bucket to return the exact requested time window. This ensures that for overlapping relative queries between write flushes, we only need to dynamically calculate the narrow slice of recent data rather than the entire aggregate.
• Invalidation Point: A Bento-triggered update will invalidate ALL cached data for that entire table, including the historical intermediate buckets/bins. Because we aren't introspecting the payload to see if the inserted data lands in a historical bin (late-arriving data) or the current one, we must drop the whole table's cache to guarantee accuracy. The time-buckets still heavily optimize read performance between flushes (by standardizing cache hits for overlapping queries), but they don't survive a write.

4. Raw SQL cache bypass (implementing option C)
We are moving forward with option C for admin workflows. Any raw SQL executed against the /v1/query endpoint will completely bypass the API cache layer. This ensures that admins executing ad-hoc queries (like the ones in our E2E tests) will always hit ClickHouse directly, avoiding the confusion of stale cached aggregates during manual debugging.

[EricAndrechek]

Worth noting: we do eventually want realtime (push-based) data even for arbitrary queries/pipes, but this is proving more complicated than original though, even for "simple" cases like SUMs, as evaluating the WHERE and LIMIT cases requires us to build a lite version of the CH SQL engine which is beyond scope, so we are deferring anything beyond raw event streaming to use CH as the only source, and not attempt any aggregations with NATS buffer / pseudo-WAL data. This means a follow-up issue will be opened SOON™️ has been opened (#87) to address this and REMOVE code/functionality that is currently half baked to do this, instead only allowing streams for an entire table. More on that in #87.

We also want the time-bucketed approach to be as CH-default/CH-managed as possible, so that we don't have to think about and implement different bucketting/binning tactics for different queries (ie SUM vs COUNT).

Additionally, we are acknowledging the limitations that our Bento-triggered full-table invalidation approach causes, including:

• [PRIORITY 1] We still need to work out how this will be influenced and will operate with non-standardized ingest events (easy), such as UPDATES, DELETES, and raw SQL from the admin route. The plan was that these would ALSO flow through the queue/buffer that ingested data was on for insertion, to remain properly ordered, but would mean that read-only raw admin sql queries would be significantly delayed (read-only could have a different, faster route, but we'd presumably want raw admin SQL to be strongly consistent and correct AND it would be hard (custom CH SQL parser required) to determine what raw SQL query was "just" a read...). So should any reference to a table name (as basic string matching as a stop-gap until a CH SQL parser exists, knowing that it will over-match (false positives) and (hopefull??) no false-negatives, which is the pattern/trade-off we want for cache invalidation) mean that that table's cache(s) are all invalidated? Likely the desired path/plan for now, but will need to re-evaluate later.
• [PRIORITY 1] CH queries that take longer to execute than the period between cache invalidations will cause errors and issues. This may be a case where singleflight needs to be re-evaluated or not used – for instance if a query took >1 minute to run, we wouldn't be able to hold open the request for that long, and would also potentially want to introduce a singleflight-queue for this query.
• [PRIORITY 2] CH tables with heavy write traffic will have constantly invalidated caches, meaning effectively no cache. This may be ok, though, assuming that singleflight works properly.
• [PRIORITY 3] Eventually, when we consider re-adding more filtering to NATS event streaming, we may want to sort cache invalidations by inserted data times and/or contents that WHERE or LIMIT filters would apply on to avoid invalidating any caches we don't speculate would change following the batched insertion. More complicated, but much higher CACHE hit rates. Low priority for MVP.