PostgreSQL in Practice #2 Connection Pooling: The max_connections Misconception and PgBouncer

The first PostgreSQL operational problem a growing service meets is usually not queries but connections. The symptoms are stock: FATAL: sorry, too many clients already, or connection counts that look fine while responses slow down anyway. This chapter covers the structure of the problem and its standard solution, connection pooling.

PostgreSQL connections are expensive #

One foundational fact: PostgreSQL creates one server process per connection. A process — not a thread. Every connection costs a fork and several MB of memory, and coordination overhead between processes grows with the count. Two consequences follow. First, a connect-per-request pattern is a disaster (connection cost dwarfs query cost). Second, raising max_connections to 5000 is not a solution.

The second one is counterintuitive. A fact confirmed repeatedly by benchmarks: once concurrently active connections exceed a few multiples of the CPU core count, total throughput goes down, not up. A 16-core server can genuinely execute only a dozen-odd queries at once; connections beyond that only add context switching and lock contention. A service that “needs 1,000 connections” actually needs 1,000 clients sharing a dozen-odd execution slots in an orderly way — and the thing that does the orderly sharing is a pool.

Layer 1: the application pool #

The first layer is your framework’s pool (HikariCP, SQLAlchemy’s pool, Go database/sql, and so on). It opens N connections at server start and requests borrow them, eliminating connect-per-request. It’s the pool covered in the SQLAlchemy course. With only a few app instances, this is all you need.

The problem is horizontal scaling. A pool of 20 per instance × 50 instances = 1,000 connections: each pool individually polite, their sum crushing the database. With serverless (Lambda and friends), the instance count itself is out of your control, which makes it worse. That’s when the second layer becomes necessary.

Layer 2: an external pooler like PgBouncer #

PgBouncer is a lightweight proxy standing between the apps and the database. It hands the apps as many client connections as they want while keeping only a small number of real connections to the database. The key setting is the pool mode.

The working default is transaction mode. Most web-app connections spend most of their time “borrowed but idle,” so tying real-connection occupancy to transactions lets thousands of clients ride on dozens of real connections. The price is explicit: after each transaction you may get a different real connection next time, so anything that leaves state on the session is off the table. Session-level prepared statements, session variables changed with SET, session-scoped advisory locks, LISTEN — all affected. (Recent drivers and PgBouncer versions have improved prepared-statement support, but “session state doesn’t work” remains the safe default instinct.)

In managed environments, the same role is sold as a product — on AWS, that seat is RDS Proxy.

A sense of the right size #

So how many real connections (the pool size)? The widely used starting formula is cores × 2〜4, adjusted by measurement (larger for workloads with lots of disk waits). What matters is the direction: when performance disappoints, shrinking the pool often improves it. The judgment material is the observation routine of the next chapter — look at active vs idle connections in pg_stat_activity; if most are idle, you have connections to spare, not a shortage.

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Summary #

• A PostgreSQL connection is one process. Connect-per-request is off-limits, and raising max_connections is not a fix.
• Beyond a few multiples of the core count, active connections reduce throughput. What’s needed isn’t connections — it’s orderly sharing.
• Layer 1 is the app pool. When instance counts multiply the total, add layer 2: an external pooler like PgBouncer.
• PgBouncer’s working standard is transaction mode, at the cost of session-state features (SET, session prepared statements, and so on).
• Start pool size at cores × 2〜4 and adjust by measurement. The idle ratio in pg_stat_activity is the judgment material — and that observation routine is next chapter’s topic.