Question
Session pooling or transaction pooling for low‑traffic PostgreSQL workloads?
Rey Dey
0 reputation · 14 Nov 2024, 20:07 UTC
67.8K views1
Loading…
0 reputation · 14 Nov 2024, 20:07 UTC
Goal: Reduce the operating cost of a low‑traffic PostgreSQL deployment by minimizing idle backend memory consumption through PgBouncer pooling while keeping the application functional.
Constraint: Session pooling preserves temporary tables, prepared statements, and session‑level GUCs but allocates a dedicated backend for each pooled connection, increasing memory use even when idle. Transaction pooling frees backends after each transaction, lowering baseline memory, but discards session state, requiring the application to avoid or recreate temporary objects and prepared statements per transaction. Uncertainty remains about which mode offers the best trade‑off given occasional traffic bursts and the need to preserve compatibility without code changes.
Which pooling mode yields the lowest memory footprint without breaking applications that rely on temporary tables or prepared statements? How can we size the backend pool to avoid latency spikes during infrequent transaction bursts?
33275 reputation · 15 Nov 2024, 01:02 UTC
For low‑traffic PostgreSQL workloads, transaction pooling (PgBouncer in transaction mode) usually yields the lowest memory footprint while keeping the application functional, as long as the app does not rely on session‑state features such as temporary tables, prepared statements, or session‑level GUCs.
Transaction pooling reuses a single backend connection across many client sessions, freeing the backend after each transaction. This reduces the number of idle backends and thus the baseline memory consumption. Session pooling, by contrast, holds a dedicated backend for each pooled connection, preserving temporary objects and prepared statements but consuming more memory even when idle.
postgresqltransaction mode (pool_mode = transaction).max_client_conn to the expected maximum number of concurrent application connections and default_pool_size (or reserve_pool_size) to handle peak concurrent requests during bursts.pg_stat_activity to verify that the number of active backends stays near the pool size and that idle in transaction states are rare and short‑lived.To confirm whether transaction pooling is safe for your deployment, please specify: does your application rely on temporary tables, prepared statements, or any session‑level GUCs that must persist across transactions?
Use comments to ask for clarification. Post a solution as an answer.
5,280 reputation · 15 Nov 2024, 05:56 UTC
When you keep pool_size low (5–10) to trim idle memory, set reserve_pool_size to a few times that value. PgBouncer will temporarily allocate backends from the reserve pool when pool_size is saturated, keeping requests from queuing. Monitor pool_wait in pgbouncer_stats; a spike indicates the reserve pool is too small. Remember to bump max_db_connections by the same amount, otherwise the backend will reject the extra connections.
If the application occasionally needs to keep session state, you can switch only that user or database to session mode while keeping the rest in transaction mode.