Postgres “too many connections”: what is holding them, and how to size the pool.

Two different things get called “too many connections”. Either the database server ran out of slots, or your app’s own pool ran out of connections and a request gave up waiting. The fixes are different, so read the exact text first.

Connect with psql and look before you change anything. pg_stat_activity has one row per connection. A row whose state is idle is a connection sitting in some pool, doing nothing. idle in transaction is worse: a transaction was opened and never committed, and it also holds locks.

• Many `idle` rows from one `application_name`: a pool sized too big, or a process that creates a new pool per request. Cap the pool (below) and create it once at startup.
• Rows from processes you forgot: an old deploy, a preview, a worker, a notebook, a BI tool left open. Each one keeps its own pool.
• `idle in transaction` for minutes: a code path that opens a transaction and returns early, or a debugger stopped mid-request. Fix the code, and set idle_in_transaction_session_timeout so Postgres ends them for you.
• Lots of `active` rows, all slow: the connections are busy, not leaked. More connections will not help; slow queries will. See find slow queries.

-- Who holds connections to this database, and doing what
SELECT usename, application_name, state, count(*) AS conns,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE datname = current_database()
  AND backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY conns DESC;

-- Sessions stuck inside an open transaction
SELECT pid, usename, application_name, now() - state_change AS stuck_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY stuck_for DESC;

Every process that talks to the database keeps its own pool, and every connection in it can be open at the same time. The number that has to fit under the server’s limit is the sum over every process:

(web processes × connections per process) + (job processes × connections per process) + release command or migrations + anything else that connects (previews, `psql`, BI tools)

A connection per thread is enough. A Puma process with 3 threads never needs more than 3 connections for requests. Give it more and they sit idle. Give it fewer and threads queue for one, which is the Rails timeout above.

# Rails (config/database.yml). Rails 8.1 also accepts max_connections:
production:
  url: <%= ENV["DATABASE_URL"] %>
  pool: <%= ENV.fetch("DB_POOL", 6) %>   # 3 Puma threads + 3 job threads

// Node (pg): one Pool per process, created once at startup
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 8 });

# Django: persistent connections, one per worker thread
DATABASES["default"]["CONN_MAX_AGE"] = 600

# SQLAlchemy: cap the overflow too, or 5 becomes 15
engine = create_engine(url, pool_size=5, max_overflow=2)

A Canner database accepts up to 20 connections at once (background jobs docs). A project runs one process, so the budget is easy to lay out. A separate Worker project connected to the same database counts against the same 20:

• Rails with GoodJob in the web process: 3 Puma threads + 3 job threads + 3 for GoodJob’s listener and housekeeping = 9. The docs’ example sets DB_POOL to 12, which leaves 8.
• Node with one `pg.Pool`: the default max of 10 uses half the budget. A preview deploy of the same app is another process with the same DATABASE_URL and another 10. Set max: 8 and both fit, with room for psql.
• Prisma 6: the default depends on the CPU count of the machine the process sees, not on your plan. On a host with 8 physical cores that is 17. Set connection_limit yourself.
• The release command: migrations run while the current version is still serving, so they need a connection or two of headroom.

Each Postgres connection is a separate server process with its own memory. Past the point where the CPU and disk are busy, more connections only add contention. The PostgreSQL wiki’s rule of thumb for active connections is (core_count × 2) + effective_spindle_count, a much smaller number than most people expect.

max_connections also needs a server restart. On a managed database you usually can’t set it at all; the provider sizes it from memory. If you are at the limit because of idle connections, smaller pools fix it for free.

A pooler sits between your app and Postgres and hands out server connections. What it can share depends on the mode. In session mode (PgBouncer’s default) a client keeps one server connection until it disconnects. Nothing breaks, and nothing is multiplexed: 20 open clients still need 20 server connections. In transaction mode the server connection goes back to the pool at the end of each transaction, so hundreds of mostly idle clients can share a few server connections. The price is that anything tied to a session stops working.

• Use transaction mode when many short-lived or serverless processes each open connections. Then turn off what depends on a session: DISABLE_SERVER_SIDE_CURSORS = True in Django, pgbouncer=true on Prisma’s pooled URL with a direct URL for migrations, and advisory_locks: false for Rails migrations.
• Stay on session mode for one or a few long-running processes, and for anything built on LISTEN/NOTIFY or advisory locks: GoodJob, migration locks, Postgres-backed queues.

Canner runs one process per project and allows up to 20 connections per database, shared with any separate Worker project connected to it. That suits a web app with its jobs in the same process. If your design needs dozens of separate instances or serverless functions talking to one database at the same time, you need a transaction-mode pooler in front of a server sized for it, and Canner does not offer that today.