The pgpro_result_cache extension caches query results in shared memory using hints, improving performance for subsequent query executions. Cached results are not persisted over server restarts.

The pgpro_result_cache extension is provided with Postgres Pro Enterprise as a separate pre-built package pgpro-result-cache-ent-18 (for the detailed installation instructions, see Chapter 17).

To enable pgpro_result_cache, complete the following steps:

Specify any of supported hints in the special comment that begins with /*+ and ends with */. Note that pgpro_result_cache reads only the first block of comments. If multiple hints are specified, only the last hint applies.

-- Caches the current time
SELECT /*+result_cache*/ now();

-- Returns the previously saved time from cache
SELECT /*+result_cache*/ now();

CREATE TABLE t as SELECT generate_series(1,20) x;

-- Caches values from 1 to 20, returns values from 1 to 10
SELECT /*+result_cache(limit 10)*/ x FROM t;

-- Returns values from 6 to 20
SELECT /*+result_cache(offset 5)*/ x FROM t;

-- Both queries return values from 6 to 15
SELECT /*+result_cache(offset 5 limit 10)*/ x FROM t;
SELECT /*+result_cache(limit 10 offset 5)*/ x FROM t;

-- Caches values from 6 to 20, returns values from 11 to 20
SELECT /*+result_cache(offset 5 limit 10)*/ x FROM t OFFSET 5;

-- Caches values from 1 to 10, returns values from 6 to 10
SELECT /*+result_cache(offset 5 limit 10)*/ x FROM t LIMIT 10;

SET pgpro_result_cache.enforced = on;

-- Caches the current time
SELECT now();

-- Returns the previously saved time from cache
SELECT now();

-- Returns the current time
SELECT /*+no_result_cache*/ now();

All cached result sets are stored in shared memory. You can get information about these result sets using the pgpro_result_cache_data view.

The pgpro_result_cache.max_memory_size parameter sets the maximum size of shared cache memory. The pgpro_result_cache.max_entries parameter specifies the maximum number of cached result sets. If any of these limits is reached when adding a new value, pgpro_result_cache removes result sets from cache memory using the Least Recently Used (LRU) algorithm.

You can also specify the lifetime (Time To Live, TTL) of cached result sets using the pgpro_result_cache.ttl parameter. A result set is considered valid and stored in cache memory until its TTL expires.

By default, automatic cache invalidation is disabled. Note the following specifics of the extension work:

A cached result set is identified by a combination of the database_id, query_id, const_hash, params_hash, and query_string attributes. database_id and query_id are the attributes assigned by the Postgres Pro Enterprise server.

const_hash represents a hash digest of all constants contained in the query. Constants with the same value but different types, for example, 1 and '1', will produce different hash values. 0 means there are no constants.

params_hash stores a hash digest of all parameter values used in the query. 0 means no parameters were defined.

The pgpro_result_cache extension supports automatic invalidation to ensure consistency of cached results by tracking database changes and applying specific restrictions. By default, automatic invalidation is disabled. To enable it, set the pgpro_result_cache.consistent parameter to on. Changing this parameter resets the cache.

When automatic invalidation is activated, the extension works as follows:

To enable automatic invalidation at the cluster level, set the pgpro_result_cache.consistent and pgpro_result_cache.wal parameters to on on the primary node and all standby nodes. In this case, the primary and each standby store their own cached result sets. Each DML or DDL operation on the primary causes automatic cache invalidation for the corresponding relations on standbys using WAL files.

You can use hints of pgpro_result_cache together with hints supported by the pg_hint_plan extension. Each extension reads its supported hints and ignores other hints.

/*+result_cache SeqScan(t1)*/
SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);

Only superusers can call the functions listed below.