spiceai/docs

spiceai/

docs

Help Login

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.6.x/features/caching/index.md

spiceai/docs | Spice Cloud Platform

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.6.x/features/caching/index.md

spiceai/docs/README.md

title: 'Caching' sidebar_label: 'Caching' description: 'Learn how to use Spice in-memory caching' sidebar_position: 3 pagination_prev: null pagination_next: null tags: [caching]

Spice supports in-memory caching for SQL query results and search results, which are both enabled by default when querying or searching via the HTTP (/v1/sql, /v1/search) and Arrow Flight APIs.

Results caching improves performance for repeated requests and non-accelerated results, such as refresh data returned on zero results.

The cache uses a least-recently-used (LRU) replacement policy. You can configure the cache to set an item expiration duration, which defaults to 1 second.

`caching` Parameters

Parameter name	Optional	Description
`sql_results`	Yes	Configures the Runtime cache for results from SQL queries.
`search_results`	Yes	Configures the Runtime cache for results from searches.

Common Caching Parameters

Every cache type (sql_results, search_results) supports the following parameters:

Parameter name	Optional	Description
`enabled`	Yes	Defaults to `true`.
`max_size`	Yes	Maximum cache size. Defaults to `128MiB`.
`eviction_policy`	Yes	Cache replacement policy when the cache reaches `max_size`. Defaults to `lru`, which is currently the only supported value.
`item_ttl`	Yes	Cache entry expiration duration (Time to Live). Defaults to 1 second.
`hashing_algorithm`	Yes	Selects which hashing algorithm is used to hash the cache keys when storing the results. Defaults to `siphash`. Supports `siphash` or `ahash`.

Choosing a `hashing_algorithm`

The hashing algorithm determines how cache keys are hashed before being stored, impacting both lookup speed and protection against potential DOS attacks.

siphash (Default): Uses the SipHash1-3 algorithm for hashing the cache keys, the default hashing algorithm of Rust. This hashing algorithm is a secure algorithm that implements verified protections against "hash flooding" denial of service (DoS) attacks. Reasonably performant, and provides a high level of security.
ahash: Uses the AHash algorithm for hashing the cache keys. The AHash algorithm is a high quality hashing algorithm, and has claimed resistance against hashing DoS attacks. AHash has higher performance than SipHash1-3, especially when used with cache_key_type: plan.

Consider using ahash if maximum performance is most important, or where hashing DoS attacks are unlikely or a low risk. More information on the security mechanisms of AHash are available in the AHash documentation.

`caching.sql_results` Parameters

In addition to the common caching parameters, sql_results also supports additional parameters:

Parameter name	Optional	Description
`cache_key_type`	Yes	Determines how cache keys are generated. Defaults to `plan`. `plan` uses the query's logical plan, while `sql` uses the raw SQL query string.

Choosing a `cache_key_type`

plan (Default): Uses the query's logical plan as the cache key. This approach matches semantically equivalent queries, even if their SQL syntax differs. However, it requires query parsing, which introduces some overhead.
sql: Uses the raw SQL string as the cache key. This method provides faster lookups but requires exact string matches. Queries with dynamic functions, such as NOW(), may produce unexpected results because the cache key changes with each execution. Use sql only when query results are predictable and consistent.

Use sql for the lowest latency with identical queries that do not include dynamic functions. Use plan for greater flexibility and semantic matching of queries.

Cached Responses

Responses from HTTP APIs include a header that indicates the cache status of the applicable cache:

Cache	Header Key
`sql_results`	`Results-Cache-Status`
`search_results`	`Search-Results-Cache-Status`

The value of the header indicates the status of the cache:

Header value	Description
`HIT`	The query result was served from the cache.
`MISS`	The cache was checked, but the result was not found.
`BYPASS`	The cache was bypassed for this query (e.g., when `cache-control: no-cache` is specified).
header not present	The cache did not apply to this query (e.g., when caching is disabled or querying a system table).

Examples

Cached Response

Uncached Response

Bypassed Cache with `cache-control: no-cache`

Cache Control

You can control caching behavior for specific requests using HTTP headers. The Cache-Control header helps skip the cache for a request while caching the results for subsequent requests.

HTTP/Flight API

The following endpoints support the standard HTTP Cache-Control header:

SQL query (HTTP and Arrow Flight)
Search (HTTP)

The no-cache directive skips the cache for the current request but caches the results for future requests.

Other Cache-Control directives are not supported.

HTTP Example

Arrow FlightSQL Example

The following example skips the cache for a specific query using FlightSQL in Rust:

The cache can be controlled using JDBC properties. For example,

`spice` CLI

The spice sql and spice search commands accept a --cache-control flag that follows the same behavior as the HTTP Cache-Control header:

Custom Cache Keys

Set the Spice-Cache-Key header to supply a custom cache key. When set, a supplied cache key takes precedence over caching.sql_results.cache_key_type.

:::info[Info] A valid cache key consists of up to 128 alphanumeric characters (and the characters - and _). :::

HTTP Example

Consider the case of two semantically equivalent queries:

Time: 0.0251325 seconds. 2 rows.
sql> select * from users where org_id = 1;
+----+--------+-------+----------------+
| id | org_id | name  | email          |
+----+--------+-------+----------------+
| 1  | 1      | Jane  | jane@spice.ai  |
| 2  | 1      | Sarah | sarah@spice.ai |
+----+--------+-------+----------------+

Time: 0.008993042 seconds. 2 rows.
sql> select * from users where split_part(email, '@', 2) = 'spice.ai';
+----+--------+-------+----------------+
| id | org_id | name  | email          |
+----+--------+-------+----------------+
| 1  | 1      | Jane  | jane@spice.ai  |
| 2  | 1      | Sarah | sarah@spice.ai |
+----+--------+-------+----------------+

To share a cache key for these queries, set Spice-Cache-Key. The first request is a cache miss:

The subsequent request with the different (but semantically equivalent) query is a cache hit:

:::warning[Note] When supplying a custom cache key, ensure the semantic equivalence of queries. For example, this is expected behavior:

:::

spiceai/docs/README.md

title: 'Caching' sidebar_label: 'Caching' description: 'Learn how to use Spice in-memory caching' sidebar_position: 3 pagination_prev: null pagination_next: null tags: [caching]

Spice supports in-memory caching for SQL query results and search results, which are both enabled by default when querying or searching via the HTTP (/v1/sql, /v1/search) and Arrow Flight APIs.

Results caching improves performance for repeated requests and non-accelerated results, such as refresh data returned on zero results.

The cache uses a least-recently-used (LRU) replacement policy. You can configure the cache to set an item expiration duration, which defaults to 1 second.

`caching` Parameters

Parameter name	Optional	Description
`sql_results`	Yes	Configures the Runtime cache for results from SQL queries.
`search_results`	Yes	Configures the Runtime cache for results from searches.

Common Caching Parameters

Every cache type (sql_results, search_results) supports the following parameters:

Parameter name	Optional	Description
`enabled`	Yes	Defaults to `true`.
`max_size`	Yes	Maximum cache size. Defaults to `128MiB`.
`eviction_policy`	Yes	Cache replacement policy when the cache reaches `max_size`. Defaults to `lru`, which is currently the only supported value.
`item_ttl`	Yes	Cache entry expiration duration (Time to Live). Defaults to 1 second.
`hashing_algorithm`	Yes	Selects which hashing algorithm is used to hash the cache keys when storing the results. Defaults to `siphash`. Supports `siphash` or `ahash`.

Choosing a `hashing_algorithm`

The hashing algorithm determines how cache keys are hashed before being stored, impacting both lookup speed and protection against potential DOS attacks.

siphash (Default): Uses the SipHash1-3 algorithm for hashing the cache keys, the default hashing algorithm of Rust. This hashing algorithm is a secure algorithm that implements verified protections against "hash flooding" denial of service (DoS) attacks. Reasonably performant, and provides a high level of security.
ahash: Uses the AHash algorithm for hashing the cache keys. The AHash algorithm is a high quality hashing algorithm, and has claimed resistance against hashing DoS attacks. AHash has higher performance than SipHash1-3, especially when used with cache_key_type: plan.

`caching.sql_results` Parameters

In addition to the common caching parameters, sql_results also supports additional parameters:

Parameter name	Optional	Description
`cache_key_type`	Yes	Determines how cache keys are generated. Defaults to `plan`. `plan` uses the query's logical plan, while `sql` uses the raw SQL query string.

Choosing a `cache_key_type`

plan (Default): Uses the query's logical plan as the cache key. This approach matches semantically equivalent queries, even if their SQL syntax differs. However, it requires query parsing, which introduces some overhead.
sql: Uses the raw SQL string as the cache key. This method provides faster lookups but requires exact string matches. Queries with dynamic functions, such as NOW(), may produce unexpected results because the cache key changes with each execution. Use sql only when query results are predictable and consistent.

Use sql for the lowest latency with identical queries that do not include dynamic functions. Use plan for greater flexibility and semantic matching of queries.

Cached Responses

Responses from HTTP APIs include a header that indicates the cache status of the applicable cache:

Cache	Header Key
`sql_results`	`Results-Cache-Status`
`search_results`	`Search-Results-Cache-Status`

The value of the header indicates the status of the cache:

Header value	Description
`HIT`	The query result was served from the cache.
`MISS`	The cache was checked, but the result was not found.
`BYPASS`	The cache was bypassed for this query (e.g., when `cache-control: no-cache` is specified).
header not present	The cache did not apply to this query (e.g., when caching is disabled or querying a system table).

Examples

Cached Response

Uncached Response

Bypassed Cache with `cache-control: no-cache`

Cache Control

You can control caching behavior for specific requests using HTTP headers. The Cache-Control header helps skip the cache for a request while caching the results for subsequent requests.

HTTP/Flight API

The following endpoints support the standard HTTP Cache-Control header:

SQL query (HTTP and Arrow Flight)
Search (HTTP)

The no-cache directive skips the cache for the current request but caches the results for future requests.

Other Cache-Control directives are not supported.

HTTP Example

Arrow FlightSQL Example

The following example skips the cache for a specific query using FlightSQL in Rust:

The cache can be controlled using JDBC properties. For example,

`spice` CLI

The spice sql and spice search commands accept a --cache-control flag that follows the same behavior as the HTTP Cache-Control header:

Custom Cache Keys

Set the Spice-Cache-Key header to supply a custom cache key. When set, a supplied cache key takes precedence over caching.sql_results.cache_key_type.

:::info[Info] A valid cache key consists of up to 128 alphanumeric characters (and the characters - and _). :::

HTTP Example

Consider the case of two semantically equivalent queries:

Time: 0.0251325 seconds. 2 rows.
sql> select * from users where org_id = 1;
+----+--------+-------+----------------+
| id | org_id | name  | email          |
+----+--------+-------+----------------+
| 1  | 1      | Jane  | jane@spice.ai  |
| 2  | 1      | Sarah | sarah@spice.ai |
+----+--------+-------+----------------+

Time: 0.008993042 seconds. 2 rows.
sql> select * from users where split_part(email, '@', 2) = 'spice.ai';
+----+--------+-------+----------------+
| id | org_id | name  | email          |
+----+--------+-------+----------------+
| 1  | 1      | Jane  | jane@spice.ai  |
| 2  | 1      | Sarah | sarah@spice.ai |
+----+--------+-------+----------------+

To share a cache key for these queries, set Spice-Cache-Key. The first request is a cache miss:

The subsequent request with the different (but semantically equivalent) query is a cache hit:

:::warning[Note] When supplying a custom cache key, ensure the semantic equivalence of queries. For example, this is expected behavior:

:::

version: v1
kind: Spicepod
name: app

runtime:
  caching:
    sql_results:
      enabled: true
      max_size: 1GiB # Default 128 MiB
      item_ttl: 1m # Default 1s
    search_results:
      enabled: true
      max_size: 1GiB # Default 128 MiB
      item_ttl: 1m # Default 1s

version: v1
kind: Spicepod
name: app

runtime:
  caching:
    sql_results:
      enabled: true
      max_size: 1GiB # Default 128 MiB
      item_ttl: 1m # Default 1s
    search_results:
      enabled: true
      max_size: 1GiB # Default 128 MiB
      item_ttl: 1m # Default 1s

$ curl -XPOST -i http://localhost:8090/v1/sql -d 'select * from taxi_trips limit 1;'
HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
results-cache-status: HIT
vary: origin, access-control-request-method, access-control-request-headers
content-length: 416
date: Thu, 13 Feb 2025 03:05:39 GMT

$ curl -XPOST -i http://localhost:8090/v1/sql -d 'select * from taxi_trips limit 1;'
HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
results-cache-status: HIT
vary: origin, access-control-request-method, access-control-request-headers
content-length: 416
date: Thu, 13 Feb 2025 03:05:39 GMT

$ curl -XPOST -i http://localhost:8090/v1/sql -d 'select * from taxi_trips limit 1;'
HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
results-cache-status: MISS
vary: origin, access-control-request-method, access-control-request-headers
content-length: 416
date: Thu, 13 Feb 2025 03:13:19 GMT

$ curl -XPOST -i http://localhost:8090/v1/sql -d 'select * from taxi_trips limit 1;'
HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
results-cache-status: MISS
vary: origin, access-control-request-method, access-control-request-headers
content-length: 416
date: Thu, 13 Feb 2025 03:13:19 GMT

$ curl -H "cache-control: no-cache" -XPOST -i http://localhost:8090/v1/sql -d 'select * from taxi_trips limit 1;'
HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
results-cache-status: BYPASS
vary: origin, access-control-request-method, access-control-request-headers
content-length: 416
date: Thu, 13 Feb 2025 03:14:00 GMT

$ curl -H "cache-control: no-cache" -XPOST -i http://localhost:8090/v1/sql -d 'select * from taxi_trips limit 1;'
HTTP/1.1 200 OK
content-type: text/plain; charset=utf-8
results-cache-status: BYPASS
vary: origin, access-control-request-method, access-control-request-headers
content-length: 416
date: Thu, 13 Feb 2025 03:14:00 GMT

# Default behavior (uses cache)
curl -XPOST http://localhost:8090/v1/sql -d 'SELECT 1'

# Skip cache for this query, but cache the results for future queries
curl -H "cache-control: no-cache" -XPOST http://localhost:8090/v1/sql -d 'SELECT 1'

# Default behavior (uses cache)
curl -XPOST http://localhost:8090/v1/sql -d 'SELECT 1'

# Skip cache for this query, but cache the results for future queries
curl -H "cache-control: no-cache" -XPOST http://localhost:8090/v1/sql -d 'SELECT 1'

let sql_command = arrow_flight::sql::CommandStatementQuery {
    query: "SELECT 1".to_string(),
    transaction_id: None,
};
let sql_command_bytes = sql_command.as_any().encode_to_vec();

let mut request = FlightDescriptor::new_cmd(sql_command_bytes).into_request();

request
  .metadata_mut()

let sql_command = arrow_flight::sql::CommandStatementQuery {
    query: "SELECT 1".to_string(),
    transaction_id: None,
};
let sql_command_bytes = sql_command.as_any().encode_to_vec();

let mut request = FlightDescriptor::new_cmd(sql_command_bytes).into_request();

request
  .metadata_mut

Properties props = new Properties();
props.setProperty("cache-control", "no-cache");
Connection conn = DriverManager.getConnection("jdbc:arrow-flight-sql://localhost:50051", props);

Properties props = new Properties();
props.setProperty("cache-control", "no-cache");
Connection conn = DriverManager.getConnection("jdbc:arrow-flight-sql://localhost:50051", props);

# Default behavior (use cache if available)
spice sql
# Same as above
spice sql --cache-control cache
# Skip cache for this query, but cache the results for future queries
spice sql --cache-control no-cache

# Default behavior (use cache if available)
spice search
# Same as above
spice search --cache-control cache
# Skip cache for this search, but cache the results for future searches
spice search --cache-control no-cache

# Default behavior (use cache if available)
spice sql
# Same as above
spice sql --cache-control cache
# Skip cache for this query, but cache the results for future queries
spice sql --cache-control no-cache

# Default behavior (use cache if available)
spice search
# Same as above
spice search --cache-control cache
# Skip cache for this search, but cache the results for future searches
spice search --cache-control no-cache

$ curl -i -XPOST http://localhost:8090/v1/sql -H"spice-cache-key: users_spiceai" -d "select * from users where org_id = 1;"
HTTP/1.1 200 OK
content-type: application/json
x-cache: Miss from spiceai
results-cache-status: MISS
vary: Spice-Cache-Key
vary: origin, access-control-request-method, access-control-request-headers
content-length: 119
date: Thu, 24 Jul 2025 14:15:53 GMT

[{"id":1,"org_id":1,"name":"Jane","email"

$ curl -i -XPOST http://localhost:8090/v1/sql -H"spice-cache-key: users_spiceai" -d "select * from users where org_id = 1;"
HTTP/1.1 200 OK
content-type: application/json
x-cache: Miss from spiceai
results-cache-status: MISS
vary: Spice-Cache-Key
vary: origin, access-control-request-method, access-control-request-headers
content-length: 119
date: Thu, 24 Jul 2025 14:15:53 GMT

[{"id":1,"org_id":1,"name":"Jane","email"

$ curl -i -XPOST http://localhost:8090/v1/sql -H"spice-cache-key: users_spiceai" -d "select * from users where split_part(email, '@', 2) = 'spice.ai';"
HTTP/1.1 200 OK
content-type: application/json
x-cache: Hit from spiceai
results-cache-status: HIT
vary: Spice-Cache-Key
vary: origin, access-control-request-method, access-control-request-headers
content-length: 119
date: Thu, 24 Jul 2025 14:18:00 GMT

$ curl -i -XPOST http://localhost:8090/v1/sql -H"spice-cache-key: users_spiceai" -d "select * from users where split_part(email, '@', 2) = 'spice.ai';"
HTTP/1.1 200 OK
content-type: application/json
x-cache: Hit from spiceai
results-cache-status: HIT
vary: Spice-Cache-Key
vary: origin, access-control-request-method, access-control-request-headers
content-length: 119
date: Thu, 24 Jul 2025 14:18:00 GMT

$ curl -i -XPOST http://localhost:8090/v1/sql -H"spice-cache-key: users_spiceai" -d "select 1"
HTTP/1.1 200 OK
content-type: application/json
x-cache: Hit from spiceai
results-cache-status: HIT
vary: Spice-Cache-Key
vary: origin, access-control-request-method, access-control-request-headers
content-length: 119
date: Thu, 24 Jul 2025 14:21:32 GMT

[{"id":1,"org_id":1,"name":"Jane","email":

$ curl -i -XPOST http://localhost:8090/v1/sql -H"spice-cache-key: users_spiceai" -d "select 1"
HTTP/1.1 200 OK
content-type: application/json
x-cache: Hit from spiceai
results-cache-status: HIT
vary: Spice-Cache-Key
vary: origin, access-control-request-method, access-control-request-headers
content-length: 119
date: Thu, 24 Jul 2025 14:21:32 GMT

[{"id":1,"org_id":1,"name":"Jane","email":

title: 'Caching' sidebar_label: 'Caching' description: 'Learn how to use Spice in-memory caching' sidebar_position: 3 pagination_prev: null pagination_next: null tags: [caching]

caching Parameters

Common Caching Parameters

Choosing a hashing_algorithm

caching.sql_results Parameters

Choosing a cache_key_type

Cached Responses

Examples

Cached Response

Uncached Response

Bypassed Cache with cache-control: no-cache

Cache Control

HTTP/Flight API

HTTP Example

Arrow FlightSQL Example

spice CLI

Custom Cache Keys

HTTP Example

title: 'Caching' sidebar_label: 'Caching' description: 'Learn how to use Spice in-memory caching' sidebar_position: 3 pagination_prev: null pagination_next: null tags: [caching]

caching Parameters

Common Caching Parameters

Choosing a hashing_algorithm

caching.sql_results Parameters

Choosing a cache_key_type

Cached Responses

Examples

Cached Response

Uncached Response

Bypassed Cache with cache-control: no-cache

Cache Control

HTTP/Flight API

HTTP Example

Arrow FlightSQL Example

spice CLI

Custom Cache Keys

HTTP Example

`caching` Parameters

Choosing a `hashing_algorithm`

`caching.sql_results` Parameters

Choosing a `cache_key_type`

Bypassed Cache with `cache-control: no-cache`

`spice` CLI

`caching` Parameters

Choosing a `hashing_algorithm`

`caching.sql_results` Parameters

Choosing a `cache_key_type`

Bypassed Cache with `cache-control: no-cache`

`spice` CLI