There can be many reasons to make you want to cache SQL queries. Some of them are valid, e.g. reducing the number of round-trips (esp. when dealing with high-latency). Others might be micro-optimizations that are just not worth it. Regardless of your reasons for wanting to cache SQL queries, implementing them can be cumbersome.

Subject

I am going to use Slonik (PostgreSQL client for Node.js) and node-cache to demonstrate the usual way to implement cache and a declarative way to add cache to your existing codebase.

Let's assume a simple query-method to get country PK value using another unique identifier:

const getCountryIdByCodeAlpha2 = (
  connection: DatabaseConnectionType,
  countryCode: string
): Promise<DatabaseRecordIdType> => {
  return connection.maybeOneFirst(sql`
    SELECT id
    FROM country
    WHERE code_alpha_2 = ${countryCode}
  `);
};

This type of query is particularly common when ingesting data from external inputs (e.g. user submitted input or data that has been collected using scraping).

Measuring the problem

In the particular case that prompted me to explore caching, this query was called 7k+ times/ minute. Aside from this query, there were a dozen of other similar queries that collectively were executed well over 50k+ times/ minute. None of them affect my database server performance (PostgreSQL is already good at caching), but they:

• generate unnecessary logs
• increase the overall time needed to complete the task

The time it takes for PostgreSQL to execute such a query is minuscule, e.g.

EXPLAIN ANALYZE
SELECT id
FROM country
WHERE code_alpha_2 = 'gb';

Index Only Scan using country_code_alpha_2_id_idx on country  (cost=0.14..1.16 rows=1 width=4) (actual time=0.425..0.426 rows=1 loops=1)
  Index Cond: (code_alpha_2 = 'gb'::citext)
  Heap Fetches: 0
Planning Time: 0.069 ms
Execution Time: 0.439 ms

However, we have to also add the network time. In my case, the latency between the worker agent and the database is ~3ms.

ping ***.aivencloud.com                                                     17:31:54
PING ***.aivencloud.com (34.90.***.***): 56 data bytes
64 bytes from 34.90.***.***: icmp_seq=0 ttl=53 time=3.166 ms
64 bytes from 34.90.***.***: icmp_seq=1 ttl=53 time=2.627 ms
64 bytes from 34.90.***.***: icmp_seq=2 ttl=53 time=2.873 ms

That means that executing a query and getting the result takes at least 7.5ms (0.5ms query execution time + 2 trips). Put it another way, every 60 seconds, we waste ~350 seconds of computing time (spread across many servers). Overtime, this adds up to a lot (70 hours over month).

Implementing cache

All you need to implement cache is some storage service with a mechanism to limit how long and how many items can be stored. node-cache is such an abstraction for synchronously storing/ retrieving objects in memory. Using node-cache, you use set method to store cache and get method to retrieve cache; node-cache handles invalidation and storage limits behind the scenes. This is how getCountryIdByCodeAlpha2 would look like if it used node-cache:

const cache = new NodeCache({
  checkperiod: 60,
  maxKeys: 10000,
  stdTTL: 60,
  useClones: false,
});

const getCountryIdByCodeAlpha2 = async (
  cache: NodeCache,
  connection: DatabaseConnectionType,
  countryCode: string
): Promise<DatabaseRecordIdType> => {
  const maybeCountryId = cache.get(countryCode);

  if (maybeCountryId) {
    return maybeCountryId;
  }

  const maybeResult = await connection.maybeOneFirst(sql`
    SELECT id
    FROM country
    WHERE code_alpha_2 = ${countryCode}
  `);

  cache.set(maybeResult, maybeResult);

  return maybeResult;
};

However, this way of adding cache has a few disadvantages:

• It introduces a lot of boilerplate around every query.
• It introduces an additional dependency (NodeCache instance) that needs to be passed around throughout your codebase along with the database connection handle.

If you had to go this way, 9/10 I would say it is not worth it. Luckily, there is a better way.

Declarative cache

Slonik has a concept of interceptors (middlewares) that can be used to capture and modify SQL request and response. This makes them perfect for implementing cache. Such interceptor already exists: slonik-interceptor-query-cache.

slonik-interceptor-query-cache uses SQL comments to recognize which queries should be cached and for how long. Specifically, it searches for comment @cache-ttl. @cache-ttl comment indicates for how long the query should be cached. Queries without @cache-ttl are not cached at all, i.e. In order to cache the result of the earlier query for 60 seconds, the only change we need to make is to add a @cache-ttl comment to our query:

const getCountryIdByCodeAlpha2 = (
  connection: DatabaseConnectionType,
  countryCode: string
): Promise<DatabaseRecordIdType> => {
  return connection.maybeOneFirst(sql`
    -- @cache-ttl 60
    SELECT id
    FROM country
    WHERE code_alpha_2 = ${countryCode}
  `);
};

Now this query will be cache result for each unique countryCode for 60 seconds.

slonik-interceptor-query-cache does not implement storage, though. You can use node-cache, lru-cache, Redis, or any other storage engine. To use them, you simply need to abstract their interface using get and set methods, and provide them to slonik-interceptor-query-cache. Continuing with the node-cache example, this is how you would initiate Slonik with the query cache interceptor using node-cache as a storage engine:

import NodeCache from 'node-cache';
import {
  createPool
} from 'slonik';
import {
  createQueryCacheInterceptor
} from 'slonik-interceptor-query-cache';

const nodeCache = new NodeCache({
  checkperiod: 60,
  stdTTL: 60,
  useClones: false,
});

const hashQuery = (query: QueryType): string => {
  return JSON.stringify(query);
};

const pool = createPool('postgres://', {
  interceptors: [
    createQueryCacheInterceptor({
      storage: {
        get: (query) => {
          return cache.get(hashQuery(query)) || null;
        },
        set: (query, cacheAttributes, queryResult) => {
          cache.set(hashQuery(query), queryResult, cacheAttributes.ttl);
        },
      },
    }),
  ]
});

and that is it: with minimal code changes, now you can cache any query just by adding a comment to SQL. Among other benefits, this:

• allows you to quickly test the impact of caching a specific query
• allows you to quickly enable/ disable query caching (by simply adding/ removing the query cache interceptor)
• does not affect how you write test cases
• does not add boilerplate code to every query
• does not require to passthrough an additional dependency to every query invocation