Tuning PgBouncer on a Modest VPS for Bursty Workloads

When Gatsu's POS traffic started climbing, the first symptom was not high CPU but hanging connections. Our PostgreSQL was idle, but clients could not get a slot. The fix: tune PgBouncer properly.

Pool mode: transaction, almost always

For stateless web apps like a POS, transaction pooling gives the best multiplexing ratio. Things to keep in mind:

• Avoid SET SESSION in app code — use SET LOCAL inside a transaction.
• Server-side cursors (DECLARE CURSOR) will not work; use client-side cursors or plain pagination.
• Prepared statements: use server_reset_query_always = 1 and make sure your ORM does not rely on plan cache across transactions.

The numbers I run on a 2 vCPU / 4 GB VPS

[databases]
gatsu = host=127.0.0.1 dbname=gatsu_prod

[pgbouncer]
pool_mode = transaction
max_client_conn = 600
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 1200
server_idle_timeout = 60
query_wait_timeout = 30
ignore_startup_parameters = extra_float_digits

A few notes:

• default_pool_size = 25 means at most 25 real connections per database. Match this with PostgreSQL's max_connections and the number of pools you run. Rough rule: max_connections ≥ Σ pool_size + superuser_reserve + 10.
• max_client_conn = 600 lets up to 600 clients feel connected concurrently. Works well with long HTTP keep-alives.
• reserve_pool_size is small but critical during bursty traffic — it gives the queue room to breathe.

Monitoring properly

I always wire up a small dashboard with these metrics:

• SHOW POOLS — is cl_waiting frequently > 0? Pool size is too small.
• SHOW STATS — avg_query_time per database. Sudden jumps are usually unindexed queries, not PgBouncer.
• App-side p95 latency. PgBouncer can hide Postgres issues if you only look at one side.

What I wish I knew earlier

PgBouncer is a critical component that gets treated as set-and-forget. In reality, the numbers above shift as traffic grows. Add proactive alarms on cl_waiting before users complain.