Sizing a Postgres Connection Pool for a Small Turkey Booking App

A small tour-booking backend I help maintain runs on a 2 vCPU / 4 GB VM in a Turkish datacenter, Postgres 15, and a Go API that handles about 40 requests per second at peak. Last spring, right before the season started, the API started returning 500s under load. The error was pq: sorry, too many clients already. The app had a pool of 100 connections, the DB had max_connections = 100, and a cron job opened a second pool of 20. Nobody had done the arithmetic.

What I did was not clever. I made the pool size a function of the database, not of the app. First, I measured what a single request actually needs:

SELECT pid, state, wait_event_type, query
FROM pg_stat_activity
WHERE datname = 'bookings'
ORDER BY state;

Most of the time the connections were idle, not active. The API was holding connections during template rendering and an external payment call. I set MaxOpenConns to 25, MaxIdleConns to 25, and ConnMaxLifetime to 30 minutes, then added a pgbouncer in front with pool_mode = transaction:

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 200

The API now talks to pgbouncer, and pgbouncer holds at most 20 server connections per database. The cron job got its own pool of 5. The 500s stopped, and p95 latency dropped from 900 ms to 210 ms because the DB was no longer thrashing between 100 nearly-idle backends.

Why this works: Postgres forks a process per connection, and each one costs memory plus scheduler attention. A pool of 100 looks generous but mostly buys context switches. Transaction pooling is the right trade for a short-query workload, but it has a real cost: session state does not survive between transactions. SET, advisory locks, and prepared statements at the session level break. We had one query using SET LOCAL statement_timeout, which was fine, but a LISTEN/NOTIFY feature had to move to a dedicated direct connection.

Where it fails: if a request holds a transaction open while waiting on a slow external call, transaction pooling just moves the queue to pgbouncer, and you get timeouts instead of connection errors. I hit this with a payment provider that occasionally took 8 seconds. The fix there was not a bigger pool; it was moving the payment call outside the transaction. Also, ConnMaxLifetime shorter than the pgbouncer restart window will cause reconnect storms, which I learned the hard way during a deploy at 2 a.m.

The numbers above are from one VM and one workload, so treat them as a starting point, not a recipe. What I would genuinely like to know: how do people here decide between pgbouncer transaction mode and just keeping a small direct pool in the app, especially when the workload has a few long-running reports mixed in with the fast booking queries?