Connection pools: how to size one and why more is slower

Every database has a maximum number of simultaneous connections, and it is lower than most people assume. Exceeding it does not slow things down gracefully. It produces an error, and it produces it under load, which is precisely when you least want a new failure mode:

FATAL: sorry, too many clients already

That message is not a capacity problem and a bigger plan will not fix it. It is arithmetic: the number of connections your application processes are allowed to open, multiplied by the number of processes, is larger than the number the database was configured to accept. This post is about doing that arithmetic before production does it for you - what a connection costs, why a small pool beats a large one, the numbers to start from, the timeouts that turn a stall into a clean error, and how to find the leak when the pool drains over hours.

What a connection actually costs#

In PostgreSQL, a connection is an operating system process. The postmaster forks a backend for each one, and that backend lives until the client disconnects. It carries a few megabytes of private memory of its own, plus the cache and buffer structures it touches, plus whatever work_mem its query allocates while running. A hundred idle connections are a hundred processes the kernel has to schedule and a real slice of memory gone before a single query runs.

Opening one is not free either. A new connection means a fork, authentication, TLS negotiation if you use it, and the session setup your framework runs. On a local network that is a handful of milliseconds; across the internet with TLS it can be tens. For a web request that takes 30 ms of real work, paying 20 ms to open a connection each time doubles your latency for nothing.

MongoDB is cheaper per connection - a thread rather than a process - but not free, and the server still has a ceiling. The driver's pool is what you actually control there, and the same reasoning applies to it.

A pool solves both problems. It opens a fixed number of connections once, hands them out to whatever part of your code asks, takes them back at the end of the request and keeps them warm. Every mainstream framework has one. The question is never whether to pool; it is what number to put in max.

Why more connections make things slower#

This is the counter-intuitive part, and it is the reason the answer is usually a smaller number than people expect.

A database server can genuinely do a limited number of things at once: roughly as many as it has CPU cores, plus some overlap for whatever is waiting on disk. Past that point, extra connections do not get more work done. They queue - but instead of queueing politely in your pool where it costs nothing, they queue inside the database, where each waiting query holds memory, locks and a scheduling slot, and where context switching between hundreds of backends burns CPU that could have been running queries.

The result is a throughput curve that rises, flattens, and then falls. Ten connections that stay busy will beat a hundred that thrash, and the hundred will have worse tail latency as well as worse throughput, because every query now waits behind ninety-nine others instead of nine.

There is a tidy way to see this. Concurrency equals throughput multiplied by latency. If your application does 500 queries a second and the average query takes 4 ms, then on average two connections are busy at any instant. Two. The pool of fifty that you copied from a blog post is not making anything faster; it is insurance against a spike that a queue would handle better.

The arithmetic nobody does#

The number in your configuration file is per process. Almost every incident starts with forgetting that.

PostgreSQL's default max_connections is 100, and three of those are held back by superuser_reserved_connections so that an administrator can still get in. Your real budget is 97. Filling it to 98 means the next deploy, which briefly runs old and new processes together, fails.

Count every process that connects, not every server. In a PHP or WordPress stack there is no pool at all in the usual sense: each PHP-FPM worker holds its own connection, so pm.max_children is your pool size, and forty children is forty connections. In a containerised setup, scaling from two containers to four doubles the total without anyone touching a database setting. And anything that runs one process per request - a serverless platform, a CGI-style host - multiplies without limit, which is exactly the case PgBouncer exists for.

Sizing a pool: a formula and a starting point#

The oldest and still the best rule of thumb comes from the PostgreSQL community, and HikariCP's documentation made it famous:

connections = (core_count * 2) + effective_spindle_count

On a 2 vCPU server with NVMe storage that lands at about 5 or 6 for the whole application, not per process. On 4 vCPU, about 9 or 10. Those numbers look absurdly small to anyone who has been running a pool of 50 and never noticed, and that is the point: they were never using 50, they were queueing inside the database instead of in front of it.

A practical starting point for a small application:

Then adjust with evidence. If the pool is never exhausted and queries are fast, it is big enough - a pool that is too large shows no symptom at all until the day it does. If requests are waiting to acquire a connection while the database CPU is idle, raise it. If requests are waiting and the database CPU is saturated, the pool is not the problem: fix the queries with EXPLAIN ANALYZE or add an index, because a bigger pool will only spread the same CPU more thinly.

On RE:NODE the database lines are a server of your own rather than a slice of a shared cluster, with the files and the console available, so max_connections is whatever postgresql.conf on that server says and you can change it. Be careful raising it on a small tier: memory is the binding constraint, and when a container reaches its memory limit here it is stopped and restarted clean rather than left to swap. A hundred backends on a 1 GB plan turn a slow afternoon into a restart. Tuning Postgres for small servers covers which number to raise first, and it is rarely this one.

Pool settings in the libraries you are using#

Every pool exposes the same handful of ideas under different names. These are the defaults, which are worth knowing because several of them are wrong for a server application.

Three of those defaults deserve a comment. SQLAlchemy's max_overflow means the real ceiling is 15 per process, not 5, which surprises people doing the arithmetic above. Django historically opened and closed a connection per request, which is safe and slow; CONN_MAX_AGE set to something like 60 reuses it, and recent versions can use a real pool with psycopg 3 - check the documentation for the version you are on, because this changed recently. And the MongoDB drivers' default of 100 per process is far more than any small application needs.

// node-postgres: one pool per process, created onceimport { Pool } from "pg";export const pool = new Pool({  connectionString: process.env.DATABASE_URL,  max: 5,  idleTimeoutMillis: 30_000,  connectionTimeoutMillis: 5_000,   // fail fast instead of hanging});

# SQLAlchemy: the real ceiling here is 8, not 5engine = create_engine(    os.environ["DATABASE_URL"],    pool_size=5,    max_overflow=3,    pool_timeout=5,       # seconds to wait for a connection    pool_recycle=1800,    # reconnect before anything else drops it    pool_pre_ping=True,   # check liveness before handing one out)

Create the pool once, at module level or in your application's startup, and never inside a request handler. A pool created per request is not a pool; it is a connection with extra steps, and it is the single most common way this goes wrong in small codebases.

Timeouts: failing fast instead of piling up#

A pool without timeouts converts a slow database into a hung application. Four timeouts matter, and they should have different values.

• Acquire timeout (connectionTimeoutMillis, pool_timeout, connectionTimeout). How long a request waits for a free connection before giving up. Set it to a few seconds. A request that has already waited five seconds is a request nobody is still watching, and failing it frees the slot for one that matters.
• Statement timeout. Set on the database or the session, this kills a query that runs too long. SET statement_timeout = '10s' for web traffic, higher for reports, off for migrations.
• Idle in transaction timeout. idle_in_transaction_session_timeout defaults to 0, meaning never, and that default has caused more outages than any other. A connection that opened a transaction and then went away holds its locks and blocks vacuum indefinitely. Thirty seconds is generous.
• Maximum lifetime (maxLifetime, pool_recycle). Close and reopen connections periodically so that a firewall, a proxy or a failover does not leave you holding sockets the other end has already forgotten.

-- Sensible defaults for an application role, set onceALTER ROLE app_user SET statement_timeout = '15s';ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';ALTER ROLE app_user SET lock_timeout = '3s';

Setting these on the role rather than in application code means they apply to every connection, including the one somebody opens from a laptop at two in the morning. Which role to use, and why it should not be the superuser, is Postgres roles and permissions.

The point of all four is the same: turn an unbounded wait into a fast, visible error that your health checks and logs can see. An application that returns a 503 in two seconds is recoverable; one that holds ten thousand requests open until the load balancer times out is not. Graceful shutdown and health checks covers the other half of that behaviour.

PgBouncer, and when you need it#

PgBouncer is a small proxy that sits in front of PostgreSQL. Applications connect to it, and it keeps a much smaller pool of real connections to the database, handing them out as needed. It exists because PostgreSQL's connection model is expensive and some deployment shapes create connections by the thousand.

[databases]appdb = host=127.0.0.1 port=5432 dbname=appdb[pgbouncer]listen_port = 6432pool_mode = transactionmax_client_conn = 1000default_pool_size = 10

Three pool modes, and the choice is the whole decision:

• session - a client keeps its server connection until it disconnects. Safe, compatible with everything, and it saves you almost nothing.
• transaction - the server connection is returned at the end of each transaction. This is the mode worth having, and the one that multiplexes a thousand clients onto ten backends.
• statement - returned after every statement. Breaks multi-statement transactions. Rarely appropriate.

Transaction mode has conditions attached, because anything that lives in a session rather than a transaction stops being reliable: SET statements, session-level advisory locks, LISTEN/NOTIFY, and cursors held outside a transaction. Server-side prepared statements were a problem for years; recent PgBouncer versions support them through the protocol with max_prepared_statements, but check your version before relying on it.

You need PgBouncer when you have many short-lived processes that each want a connection - a PHP stack with hundreds of workers, a platform that spawns a process per request, or several applications sharing one database. You do not need it for one Node or Python application with a properly sized pool, where it would be a second process to run for no benefit. Adding it "for scale" while running four app processes is a classic case of solving a problem you have not got.

Finding the leak#

A pool that exhausts slowly over hours, and recovers on restart, is not short of capacity. It has a path through the code that acquires a connection and never returns it.

The pattern is always the same: an early return, a thrown exception, or a branch that skips the release. Anywhere you see a manual connect without the release in a finally block or a context manager, you have a candidate.

// The leak: an exception here never releases the clientconst client = await pool.connect();const rows = await client.query(sql);   // throwsclient.release();// The fixconst client = await pool.connect();try {  return await client.query(sql);} finally {  client.release();}

Better still, do not check connections out by hand. pool.query() in node-postgres, a with block in Python, a context manager or a repository layer - all of them make the release automatic, and none of them can be forgotten in a code path added later.

To confirm what you are seeing, ask the database rather than guessing:

-- Who is connected, and what are they doing?SELECT state, count(*) FROM pg_stat_activity GROUP BY state;-- The dangerous ones: open transactions doing nothingSELECT pid, usename, state,       now() - state_change AS idle_for,       left(query, 60) AS last_queryFROM pg_stat_activityWHERE state = 'idle in transaction'ORDER BY idle_for DESC;

// MongoDB: current, available and total ever createddb.serverStatus().connections

A pile of idle in transaction sessions is a leaked transaction, not a leaked connection, and it is worse: it holds locks and it stops vacuum from cleaning up. A pile of plain idle sessions equal to your configured maximum is a healthy pool at rest. Growth in totalCreated on MongoDB, or connections that keep climbing after traffic flattens, is the signature of a pool being recreated somewhere instead of reused.

Export three numbers from your pool and watch them: connections in use, requests waiting, and the 99th percentile wait time to acquire. Utilisation alone tells you nothing - a pool at 100 per cent with nobody waiting is perfectly sized. Wait time is the metric that means something, and monitoring that tells you something makes the general case for picking metrics this way. If wait time is high, the database is busy or the queries are slow, and knowing when to upgrade starts with telling those two apart.

FAQ#

What does "sorry, too many clients already" mean?

That PostgreSQL has reached max_connections and refused a new session. Count your application processes multiplied by their pool size, plus workers, cron jobs and any client you have open. That total is above the limit, and the fix is almost always a smaller pool rather than a higher limit.

Should I just raise max_connections?

Rarely. Each connection is a process with its own memory, so raising the limit trades memory you need for queries against connections that will sit idle. Raise it only when you have confirmed the connections are all doing useful work, and lower your pool first.

What is a good pool size?

Start at roughly twice the number of CPU cores on the database, spread across all your application processes, plus a few spare for migrations and administration. For a small app on a 2 vCPU database that is around 8 connections in total - not per process.

Do I need PgBouncer?

Only if you have many short-lived processes each wanting a connection: a PHP stack with a large pm.max_children, a process-per-request platform, or several applications sharing one database. One well-pooled application does not benefit, and transaction pooling restricts session features you might be using.

Why does my pool run out overnight but work after a restart?

That is a leak. A code path acquires a connection and never releases it, usually because an exception skipped the release. Wrap every manual checkout in finally or a context manager, and prefer the pool's own query helper so the release cannot be forgotten.

Does MongoDB have the same problem?

The same shape, less severe. Its connections are threads rather than processes, but the driver default of maxPoolSize 100 per process still multiplies across your application. Lower it, set a wait queue timeout, and check db.serverStatus().connections when in doubt.