Connection Pooling: Why Your Database Falls Over at 100 Users

Your app is fine in development. It is fine in staging. Then you launch, a hundred users show up at once, and the database does not slow down gracefully, it falls over. Queries that took 5ms now take 5 seconds or time out entirely. CPU is pegged but throughput has cratered. The instinct is to scale up the database. Usually the real problem is much cheaper to fix: you are opening too many connections.

A PostgreSQL connection is not a lightweight thing, and treating it like one is the most common way teams accidentally cap their own scalability. Understanding why is the difference between a one-line config change and a panicked database upgrade. Pooling is the third lever in our Postgres performance guide — the one that matters once your queries are already indexed and N+1-free.

A connection is a whole process

In PostgreSQL, every client connection gets its own backend operating-system process on the server. That process reserves memory for sorting, hashing, caching, and bookkeeping. It is not a thread, not a fiber, not a cheap coroutine. It is a full process, and the server pays for each one whether it is running a query or sitting idle.

This is why the PostgreSQL documentation ships a conservative default of 100 for max_connections. The limit is not arbitrary timidity; it reflects the per-connection memory and scheduling cost. A box that runs beautifully at 20 active connections can thrash badly at 200, because now the OS is context-switching between hundreds of processes all contending for the same CPU cores and memory bandwidth.

Throughput vs. concurrency on a typical 8-core database:
 
 connections   |  throughput
 ------------- | -----------------------------
   10          |  ##########  (climbing)
   20          |  ##################  (peak)
   50          |  ###############  (declining)
  100          |  ########  (thrashing)
  200          |  ###  (falling over)

The shape that matters: throughput rises, peaks somewhere near your core count times a small factor, then falls. More connections past the peak make things slower, not faster. This is counterintuitive if you imagine connections as free queue slots.

Where the 100 users go wrong

Here is the trap. Your web framework opens one database connection per request, or worse, per worker, and you run many workers. Do the arithmetic:

 10 app servers
  x 4 worker processes each
  x 5 connections per worker pool
  = 200 connections demanded from the database

Two hundred connections against a max_connections of 100 means half your workers get FATAL: sorry, too many clients already, while the hundred that do connect drag the server into thrashing. You have built a system that is guaranteed to overwhelm the database precisely when traffic is highest. And most of those connections are idle most of the time, holding a heavyweight server process hostage while they wait on the network or the application.

The key realization: the number of connections you need is governed by how many queries are running simultaneously, not by how many users or workers you have. Those are wildly different numbers. A hundred users clicking around generate only a handful of truly concurrent queries at any instant.

Pooling: reuse instead of reopen

A connection pooler sits between your application and the database and maintains a small, fixed set of real database connections. Application code borrows a connection, runs its query, and returns it to the pool in milliseconds. The same physical connection serves thousands of requests over its lifetime.

PgBouncer is the standard lightweight pooler for PostgreSQL. The decisive setting is the pool mode:

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
 
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
 
; transaction mode: a server connection is assigned only
; for the duration of a transaction, then handed to the
; next client. This is what lets 1000 clients share 20 connections.
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

With pool_mode = transaction, a backend connection is held only while a transaction is actually in flight. A thousand application clients can connect to PgBouncer, but they share a pool of just 20 real PostgreSQL backends. Since only a handful of transactions run at any instant, 20 backends is plenty, and the database stays comfortably inside its sweet spot.

Sizing the pool: smaller than you think

The most common mistake after adopting a pooler is making the pool too big, which recreates the original problem one layer up. A useful starting point comes from the PostgreSQL wiki's guidance and years of field experience: a good pool size is roughly

pool_size ≈ (number of CPU cores * 2) + effective_spindle_count

For an 8-core server on SSD storage, that lands somewhere around 16 to 25 connections, not hundreds. Counterintuitively, a smaller pool often produces higher total throughput, because the database spends its time executing queries instead of fighting over CPU and locks.

This is not unique to PostgreSQL. MySQL connections are threads rather than processes, but pooling still matters because each connection consumes memory and the server still has a practical concurrency ceiling; the MySQL documentation covers max_connections and thread handling. The principle is universal: bound your real concurrency to what the hardware can actually run in parallel.

Putting it together

The mental shift is from "one connection per user" to "one connection per concurrently-running query, shared by everyone." Concretely:

1. Stop opening a database connection per request or per worker directly against Postgres.
2. Point your application at a pooler (PgBouncer in transaction mode is the common choice).
3. Keep the real pool small, near your core count, not your user count.
4. Set max_client_conn high so clients are never rejected at the pooler; let the small server pool throttle real work.
5. Watch the database's active-connection count under load. It should stay flat near the pool size even as users climb.

Do this and the same database that "fell over at 100 users" will quietly serve thousands, on the same hardware, because it is finally doing the amount of concurrent work it was always able to handle.

Takeaways

• Each PostgreSQL connection is a full OS process with real memory and scheduling cost, which is why max_connections defaults to 100.
• Database throughput peaks near a small multiple of your core count, then declines; piling on connections past that point makes everything slower.
• "One connection per user/worker" demands far more connections than you have concurrent queries, and that gap is what topples the server under load.
• A pooler like PgBouncer in transaction mode lets thousands of clients share a tiny pool of real backends.
• Size the pool near cores * 2, not by user count. Smaller pools usually mean higher total throughput.