Skip to main content

Cache Configuration

PolyScale provides a flexible configuration hierarchy which allows fine grained control over which queries are cached. This offers a mechnaism 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.
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 cachable. 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 cachable.

tip

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

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 paramaters 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 heirachy: TABLE > TEMPLATE:

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

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