in a SaaS billing service we hit a sudden spike every time a new plan rolled out – the API surged past 10k rps and our PostgreSQL nodes started queuing, pushing response times past 300 ms. the fix landed on a hybrid connection‑pool + cache layer that kept the hot path under 50 ms without over‑provisioning the DB.
the bottleneck
when the burst hit, the primary culprit was a flood of identical SELECTs for subscription status. each request opened a fresh TCP socket, negotiated SSL, and then waited for a free backend connection. even with pgbench‑tuned max_connections, the kernel hit its limit and started rejecting new sockets, causing retries in the client library.
why pgbouncer?
pgbouncer sits between the app and PostgreSQL, reusing a small pool of persistent connections while multiplexing many client sessions. this reduces TLS handshake overhead and keeps the number of server‑side processes low. the trade‑off is that long‑running transactions can starve others if not timed out.
[databases]
mydb = host=pg-primary port=5432 dbname=mydb pool_size=100 reserve_pool=20
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
max_client_conn = 2000
default_pool_size = 50
reserve_pool_size = 5
server_idle_timeout = 300
the reserve_pool lets a few extra sessions bypass the main pool when the main one is saturated, preventing a hard wall at max_client_conn. however, setting it too high can let a burst swamp the DB again, so we couple it with a low server_idle_timeout to recycle idle connections quickly.
adding a redis read‑through cache
for the hot SELECT we introduced a Redis read‑through layer. the first request hits PostgreSQL, stores the result with a TTL, and subsequent requests pull from Redis. this cuts the DB load dramatically, but introduces cache‑staleness risk. we mitigated it by invalidating the key on any UPDATE/INSERT to the subscription table using a simple NOTIFY/LISTEN trigger.
CREATE OR REPLACE FUNCTION notify_subscription_change() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('subscription_change', TG_TABLE_NAME || ':' || NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sub_change AFTER INSERT OR UPDATE ON subscriptions
FOR EACH ROW EXECUTE FUNCTION notify_subscription_change();
the listener in the app clears the Redis entry, forcing a fresh DB read on the next request. the edge case to watch: if the listener crashes, stale data can persist until TTL expires, so we also set a max‑age fallback.
trade‑offs and failure modes
- pool sizing – too small and you get queue latency; too large and you risk exhausting DB resources.
- cache consistency – eventual consistency is acceptable for billing reads, but not for write‑through paths; always pair invalidation with a fallback DB fetch.
- network partitions – if Redis becomes unavailable, the app should gracefully fall back to DB reads; circuit‑breaker patterns help here.
results and next steps
after deploying pgBouncer with a 50‑connection pool and a 5‑second Redis TTL, the 10k rps burst settled to ~45 ms avg latency, and PostgreSQL CPU dropped from 85 % to 30 %. the system now handles future plan roll‑outs with headroom.
what patterns have you used to keep query latency low under bursty traffic, and how do you balance pool size against cache consistency?
