08.07.2026 Problem:
Production Patroni-managed PostgreSQL 15.17 cluster (Patroni 3.3.2) experienced intermittent performance degradation that caused application delays and connection resets. The environment uses a connection pooler (PGBouncer) fronting ~1,600 read-only connections while the primary accepted ~1,600 direct read-write connections. The team reported long query latencies, mass connection timeouts, and large temporary file creation observed in database logs. Supplied configuration excerpts showed max_connections=4000, shared_buffers=92GB, work_mem=2MB, maintenance_work_mem=5GB, pg_stat_statements.max=15000, and idle_in_transaction_session_timeout=3600s.
Process:
Step 1: Intake — reproduce scope and collect artifacts
Client-provided logs, Patroni cluster state, and the postgresql.conf excerpt were reviewed to scope the incident. Observed symptoms in the artifacts: recurring high-latency OLTP queries, temporary files created by queries, and frequent connection resets. The cluster metadata confirmed a single primary with a streaming replica and the use of PGBouncer; these items established that primary-side I/O contention could directly impact client-facing latency.
Step 2: Log analysis — identify the most damaging SQL pattern
Database activity traces and log rotation files were scanned for long-running statements and temp-file generation. A repeated full-table reporting aggregation executed by a monitoring role was found to scan the largest partitioned tables and generate large temp files (~190 MB per run). That reporting job ran multiple concurrent copies (four observed) and aligned with spikes in disk I/O and OLTP latency. This explained how a monitoring workload could cascade to application-visible failures.
Step 3: Resource and configuration review — find contention points
postgresql.conf parameters were compared to workload needs. maintenance_work_mem was set high (5GB), allowing autovacuum workers to consume many GB of RAM concurrently; pg_stat_statements.max was large (15k) increasing view scan cost; idle_in_transaction_session_timeout was 3600s permitting sessions to hold locks for up to an hour; global work_mem was low (2MB) causing operations to spill to disk. Together these settings explained memory contention, blocked autovacuum, and excessive temp-file I/O under peak monitoring activity.
Step 4: Targeted mitigation — limit monitoring impact and free resources
Recommendations were aligned to the findings: avoid running heavy reporting on the primary (redirect monitoring queries to the replica or schedule as a controlled batch), and remove parallelism from that job (set max_parallel_workers_per_gather=0 for the job) to prevent starvation of parallel worker slots. Configuration changes to reduce resource contention were proposed: idle_in_transaction_session_timeout lowered to 60s to release locks quickly; maintenance_work_mem reduced to 1GB to cap autovacuum memory use; global work_mem increased modestly to 8MB while raising the monitoring role’s work_mem to 64MB to reduce temp-file writes from its frequent aggregates. Each proposed change targeted a specific bottleneck uncovered in steps 2–3.
Step 5: Implement changes and validate
The recommended configuration changes were applied to the PostgreSQL configuration and the server was instructed to reload settings. Monitoring workloads were redirected off the primary or converted to single-instance, non-parallel batch runs. Post-change metrics and logs were examined for reduced temp-file creation, lower disk I/O spikes, and decreased OLTP latency; these validation observations led into the implemented solution below.
Solution:
The implemented fix combined workload placement and PostgreSQL configuration tuning. Heavy reporting queries were moved to the replica or scheduled as non-parallel batch jobs (with max_parallel_workers_per_gather set to 0 for those runs). PostgreSQL configuration adjustments included setting idle_in_transaction_session_timeout to 60s, lowering maintenance_work_mem to 1GB, setting global work_mem to 8MB, and raising the monitoring role’s work_mem to 64MB, followed by a configuration reload.
Architecturally, these changes prevent monitoring workloads from saturating primary I/O and stealing parallel worker slots, reduce autovacuum memory contention, and limit long-held locks that block maintenance. Applying the changes at the PostgreSQL process level ensured the server enforced lower resource usage immediately without requiring full restarts.
Conclusion:
After the fixes, the environment showed improved stability: OLTP latency spikes subsided, temporary-file I/O reduced, and connection resets decreased. The combination of moving read-heavy reporting off the primary and tuning PostgreSQL memory/timeouts mitigated the risk of repeated cascade failures and restored predictable operational behavior.