Spice.ai caches SQL database data at two layers: dataset acceleration, which materializes upstream tables into a fast local engine, and SQL results caching, which stores query results in memory for repeated queries.
Together, these layers reduce load on upstream databases and deliver sub-millisecond query performance. Dataset acceleration is suited for working sets that applications query repeatedly, while results caching handles identical SQL queries across short time windows — for example, a dashboard that multiple users hit with the same query within seconds.
Accelerate a PostgreSQL table locally with periodic refresh:
With CDC-based refresh using Debezium:
Enable the SQL results cache with a longer TTL and stale-while-revalidate for dashboard workloads:
With this configuration, the first execution of a query runs against the accelerated table, and the result is cached in memory. Identical queries within 30 seconds are served from the in-memory cache. After 30 seconds, the cached result is served stale while Spice re-executes the query in the background. After 5 minutes 30 seconds without access, the entry is evicted.
The Results-Cache-Status response header indicates cache state for each query:
| Value | Meaning |
|---|---|
HIT | Result served from the cache. |
MISS | No cached result; query executed against the accelerator. |
STALE | Stale result served while revalidating in the background. |
BYPASS | Cache bypassed by client request. |
Clients can control cache behavior per-request using the Cache-Control header (HTTP API) or --cache-control flag (Spice SQL REPL). For example, Cache-Control: no-cache bypasses the cache for a single request while still caching the result for future queries. Cache-Control: only-if-cached returns only cached results, erroring on a miss.
