Caching and query lanes

datahashi caches repeated queries and splits traffic into lanes. Dashboards stay fast. The warehouse does not see a spike of identical work.

flowchart TD
    Q["Incoming queries<br/>REST · MCP · SQL Wire"] --> L
    subgraph L["Query admission lanes and limiter"]
        direction TB
        L1["Interactive lane: REST and SQL Wire, high priority, fast timeout"]
        L2["LLM lane: MCP agents, parallel tool calls, isolated cap"]
        L3["Batch lane: CSV exports and rollup builds, long timeouts"]
    end
    L --> C
    subgraph C["Multi-tier caching"]
        direction TB
        C1["1 · In-process live cache<br/>collapses identical in-flight queries"]
        C2["2 · Proactive cache warming<br/>refreshes hot queries before expiry"]
        C3["3 · Shared Redis cache<br/>multi-instance fleet hits"]
        C4["4 · DuckDB rollup storage<br/>pre-aggregated Parquet hits, ~5-20ms"]
        C1 --> C2 --> C3 --> C4
    end
    C -->|"cache miss"| W[("Physical data warehouse")]

A query reaches the warehouse only after lanes, cache, and rollup checks.

Query admission lanes

Without lanes, a CSV export of 100,000 rows can fill the connection pool. Dashboards then time out. Agents wait.

Lanes are off until you set ENGINE_LANES=true. The engine then admits interactive traffic first, then MCP, then CSV export and batch.

Lane	Traffic source	Cap
Interactive	REST /v1/query and PostgreSQL wire	The process cap, MAX_CONCURRENT_QUERIES
LLM	MCP /mcp tool calls	ENGINE_LANE_LLM_MAX (default: half of the process cap)
Batch	CSV streams, rollup builds, and cache warming	ENGINE_LANE_BATCH_MAX (default: a quarter of the process cap)

Each lane queues at most ENGINE_LANE_MAX_QUEUE waiters (default 64). A waiter not admitted after ENGINE_LANE_MAX_WAIT (default 30s) gets 503 overloaded and Retry-After. A full batch lane does not take an interactive slot.

Live cache and warming

The engine holds an in-memory live cache. Entries are keyed by caller identity and row-level security parameters.

• Request collapsing (singleflight). If five widgets ask for the same metric at the same second, the engine runs one warehouse query. All five callers share that response.
• Proactive warming (LIVE_CACHE_WARM_ENABLED). The engine finds hot queries (hit threshold ≥ 3). It refreshes them in the background before the TTL expires. Users see cache-hit latency.

Configure live cache:

LIVE_CACHE_TTL=15s
LIVE_CACHE_MAX_ENTRIES=2000
LIVE_CACHE_WARM_ENABLED=true
LIVE_CACHE_WARM_HIT_THRESHOLD=3
LIVE_CACHE_WARM_MAX_CONCURRENT=4
LIVE_CACHE_WARM_TIMEOUT=30s

Shared Redis cache

For a multi-node engine cluster behind a load balancer, set a shared Redis instance:

REDIS_ADDR="redis.internal:6379"
REDIS_PASSWORD="secret_redis_auth"
REDIS_RESULT_TTL=30s
REDIS_KEY_PREFIX="engine:qr"

A query answered on Engine Node A is saved to Redis. When Engine Node B receives the same query, it serves the cached result.

Rollups with DuckDB

For hot metrics, pre-aggregation rollups skip the warehouse:

• Data is stored as column-oriented Parquet files.
• Embedded DuckDB evaluates joins and filters in 5–30 milliseconds.
• Rollup live tail keeps the current period up to date.

See Rollups for syntax.

Cost limits

Stop runaway queries and unexpected warehouse bills:

# Hard cap on rows returned per query
ENGINE_MAX_ROWS=50000

# Per-query scanned byte ceiling (fails query if warehouse estimates over cap)
ENGINE_MAX_BYTES=10737418240  # 10 GB

# Tenant rolling window budget
ENGINE_BUDGET_WINDOW=1h
ENGINE_BUDGET_MAX_BYTES=107374182400 # 100 GB / hour
ENGINE_BUDGET_MAX_QUERIES=5000       # 5,000 queries / hour

Next: Install Query Runner · Self-host the community engine.