A Slow Express API and Intermittent 502s
A Node.js 20 / Express 4 service, orders-api, runs six containers behind a load balancer, each with a pg pool of max: 20. PostgreSQL is configured with max_connections = 200. At 200 req/s the p95 latency is 1.8 s and the p99 is 4.2 s, while container CPU sits at 25%. pg_stat_activity shows 118 connections in use, with eight sessions idle in transaction for several minutes. GET /orders/:id fetches the order row, then loops over its lines issuing one SELECT ... FROM products WHERE id = $1 per line. GET /orders does the same per order and accounts for 70% of traffic. Separately, about 0.3% of requests return 502 with no application error log, and the logs contain UnhandledPromiseRejection entries from a new POST /orders handler. That handler is async, awaits db.query, and its only try/catch wraps the final res.json call. The team's first proposal is to raise the pool max to 50.
1. Evaluate the proposal to raise the pool max to 50. What would you do first, and why?
Read the worked answer
The arithmetic alone kills the proposal: 6 containers × 50 = 300 potential connections against max_connections = 200. The first time traffic ramps, clients get FATAL: sorry, too many clients already, which turns a latency problem into an availability problem. Even 6 × 20 = 120 is already consuming most of the budget once you leave room for migrations, admin sessions and monitoring. Raising a pool does not create database capacity; each Postgres backend costs memory and context switching, and throughput per connection falls once you exceed available cores.
More importantly, the idle in transaction sessions say the pool is leaking rather than undersized. A session sitting in that state has executed BEGIN and never committed, rolled back or released the client - typically an error path that skips client.release(), or a client checked out for a multi-statement transaction with no finally. Those sessions hold locks, block vacuum, and make every other query wait, which is exactly the shape of a p99 of 4.2 s at 25% CPU.
Order of work: locate the leaking code path using pg_stat_activity with application_name set per service plus state_change timestamps; wrap checkouts in try/finally with release(); add idle_in_transaction_session_timeout and statement_timeout as backstops. Then fix query efficiency. Only then tune pool size - and consider PgBouncer in transaction mode, which lets many clients share far fewer real backends.
2. CPU is at 25% but p95 is 1.8 s. Explain where the time goes and how you would verify before changing code.
Read the worked answer
Low CPU with high latency is the signature of waiting, not computing. The handler makes one query for the order and then one query per line, sequentially. On GET /orders that multiplies: if a page returns 20 orders averaging 15 lines, one request issues roughly 300 sequential round trips. Each round trip is only a few milliseconds of network, which alone would not explain 1.8 s - but with 120 connections saturated and eight of them stuck in transaction, every one of those queries also queues for a pool client. Sequential round-trip time plus sequential acquire time, multiplied by 300, produces exactly this profile: high latency, idle CPU, and a fat p99 tail for requests that got unlucky in the queue.
Verify before rewriting. Enable pg_stat_statements and look at calls-per-execution for the product lookup; instrument the pool wrapper to count queries and acquire-wait per request and log the top 1% of requests; run EXPLAIN (ANALYZE, BUFFERS) on the order and line queries to rule out a sequential scan on a large table. A query counter alone will confirm the N+1 with a single request.
Then fix in this order: replace the loop with one set-based query (WHERE p.id = ANY($1::int[])) or a join, which removes 299 round trips; ensure supporting indexes exist on the join and filter columns; cache the product catalogue if it changes rarely, with a short TTL; reduce the page size on the list endpoint. Measure p95 after each step.
3. Explain the 0.3% of 502s and what a correct fix looks like beyond adding a try/catch.
Read the worked answer
Express 4 does not await handler return values. When an async handler rejects, Express never sees the rejection - there is no .catch attached - so it becomes an unhandled promise rejection. On Node 15 and later the default behaviour for unhandled rejections is to throw, which terminates the process. The container exits, in-flight requests on that socket get reset, and the load balancer returns 502 to those clients. Kubernetes then restarts the pod, which is why the failure rate is small but persistent: each crash costs a handful of requests.
The existing try/catch cannot help. It wraps res.json, but the throw happens at await db.query, outside it, and even a correct try/catch would need to call next(err) - a bare catch that swallows the error would return a hang instead of a crash.
Correct fix, in layers: wrap handlers in a helper such as const ah = fn => (req, res, next) => Promise.resolve(fn(req, res, next)).catch(next) (or use express-async-errors, or upgrade to Express 5, which forwards rejected promises automatically); add a four-argument error middleware that logs with a request id and returns a controlled 500; add unhandledRejection and uncaughtException handlers that log and exit after a grace period rather than continuing in an unknown state; add a readiness probe so the pod is removed from the load balancer before shutdown; and handle SIGTERM by closing the server and draining the pool.