G.5. pgpro_result_cache — save query results for reuse

G.5. pgpro_result_cache — save query results for reuse
Prev	Up	Appendix G. Postgres Pro Modules and Extensions Shipped as Individual Packages	Home	Next

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-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'
```
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.
Enable the extension using the pgpro_result_cache.enable parameter.
```
SET pgpro_result_cache.enable = ON;
```

G.5.3. Usage #

To process a query by the pgpro_result_cache extension, use the result_cache hint in the special comment that begins with /*+ and ends with */.

/*+result_cache*/ select now() from generate_series(1,5);

If a valid cached result set for this query exists in shared memory, it is returned immediately without query execution and the hit count increments. Otherwise, the query is executed and the result set is saved to shared memory with the current timestamp. The pgpro_result_cache_data view shows all cached result sets.

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) for 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.

If automatic invalidation is disabled (default):

The extension caches results of non-immutable functions causing subsequent calls to return identical output.
The extension captures a result set at transaction time and then does not track updates to cached data even if the transaction is rolled back or not committed. Subsequent queries will return the original cached data even if the underlying data was changed by later transactions.

Note

Cached results bypass the row-level security policy. Ensure that sensitive queries are excluded from caching.

G.5.4. 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.5. Automatic Invalidation #

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:

Caches only queries that do not make changes to a database. Does not serve transactions (neither read nor write) that have uncommitted changes.
On each DML operation, such as INSERT, UPDATE, DELETE, or TRUNCATE, invalidates cached results associated with modified relations.
On each DDL operation, such as ALTER TABLE or DROP TABLE, invalidates cached results associated with modified relations.
Does not cache queries that use temporary tables.
Does not cache queries that use unlogged tables.
Does not cache queries that use custom functions or types.
Does not cache queries that use non-immutable functions.

Note

Automatic invalidation does not work for table partitions if their data was changed implicitly.

G.5.6. Compatibility with pg_hint_plan #

You can use the result_cache hint 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);

G.5.7. Views #

G.5.7.1. The `pgpro_result_cache_data` View #

The pgpro_result_cache_data view shows all cached 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 of the result set in cache memory, in bytes
`query_string`	`text`	Query text in cache-readable format, without comments or EXPLAIN (ANALYZE) prefixes

G.5.7.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
`entries`	`bigint`	Current number of cached entries, including expired ones (if not vacuumed), limited by pgpro_result_cache.max_entries
`hits`	`bigint`	Number of successful cache retrievals
`inserts`	`bigint`	Number of new entries, including replacements of expired ones
`evicts`	`bigint`	Entries removed by the Least Recently Used (LRU) algorithm due to the `pgpro_result_cache.max_entries` limit
`cleanups`	`bigint`	Entries removed by the Least Recently Used (LRU) algorithm due to the `pgpro_result_cache.max_memory_size` limit
`not_cached`	`bigint`	Queries excluded from caching (too fast, too large, other conditions)

G.5.8. Functions #

Only superusers can call the functions listed below.

pgpro_result_cache_reset() returns bool #: Clears the cache and resets all result cache counters. Returns true if cached result sets were removed and false if the cache was empty.

G.5.9. 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) #: Sets the size of shared memory used for result set caching, in kilobytes. 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) #: Sets the maximum memory consumption by a single result set, in kilobytes. The default value is 16kB. Only superusers can change this setting. Must not exceed pgpro_result_cache.max_memory_size / 2. The query text is stored in memory along with the result data, so this parameter value should be large enough to fit both. If set at runtime, it is applied only to new allocations and the cached data is not evicted automatically.
pgpro_result_cache.ttl (integer) #: Sets the lifetime of a cache entry, in seconds. The default value is -1, which means no lifetime limit. Only superusers can change this setting.
pgpro_result_cache.min_exec_time (integer) #: Sets the minimum execution time for a query, in milliseconds. The default value is -1, which means no time limit. 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.
pgpro_result_cache.consistent (boolean) #: Enables automatic invalidation of cached results. The default value is off. Only superusers can change this setting.
pgpro_result_cache.enforced (boolean) #: Enables pgpro_result_cache to cache result sets for all queries automatically regardless of the result_cache hint. The default value is off. Only superusers can change this setting. Changing this parameter resets the cache. This parameter is intended for debugging purposes and is not recommended for production.
pgpro_result_cache.invisible (boolean) #: Hides pgpro_result_cache information from the EXPLAIN output. The default value is off. Only superusers can change this setting. Changing this parameter resets the cache. This parameter is intended for debugging purposes and is not recommended for production.

Prev	Up	Next
G.4. pgpro_pwr — workload reports	Home	G.6. pgpro_stats — a means for tracking planning and execution statistics of all SQL statements executed by a server