Monitoring query performance with pg_stat_statements is standard operating procedure for many production PostgreSQL deployments. It operates with minimal overhead in the background, quietly tracking execution counts, runtimes, and a host of other valuable performance statistics.

However, beneath its simple exterior lies a shared-memory hash table governed by strict lock semantics. When massive, dynamic queries push that table to its capacity, an aggressive eviction mechanism kicks in. Under specific conditions, this routine can stall incoming queries long enough to trigger an unrecoverable failover loop in Patroni clusters running versions prior to 4.1.4.

Here is an analysis of how high-cardinality queries, hash table garbage collections, and Patroni health-check logic intersected to produce an unexpected outage scenario.

The Perfect Storm: 50,000+ Parameters and Hash Explosion

pg_stat_statements normalizes incoming SQL by replacing literal constants with parameter placeholders ($1, $2, etc.), hashing the normalized query string, and storing the aggregated metrics in a fixed-size shared memory hash table governed by pg_stat_statements.max (defaulting to 5,000 entries).

The problem begins when applications emit massive dynamic queries—specifically statements with tens of thousands of parameters:

SELECT id, status FROM orders WHERE id IN ($1, $2, $3, ... $50000);

While the PostgreSQL engine treats these parameters as values, two things happen at this scale:

1. Parameter Cardinality Variance: If the query generator dynamically constructs clauses based on variable input batch sizes (e.g., 49,812 parameters in one run, 49,905 in the next), each query has a distinct normalized structure.
2. Hash Saturation: Even if the parameter count is identical, due to implicit casting the normalized queries could differ in signature, the result being a completely new query ID which consumes an entry in the pg_stat_statements hash table.

When applications run a large number of queries with fluctuating signatures, the hash table rapidly approaches its capacity limit.

The Eviction Bottleneck: Shared Locks and Stalls

When a new (not-yet-seen) query is stored by pg_stat_statements, pgss_store (line 1355) promotes that lock to exclusive and when the hash table hits pg_stat_statements.max it executes the query & text-file garbage collector while still holding it. Every SQL query which is passed to pg_stat_statements will hang until this is complete:

/* Need exclusive lock to make a new hashtable entry - promote */
LWLockRelease(pgss->lock);
LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
...
/* If needed, perform garbage collection while exclusive lock held */
if (do_gc)
gc_qtexts();

To preserve shared memory integrity while clearing low-value entries (identifying statements with the lowest execution count or total time), the extension must coordinate updates across backend processes:

• pg_stat_statements relies on an exclusive lock (LWLock) over the shared state hash table during deallocation passes.
• The allocator must scan, sort, and evict candidates while backends simultaneously attempt to register new queries.
• Because processing massive 50k-parameter query strings involves computing large hash fingerprints, string copies, and scanning memory, the eviction cycle stalls.

It’s worth repeating that during this process, any incoming query attempting to acquire an entry in pg_stat_statements blocks waiting on the shared lock. In high-throughput environments dealing with dynamic multi-thousand-parameter queries, this garbage collection pause frequently exceeds two seconds.

Cascading Failures: statement_timeout Hits the Sentinel

A two-second lock stall might normally register as a minor transient query latency spike. However, many enterprise production environments define strict safeguard timeouts, such as setting statement_timeout (or application-level query timeouts) between 1 and 2 seconds on routine connection pools.

When garbage collection blocks the backend pipeline:

1. Active sessions waiting to register statements hit the timeout threshold.
2. The database raises ERROR: canceling statement due to statement timeout.
3. Client connections reset, thread pools saturate, and monitoring probes start throwing query failures.

This is where Patroni enters the failure chain and the final hole in the Swiss Cheese lines up.

Why Patroni (< 4.1.4) Was Affected

Patroni’s REST engine and guardian loop continuously evaluate the health of the primary node using periodic SQL queries to verify that PostgreSQL is responsive and capable of handling traffic. These SQL queries are prefixed with a hardcoded statement_timeout=2000 (2 seconds) to guard against unresponsive instances from stalling Patroni and preventing a failover.

In versions prior to Patroni 4.1.4, Patroni’s primary health check mechanism had a critical vulnerability when handling local query timeouts:

• pg_stat_statements Inheritance: The connection Patroni used to query PostgreSQL would inherit server-side defaults, allowing the health check queries to be subjected to pg_stat_statements collection, and any subsequent garbage collection slowdowns.
• Uncontrollable Failover Trigger: If the local check query errored out or timed out during the pg_stat_statements lock freeze, the Patroni loop treated the primary as non-viable or dead.
• Bypassing Safety Checks: Instead of treating the timeout as an internal, transient execution hiccup and validating alternative recovery paths (like retrying), the leader election lease was voluntarily abandoned.

Because Patroni deemed the primary dead, it released its Distributed Consensus Store (DCS) lock (etcd/Consul) triggering a sudden, uncontrolled cluster failover to a replica. Unfortunately once traffic shifts to the replica, the same application workload hits the new primary, causing another hash table saturation, another deallocation lock, and a cascade of repeated failovers.

Similar problems were described back in 2020, but the issue was usually diagnosed as poorly configured or overloaded hardware, or misconfigured Patroni timing settings. It’s only now that the relationship with statement_timeout=2000 and pg_stat_statements induced hangs is understood:

https://github.com/patroni/patroni/issues/1675
https://github.com/patroni/patroni/issues/1677

How It Was Resolved

Mitigating this failure requires addressing multiple links in the chain, protecting all of them to ensure there is no possibility of this recurring:

1. Upgrade Patroni to >= 4.1.4

A bug report was filed #3436, and the Patroni developers quickly addressed how timeouts and unexpected errors on health queries affected leader lease renewal with #3663. In version 4.1.4 and later, pg_stat_statements is turned off in the session that executes the healthcheck query, and query timeout anomalies are handled with significantly greater resilience, preventing the node from immediately giving up the DCS lease solely due to a transient execution error.

2. Sanitize and Batch Dynamic Parameter Queries

Avoid generating unnested arbitrary-length parameter lists. Instead of dynamic $1, ..., $N parameters, pass arrays to queries:

-- Bad: Creates a distinct statement footprint for every array length
SELECT id, status FROM orders WHERE id IN ($1, $2, ... $50000);
-- Good: Stable query signature regardless of array length
SELECT id, status FROM orders WHERE id = ANY($1::bigint[]);

Using = ANY($1) guarantees that whether you pass 5 IDs or 50,000 IDs, the query produces a single entry in pg_stat_statements, eliminating hash table churn.

3. Tune pg_stat_statements Configuration

If high parameter variance is unavoidable in legacy systems, then consider setting pg_stat_statements.track to none to avoid recording statistics, and only turn it back to all or top when data is needed for problem diagnosis.

4. Configure Per-User Settings

If you are stuck on Patroni < 4.1.4 and can’t make any other changes, then alter Patroni’s monitoring user so it never triggers pg_stat_statements:

ALTER ROLE patroni SET pg_stat_statements.track = 'none';

Takeaways

Extensions which use shared_preload_libraries are loaded directly into the database engine and get privileged access, so configuration and use of them must be rigorously reviewed to ensure stable operations.

When dynamic query generators pair with monitoring extensions, the cost of metadata bookkeeping can rival or exceed the work of query execution. Ensuring your cluster management orchestrators (like Patroni) are running current patch releases, and application code is utilizing array parameters instead of dynamic parameter expansion are simple, essential safeguards against query execution slowness.

This is not just a Patroni issue, as this garbage collection will affect any SQL running during the pg_stat_statements garbage collection. If you have been chasing unusual jitter in query execution times, then this could possibly be the reason.