In Module 5 you met connection pooling: instead of opening a new database connection for every request, each app server keeps a small pool of open connections and reuses them. That fix works beautifully for one server. It starts to break when you have sixty.
This lesson is about the piece that often appears next: a database proxy, a process that sits between your apps and the database, speaks the database's wire protocol, and manages connections centrally. We'll cover PgBouncer for Postgres and ProxySQL for MySQL, what they give you, and what they quietly break.
Sixty servers, one database
Tiffin has grown. At dinner time, the orders service for one region runs 40 API server pods, each with a pool of 20 connections, plus 15 background worker pods with 10 each. That's:
- 40 × 20 = 800
- 15 × 10 = 150
- 950 connections to one Postgres primary.
Most of them sit idle most of the time. A request holds a connection for maybe 5ms of a 50ms request. But Postgres has to pay for every one of them.
Why Postgres connections are expensive
Postgres runs one operating system process per connection. Each process has its own memory: a few megabytes at rest, often 10MB or more once it has run real queries and cached catalog data. Postgres also has per-connection bookkeeping that some operations scan through, so a thousand mostly-idle connections slow down everyone else a little, too.
The default max_connections is 100. You can raise it, but past a few hundred the overhead usually hurts more than it helps. A 16-core database server does useful work on maybe 30 to 60 queries at once. The other 900 connections are just overhead.