The pgpro_result_cache extension stores query results in shared memory for the database cluster. The cached data set is called the result cache. When the same query is executed again, PostgreSQL returns the cached result instead of re-running the query. That reduces server load and returns results in milliseconds. This article explains how to use pgpro_result_cache effectively.
Example: caching a query
By default, the extension is disabled. You can enable it at any level by setting: pgpro_result_cache.enable = on.
Results are cached only when the query contains the hint /+result_cache/:
/*+result_cache*/ explain (analyze) select '2+2';
...
Action: Execute and capture to cache
Execution Time: 0.101 ms
After the first run, the result is cached. The plan line shows Action: Execute and capture to cache.
On subsequent runs, the result is served from cache:
/*+result_cache*/ explain (analyze) select '2+2';
...
Action: Read from cache
Execution Time: 0.074 ms
The plan line Action: Read from cache confirms the result was returned without executing the query again.
Where caching helps
Good candidates are queries that scan a lot of data but return a small result set. Caching stores only the result, so memory use depends on result size.
Typical examples include grouped stats on web pages such as "spent this month", "spent this year", or "last 5 purchases". Instead of running the same query on every page view, you can cache the result.
Paging queries are another strong candidate. Catalog lists are often served page by page, and the UI needs page counts like select count(*) .... Cache those counts. You can also cache page slices like select * from ... LIMIT 10 OFFSET 20, which speeds up navigation and reduces database load from repeated execution.
Why not cache on the application side? It complicates application logic and is usually session-level, not shared. A shared cache is hard to maintain and ends up resembling a mini database layer. Teams often fall back to temporary tables as a cache, which pushes even more load onto the primary database. pgpro_result_cache avoids that by providing transparent, shared caching.
Shared cache across sessions
The cache is shared for the entire instance, so cached results can be returned to other sessions as well. This saves shared memory used for caching.
System catalog queries
Queries against system catalog tables are also cached. This is useful for permission or role checks. These queries are good candidates for caching. The cache does not affect the system catalog cache itself, which remains up to date.
Cached results do not survive a database restart. This is fine because result data usually has short freshness requirements and restarts are relatively rare.
pgpro_result_cache vs temporary tables
To compare query performance, use tables with many rows. A demo dataset is easy to load:
wget https://edu.postgrespro.com/demo-medium.zip
zcat demo-medium.zip | psql
psql -d demo
Create a temporary table with many rows:
create temp table tickets1 as select * from tickets;
select count(*) from tickets;
count
--------
829071
(1 row)
explain (analyze) select count(*) from tickets1;
Aggregate (cost=19881.60..19881.61 rows=1 width=8) (actual time=237.389..237.390 rows=1 loops=1)
-> Seq Scan on tickets1 (cost=0.00..18695.68 rows=474368 width=0) (actual time=0.022..146.932 rows=829071 loops=1)
Planning Time: 0.033 ms
Execution Time: 237.425 ms
explain (analyze) select count(*) from tickets;
Finalize Aggregate (cost=13012.67..13012.68 rows=1 width=8) (actual time=91.954..94.937 rows=1 loops=1)
-> Gather (cost=13012.46..13012.67 rows=2 width=8) (actual time=91.858..94.930 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
...
Planning Time: 0.056 ms
Execution Time: 94.979 ms
The temporary table query is slower (237.425 ms) than the regular table query (94.979 ms) because parallelism was not used for the temp table.
Parallelism for temporary tables
Some enterprise PostgreSQL distributions allow parallel workers on temp tables. To use it:
set enable_parallel_temptables = on;
ERROR: parameter "enable_parallel_temptables" cannot be set after connection start
Parallelism for temp tables must be enabled before the session starts. Enable it globally and reconnect:
alter system set enable_parallel_temptables = on;
select pg_reload_conf();
\c
You are now connected to database "demo" as user "postgres".
Create the temp table again:
create temp table tickets1 as select * from tickets;
SELECT 829071
explain (analyze) select count(*) from tickets1;
Finalize Aggregate (cost=17422.88..17422.89 rows=1 width=8) (actual time=99.590..102.993 rows=1 loops=1)
-> Gather (cost=17422.67..17422.88 rows=2 width=8) (actual time=99.498..102.986 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
...
Planning Time: 0.040 ms
Execution Time: 108.008 ms
Now the temp table query is faster (108.008 ms) thanks to parallel workers. The time reduction scales with the number of workers and gets close to regular table performance.
Caching results for temporary tables
Since temp table queries can be close to regular table performance, caching can still help. Run a cached query:
/*+result_cache*/ explain (analyze) select count(*) from tickets1;
...
Action: Execute and capture to cache
-> Finalize Aggregate (cost=17422.88..17422.89 rows=1 width=8) (actual time=92.794..96.184 rows=1 loops=1)
-> Gather (cost=17422.67..17422.88 rows=2 width=8) (actual time=92.707..96.176 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=16422.67..16422.68 rows=1 width=8) (actual time=88.329..88.330 rows=1 loops=3)
-> Parallel Seq Scan on tickets1 (cost=0.00..15928.53 rows=197653 width=0) (actual time=0.100..57.200 rows=276357 loops=3)
Planning Time: 0.049 ms
Execution Time: 97.428 ms
Now the result is cached. Repeat the query:
/*+result_cache*/ explain (analyze) select count(*) from tickets1;
...
Action: Read from cache
-> Finalize Aggregate (cost=17422.88..17422.89 rows=1 width=8) (never executed)
-> Gather (cost=17422.67..17422.88 rows=2 width=8) (never executed)
Workers Planned: 2
Workers Launched: 0
-> Partial Aggregate (cost=16422.67..16422.68 rows=1 width=8) (never executed)
-> Parallel Seq Scan on tickets1 (cost=0.00..15928.53 rows=197653 width=0) (never executed)
Planning Time: 0.045 ms
Execution Time: 1.000 ms
(11 rows)
The cached query runs in 1.000 ms versus 97.428 ms without caching, nearly 100x faster. Parallel workers are not used (Workers Launched: 0) because the full result is read from cache, which also reduces worker usage overall.
The key benefit is that cached results are returned instantly regardless of query complexity, without CPU or IO load.
There is no conflict with other sessions. If a session does not have the temp table, the query fails:
/*+result_cache*/ explain (analyze) select count(*) from tickets1;
ERROR: relation "tickets1" does not exist
If another session creates a temp table with the same name but different rows (limit 10), its results are cached separately:
create temp table tickets1 as select * from tickets limit 10;
/*+result_cache*/ explain (analyze) select count(*) from tickets1;
Custom Scan (ResultCache) (cost=14.25..14.26 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=1)
Cache Key: db_id: 24618, query_id: 4641162378291284321, const_hash: 0, param_hash: 0
Action: Execute and capture to cache
...
Planning Time: 0.080 ms
Execution Time: 0.121 ms
The result is cached under a different query_id: 4641162378291284321 because it is a different temp table in another session. Identical SQL text does not cause collisions because the planner distinguishes the underlying table.
Monitoring cached results
You can inspect cached entries in pgpro_result_cache_data:
select * from pgpro_result_cache_data;
db_id | query_id | const_hash | params_hash | created | exec_time_ms | hits | rows_count | data_size |
query_string
-------+---------------------+----------------------+-------------+----------------------------+--------------+------+------------+-----------+------------
------------------------
24618 | 5185884322440896420 | -3721919477379750894 | 0 | 2025-07-23 06:29:05.108726 | 0.095727 | 2 | 10 | 334 | select oid from pg_class limit 10;
24618 | 9093213827824582560 | 0 | 0 | 2025-07-23 07:04:40.728514 | 96.25065 | 1 | 1 | 186 | select count(*) from tickets1;
24618 | 4641162378291284321 | 0 | 0 | 2025-07-23 07:07:23.700508 | 0.066993 | 1 | 1 | 186 | select count(*) from tickets1;
(3 rows)
Entries are expired based on the TTL set by pgpro_result_cache.ttl.
In the current extension version, automatic invalidation when table rows change is not implemented yet. This is expected in a future Postgres Pro Enterprise release (v. 17.6.1).
Maximum cache lifetime (TTL)
The cache TTL is set with pgpro_result_cache.ttl at any level, including session and transaction. Example:
In session 1, set TTL to 1 second:
set pgpro_result_cache.ttl= '1s';
In session 2, the first run takes 54.140 ms, and subsequent runs are fast at 0.388 ms:
/*+result_cache*/ explain (analyze) select count(*) from bookings;
Time: 54.140 ms
If session 2 runs any cache-related query or reads pgpro_result_cache_data, the cache is cleared:
select * from pgpro_result_cache_data;
db_id | query_id | const_hash | params_hash | created | exec_time_ms | hits | rows_count | data_size | query_string
-------+----------+------------+-------------+---------+--------------+------+------------+-----------+--------------
(0 rows)
Queries in a session with a TTL set do not clear the cache unless they touch the cache.
Caching recommendations
For sessions connected to the same database, aim for similar TTL values. For long-running queries that return aggregate data, set TTL to 10-100x the query runtime. If a query takes 10 minutes, set TTL around an hour.
Another pattern is caching frequent queries that run in a few seconds. Set TTL to 10-100x their runtime, for example one minute.
If you try to mix both types in the same database with a one-minute TTL, long-running query caching becomes pointless because the cache expires too quickly.
Oracle Database has query and function result caches. If your application relies on this, migrating to an enterprise PostgreSQL distribution is easier because the behavior is similar.
In most applications, a small set of queries runs very often. For example, a web request may check whether a user has access to the requested data. The application server often connects as one database user, so a permissions query may run before every request. If access rights change infrequently, cache that query and invalidate the cache when rights are updated.
Look for extremely frequent queries and optimize them. The best candidates are queries that return small results, run often, and return the same data.
Caching reduces lock pressure on tables and indexes, and reduces contention on shared buffers. In practice, it removes a bottleneck and improves overall throughput.
Result caching for both queries and functions can be shared across sessions. In PostgreSQL, function results are cached because functions are invoked as SELECT function_name(params) or PERFORM function_name(params).
Query identification
The cache lives in shared memory and can serve results across sessions. A result is identified by a combination of database_id, query_id, const_hash, params_hash, and query_string.
If these properties match across sessions, the cached result from one session is returned in another. Look some examples:
Session 1:
/*+result_cache*/ explain (analyze) select 2;
QUERY PLAN
--------------------------------------------------------
Custom Scan (ResultCache) (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
Cache Key: db_id: 5, query_id: 2800308901962295548, const_hash: 16618514822564815477, param_hash: 0
Action: Read from cache
-> Result (cost=0.00..0.01 rows=1 width=4) (never executed)
Planning Time: 0.023 ms
Execution Time: 0.086 ms
(6 rows)
Session 2 gets the result from cache immediately:
/*+result_cache*/ explain (analyze) select 2;
QUERY PLAN
--------------------------------------------------------
Custom Scan (ResultCache) (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
Cache Key: db_id: 5, query_id: 2800308901962295548, const_hash: 16618514822564815477, param_hash: 0
Action: Read from cache
-> Result (cost=0.00..0.01 rows=1 width=4) (never executed)
Planning Time: 0.029 ms
Execution Time: 0.084 ms
(6 rows)
Very fast queries
If a query runs in fractions of a millisecond, caching can be slower than running the query:
/*+result_cache*/ explain (analyze) select now();
Action: Execute and capture to cache
Planning Time: 0.022 ms
Execution Time: 0.117 ms
/*+result_cache*/ explain (analyze) select now();
Action: Read from cache
Planning Time: 0.043 ms
Execution Time: 0.068 ms
explain (analyze) select now();
Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.015 ms
Execution Time: 0.010 ms
The plain now() call is faster (0.010 ms) than reading from cache (0.068 ms). Use pgpro_result_cache.min_exec_time to avoid caching very fast queries.
Memory usage
Check cache memory usage with pgpro_result_cache_stat:
select * from pgpro_result_cache_stat;
free_kb | entries | hits | inserts | evicts | cleanups | not_cached
---------+---------+------+---------+--------+----------+------------
63 | 1 | 32 | 48 | 0 | 0 | 0
(1 row)
/*+result_cache*/ explain (analyze) select count(*) from pg_class where oid=1;
select * from pgpro_result_cache_stat;
free_kb | entries | hits | inserts | evicts | cleanups | not_cached
---------+---------+------+---------+--------+----------+------------
62 | 2 | 32 | 49 | 0 | 0 | 0
(1 row)
/*+result_cache*/ explain (analyze) select count(*) from pg_class where oid=2;
select * from pgpro_result_cache_stat;
free_kb | entries | hits | inserts | evicts | cleanups | not_cached
---------+---------+------+---------+--------+----------+------------
61 | 3 | 32 | 50 | 0 | 0 | 0
(1 row)
At minimum, each cached query uses about 1 KB, which is relatively large. The cache stores not only results, but also metadata such as the query text and usage statistics.
Run 100 cached queries:
DO
$$
begin
for i in 1..100 loop
execute '/*+result_cache*/' || 'select ' || i::text;
end loop;
end;
$$
LANGUAGE plpgsql;
select * from pgpro_result_cache_stat;
free_kb | entries | hits | inserts | evicts | cleanups | not_cached
---------+---------+------+---------+--------+----------+------------
0 | 64 | 38 | 254 | 0 | 137 | 0
(1 row)
Only 64 results were cached because memory ran out. You can increase cache memory with pgpro_result_cache.max_memory_size and raise pgpro_result_cache.max_entries. The more restrictive limit will apply. The maximum entry count is 65534.
Caching prepared statements
Result caching works with prepared statements. Clear the cache and prepare a query:
select pgpro_result_cache_reset();
PREPARE q(integer) AS select /*+result_cache*/ count(*) from flights where flight_id > $1;
The query has not run, so no cached result exists. Run the query twice with different parameters:
EXECUTE q(1000);
EXECUTE q(1000);
EXECUTE q(900);
EXECUTE q(900);
select * from pgpro_result_cache_data;
db_id | query_id | const_hash | params_hash | created | exec_time_ms | hits | rows_coun
t | data_size | query_string
-------+---------------------+---------------------+----------------------+----------------------------+--------------+------+----------
--+-----------+----------------------------------------------------------------------
24618 | 8783690532955471044 | 3473803155467375404 | 6548121802712911902 | 2025-07-25 06:45:54.529826 | 15.425121 | 1 |
1 | 226 | select /*+result_cache*/ count(*) from flights where flight_id > $1;
24618 | 8783690532955471044 | 4661661556901396630 | -6467605202957820427 | 2025-07-25 06:46:10.911893 | 22.682562 | 1 |
1 | 226 | select /*+result_cache*/ count(*) from flights where flight_id > $1;
(2 rows)
Two rows means two results were cached. hits=1 shows that the repeated executions were served from cache.
Caching in functions and cursors
You can use /*+result_cache*/ inside a cursor in a function:
select pgpro_result_cache_reset();
CREATE or replace FUNCTION f1() RETURNS text
AS $$
DECLARE
var1 text := '';
r record;
BEGIN
FOR r IN (/*+result_cache*/ select distinct proowner from pg_proc)
LOOP
var1 := r.proowner::regrole;
END LOOP;
RETURN var1;
END
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Call the function twice:
select f1();
f1
----------
postgres
(1 row)
select f1();
f1
----------
postgres
(1 row)
The result is cached and reused on the second call (hits=1):
select * from pgpro_result_cache_data;
db_id | query_id | const_hash | params_hash | created | exec_time_ms | hits | rows_count | data_size |
query_string
-------+---------------------+------------+-------------+----------------------------+--------------+------+------------+-----------+---
-------------------------------------
24618 | 1529498166273990038 | 0 | 0 | 2025-07-25 07:13:52.391765 | 1.233447 | 1 | 1 | 190 | se
lect distinct proowner from pg_proc)
(1 row)
Conclusion
Result caching returns query results almost instantly and reduces load from frequent queries. It can cache both long-running queries and fast queries that run often. The major advantage is that the cache is shared across sessions, which improves overall application performance. We also reviewed an example of parallel query execution for temporary tables.
Top comments (0)