Connection pooling: go deeper

Build-up · 4 steps

Two servers, four clients

PgBouncer with two server connections and four clients, A to D. Each small box is one transaction.

Step 1 of 4

Each client keeps its server connection until it disconnects, idle gaps included. Two servers serve two clients; C and D wait.

4 interview moments

The interviewer pushes on the hard parts. Pick an approach and see how it holds up.

Deep dive 1 of 4
Interviewer
“Your orders service autoscales to 200 pods at dinner peak, each with a pool of 20. PostgreSQL allows 100 connections. What do you do?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“A Lambda function opens a PostgreSQL connection inside its handler. A burst brings 1,000 concurrent invocations. What do you change?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“A handler borrows a connection at the top, calls another service, runs 5 ms of SQL and returns the connection 50 ms later. What’s wrong?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“Thirty-second reports and 3 ms checkout queries share one pool. Checkouts start timing out whenever reports run. What do you do?”
Your move. Pick an approach:

Build-up · 7 steps

One pod at dinner peak. 500 requests a second, one query each, a pool of 20, 200 request threads and HikariCP’s default 30-second checkout timeout.

Step 1 of 7

A missing index or a lock makes queries take 400 ms instead of 4. Normally the pool runs about 2 connections busy.