Connection pooling

At 8:04pm last Friday, Tiffin's error logs filled up with one line, thousands of times:

FATAL: sorry, too many clients already

The new dinner schedule from the last lesson had started 20 app servers. Each new server tried to open its database connections, and the database said no. After the big promotion the team had put the pools back to 10 per process and raised max_connections from 300 to 800, which covered 8 servers (640 connections) but not 20. Customers saw "Something went wrong" on checkout.

Here's the arithmetic that caused it:

20 app servers x 8 processes x 10 connections each = 1,600 connections wanted
max_connections on the database                    =   800

The fix is connection pooling, done properly. It's the cheapest database fix there is: no new hardware, no change to your data, often a few lines of config. And it usually makes the database faster, not just less crowded.

What a connection costs

A database connection isn't free, and it's more expensive than most people assume.

Opening one takes several round trips: a TCP handshake, a TLS handshake, then authentication. Postgres then starts a whole new operating system process to serve that connection. Altogether that's a few milliseconds on a fast network, sometimes 10-20 ms across zones with TLS. If every request opened a fresh connection, at 2,000 requests a second you'd be paying that cost 2,000 times a second.

Keeping one costs memory. Each Postgres connection is its own process, using several megabytes even when idle, and more while running big queries. A thousand connections can eat many gigabytes of memory that would otherwise be caching data. Lots of connections also add internal bookkeeping that slows every query a little.

So you want two things: don't open connections per request, and don't keep more open than you need.

What a pool does

A connection pool keeps a small set of connections open and lends them out.

A connection pool keeps a fixed set of open database connections. Each piece of work borrows one, uses it briefly, and gives it back, so many requests share a few connections.

%% caption: Requests borrow a connection from the pool and return it when the query finishes.
flowchart TD
  A(["Request A"]) --> P["Pool<br/>10 open connections"]:::hot
  B(["Request B"]) --> P
  C(["Request C"]) -. "waits if all 10 are busy" .-> P
  P --> D[("Database")]:::store

Almost every database library has one built in. In Node.js with pg:

const { Pool } = require('pg');

const pool = new Pool({
  max: 10,                        // at most 10 connections from this process
  idleTimeoutMillis: 30_000,      // close connections idle for 30s
  connectionTimeoutMillis: 2_000, // give up if none free within 2s
});

// borrows a connection, runs the query, returns it to the pool
const { rows } = await pool.query(
  'SELECT id, name, price_paise FROM menu_items WHERE restaurant_id = $1',
  [restaurantId]
);

Tiffin was already using pools like this. The problem wasn't missing pools. It was too many pools, each too big.

How many connections do you actually need?

Here's the surprising part. A database can only truly run as many queries at once as it has CPU cores (plus some extra for queries waiting on disk). Tiffin's database has 32 cores. If 300 queries arrive at once, it doesn't run 300 in parallel. It runs about 32 and switches between them, and all that switching adds overhead.

Little's law from Module 4 tells you how many connections are in use on average:

queries per second     = 7,700
average query time     = 2 ms = 0.002 s
connections busy       = 7,700 x 0.002 ≈ 15

At peak dinner, about 15 connections are busy on average. Allow for bursts and slower moments and you might need 60-100. Not 1,600. Not even 800.

A common rule of thumb for the number of connections that keep a database busiest:

connections ≈ (CPU cores x 2) + number of disks

For a 32-core machine with fast SSDs, that suggests somewhere around 70-100. It's a starting point, not a law. Load test with a few sizes and keep the one with the best throughput.

A smaller pool is often faster. Queries that wait briefly for a connection and then run on an uncrowded database usually finish sooner than queries that all start at once and fight each other.

Why per-process pools aren't enough

Each app process has its own pool, and they can't share. So the totals multiply:

You could shrink each pool to 2, but then a single process with a burst of requests has only 2 connections while another process sits on 2 idle ones. And every time autoscaling adds a server, the total grows again. You're fighting the multiplication.

The real fix is to put one shared pool between all the app processes and the database.

An external pooler

An external connection pooler is a small, fast proxy that sits in front of the database. App processes connect to it as if it were the database. It holds a small number of real connections to the database and shares them among everyone.

%% caption: The app keeps its pools. The pooler multiplexes thousands of cheap client connections onto 80 real ones.
flowchart TD
  A1["App server 1<br/>80 client connections"] --> PB
  A2["App server 2<br/>80 client connections"] --> PB
  A20["App server 20<br/>80 client connections"] --> PB
  PB["PgBouncer<br/>thousands in, 80 out"]:::hot -- "80 real connections" --> PG[("Postgres primary")]:::store

For Postgres, the classic choice is PgBouncer. Cloud providers offer managed ones too, such as Amazon RDS Proxy. Client connections to PgBouncer are cheap, a few kilobytes each, so 1,600 of them is no problem. The database sees a steady 80.

Pooling modes

The important setting is when a real connection goes back to the shared pool:

Transaction mode is what most web apps use. Your request runs a transaction, the pooler lends it a real connection for exactly that long, then takes it back.

The catch is that your next transaction may run on a different real connection. So anything that lives on a connection between transactions breaks:

• SET statements that change settings for the session, like a time zone,
• temporary tables,
• session-level advisory locks,
• LISTEN for notifications.

Prepared statements used to be on this list too. Recent PgBouncer versions can handle them in transaction mode, but check your version and driver. Most web apps don't use the rest, but check before switching, especially if your ORM does something clever.

Where the pooler runs

You can run one PgBouncer centrally, in front of the database, or one on each app server. Central gives you one place to cap total connections, but now it's in the path of every query, so run at least two and put them behind a load balancer or a floating IP. Per-server poolers have no new central point to fail, but each still opens its own real connections, so you need to size them so that the total stays under the limit.

Hold connections briefly

A pool only works if connections come back quickly. Here's a pattern that quietly drains it:

const client = await pool.connect();
await client.query('BEGIN');
await client.query('INSERT INTO orders ...');
const payment = await paymentProvider.charge(card, total); // 1-3 seconds!
await client.query('UPDATE orders SET payment_id = $1 ...', [payment.id]);
await client.query('COMMIT');
client.release();

During the payment call, the connection sits borrowed and idle, and the transaction holds row locks. At 30 checkouts a second and 2 seconds each, that's 60 connections tied up doing nothing. If the provider slows to 10 seconds, it's 300, and the whole pool is gone.

Never hold a connection, let alone a transaction, across a slow network call. Save the order, release the connection, call the provider, then save the result in a second short transaction. Module 16 covers how to keep the two steps consistent.

Two pool settings protect you from the cases you miss:

• A wait timeout. If no connection is free within, say, 2 seconds, fail the request with a clear error instead of hanging forever. Fast failure is easier to see and recover from.
• Leak detection. Many pools can log a warning when a connection is borrowed for longer than a threshold. That's how you find the code path that forgot to release one.

Tiffin after pooling

The changes:

1. Two PgBouncer instances in transaction mode, in front of the primary, allowing 80 real connections in total (40 each).
2. App pools stay at 10 per process, but they now connect to PgBouncer. 20 servers make 1,600 cheap client connections to PgBouncer and none to the database directly.
3. The checkout code no longer holds a transaction across the payment call.

Results on the next Friday: no "too many clients" errors, database memory freed up for caching, and p99 for menu reads down from 900 ms to 250 ms, because queries now run on a database that isn't juggling hundreds of them at once. Not bad for a config change.