BasekickLabs

Query Caching

The three caches an Arc Enterprise reader node uses to serve repeated dashboard queries — result, metadata, and S3 file cache — and how to size and invalidate each one.

Arc implements multiple caching layers to optimize query performance, particularly for dashboard and monitoring use cases where the same queries are executed repeatedly.

Cache layers

Arc uses three complementary caches that work together:

CacheTTLPurposeSavings
SQL Transform Cache60sCaches SQL-to-storage-path conversionsAvoids re-parsing per query
Partition Path Cache60sCaches OptimizeTablePath() resultsAvoids repeated path resolution
Glob Cache30sCaches filesystem glob resultsAvoids repeated directory listing

SQL transform cache

When you execute a query like:

SELECT * FROM mydb.cpu WHERE time > now() - INTERVAL '1 hour'

Arc converts the table reference mydb.cpu to a read_parquet() call:

SELECT * FROM read_parquet('./data/mydb/cpu/**/*.parquet') WHERE time > now() - INTERVAL '1 hour'

This string transformation uses regex matching and happens on every query. The SQL Transform Cache stores the result so repeated queries skip this processing.

Performance impact

ScenarioTimeSpeedup
Without cache (first query)13-37μs-
With cache (repeated query)~300ns49-104x

When it helps

The SQL Transform Cache is most beneficial for:

  • Dashboard refresh: Same queries every 30s-5min
  • Monitoring alerts: Repeated threshold checks
  • API integrations: Clients polling the same metrics
  • Multi-user dashboards: Shared queries across users

Cache behavior

  • Key: the raw SQL string, the x-arc-database header and the current hour
  • TTL: 60 seconds (matches partition cache)
  • Max entries: 10,000 queries
  • Eviction: Expired entries removed first, then oldest

Partition path cache

After SQL transformation, Arc optimizes the storage path by applying time-based partition pruning. This cache stores the optimized paths.

Example

Query with time filter:

SELECT * FROM mydb.cpu WHERE time > 1704067200000000

Without cache: Scans partition metadata to find relevant directories. With cache: Returns pre-computed path like ./data/mydb/cpu/2024/01/**/*.parquet.

Performance impact

Saves 50-100ms per query on large datasets with many partitions.

Glob cache

After determining the partition path, Arc uses filesystem globs to find matching Parquet files. The Glob Cache stores these file listings.

Performance impact

Saves 5-10ms per query by avoiding repeated filesystem operations.

Cache statistics

Monitor cache performance via the pruner stats:

stats := pruner.GetAllCacheStats()

Returns:

{
  "partition_cache": {
    "size": 150,
    "hits": 12847,
    "misses": 423,
    "hit_rate_percent": 96.8
  },
  "glob_cache": {
    "size": 89,
    "hits": 8234,
    "misses": 312,
    "hit_rate_percent": 96.3
  }
}

Best practices

1. Use consistent query strings

Cache keys are based on exact SQL text. These are different cache entries:

SELECT * FROM mydb.cpu WHERE time > 1704067200000000
SELECT *  FROM mydb.cpu WHERE time > 1704067200000000  -- extra space
select * from mydb.cpu where time > 1704067200000000   -- lowercase

Normalize your queries for better cache hit rates.

2. Use parameterized time ranges

For dashboard queries, use relative time:

-- Good: Same query text on every refresh
SELECT * FROM mydb.cpu WHERE time > now() - INTERVAL '1 hour'

-- Less efficient: Different timestamp each time
SELECT * FROM mydb.cpu WHERE time > 1704067200000000

3. Monitor hit rates

Healthy dashboards should see 60-80%+ cache hit rates. Low hit rates may indicate:

  • Too many unique queries
  • Query text variations
  • TTL too short for your refresh interval

Configuration

Cache parameters are currently fixed but tuned for typical workloads:

ParameterValueRationale
SQL Transform TTL60sCovers 1-2 dashboard refresh cycles
SQL Transform Max Size10,000Handles large multi-tenant deployments
Partition Cache TTL60sBalance freshness vs. performance
Glob Cache TTL30sFiles change less frequently

Cache invalidation

Caches expire based on TTL. All three are cleared at once, on the node that did the work and on every other reader and writer of a cluster (when cluster.shared_secret is set), after:

  • compaction finishes (file paths change);
  • a DELETE removes or rewrites files, including one that stops part-way;
  • a retention run removes files, including one that aborts part-way (a dry run removes nothing and clears nothing);
  • a database is deleted.

Ingest does not clear the caches. Instead, the current hour is never cached as missing: a query whose range reaches the current hour, run before that hour's first flush, is not cached, so the hour shows up as soon as it has files. The previous hour gets the same treatment for the first 5 minutes after the boundary, for rows stamped just before it that flush just after it. Every cached transform and partition list also belongs to the hour it was made in and is not used after the hour changes.

Hours that have not started yet are cached as missing like any past hour, so rows stamped in the future can take up to about two and a half minutes to show up.

Technical details

Thread safety

All caches use sync.RWMutex for concurrent access:

  • Multiple readers allowed
  • Exclusive write lock for updates
  • Lock-free atomic counters for hit/miss tracking

Memory usage

Approximate memory per cache:

CacheEntry SizeMax EntriesMax Memory
SQL Transform~500 bytes10,000~5MB
Partition Path~200 bytes1,000~200KB
Glob~1KB1,000~1MB

Total cache overhead: ~6MB typical, ~10MB maximum.

Next steps

On this page