The caching refresh mode provides intelligent caching for HTTP-based datasets where multiple result rows can share the same request metadata. This mode is specifically designed for scenarios like API responses where the same request parameters can return different content over time or multiple rows of data.
The caching mode supports two key paradigms:
Unlike traditional refresh modes that treat datasets as single sources of truth, the caching mode treats HTTP request metadata (path, query parameters, and body) as cache keys. This approach is particularly useful for:
:::info[Future Enhancement]
While currently designed for HTTP-based datasets, future versions of Spice will extend the caching mode to support arbitrary queries from any data source, enabling flexible caching strategies across all connector types.
:::
The caching mode uses HTTP request filter values as cache keys rather than enforcing primary key constraints. When a refresh occurs:
request_path, request_query, and request_body acts as the cache key. A declared acceleration.primary_key does not replace it — see Cache Key Behavior (opens in a new tab).fetched_at timestamp indicating when the data was retrievedDatasets using caching mode include the following metadata fields in addition to the content data:
| Field Name | Type | Description |
|---|---|---|
request_path | String | The URL path used for the request |
request_query | String | The query parameters used for the request |
request_body | String | The request body (for POST requests) |
content | String | The response content |
response_status | UInt16 | The HTTP status code of the response (e.g., 200, 404, 500) |
response_headers | Map(String, String) | The HTTP response headers as key-value pairs |
fetched_at | Timestamp | The timestamp when the data was fetched (based on HTTP Date header) |
The fetched_at timestamp uses the HTTP Date response header when available, falling back to the current system time if not present.
To use caching mode, configure an HTTP/HTTPS dataset with refresh_mode: caching:
Cache TV show search API results where the same query may return different results over time:
This configuration:
Cache responses from a TV show episodes API:
Cache responses from multiple TVMaze API endpoints:
Query cached data using standard SQL, filtering by request metadata or content:
The caching mode supports the Stale-While-Revalidate (SWR) pattern for acceleration, providing optimal performance by serving cached data immediately while refreshing in the background.
When configured with background refresh, the caching mode:
This pattern reduces query latency by eliminating wait times for data fetches while keeping the cache reasonably fresh.
Configure background refresh using refresh_check_interval to specify how frequently the cache should be updated:
The SWR pattern is particularly valuable for caching API responses:
Combine background refresh with on-demand refresh for maximum flexibility:
With this configuration:
/v1/datasets/tv_search_swr/acceleration/refreshThe caching mode supports persisting cached data to disk using file-based acceleration engines, enabling the cache to survive application restarts and reducing cold start times.
Three acceleration engines support file persistence for caching mode:
Enable file persistence by setting acceleration.mode: file and specifying an acceleration engine:
DuckDB provides excellent performance for analytical queries on cached data:
SQLite is ideal for lightweight caching scenarios:
Cayenne provides optimized performance for Spice workloads:
Persisting the cache to disk provides several advantages:
Choose between in-memory and file-based caching based on your requirements:
| Aspect | Memory Mode (mode: memory) | File Mode (mode: file) |
|---|---|---|
| Performance | Fastest - all data in RAM | Fast with disk I/O |
| Persistence | Lost on restart | Survives restarts |
| Capacity | Limited by available memory | Limited by disk space |
| Cold start | Slow - must refetch all data | Fast - loads from disk |
| Best for | Small, frequently changing caches | Large, stable caches |
| Engines | cayenne (default), arrow (default on Windows) | duckdb, sqlite, cayenne |
For optimal performance, combine the SWR pattern with file persistence:
This configuration provides:
When new data is fetched for a given cache key (request metadata):
DELETEThe cost of caching one response therefore does not grow with everything already cached. On an accelerator that does not implement deletes, the write falls back to reading the acceleration back, filtering it in memory and overwriting it; the fallback is chosen while planning, so a replacement is never left half-applied. A cache key nothing holds yet is appended with no DELETE at all.
The delete and the append are two statements rather than one transaction, and the delete runs first because that is the order that fails safely: if the append fails, the entry is gone and the next read re-fetches it, which is a cache miss and always a correct answer. Appending first would leave the key holding both responses, and a duplicated source row is a wrong answer that queries go on returning.
A key is claimed by one writer for the duration of a replacement, so a concurrent reader that sees the delete-then-append gap reads a miss and cannot append its own copy beside a response it never saw. The claim is released if the writer is cancelled or its fetch never returns.
This behavior differs from other modes:
full mode: Replaces the entire datasetappend mode: Adds new rows without removing existing oneschanges mode: Applies CDC eventscaching mode: Replaces rows matching the specific cache keyAn origin that answers a cached request with no rows, such as [], has retracted the rows it returned before. On the cayenne engine, that empty response replaces the entry: the stored rows for that request are deleted, so a later caching_stale_if_error (opens in a new tab) fallback or stale-while-revalidate (opens in a new tab) read does not serve the retracted rows. The replacement is scoped to the one request the lookup names. A request part the lookup does not name is matched as empty, the value the HTTP connector stores for a part it does not send, so an empty answer to a GET leaves a POST response or a response to another request_query under the same path in place. When the lookup's filters do not name a single request, the empty response is not written and the earlier entry is kept.
On duckdb, sqlite, and arrow, an empty response is not written, so the entry keeps the rows it held before until it is evicted or a non-empty response replaces it. Use engine: cayenne when the origin can retract a response. In v2.4.0-rc.2 and earlier, an empty response to a GET lookup did not replace a cayenne entry either.
Concurrent cache misses for the same cache key share one origin fetch. The first request fetches from the origin and writes the result; requests that miss on the same key while that fetch is in flight wait for it and return its rows, without a second origin request and without writing a second copy. An empty result is shared the same way.
A waiting request shares the fetch only when that fetch returns enough rows for it. An unbounded fetch serves any request. A fetch bounded by LIMIT n serves only requests whose own limit is n or lower, so a LIMIT 3 miss that arrives during a LIMIT 1 fetch queries the origin itself, and does not write. A stale-while-revalidate refresh shares its fetch with misses on the same key in the same way.
A waiting request makes its own origin request when the shared fetch fails, is cancelled, or returns a 429 or 5xx response, or when it has waited 30 seconds. caching_stale_if_error (opens in a new tab) then applies to that request as it does to any other.
A cache entry is always addressed by HTTP request metadata — request_path, request_query, and request_body — whether or not a primary_key is declared. A lookup is built from the request alone, because that is all a query supplies before the response exists.
The HTTP connector sends a GET when a query names no request_body value, and a POST with each body the query names. It stores a GET response with request_body = '' and a POST response with the body it sent. When the dataset schema includes request_body, a lookup keeps the two apart:
request_path, request_query, request_body, or request_headers that names no body value reads only cached GET responses. A POST response cached for the same path does not answer it. A predicate the connector does not turn into a request value, such as request_body <> 'x' or request_body LIKE '%x%', still sends a GET, so it also reads only GET responses.request_body = '', sends a POST with an empty body. Its response would be stored exactly as a GET response is, so the two could not be told apart. This query therefore bypasses the accelerator: each call that reaches it goes to the origin, and its response is not stored. The SQL results cache (opens in a new tab) in front of the accelerator can still return an identical query's earlier result within its item_ttl; Prefer the origin, fall back on failure (opens in a new tab) explains how to send every query to the origin.content or response_status, reads every cached entry, as an unfiltered scan does.Entries persisted by a runtime without this separation keep the values they were written with. In a file-backed cache, a response to an explicit-empty POST written by such a runtime is stored with request_body = '', so it can still answer a GET lookup until a refresh replaces it or the entry is evicted.
A refresh of a cached GET response, including a stale-while-revalidate (opens in a new tab) refresh, requests it from the origin again as a GET.
A declared primary_key does not change how entries are addressed. It is a uniqueness constraint on the rows the accelerator stores, and it must name columns that exist in the dataset schema:
columns: block (opens in a new tab) — an HTTP dataset's schema is otherwise the request/response metadata plus content.caching mode is built for: a response holding several rows is refused rather than partially cached, the entry is not written, and every query for it goes to the origin.:::warning
primary_key takes a string naming one or more columns, optionally parenthesized — primary_key: id or primary_key: '(id, season, number)'. A YAML list (primary_key: [id, season, number]) is not a valid value and fails to parse, so the whole Spicepod is rejected at load.
:::
Example with a key over response fields, projecting them with a columns: block:
Entries are still addressed by request_path here — the key only deduplicates episodes across refreshes of the same endpoint. Declaring a columns: block replaces the default schema, so every metadata field the dataset needs to stay queryable (request_path above, and anything a refresh_sql filters on) must be listed in it.
The fetched_at timestamp respects the HTTP Date response header when present. This provides:
If the Date header is not present, the system falls back to using the current system time.
Transient HTTP errors never reach the cache. Specifically:
The HTTP connector (opens in a new tab) retries these statuses up to its max_retries. When the last attempt still returns one, the fetch fails with an error: the querying client receives that error, or the expired cached entry when caching_stale_if_error (opens in a new tab) permits serving it, and nothing is written to the cache. A row that carries one of these statuses in a response_status column is also kept out of the cache, so subsequent cache reads return valid data rather than error responses from temporary failures.
The caching mode supports standard refresh configuration options. See Stale-While-Revalidate Pattern (opens in a new tab) for background refresh details and Cache Persistence (opens in a new tab) for file-based caching configuration.
| Parameter | Description | Default |
|---|---|---|
refresh_check_interval | How often to refresh cached data in the background | None |
refresh_sql | SQL query defining what data to cache | None |
refresh_on_startup | Whether to refresh on startup (auto or always) | auto |
on_zero_results | Ignored in caching mode. Caching mode always queries the source on a cache miss, regardless of this setting. | return_empty |
engine | Acceleration engine (arrow, duckdb, sqlite, cayenne) | cayenne (arrow on Windows) |
mode | Persistence mode (memory or file) | memory |
The caching mode provides parameters to control cache freshness and staleness behavior:
| Parameter | Description | Default |
|---|---|---|
caching_ttl | Duration that cached data is considered fresh. After this period, data becomes stale and triggers a background refresh. | 30s |
caching_item_ttl | Alias for caching_ttl, spelling the item_ttl suffix used by the results, search-results and embeddings caches (opens in a new tab). Set one or the other — setting both to different values is a load error. | 30s |
caching_stale_while_revalidate_ttl | Duration after caching_ttl expires during which stale data is served while refreshing in the background. After this period, queries wait for fresh data. | None |
caching_stale_if_error | How much staleness past caching_ttl to tolerate before an upstream error is propagated instead of the expired entry. A duration (600s), or enabled (unbounded) / disabled (never serve stale). | disabled |
The caching_ttl parameter defines how long cached data is considered fresh before it becomes stale. Once cached data exceeds this age, the SWR pattern triggers background refresh to update the cache while continuing to serve the stale data during the caching_stale_while_revalidate_ttl window.
If caching_stale_while_revalidate_ttl is omitted, cached data becomes rotten immediately after caching_ttl expires, and queries will wait for fresh data rather than returning stale results. When a value is specified, stale data is served during that window after caching_ttl expires while a background refresh occurs. Once the combined caching_ttl + caching_stale_while_revalidate_ttl period has passed, queries will wait for fresh data instead of returning stale results.
Configuring Cache TTL:
How Cache TTL Works:
fetched_at timestamp is recordednow - fetched_at > caching_ttlcaching_stale_while_revalidate_ttlTTL Format: Duration strings support common units:
30s, 90s5m, 15m1h, 24h1h30m, 2h15m30sDefault Behavior: When caching_ttl is not specified, it defaults to 30s (30 seconds). This provides a reasonable balance between freshness and cache efficiency for most use cases. When caching_stale_while_revalidate_ttl is not specified, stale data is not served after the TTL expires, and queries will wait for fresh data.
A caching_ttl of 0s marks entries stale immediately. That setting does not mean queries always read the accelerator. See Prefer the origin, fall back on failure (opens in a new tab).
Use this pattern for availability-first HTTP read-through caching: prefer the origin while it is healthy, and read the accelerator only if the origin fails.
Write a zero duration with a unit, such as 0s. A bare 0 is a YAML integer, which Spice rejects when it loads the dataset: Invalid 'caching_ttl' param value: Int(0). Expected a duration string.
caching_ttl: 0s marks an entry stale as soon as it is stored, so the accelerator is not a fresh result. caching_stale_while_revalidate_ttl: 0s closes the window that would return that stale entry while the origin is revalidated. While the origin is healthy, Spice does not serve the accelerator and returns the origin response. A successful origin response is still stored. The accelerated entry is consulted only when a later origin request fails, including a 429 or 5xx response once the HTTP connector has exhausted max_retries, and caching_stale_if_error is enabled or a finite duration.
A finite duration such as 10m serves the cached entry only while its staleness past caching_ttl stays within that duration. With caching_ttl: 0s, that staleness starts as soon as the entry is stored, so the duration is the maximum age of a fallback entry. enabled has no age bound. caching_stale_if_error: 0s means disabled, the same as leaving the fallback off.
caching_ttl: 0s on its own does not mean "always use the accelerator." With stale-while-revalidate at 0s (or omitted) and caching_stale_if_error left disabled, every read waits on the origin, and an origin failure is returned to the caller.
These rules describe the accelerator. The SQL results cache (opens in a new tab) sits in front of it and is on by default with an item_ttl of 1s: an identical query inside that window returns the earlier result, including a fallback served during an outage, without a new origin request. When every query must reach the origin, send it with Cache-Control: no-cache (opens in a new tab) or set runtime.caching.sql_results.enabled: false. The HTTP connector's response cache (opens in a new tab) is not involved, because an accelerated dataset skips it whatever Cache-Control the origin sends.
caching_stale_if_error: enabled keeps expired entries as fallback material and does not derive an eviction deadline from age. Pair that unbounded setting with caching_max_size, caching_max_items, or a retention_period / retention_sql rule so stale entries do not grow without a bound. A finite caching_stale_if_error duration already supplies an eviction deadline. See Cache Size and Item Limits (opens in a new tab).
A TTL alone does not bound how much a caching accelerator holds — a workload that keeps fetching new cache keys grows the acceleration indefinitely, and with caching_stale_if_error: enabled expired entries are deliberately kept as fallback material and are never expired away — a duration value instead derives an eviction deadline, so it keeps the fallback bounded. Two parameters put a ceiling on it:
| Parameter | Description | Default |
|---|---|---|
caching_max_size | Byte budget for the rows the acceleration stores, e.g. 512MiB or 1GB. A plain integer is a byte count. | None |
caching_max_items | Maximum number of rows the acceleration may keep, e.g. 100000. | None |
Both parameters refuse an unparseable value at load rather than falling back to unbounded — the parameter is the bound, so a silent default would leave a limit that was set but is not in force.
How eviction works:
A periodic sweep applies three bounds, in this order:
caching_ttl plus the caching_stale_while_revalidate_ttl grace period. They can no longer be served, so keeping them only consumes budget.caching_max_items.caching_max_size.Eviction is entry-granular: a cached response can span several rows (a paginated response is fetched a page at a time, each page carrying its own fetched_at), so all of an entry's rows are removed together. An entry is only as old as its oldest page, and the oldest entries are evicted first, so the cache always holds the most recent entries it can afford.
What the byte budget measures: the payload bytes of the rows the cache stores — text columns are measured exactly, fixed-width columns contribute their width, and the accelerator's own reserved caching columns are excluded. It is a payload measure, not an on-disk one: the engine's indexes and compression are not counted. The connector-managed response_headers map is also excluded and does not trigger the unmeasurable-column startup warning, so large or numerous headers can push real payload above caching_max_size. Columns of neither kind (for example a nested Map) cannot be weighed, and the runtime warns at startup naming any such column, since the budget under-counts by whatever they hold.
Sweep cadence: the sweep runs at caching_ttl, clamped to a minimum of 30 seconds and a maximum of 5 minutes. Between sweeps the acceleration may overshoot its budget by whatever the workload writes, so a budget is only ever as tight as the sweep enforcing it. When the dataset also sets retention_check_interval, the tighter of the two cadences is used.
retention_period and retention_sql apply in caching mode, and are evaluated per entry rather than per row — a rule matching one page of a paginated response removes the whole response. A retention rule is also a bound, so a dataset that sets one does not need a caching_max_* budget to be bounded.
Configurations that cannot be enforced are reported at startup, naming the dataset, so unexplained growth is diagnosable from the log:
caching_max_size under-counts.caching_stale_if_error: enabled with no caching_max_size, caching_max_items, retention_period or retention_sql — nothing evicts from this configuration at all.The caching_stale_if_error parameter controls whether — and for how long — expired cached data is served when the upstream data source returns an error during a refresh attempt. This provides fault tolerance by returning stale data instead of failing the query when the upstream source is temporarily unavailable.
It is Spice's implementation of RFC 5861 stale-if-error, and takes a duration as well as the two keywords:
| Value | Meaning |
|---|---|
A duration, e.g. 600s, 10m | stale-if-error=N. On an upstream error, an expired entry is served only while its staleness past caching_ttl is at most N; beyond N the upstream error propagates. 0 and 0s mean disabled. |
enabled | stale-if-error=∞. No age bound, so no eviction deadline is derived from this setting either — see the warning below. |
disabled (default) | stale-if-error=0. Never serve expired data; the upstream error propagates. |
The keywords match case-insensitively. Duration units are case-sensitive, as everywhere else in acceleration config: ms is milliseconds and Ms is microseconds. A boolean (true/false, quoted or as a YAML boolean) and infinity/inf are rejected at load with Invalid 'caching_stale_if_error' value: '<value>'. Expected a duration such as '600s', or 'enabled'/'disabled'. — the unbounded window is spelled enabled.
caching_ttl plus the longer of a finite caching_stale_if_error and caching_stale_while_revalidate_ttl is the entry's eviction deadline, and a pair whose sum does not fit a duration is a load error naming both values.
When caching_stale_if_error is enabled or a duration within which the entry still falls:
When caching_stale_if_error: disabled (default), or the entry is staler than the configured duration:
A finite window fails closed on an entry whose age is unknown. The staleness a duration is checked against is computed from the oldest fetched_at across the rows being served. When that timestamp is missing or null, the entry's age cannot be proven to be within the window, so it is not served and the upstream error propagates. enabled has no bound to check and serves the entry regardless.
The window is measured when the origin request starts. Spice computes an entry's staleness at the moment it sends the request to the origin, not when the origin's error or response arrives. A slow failure does not push an entry out of the window: with caching_stale_if_error: 5s, an entry that is 3 seconds past caching_ttl when the request starts is still served when the origin fails 10 seconds later.
An expired entry with no rows is not a fallback. When the expired entry holds zero rows, Spice has nothing to serve in place of the failing origin, so the query receives the origin's error, or its 429 or 5xx response, rather than an empty result.
A 429 or 5xx is a failed fetch. Once the HTTP connector has exhausted its own max_retries on a 429 or 5xx, the fetch fails with an error rather than returning the origin's error body as a row, so caching_stale_if_error acts on it and the cached entry is kept. A fetch that succeeds but returns rows carrying a 429 or 5xx response_status is treated the same way, and the periodic background refresh does not replace a good entry with such rows.
:::warning[caching_stale_if_error: enabled alone leaves the cache unbounded]
Expired entries are deliberately retained as fallback material for a failing origin, and enabled puts no age bound on them, so the expiry sweep removes nothing. Prefer a finite duration — caching_stale_if_error: 10m keeps the fallback for a bounded window and lets the sweep evict past it — or pair enabled with caching_max_size or caching_max_items (opens in a new tab) or a retention_period / retention_sql rule. The runtime warns at startup, naming the dataset, when enabled is loaded with none of those.
:::
:::warning[Stale-While-Revalidate Configuration Conflict]
When using refresh_mode: caching, you cannot configure both the caching accelerator's caching_stale_while_revalidate_ttl and the results cache (opens in a new tab)'s stale_while_revalidate_ttl for the same dataset. These parameters control similar behavior at different layers, and having both enabled creates a conflict.
Choose one approach:
acceleration.params.caching_stale_while_revalidate_ttl for HTTP-based dataset cachingruntime.caching.sql_results.stale_while_revalidate_ttl for SQL query results caching:::
TTL Considerations:
10s, 30s): More frequent refresh, higher data freshness, more API requests10m, 1h): Fewer refreshes, lower API costs, potentially stale datacaching_ttl shorter than refresh_check_interval to define the staleness windowacceleration.enabled: truerequest_path, request_query, request_body); a declared primary_key constrains stored-row uniqueness, not entry addressingrequest_path but no request_query can be answered with a response cached for a request to the same path with a query (spiceai/spiceai#14881 (opens in a new tab))./v1/datasets/:name/acceleration/refresh API triggers a new refresh for all cache keys defined in refresh_sqlrefresh_mode: caching and stale-while-revalidate. Caching Accelerator (opens in a new tab)