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. If a primary_key is explicitly specified in the acceleration configuration, it will be used instead of the metadata fields.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 | arrow (default) | duckdb, sqlite, cayenne |
For optimal performance, combine the SWR pattern with file persistence:
This configuration provides:
The caching mode uses InsertOp::Replace to handle data updates. When new data is fetched for a given cache key (request metadata):
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 keyThe caching mode determines cache keys based on the acceleration configuration:
Default (No Primary Key Specified):
request_path, request_query, and request_bodyWith Primary Key Specified:
primary_key columns as the cache keyExample with custom primary key:
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.
The caching mode supports standard refresh configuration options. See Stale-While-Revalidate Pattern for background refresh details and Cache Persistence 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 | Behavior when cache returns no results (return_empty, use_source) | return_empty |
engine | Acceleration engine (arrow, duckdb, sqlite, cayenne) | arrow |
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_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 | When set to enabled, serves expired cached data if the upstream source returns an error. Valid values: enabled, disabled. | 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.
The caching_stale_if_error parameter controls whether 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.
When caching_stale_if_error: enabled:
When caching_stale_if_error: disabled (default):
:::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'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: trueprimary_key is specified, cache keys default to request metadata fields (request_path, request_query, request_body)/v1/datasets/:name/acceleration/refresh API triggers a new refresh for all cache keys defined in refresh_sqlmode | Persistence mode (memory or file) | memory |