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-17
(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_libraries variable in the
postgresql.conf file:
shared_preload_libraries = 'pgpro_result_cache'
Note that the library names in the
shared_preload_libraries variable must be
added in the specific order. For information on compatibility of
pgpro_result_cache with other extensions,
see Section G.6.4.
Restart the database server for the changes to take effect.
To verify that the pgpro_result_cache library
has been installed correctly, run the following command:
SHOW shared_preload_libraries;
Create the pgpro_result_cache extension 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.
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);
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.
pgpro_result_cache and the pg_hint_plan extension ignore each other's hints.
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.
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 |
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) |
Only superusers can call the functions listed below.
pgpro_result_cache_reset() returns bool
#Clears the cache and resets all result cache counters.
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 exceed
pgpro_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.
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.