Skip to main content

Cache Configuration

What to Cache?#

As a best practice, it is recommended that once database queries are flowing through PolyScale, do not immediately configure any cache configurations (unless of course you have a clear understanding of what to cache). Rather leave the data to flow for a period of time so that PolyScale can monitor traffic and highlight queries that are high value candidates to be cached.

All database traffic flowing through PolyScale will be captured within the Observability tab of each cache. PolyScale provides visualizations and metrics to make it easy to examine query behavior. A Cacheability score is provided that indicates query candidates that are best suited to be cached, as well as a Recommended TTL value.

Query and Table Caching#

PolyScale provides a flexible configuration hierarchy which allows fine grained control over which queries are cached. This offers a mechanism to easily set Time To Live (TTL) values on large numbers of queries with a single action as well as set specific TTL values on individual queries as needed.

PolyScale offers two cache modes, which define which query responses are cached:

ModeDescription
TABLEApply a given TTL to all SQL queries that utilize the specified table.
QUERY TEMPLATEApply a given TTL to a set of similar SQL queries.

Table TTL#

Apply a given TTL to all SQL queries that utilize the specified table.#

Settling a Table TTL will apply the specified TTL value to all SQL queries that reference that table. For example, consider the following queries:

SELECT AVG(price) FROM products;
SELECT name FROM products WHERE category="shoes" LIMIT 50;

Setting a TABLE TTL on the PRODUCTS table for say 60 seconds, would mean that both queries would be cached for 60 seconds.

Multi-table Queries (JOIN's)#

For queries that utilize more than one table, TTL values must be set on all referenced tables before the query is deemed cacheable. For example, if a query joined across PRODUCTS and SALARIES tables, if a TTL value was set on one table and not the other, the query would not be cacheable.

tip

If different TTL values are configured for different tables used by a query, the lowest TTL is derived.

Query Template TTL#

Apply a given TTL to a set of similar SQL queries.#

A Query Template can be used to set a TTL value across a set of similar queries. Consider the following examples:

SELECT name, age FROM users WHERE id = 2;
SELECT name, age FROM users WHERE id = 6;
SELECT name, age FROM users WHERE id = 14;

Whilst these queries are syntactically different, many use cases require the ability to easily set a TTL value for all individual queries, even though they are unique. A query Template does exactly this. From a TTL lookup perspective, PolyScale normalizes the queries and removes the query parameters. In the above examples this yields the following query Template:

SELECT name, age FROM users WHERE id = ?;

A query Template can simply be thought of as a SQL query with the parameters removed which is assigned a TTL. Once a TTL is set on this template, all queries that match (once parameters are removed) inherit the TTL value.

Combining TTL Modes#

The two TTL modes aforementioned can be used in combination to offer unlimited flexibility with regard to what queries are cached. The TTL modes follow a specific hierarchy: TABLE > TEMPLATE:

  1. TABLE - setting a table mode TTL applies to all queries that utilize that table.

  2. TEMPLATE - a TABLE TTL can be overwritten by a TEMPLATE TTL. The TABLE TTL value will be used unless a TEMPLATE TTL value is found.