G.5. pgpro_result_cache — save query results for reuse #
G.5.1. Description #
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.
G.5.2. Installation #
The pgpro_result_cache extension is provided with Postgres Pro Enterprise as a separate pre-built package pgpro-result-cache-ent-16 (for the detailed installation instructions, see Chapter 17).
To enable pgpro_result_cache, complete the following steps:
Add the library name to the
shared_preload_librariesvariable in thepostgresql.conffile:shared_preload_libraries = 'pgpro_result_cache'
Note that the library names in the
shared_preload_librariesvariable must be added in the specific order. For information on compatibility of pgpro_result_cache with other extensions, see Section G.5.4.Restart the database server for the changes to take effect.
To verify that the
pgpro_result_cachelibrary has been installed correctly, run the following command:SHOW shared_preload_libraries;
Create the
pgpro_result_cacheextension using the following query:CREATE EXTENSION pgpro_result_cache;
It is essential that the library is preloaded during server startup because pgpro_result_cache has a shared memory cache that can be initialized only during startup. The pgpro_result_cache extension should be created in each database where query caching is required.
G.5.3. Hints #
G.5.3.1. The result_cache Hint #
If a valid (TTL/Time To Live is not expired) cached result set exists in memory, it is returned immediately without query execution and the hit count increments. Otherwise, the query is executed and the result set is stored in shared memory with the current timestamp.
/*+result_cache*/ select now() from generate_series(1,5);
G.5.4. Compatibility with Other Extensions #
The pgpro_result_cache extension employs flexible hint parsing, skipping any unrecognized tokens. All valid hints must conform to this syntax:
/*+ token_no_args token_no_args() token_with_args(optional, arguments()) */
Spaces are optional, except after token_no_args without parentheses. All parentheses must always be properly paired and closed.
The extension ignores non-conforming hints, eventually displaying warnings in the logs, and expects the same behavior from other extensions: they should follow the same hint syntax while disregarding unfamiliar tokens.
Note
pgpro_result_cache and the pg_hint_plan extension ignore each other's hints.
G.5.5. Cached Result Set Identification #
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.
G.5.6. Views #
G.5.6.1. The pgpro_result_cache_data View #
The pgpro_result_cache_data view shows all captured result sets. The columns of the view are shown in Table G.79.
Table G.79. pgpro_result_cache_data Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
query_id | bigint | Standard query ID |
const_hash | bigint | Hash of non-parameterized constants |
params_hash | bigint | Hash of the parameters used to execute the query |
created | timestamp | First caching timestamp |
exec_time_ms | real | Query execution time in milliseconds |
hits | int | Execution counter |
rows_count | int | Number of rows in the cached result set |
data_size | bigint | Total size (in bytes) of the result set in cache memory |
query_string | text | Query text in cache-readable format, without comments or EXPLAIN (ANALYZE) prefixes |
G.5.6.2. The pgpro_result_cache_stat View #
The pgpro_result_cache_stat view shows cache counters. The columns of the view are shown in Table G.80.
Table G.80. pgpro_result_cache_stat Columns
| Name | Type | Description |
|---|---|---|
free_kb | bigint | Available cache memory (in kilobytes), limited by pgpro_result_cache.max_memory_size (integer, kB) |
entries | bigint | Current number of cached entries, including expired ones (if not vacuumed), limited by pgpro_result_cache.max_entries (integer) |
hits | bigint | Number of successful cache retrievals |
inserts | bigint | Number of new entries, including replacements of expired ones |
evicts | bigint | Entries removed by LRU (Least Recently Used) policy due to the max_entries limit |
cleanups | bigint | Additional entries purged to meet the max_memory_size limit |
not_cached | bigint | Queries excluded from caching (too fast, too large, other conditions) |
G.5.7. Functions #
Only superusers can call the functions listed below.
-
pgpro_result_cache_reset() returns bool# Clears the cache and resets all result cache counters.
G.5.8. Configuration Parameters #
-
pgpro_result_cache.enable(boolean) # Enables the pgpro_result_cache functionality. The default value is
off. Only superusers can change this setting.-
pgpro_result_cache.max_memory_size(integer,kB) # Sets the size of shared memory used for result set caching. The default value is
64kB. This parameter can only be set at server start.-
pgpro_result_cache.max_entries(integer) # Sets the maximum number of cached result sets. The default value is
128. This parameter can only be set at server start.-
pgpro_result_cache.max_entry_size(integer,kB) # Sets the maximum memory consumption by a single result set. The default value is
16kB. Only superusers can change this setting. Must not exceedpgpro_result_cache.max_memory_size/ 2-
pgpro_result_cache.ttl(integer,s) # Sets the lifetime of a cache entry. The default value is
-1(disabled). Only superusers can change this setting.-
pgpro_result_cache.min_exec_time(integer,ms) # Sets the minimum execution time for a query. The default value is
-1(disabled). A positive integer means that queries with execution time less than this value will not be stored in cache. Only superusers can change this setting.
G.5.9. Important Notes #
When using the pgpro_result_cache extension, be aware of the following:
The extension caches results of non-immutable functions, causing subsequent calls to return identical output.
pgpro_result_cache does not track updates to cached data. It captures a result set at transaction time and maintains it until its TTL expires, even if the transaction is rolled back, not committed, or modified by another user. Subsequent queries will return the original cached data, even if the underlying data has changed.
Cached results bypass row-level security. Ensure sensitive queries are excluded from caching.