PostgreSQL Extended Query Pooling & Caching

This page explains how PostgreSQL prepared statements interact with ProxySQL’s connection pool, how the global statement cache is bounded and cleaned up, how to track memory consumption, and how to monitor all of these behaviours through the admin interface. All content on this page applies to the PostgreSQL Extended Query Protocol as supported in ProxySQL v3.0.3+.

Connection Pooling Interaction

Statements live on specific backend connections

When ProxySQL prepares a statement on a backend connection in response to a client Parse message (or during lazy preparation at execute time), it assigns the statement a name of the form proxysql_ps_{n} scoped to that specific connection. A different backend connection — even one in the same hostgroup — knows nothing about it.

ProxySQL maintains a backend map per connection that links each process-wide global statement ID to the proxysql_ps_{n} name on that connection. The same logical statement can exist on multiple backend connections simultaneously, each with its own local name, all pointing at the same global cache entry.

Pooled connections retain their prepared statements

When a backend connection is returned to the pool after serving a request, any prepared statements on that connection are left in place. ProxySQL does not send Close messages to the backend when a client-side statement is closed. The next client whose Bind/Execute is routed to that same connection can reuse the already-prepared statement without incurring another Parse round-trip to the backend.

This lazy retention improves throughput for workloads where the same statements are executed frequently across different client sessions. It does, however, mean that backend connections accumulate statement state over time. The backend reference count on each global entry reflects how many connections currently hold that statement.

Connection destruction and cleanup

When a backend connection is destroyed — due to a backend error, idle timeout, or connection limit — ProxySQL walks all backend map entries associated with that connection and decrements the backend reference count on each referenced global entry. If both the client reference count and the backend reference count for a global entry subsequently reach zero, that entry becomes a candidate for eviction from the global cache. The actual removal is performed by a background purge cycle rather than inline with the connection teardown, so an entry may persist for a short period after all references are gone.

Statement Cache Management

The global statement map

ProxySQL maintains a process-wide cache of prepared statement metadata. Every unique statement that has been prepared at least once is recorded in this global map, keyed by a digest computed from the SQL text, the connected user, the target schema, and the protocol-level parameter types from the Parse message.

Each entry stores:

• The original SQL query text
• Parameter type metadata received from the Parse message
• A globally unique numeric ID used to cross-reference client and backend maps
• Client and backend reference counts

The global map is shared across all client sessions and all backend connections. When two clients prepare the same statement — same SQL, same user, same schema, same parameter types — they share one global entry rather than creating two independent records.

Cache size limit

The size of the global map is bounded by the pgsql-max_stmts_cache admin variable. When the number of cached entries reaches the configured limit, ProxySQL will not add new entries until space has been reclaimed by the background eviction process. Inspect and update this limit through the admin interface:

-- Check current limit
SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name = 'pgsql-max_stmts_cache';

-- Update the limit
SET pgsql-max_stmts_cache = 20000;
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;

Eviction heuristics

The background eviction process applies several conditions before removing a global entry:

1. Both reference counts must be zero. An entry with any active client or backend references is never evicted, regardless of how long it has been idle.
2. Minimum unused fraction threshold. Eviction only runs when the proportion of zero-reference entries exceeds a configured fraction of the total cache. This avoids unnecessary purge overhead when the cache is mostly active.
3. Rate-limited purge cycles. The purge loop runs on a timer rather than on every statement close, preventing bursts of Close operations from triggering continuous purge work.
4. ID recycling. When an entry is evicted, its global ID is returned to a free list and can be reused by a future statement, keeping the numeric ID space compact.

Normal cache lifecycle

During application startup the global cache grows steadily as new statements are prepared for the first time. Once all distinct statements in the application have been prepared at least once, the cache stabilises. Unused entries — statements that belonged to connections that have since closed and are no longer actively referenced — accumulate gradually and are removed by the periodic purge cycle. Under normal conditions the cache reaches a steady state with a small fraction of zero-reference entries waiting for the next purge pass.

Memory Tracking

ProxySQL accounts for the memory consumed by prepared statement state at several levels:

• Query text: the raw SQL string stored in each global map entry
• Parameter metadata: type descriptors for each bind parameter position
• Backend handles: per-connection bookkeeping for each global ID that has been prepared on that connection

Aggregate memory figures for these categories are exposed through the admin interface in the stats_memory_metrics table. Querying this table regularly is useful when diagnosing memory growth in long-running deployments:

SELECT *
FROM stats_memory_metrics
WHERE variable_name LIKE '%stmt%';

A steadily growing prepared-statement memory figure usually indicates one of two patterns:

• The application generates dynamic SQL with varying literals rather than parameterised queries, causing each unique statement text to be treated as a separate cache entry. The fix is to use bind parameters ($1, $2, etc.) so that structurally identical queries hash to the same global entry.
• Prepared statements are being accumulated but never closed, so their reference counts never reach zero and eviction cannot reclaim them. The fix is to ensure application code sends Close messages (or uses driver APIs that do so) when statements are no longer needed.

Both patterns are identifiable by combining memory metrics with the statement count and reference-count columns in the stats table described in the next section.

Monitoring and Observability

The stats_pgsql_prepared_statements_info table

ProxySQL exposes PostgreSQL prepared statement cache state through the stats_pgsql_prepared_statements_info admin table. The table presents one row per cached global statement and includes the SQL text, reference counts, associated user and schema context, and the digest used for cache lookups.

-- View all cached PostgreSQL prepared statements, most-referenced first
SELECT *
FROM stats_pgsql_prepared_statements_info
ORDER BY ref_count_client DESC;

-- View statements with no active references (eviction candidates)
SELECT global_statement_id, query, ref_count_client, ref_count_backend
FROM stats_pgsql_prepared_statements_info
WHERE ref_count_client = 0 AND ref_count_backend = 0;

-- Count total cached statements and how many are unreferenced
SELECT
    COUNT(*) AS total_cached,
    SUM(CASE WHEN ref_count_client = 0 AND ref_count_backend = 0 THEN 1 ELSE 0 END) AS zero_ref
FROM stats_pgsql_prepared_statements_info;

-- Find statements held open by many clients (useful for detecting connection leaks)
SELECT global_statement_id, query, schemaname, username, ref_count_client, ref_count_backend
FROM stats_pgsql_prepared_statements_info
WHERE ref_count_client > 10
ORDER BY ref_count_client DESC;

Key metrics to watch

Related Documentation

• PostgreSQL Prepared Statements Architecture
• PostgreSQL Prepared Statement Lifecycle
• PostgreSQL Prepared Statement Cache