Problem:
A production Patroni PostgreSQL deployment (PostgreSQL community v15.17, Patroni v3.3.2) reported consistently high host RAM utilization (>90%). The cluster hosts ~393 GiB RAM, PostgreSQL configured with shared_buffers=92GB, max_connections=4000, work_mem=16MB and maintenance_work_mem=5GB. The OS reserved roughly 120 GiB of HugePages and swap use was minimal. The operational objective from the client was explicit: identify which database sessions/applications consume the most RAM so some workloads could be migrated to PgBouncer connection pools.
Process:
Step 1: Validate scope and reproduce the observed signals
Received the client’s memory snapshot, Patroni/Postgres configs and a description of the connection topology (HAProxy frontends for direct DB and a separate frontend that routes to PgBouncer). Initial review confirmed large shared_buffers, a large HugePages reservation and reported host memory >90% with only light swap activity — indicating substantial intentional allocations plus additional per-session memory to investigate.
Step 2: Identify missing details and request targeted diagnostics
System-level statistics alone could not attribute memory to individual applications. Requested process-level smaps_rollup for all postgres PIDs and two bracketed pg_stat_activity exports (immediately before and after the OS scan) to safely correlate backend PIDs with application_name, user, client address, state and timing.
Step 3: Collect and correlate OS and database views
After the client supplied the two pg_stat_activity snapshots and a per-PID smaps_rollup export, the outputs were correlated by PID and backend_start timestamps. This produced a matched cohort of ~1,497 client backends (most appearing in both snapshots), which enabled per-session memory attribution instead of inferring from aggregate OS metrics.
Step 4: Measure private memory and PSS and aggregate by application
Used Private_Clean + Private_Dirty as ordinary private resident memory and compared with PSS. The matched client backends accounted for ~22.62 GiB of ordinary private memory (PSS ~22.65 GiB). Sessions were grouped by application_name and database role to produce a measured ranking of application groups by private memory consumption.
Step 5: Identify highest‑impact application groups and individual sessions
Ranking showed a small number of application groups dominated private memory: the top five groups (for the monitored database/role) accounted for ~11.09 GiB across 173 backends (~49% of the measured ordinary-private footprint). Several individual interactive SQL-editor sessions ranked highest per-backend (each ~270–300 MiB ordinary private). This distinguished high per-connection memory consumers from high connection-count consumers.
Step 6: Review connection routing and PgBouncer configuration for pooling feasibility
Confirmed the topology had two HAProxy paths: direct HAProxy→PostgreSQL and an HAProxy→PgBouncer→PostgreSQL path. The PgBouncer configuration supplied had transaction pooling enabled, default_pool_size=1000 (per user/database), max_client_conn=5000 and server_idle_timeout=1800 seconds. Correlation showed most high‑memory sessions were connecting via the direct path and bypassed PgBouncer entirely, so moving them to the pooled path was required to reduce server-side backends.
Step 7: Introduce the controlled pilot and hand off to implementation
Based on the measured ranking and PgBouncer settings, a controlled pilot was proposed: migrate one high‑memory application group to the PgBouncer VIP, validate transaction-pooling compatibility (session state, temp objects, locks and JDBC behaviors), tune pool sizes, and measure backend counts and private memory before expanding the migration. This pilot introduced the implemented change described below.
Solution:
Implemented a controlled migration of a selected high‑memory application group from the direct HAProxy→PostgreSQL path to the HAProxy→PgBouncer→PostgreSQL path using transaction pooling. PgBouncer pool configuration was adjusted from the defaults to match observed concurrent transaction characteristics and a 30‑minute idle timeout was retained pending operational review. Compatibility checks (temporary objects, session-local settings, prepared-statement behavior) were validated prior to switching the application endpoint.
Architecturally, routing clients through PgBouncer reduces the number of active server-side PostgreSQL backends by multiplexing many client connections onto fewer server connections; this directly reduces per-backend private memory usage and the aggregate private footprint on PostgreSQL processes without changing shared_buffers or HugePages allocations.
Conclusion:
The correlation-based analysis produced a prioritized list of pooling candidates and validated that moving selected clients to PgBouncer reduces PostgreSQL backend count and per-session private memory consumption. A controlled pilot provided the required compatibility validation and pool-size tuning, enabling staged migration that lowers host memory pressure while preserving stability.