Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

Cache PostgreSQL prepared-query results

Experimental prepared-query result caching is available in the published ProxySQL 3.1.12 and 4.0.12 prereleases. Stable 3.0.12 does not include it. Eligible extended-query executions can reuse saved rows without another backend execution. Both the native PostgreSQL backend and the libpq backend support this cache path.

Result caching is separate from the prepared-statement metadata cache. It uses the existing query cache, its memory budget, TTL, and empty-result policy. There is no separate enable switch: existing rules with positive cache_ttl can select eligible prepared requests after upgrading. Review broad rules before enabling that behavior.

Check the client’s execution pattern

The client must complete preparation before the execution cycle:

Parse(named or unnamed statement) -> Sync -> preparation completes
Bind(unnamed portal) -> [Describe portal] -> Execute(max_rows=0) -> Sync

Parse prepares SQL, Bind supplies parameters, and a portal represents one execution. The statement can be named or unnamed, but the execution portal must be unnamed. Each execution cycle must contain one Bind and Execute, optionally Describe of that portal, and an explicit Sync. Execute must fetch the whole result without a row limit.

For example, libpq applications can use PQprepare to finish preparing a statement, then PQexecPrepared for each execution. A driver that combines Parse with execution, batches several executions into one cycle, or fetches rows incrementally does not use the cached exchange. SQL PREPARE and EXECUTE text commands are not a substitute for these protocol operations.

Text and binary parameters, NULL and empty values, zero parameters, and supported text or binary result formats work. Mixed per-column result formats are supported; requests for different formats can use separate entries.

Select a lookup and freshness policy

Choose a frequent read that may reuse an earlier answer. Suppose an existing account catalog_reader connects to database catalog and can read public_labels(id, label). The application prepares:

SELECT label FROM public_labels WHERE id = $1

In ProxySQL Admin, choose an unused rule ID and adapt the account, database, and exact SQL spelling:

INSERT INTO pgsql_query_rules
  (rule_id, active, username, database, match_pattern,
   cache_ttl, cache_empty_result, apply)
VALUES
  (970101, 1, 'catalog_reader', 'catalog',
   '^SELECT label FROM public_labels WHERE id = [$]1$', 5000, 0, 1);
LOAD PGSQL QUERY RULES TO RUNTIME;
SAVE PGSQL QUERY RULES TO DISK;

cache_ttl is milliseconds; 5000 permits five seconds of reuse. Reads do not restart that lifetime. Writes do not invalidate saved results. TTL also cannot make a lagging backend’s answer newer than it was when fetched. Keep reads that must confirm a write immediately on an uncached path.

This rule excludes empty results. Set cache_empty_result=1 to include them, or NULL to use pgsql-query_cache_stores_empty_result. Check rule order: apply=1 ends processing, so this example assumes no later rule must supply another action. An earlier terminating rule can prevent it from matching.

Understand bypasses and safety limits

Caching bypasses transactions, hostgroup-locked sessions, untracked startup options, and sessions with an attached backend that could hold local state. It also bypasses query rewrites, forced new connections, explicitly disabled multiplexing, replication-lag requirements, and a nonempty global pgsql-init_connect. Named portals, implicit synchronization, and unsupported execution-cycle shapes do not use this cache.

Only complete, successful, nonlocking SELECT results with columns qualify for storage. Notices, errors, command-only or zero-column results, multiple results, and responses already being streamed are excluded. Detected session-state changes also prevent insertion, as does a nonempty backend init_connect. Cache bypass leaves normal backend execution and existing protocol restrictions in effect.

These checks do not establish SQL purity or row-level-security safety. Avoid volatile functions, functions with side effects, temporary-table reads, or queries depending on untracked session or authorization context. Decide whether skipping execution is acceptable for the specific application read.

Observe hits and diagnose misses

Compare these Admin counters before and after repeated application executions:

SELECT Variable_Name, Variable_Value
FROM stats_pgsql_global
WHERE Variable_Name IN (
  'Query_Cache_count_GET', 'Query_Cache_count_GET_OK',
  'Query_Cache_count_SET', 'Query_Cache_Memory_bytes',
  'Query_Cache_Entries', 'Query_Cache_Purged',
  'Com_frontend_stmt_execute', 'Com_backend_stmt_execute');

GET counts lookups, GET_OK hits, and SET insertions. Repeated warmed requests should produce hits while avoiding corresponding backend executions. Compare changes with backend activity: counters include concurrent traffic, and cache metrics combine simple and extended queries.

If repeated requests miss, first check the exchange shape, rule order, and session eligibility. Then compare parameter values and types, formats, Describe requests, tracked settings, and destination hostgroups. Matching requests can share entries across connections, while different representations may need separate warmups. Simple-query and extended-query entries are separate.

Extended results share pgsql-query_cache_size_MB with simple-query results; memory limits are soft. Watch memory, entries, and purges when choosing the budget. pgsql-query_cache_soft_ttl_pct optionally starts refresh before hard expiry. See query-cache configuration.

Disable caching and flush results

To stop the example rule selecting requests:

UPDATE pgsql_query_rules SET active=0 WHERE rule_id=970101;
LOAD PGSQL QUERY RULES TO RUNTIME;
SAVE PGSQL QUERY RULES TO DISK;

Disabling the rule does not remove stored entries. Clear saved results immediately with:

PROXYSQL FLUSH PGSQL QUERY CACHE;

This clears the whole PostgreSQL result cache, including simple-query entries. Flush after changes to backend defaults, permissions, schema semantics, or hostgroup initialization settings. Enabling a hostgroup’s init_connect prevents new insertion but does not invalidate entries warmed beforehand.

For MySQL applications, see prepared-query result caching.