What is a database connection in Postgres?

A Postgres connection is a dedicated backend process, not a lightweight socket. The server forks a separate process for each connection, gives it its own memory for plans and buffers, and keeps it alive until the client disconnects. That cost is why Postgres caps `max_connections` near a hundred by default.

What does a connection pool actually do?

A connection pool keeps a fixed set of open connections and lends them to threads as they run queries. It avoids paying the fork, handshake, and authentication cost on every query. An ORM pool does this inside one process, while a pooler like PgBouncer does it across your whole fleet.

Does Rails pool database connections by default?

Rails pools connections within each process through ActiveRecord, sized by `pool:` in database.yml. That pool only manages the threads inside one Ruby process, and it has no view of the other processes hitting the same database. It cannot cap the fleet-wide total that Postgres actually enforces.

Do I need PgBouncer if I use Neon or Supabase?

No, because those services run the pooler for you behind a dedicated connection string. Neon, Supabase, and RDS Proxy all provide a transaction-mode pooler, so you point your app at the pooled endpoint instead of self-hosting PgBouncer. You still keep a direct connection for migrations.

You Should Care About Database Connections

I recently set up a dedicated PgBouncer pod for Designer Discount Club, and it is a good excuse to explain how database connections actually work. Most of us reach for an ORM like ActiveRecord and never look at what sits underneath it. The reason I needed PgBouncer is specific: DDC's background jobs are bursty, the Sidekiq workers autoscale hard under load, and without a pooler in front of Postgres they would open more connections than the database can hold. The examples below are Rails and Postgres, but the model applies to almost any framework and any database.

What is a database connection?

When your app opens a connection to Postgres, it does not get a lightweight handle into a shared server. Postgres uses a process-per-connection model: a supervisor called the postmaster forks a brand new backend process dedicated to that one connection, and that process lives until the connection closes. Opening it is not free either, because the client and server complete a TCP handshake, then authentication, then session setup before a single query runs.

flowchart LR
subgraph APP["Your app"]
  C1["Client conn 1"]
  C2["Client conn 2"]
  C3["Client conn 3"]
end
C1 -->|"TCP + auth"| PM
C2 -->|"TCP + auth"| PM
C3 -->|"TCP + auth"| PM
PM{{"postmaster<br/>(supervisor)"}}
PM -->|"fork()"| B1["Backend 1<br/>~5-10 MB"]
PM -->|"fork()"| B2["Backend 2<br/>~5-10 MB"]
PM -->|"fork()"| B3["Backend 3<br/>~5-10 MB"]
B1 --> SHM[("Shared buffers<br/>WAL / tables")]
B2 --> SHM
B3 --> SHM

One client connection maps to one dedicated Postgres backend process.

Each backend holds its own memory for query plans, caches, and sort buffers, on the order of 5 to 10 MB. Multiply that by a few thousand connections and the server spends its memory and its CPU scheduling processes instead of answering queries. This is why Postgres ships with max_connections set near 100 rather than 100,000. The limit is deliberately low.

A connection also carries state across queries, and prepared statements are the clearest example. By default, Rails uses server-side prepared statements. The first time it runs a given query shape, it sends a PREPARE so Postgres parses and plans the query once, then sends EXECUTE to reuse that saved plan on every later call.

sequenceDiagram
participant A as Rails (ActiveRecord)
participant B as One Postgres backend
A->>B: PREPARE s1 ("... WHERE id = $1")
B-->>A: parsed + planned, saved as s1
A->>B: EXECUTE s1 (id = 42)
B-->>A: rows
A->>B: EXECUTE s1 (id = 99)
B-->>A: rows
Note over B: the plan for s1 lives only on THIS backend

A prepared statement is parsed and planned once, then reused, on one specific backend.

That makes repeated queries faster, and it depends on your next query reaching the same backend process. A connection pooler can route that next query to a different backend, which is what breaks prepared statements later in this post.

What is connection pooling?

Opening a fresh connection per query would pay the fork, handshake, and authentication cost every single time, which is far too expensive for a busy app. A connection pool fixes that by keeping a fixed set of connections open and lending them out. A thread checks out a connection, runs its queries, and returns it to the pool for the next thread to use.

flowchart TB
subgraph PROC["One Ruby process"]
  direction LR
  T1["Thread 1"] ~~~ T2["Thread 2"] ~~~ T3["Thread 3<br/>(waiting)"]
end
PROC --> POOL
subgraph POOL["ActiveRecord pool, size 5"]
  direction LR
  S1["busy"] ~~~ S2["busy"] ~~~ S3["idle"] ~~~ S4["idle"] ~~~ S5["idle"]
end
POOL ==>|"reuses 5 real connections"| DB[("Postgres")]

A pool keeps a few connections open and shares them across many threads in one process.

In Rails this is the ActiveRecord pool, sized by pool: in config/database.yml and defaulting to five connections. Every Ruby process keeps its own separate pool. The pool only knows about the threads inside its own process, and it has no idea how many other processes are connected to the same database. That per-process scope is the detail behind most connection problems at scale.

How do autoscaled workers starve the pool?

DDC runs a Rails web tier and a Sidekiq background-job tier on GKE, all pointed at one Cloud SQL Postgres. I autoscale the Sidekiq workers with Kubernetes Event-Driven Autoscaling (KEDA), which adds and removes pods based on the Sidekiq queue depth rather than CPU or memory. Queue depth is the right signal here, because one catalog-sync job can enqueue tens of thousands of child jobs in seconds while CPU stays flat as the backlog builds.

Each worker pod runs ten Sidekiq threads and a connection pool of ten to match them. Three pods hold thirty connections, which is fine. A backlog that pushes KEDA to sixty pods holds six hundred connections, and Postgres is still capped near one hundred.

flowchart TB
subgraph FLEET["Sidekiq pods (KEDA-scaled)"]
  direction LR
  P1["Pod 1<br/>pool 10"]
  P2["Pod 2<br/>pool 10"]
  P3["Pod 3<br/>pool 10"]
  PMORE["..."]
  P60["Pod 60<br/>pool 10"]
end
P1 --> SUM
P2 --> SUM
P3 --> SUM
P60 --> SUM
SUM{{"600 connections requested"}}
SUM -->|"first 100 accepted"| OK[("Postgres<br/>max_connections = 100")]
SUM -->|"the rest rejected"| ERR["FATAL: sorry,<br/>too many clients already"]

Each autoscaled pod brings its own pool. Sixty pods want 600 connections against a 100-connection database.

The Rails config shows why nothing stops this. The pool size is set per process, so it scales linearly with the number of pods.

config/database.yml (production)

production:
  adapter: postgresql
  database: ddc_production
  # Per process. Sixty pods means sixty separate pools of this size.
  pool: <%= ENV.fetch("RAILS_MAX_THREADS", 10) %>
  host: <%= ENV["DATABASE_HOST"] %>

There is no setting in here for the total across the fleet, because Rails has no concept of the fleet. The database enforces the only global limit that exists, max_connections, and it rejects every connection past that limit with FATAL: sorry, too many clients already. The workers would hit that wall long before they drained the queue.

How does PgBouncer fix it?

PgBouncer is a connection pooler that runs as its own process between your fleet and Postgres. Your apps and workers connect to PgBouncer, which is cheap to connect to, and PgBouncer keeps a small set of real Postgres connections that it lends out on demand. It multiplexes thousands of client connections onto a few dozen real ones.

flowchart TB
subgraph CLIENTS["Apps + workers"]
  direction LR
  X1["client"]
  X2["client"]
  X3["client"]
  X4["client"]
end
CLIENTS ==>|"up to 5000 clients"| PB
PB{{"PgBouncer<br/>pool_mode = transaction"}}
PB ==>|"~25 real backends"| DB[("Postgres<br/>max_connections = 100")]

PgBouncer multiplexes many cheap client connections onto a few real Postgres backends.

How aggressively it multiplexes is set by pool_mode.

Mode	Backend returns to the pool	Safe for	Catch
`session`	when the client disconnects	everything	barely better than no pooler under fan-out
`transaction`	at the end of each transaction	most apps	breaks cross-transaction session state
`statement`	after each statement	autocommit-only workloads	forbids multi-statement transactions

Transaction mode is the one that does the real work. A client holds a real backend only from BEGIN to COMMIT, and then PgBouncer returns it to the pool, so idle clients between transactions hold nothing. That is how twenty-five real connections can serve six hundred clients at once.

pgbouncer.ini

[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb
 
[pgbouncer]
listen_port = 6432
auth_type   = scram-sha-256
auth_file   = /etc/pgbouncer/userlist.txt
 
# The line that matters. Hand the backend back at COMMIT.
pool_mode = transaction
 
# Cheap: client connections are lightweight, so set this high.
max_client_conn = 5000
 
# Expensive: real Postgres backends. Keep this under max_connections.
default_pool_size = 25

Point the apps at PgBouncer on port 6432 and shrink the per-process pools, since the pooler owns the real limit now. Transaction mode gives up one guarantee in return: that a connection stays yours across statements. That breaks the two Rails defaults from earlier in this post. Server-side prepared statements can execute on a backend that never prepared them, and Rails migrations take a session-scoped advisory lock that a later transaction may never land on. Turn both off on the pooled connection, and run migrations through a separate direct connection.

config/database.yml (pooled + migration connections)

production:
  primary:
    url: <%= ENV["DATABASE_URL"] %>            # PgBouncer, port 6432
    prepared_statements: false
    advisory_locks: false
  primary_migration:
    url: <%= ENV["DIRECT_DATABASE_URL"] %>     # direct Postgres, port 5432
    migrations_paths: db/migrate

How do Neon and friends do this for you?

Managed Postgres services bake this layer in, so you connect to a pooled endpoint and never run the pooler yourself. Supabase exposes a transaction-mode pooler on a separate port, 6543, next to the direct one. Neon gives you a pooled connection string for serverless workloads when you add -pooler to the host name. RDS Proxy is the managed version for Amazon RDS and Aurora.

flowchart LR
APP["App + workers"] ==>|"pooled endpoint<br/>(-pooler / :6543)"| MP
MIG["Migrations"] -->|"direct endpoint (:5432)"| DB
MP{{"Managed pooler<br/>(PgBouncer-equivalent)<br/>transaction mode"}}
MP ==> DB[("Postgres")]

Managed Postgres runs the same transaction pooler for you, behind a pooled endpoint.

In every one of these cases you are talking to PgBouncer or a close equivalent in transaction mode, with the process run and scaled for you. I run our own PgBouncer pod for DDC because Google Cloud SQL only includes managed pooling on its Enterprise Plus edition, and paying to upgrade editions makes no sense when a light pod does the same job.

Which layer should own the connection limit?

Two layers pool connections, and they cover different scopes. Your ORM pool reuses connections inside one process, and a transaction pooler caps the total across every process hitting the database. Rails gives you the first and nothing for the second. Decide whether PgBouncer or a managed endpoint owns that global limit, set it explicitly, and size the per-process pools to fit underneath it.