Your app works fine at 20 users.

At 200, Postgres starts rejecting connections.

At 2000, it doesn't matter how good your indexes are — nothing gets through.

This isn't a query problem. It's arithmetic you skipped.

Every connection is a process, not a thread

Postgres doesn't do threads per connection. It forks a whole OS process.

Each one carries its own memory: work buffers, sort space, cached query plans, TCP state. Conservatively 5-10MB per connection, often more under real workloads.

100 connections  × 8MB  ≈ 800MB
500 connections  × 8MB  ≈ 4GB
2000 connections × 8MB  ≈ 16GB

That's before a single query runs. It's the toll for existing.

Compare that to a thread in your app process — a few KB of stack. The asymmetry is the whole story: your app can spawn threads cheaply and hand each one a connection, and Postgres pays for every single one of them at process prices.

max_connections is a wall, not a suggestion

Default Postgres config: max_connections = 100.

Raise it and you're not "fixing" anything — you're moving where it breaks:

  • more RAM committed to idle connections
  • the query planner's shared caches get diluted across more backends
  • context-switch overhead climbs as the OS juggles more processes
  • a lock-heavy query now contends with 10x more potential holders

Past a few hundred real connections, throughput drops as you add more, not rises. This is not intuitive if you're used to thinking "more capacity = more concurrency" — for connections, it's the opposite past a threshold.

Where the connections actually come from

Nobody sets out to open 2000 connections. It accumulates:

Rails app servers   × pool size     (config: database.yml pool:)
  × process count    (Puma workers, or Unicorn/Passenger procs)
+ Sidekiq workers     × concurrency
+ Rake tasks, consoles, migrations left open
+ Every replica of the above across every deploy
─────────────────────────────────────────────
= way more than max_connections

A Rails app with pool: 25 running 8 Puma workers already wants 200 connections from web alone. Add Sidekiq with concurrency: 20 across 5 processes: 100 more. You hit max_connections = 100 before staging even boots.

The pool in database.yml is a lie of omission

production:
  pool: 25

This doesn't mean "25 connections, shared efficiently." It means each process gets its own pool of up to 25. Multiply by every process that loads Rails.

# What people think happens:
App (all processes) ←→ [ shared pool of 25 ] ←→ Postgres

# What actually happens:
Puma worker 1 ←→ [ pool of 25 ] ←┐
Puma worker 2 ←→ [ pool of 25 ] ←┤
Puma worker 3 ←→ [ pool of 25 ] ←┼→ Postgres
Sidekiq proc 1 ←→ [ pool of 25 ] ←┤
Sidekiq proc 2 ←→ [ pool of 25 ] ←┘

Each pool is per-process, in-memory. There is no cross-process sharing without something in front of Postgres that does the multiplexing.

PgBouncer: multiplexing, not magic

PgBouncer sits between your app and Postgres and does one job: it maintains a small number of real Postgres connections and hands them out to a large number of client connections on demand.

2000 client connections (cheap, app-side)
        ↓
    PgBouncer
        ↓
50 real Postgres connections (expensive, capped)

Your app still thinks it has hundreds of connections available. Postgres only ever sees the 50 that PgBouncer maintains. Neither side has to know about the other's reality.

The setting that matters is the pool mode:

  • session: one client gets one server connection for the whole session. Safest, least multiplexing. Barely better than no pooler.
  • transaction: a server connection is handed out per transaction, returned right after COMMIT. This is the one you want — it's where the multiplexing actually pays off.
  • statement: per-statement handout. Aggressive, breaks multi-statement transactions. Rarely worth it.

Transaction mode is what makes the 2000→50 ratio above realistic. It's also what breaks session-level features: prepared statements, advisory locks, SET outside a transaction, listen/notify. Rails' prepared statement cache is the one that bites people — disable it (prepared_statements: false in database.yml) when running behind PgBouncer in transaction mode, or you'll get cryptic "prepared statement already exists" errors under load.

Sizing the pool — the formula that actually works

The naive instinct is "more pool = more throughput." Past the point where Postgres has enough connections to keep every CPU core busy, more connections just means more contention.

A well-known starting formula (PostgreSQL wiki, borrowed from general concurrency theory):

connections = ((core_count × 2) + effective_spindle_count)

For a modern cloud instance with SSD-backed storage (effectively spindle_count ≈ 1):

8 vCPUs  → ~17 connections
16 vCPUs → ~33 connections

This feels absurdly low if you're used to max_connections = 100+. It is low — on purpose. Past that number, queries start waiting on CPU time instead of the connection itself, and the extra "capacity" is queueing latency wearing a disguise.

The real-world move: run PgBouncer with a real pool around this formula, and let PgBouncer's own queue absorb the burst instead of Postgres.

Common mistakes

1. Raising max_connections when connections run out.

Treats the symptom. RAM usage climbs, planner cache dilutes, and you'll hit the new ceiling anyway once traffic grows.

2. Assuming database.yml pool: is a global cap.

It's per-process. Multiply by worker count before you trust the number.

3. Running PgBouncer in session mode "to be safe."

Session mode barely multiplexes anything — you've added a hop with almost none of the benefit.

4. Leaving Rails' prepared statement cache on behind transaction-mode PgBouncer.

Works fine in staging at low concurrency, then throws unexplainable errors in production under load. Turn it off explicitly when pooling in transaction mode.

5. Forgetting Sidekiq (and every other background process) in the connection budget.

The web tier is rarely the whole story. Add up every process that opens a Rails connection pool.

6. Sizing the pool by vibes instead of core count.

"Let's just set it to 50" is not a strategy. Start from the core-count formula, measure, adjust.

Quick mental map

Connections dying under load?
   ↓
Count real demand: (app processes × pool) + (worker processes × concurrency)
   ↓
Compare against max_connections  →  usually demand wins, by a lot
   ↓
Put PgBouncer in front, transaction mode
   ↓
Disable Rails prepared statement cache
   ↓
Size the real Postgres-side pool from core count, not from hope
   ↓
Measure. Adjust. Repeat.

Conclusion

Connection exhaustion isn't a database problem. It's an arithmetic problem your app has been quietly accumulating since the first Puma worker was added.

max_connections was never meant to scale with your traffic — it was meant to protect Postgres from being asked to do the impossible.

PgBouncer doesn't remove the limit. It changes who's allowed to see it: your app gets to believe in abundance, and Postgres keeps its small, honest number of real connections underneath.

Do the math before production does it for you.