UNDER PRESSURE

Level 2 · The Database Is the Bottleneck

Session 17: The Database Gatekeeper: PgBouncer, ProxySQL, & Transaction Pooling

Imagine 2,000 people want to talk to a single clerk, and we put a receptionist in front who only lets 100 people in at a time. Do the other 1,900 end up slower — or does the whole system actually get 10 times faster?

Session 17 / 344 min read

1. Imagine If

A popular restaurant has 10 dining tables and 1 cashier. If 500 customers are allowed to push their way into the kitchen, they bump into each other, knock plates out of the chefs' hands, and within 5 minutes the kitchen shuts down from the chaos.

So the owner puts a guard at the front door (a bouncer). The guard only lets 10 people in to the tables. Person number 11 waits patiently in an air-conditioned waiting room. The result? The chefs cook at full speed without interruption, tables turn over quickly, and all 500 people finish eating far sooner than when they were all allowed to storm the kitchen at once.

2. What Actually Happens

A Connection Pooler / SQL Proxy (like PgBouncer for PostgreSQL or ProxySQL for MySQL) acts as a smart receptionist in front of the database:

  1. Connection Multiplexing (M:N Mapping): - On the front side (client-side): PgBouncer can hold 10,000 idle connections from hundreds of application pods with a tiny RAM footprint (~2 KB per epoll socket connection). - On the back side (server-side): PgBouncer only opens 50–100 real physical connections to the database engine (matched to the database's CPU core count).

  2. Three Pooling Modes: - Session Pooling: A database connection is lent to the client for as long as the client's TCP session is alive. Once it disconnects, the connection is returned. (Safest, but the connection savings ratio is low). - Transaction Pooling: A database connection is lent only for the duration of a transaction (BEGIN ... COMMIT). As soon as the transaction finishes, the physical connection is immediately handed over to serve a transaction from another pod. (Saves 95% of server connections!). - Statement Pooling: A connection is lent for a single query only. (Very aggressive; multi-query transactions are not allowed).

  3. Why Does Limiting Connections Increase Throughput? - Following Queueing Theory (Session 3): when load exceeds service capacity, waiting time explodes exponentially. - By capping active database connections right at the optimal number for the CPU cores (e.g., 64 active processes on a 16-core CPU), there's no CPU thrashing and no runaway lock contention. Queries finish in 2 ms instead of 200 ms!

3. What If We Try...

"Let's just use Transaction Pooling in every application!"

Why can this break your application if you're not careful? - Prepared Statements: A PREPARE stmt query template is stored in the DB server session. If the next transaction runs on a different backend connection, the query fails with prepared statement does not exist (the fix: use the named prepared statement support in PgBouncer 1.21+ or disable prepared statements in the ORM). - Session State / SET Commands: Commands like SET TIMEZONE = 'Asia/Jakarta' or LISTEN / NOTIFY stick to the physical connection. If the connection gets passed to another pod, that setting can leak into another user's transaction (session pollution). - Advisory Locks: Session-level locks aren't automatically released on commit, so they stay hanging on the physical connection.

4. The Official Name

  • Connection Pooler: A connection multiplexing intermediary (PgBouncer, ProxySQL, AWS RDS Proxy).
  • Transaction Pooling: A mode that releases the backend connection at the COMMIT/ROLLBACK transaction boundary.
  • Admission Control: The technique of holding load back at the front gate to prevent collapse downstream.
  • Connection Multiplexing: Mapping thousands of client connections onto dozens of server connections.
  • DISCARD ALL / RESET: Commands that clean up connection state before it's reused.

5. In Our World

  • AWS RDS Proxy: A managed, automatic connection pooler service for PostgreSQL and MySQL, integrated with IAM auth and auto-failover.
  • Sidecar Pooler vs Central Pooler:
  • Sidecar: PgBouncer runs in every pod (fewer network hops, but total DB connections still grow with the number of pods).
  • Central Cluster: PgBouncer runs as a centralized cluster in front of the DB (full control over the DB's absolute connection limit).

6. The Performance Tester's Lens

Crucial metrics and tests: 1. Client Wait Time on Pooler: How many milliseconds does a client request wait in PgBouncer before it gets a database connection? 2. Server Active vs Idle Pool: Is the backend pool always 100% utilized (a sign it needs a bigger pool) or often idle? 3. Prepared Statement Error Rate: Watch the application error logs after migrating to transaction pooling. 4. Saturation Curve with Pooler: Prove that the throughput curve stays flat (plateau) and doesn't collapse (hockey stick) when simulating 5,000 virtual users.

7. Question for the Next Round

Database connections are now neatly under control. But what if 95% of all queries hitting the database turn out to be just reading data (SELECT profile, catalog, balance), not writing new data?

The answer is in Session 18: Reading Far More Often Than Writing (Read Replica & Replication Lag).