DEV Community: lucy-reeves

taming burst traffic: pgbouncer + redis cache for low‑latency queries

lucy-reeves — Tue, 20 Oct 2026 18:02:19 +0000

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?

why pg‑bouncer can still choke your service under burst load

lucy-reeves — Sun, 18 Oct 2026 12:28:17 +0000

problem statement

in a typical microservice, each instance opens its own pg client pool. during a traffic spike the aggregate connection count can exceed the postgres max_connections limit, leading to FATAL: remaining connection slots are reserved for non-replication superuser errors. the usual fix is to front the database with pg‑bouncer, but many teams still see latency spikes and dropped requests.

why a naive pool still hurts

pg‑bouncer operates in transaction pooling mode by default. it hands out a physical backend connection only for the duration of a transaction, then returns it to the pool. if your service opens a transaction, performs a long async task, then commits, the connection sits idle while the task runs. bursts of such patterns quickly exhaust the pool, and pg‑bouncer starts queuing new client connections, inflating response times.

a tighter configuration

switching to statement pooling can mitigate the idle‑time problem, but it introduces its own edge cases: session‑level settings (e.g., SET timezone) and temporary tables are lost between statements. a pragmatic compromise is to keep transaction pooling but enforce a strict client‑side timeout and keep transaction bodies minimal.

# pgbouncer.ini
[databases]
mydb = host=10.1.2.3 port=5432 dbname=mydb pool_mode=transaction

[pgbouncer]
max_client_conn = 1000          # total connections from services
default_pool_size = 20          # per‑database backend connections
reserve_pool_size = 5           # extra connections for spikes
reserve_pool_timeout = 5        # seconds before using reserve pool
server_idle_timeout = 300       # close idle backends after 5 minutes

the reserve_pool_* settings give a small buffer for sudden bursts without permanently allocating more backends. combine this with a service‑level circuit breaker that backs off when the pool queue length exceeds a threshold.

trade‑offs to watch

statement vs transaction pooling – statement mode removes idle time but breaks any session‑state reliance; audit your code for SET commands or temp tables before switching.
reserve pool size – setting it too high can starve other databases on the same postgres instance; too low and spikes still cause queuing.
client timeout – aggressive timeouts prevent long‑running transactions from hogging connections, but may surface hidden latency bugs in upstream services.
monitoring – expose SHOW POOLS from pg‑bouncer and track total_wait and maxwait_us; a sudden rise signals that your reserve pool is being consumed.

production lesson

in our SaaS, moving from the default max_client_conn=200 to a tiered reserve pool cut 95th‑percentile latency from 800 ms to 250 ms during flash‑sale traffic, without increasing the postgres max_connections. the key was tightening client‑side transaction boundaries and adding a lightweight back‑off in the HTTP layer.

closing thought

how do you balance pg‑bouncer pooling mode, reserve pool sizing, and service‑side timeouts in a high‑concurrency stack

Why Your Connection Pool is Lying to You

lucy-reeves — Thu, 15 Oct 2026 03:24:06 +0000

We hit a wall last month. Traffic was stable, CPU was idle, but p99 latency spiked to 2s. The PostgreSQL server looked healthy. The culprit wasn't the database; it was our Node.js connection pool configuration.

The Illusion of Saturation

Most teams set pool.max based on a static assumption: "We have 20 connections, so we can handle 20 concurrent requests." This fails because connections aren't just resources; they are locks. When you hold a connection for 50ms, you block 19 others. When you hold it for 500ms due to a slow downstream call or a complex join, you starve the queue.

The standard pg pool in Node behaves like a fixed-size barrier. If all max connections are in use, new queries wait in a FIFO queue. There is no fallback. There is no read replica offload. Just waiting.

The Bottleneck

Our logs showed queryQueueLength climbing steadily. We weren't seeing duplicate queries, but we were seeing long-held connections. Specifically, we had a service that called an external API inside a transaction. The transaction had idleInTransactionSessionTimeout set to 60s. The API was timing out at 5s, but the connection stayed open until the timeout or manual commit.

// The mistake: holding a connection during external I/O
const client = await pool.connect();
try {
  await client.query('BEGIN');
  const response = await fetch('https://external-api.com/status'); // Blocks for 1-5s
  await client.query('UPDATE users SET last_synced = NOW() WHERE id = $1', [userId]);
  await client.query('COMMIT');
} finally {
  client.release(); // Too late, the queue was already backed up
}

The fix wasn't scaling out the postgres instance. It was architectural separation.

The Tradeoff

We split the lifecycle. We run the external API call outside the database transaction. Yes, this introduces a consistency window. If the API fails after we commit the intent, we have stale data. But in our case, eventual consistency was acceptable for sync status.

// Better: Short-lived transaction, external I/O separated
const updateResult = await pool.query(
  'BEGIN; UPDATE sync_state SET status = $1 WHERE id = $2; COMMIT', 
  ['pending', userId]
);

let finalStatus = 'failed';
try {
  const response = await fetch('https://external-api.com/status');
  finalStatus = response.ok ? 'success' : 'error';
} catch (e) {
  finalStatus = 'timeout';
}

await pool.query(
  'BEGIN; UPDATE sync_state SET status = $1 WHERE id = $2; COMMIT', 
  [finalStatus, userId]
);

The p99 dropped to 80ms. The queue depth flattened.

Edge Cases to Watch

Nested Transactions: If your ORM supports savepoints, be careful. SAVEPOINT doesn't release the underlying connection, but it adds overhead. Avoid them in tight loops.
Statement Caching: In Postgres 13+, prepared statements are cached per connection. If you have high-churn connection recycling, you might miss the cache benefits. node-postgres handles this, but custom drivers might not.
Monitoring: Don't just watch active_connections. Watch backend_xact_start age. Long-running transactions are the real killers.

Connection pooling is not a silver bullet; it's a buffer against mismanagement. If your code holds DB connections while talking to other services, you're not pooling, you're hoarding.

How do you handle external I/O dependencies that logically require a DB transaction context? Do you use saga patterns, or do you rely on idempotency and eventual consistency?