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:
|TABLE||Apply a given TTL to all SQL queries that utilize the specified table.|
|TEMPLATE||Apply a given TTL to a set of similar SQL queries.|
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;
TABLE TTL on the
PRODUCTS table for say 60 seconds, would mean that both queries would be cached for 60 seconds.
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.
If different TTL values are configured for different tables used by a query, the lowest TTL is derived.
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.
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:
TABLE - setting a table mode TTL applies to all queries that utilize that table.
TEMPLATE - a
TABLETTL can be overwitten by a
TABLETTL value will be used unless a
TEMPLATETTL value is found.