What is connection pooling, and how does it work?

Connection pooling means keeping a set of database connections open and lending them to requests as needed, instead of opening a new connection for every request and closing it afterwards. A pool saves the cost of connecting, which in PostgreSQL includes a TCP handshake, usually a TLS handshake, authentication and a new server process, and it caps how many connections the database has to juggle. On large systems that cap matters even more than the speed-up, so pools usually come in two tiers: small pools inside each app process, and an external pooler such as PgBouncer in front of the database.

What opening a connection costs

Opening a PostgreSQL connection is a small project in itself:

1. The client resolves the database's address and opens a TCP connection.
   
   
2. For an encrypted connection, client and server complete a TLS handshake.
   
   
3. The server authenticates the user, for example with a SCRAM-SHA-256 challenge and response.
   
   
4. PostgreSQL starts a new backend process for this client. Its documentation describes a "process per user" model: a supervisor process, the postmaster, spawns one backend process for every connection.
   
   
5. The new backend starts with cold caches, so its first queries do extra work.
   
   

In an analysis of connection scalability, PostgreSQL committer Andres Freund ranked the costs of a new connection as TLS first, then network latency, then Postgres's own work. Whatever the split on your setup, paying it on every web request adds latency to every page and keeps the database busy starting and stopping processes. You can measure it with pgbench, whose -C option opens a new connection for every transaction:

pgbench -i -s 10 bench                  # create test tables once
pgbench -c 20 -j 4 -T 60 -S bench       # 20 clients reuse their connections
pgbench -c 20 -j 4 -T 60 -S -C bench    # new connection per transaction

Compare the transactions per second and average latency of the last two runs. The gap is what a pool saves you.

How a pool works

A pool is a small manager that owns a set of open connections and follows the same cycle for each one:

1. Open. At start-up it opens a minimum number of connections.
   
   
2. Lend. When code asks for a connection, it hands over an idle one. If none is idle and the pool is below its maximum, it opens a new one. If it is at the maximum, the caller waits, up to a timeout, and then gets an error.
   
   
3. Use. The caller runs its queries; nobody else can use that connection meanwhile.
   
   
4. Return. The caller gives the connection back, and the pool resets it, for example by rolling back an unfinished transaction, so the next borrower starts clean.
   
   
5. Maintain. The pool checks connections before lending them, replaces old ones after a maximum lifetime, and closes spare idle ones.
   
   

Step 2 is easy to overlook: a pool is also a queue. When every connection is busy, callers wait instead of piling more concurrent work onto the database. That backpressure is a feature: the PostgreSQL wiki notes that a server usually finishes a batch of transactions sooner running a handful at a time than running hundreds at once.

App-side pools vs external poolers

A single server with a few processes needs only an app-side pool. Once you run many processes on many machines, you need both.

How connection pools work on large systems

The multiplication problem

Every process keeps its own pool, so the number of database connections is a product: machines × processes per machine × connections per process. Forty pods running four Gunicorn workers with a pool of five each is 800 connections. Let the autoscaler grow to 100 pods and it becomes 2,000, against a PostgreSQL default max_connections of 100. Autoscaling the app tier quietly autoscales the load on the one component that can't scale out.

Two tiers of pooling

The standard answer is two tiers. Each process keeps a small pool of connections to PgBouncer, which are cheap: PgBouncer needs about 2 kB of memory per connection by default. PgBouncer, in transaction pooling mode, lends one of a few dozen real server connections only for the length of each transaction.

Here is why that works, in an illustrative example. A web request takes 80 ms, but it spends only 6 ms inside three short database transactions; the rest is template rendering, cache calls and network time. The app-side connection is tied up for most of the request, but PgBouncer's server connection is busy for only 6 of those 80 ms, about 7.5% of the time. One server connection can therefore serve roughly a dozen requests in flight. The ratio collapses if requests hold long transactions: with Django's ATOMIC_REQUESTS = True, each whole view runs in one transaction, so the server connection is held for the entire request.

Budget connections by workload

On a busy exam day, the danger is not only too many connections but the wrong work holding them. A report export or a backlog of Celery tasks can take every connection just when students are submitting answers. Give each workload its own database user and its own pool size in PgBouncer, and keep a few slots for people:

PostgreSQL keeps three slots for superusers by default (superuser_reserved_connections), and from version 16 reserved_connections can hold more for roles granted pg_use_reserved_connections, so an engineer can still log in when every pool is full. Each read replica needs its own budget, because it has its own connection limit.

Plan for failover and dropped connections

Pools hold connections for a long time, so they are the first to notice when something in between cuts them: a database failover or restart, or a firewall or load balancer that drops idle connections. Check connections before lending them, and keep the maximum lifetime below any timeout imposed by the network. HikariCP's documentation, for instance, recommends a lifetime several seconds shorter than any database or infrastructure limit. How to size each tier is covered in sizing a connection pool, and the pooler itself in the PgBouncer explainer.

Connection pooling in Django

Django gives you three options, and its default is no reuse at all:

• Default, CONN_MAX_AGE = 0: Django opens a connection on the first query of a request and closes it at the end of the request.
  
  
• Persistent connections, CONN_MAX_AGE above 0: each thread keeps its connection across requests, up to that age in seconds, or indefinitely with None. Set CONN_HEALTH_CHECKS = True (Django 4.1 and later) so a dead connection is noticed before a request uses it. This is reuse, not pooling: connections aren't shared between threads, so the database must allow at least one connection per worker thread. Django's documentation says to turn persistent connections off under ASGI.
  
  
• A real pool (Django 5.1 and later): with psycopg 3 and the psycopg[pool] package, set "pool" in OPTIONS. Django keeps one pool per database alias in each process, and CONN_MAX_AGE must stay at 0.
  
  

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "exams",
        "CONN_MAX_AGE": 0,  # must be 0 when the pool is on
        "OPTIONS": {
            "pool": {"min_size": 2, "max_size": 8, "timeout": 5},
        },
    },
}

Without options, psycopg's pool keeps a fixed four connections per process (its max_size defaults to min_size), waits up to 30 seconds for a free connection, and replaces connections after an hour. Two more Django details matter at scale. Behind PgBouncer in transaction mode, set DISABLE_SERVER_SIDE_CURSORS = True, because a server-side cursor can't survive a change of server connection between transactions. And in long-running processes outside the request cycle, such as management commands, call django.db.close_old_connections(), or connections stay open until something times out.

Settings that matter: size, timeout, lifetime

Signs your pool is misconfigured

• Requests wait while database CPU is low. The pool is too small, or code holds connections too long, for example by calling an SMS API inside a transaction. SQLAlchemy reports this as "QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30", and psycopg's pool raises PoolTimeout.
  
  
• PostgreSQL says "sorry, too many clients already". Pools multiplied by processes exceed max_connections; see fixing too many connections.
  
  
• Hundreds of connections sit idle in pg_stat_activity. Pools are larger than your concurrency, or every thread holds a persistent connection.
  
  
• Connections sit "idle in transaction". Code opened a transaction and then waited on something else, holding locks and a connection.
  
  
• Errors such as "server closed the connection unexpectedly" appear after a failover or a quiet night. The pool lent out a dead connection; turn on health checks and shorten the lifetime.
  
  
• PgBouncer shows waiting clients. In SHOW POOLS, a non-zero cl_waiting and a rising maxwait mean the server-side pool can't keep up.
  
  

For the wider picture of keeping a database healthy under exam-day load, see handling 100,000 concurrent users. The Django database documentation and PostgreSQL's page on how connections are established are the primary references for this guide.

Key takeaways

• A pool keeps connections open and lends them out, saving connection set-up on every request.
  
  
• A pool is also a queue: when it is full, callers wait instead of overloading the database.
  
  
• On large systems, total connections are machines × processes × pool size, so app-side pools alone can't cap them.
  
  
• Use two tiers: small app pools feeding PgBouncer in transaction mode, with a budget for each workload.
  
  
• In Django, the default is no reuse; use persistent connections or the native pool from Django 5.1, with health checks.
  
  

Frequently asked questions

How does connection pooling work?

A pool opens database connections in advance and keeps them in a list. When code needs one, the pool lends an idle connection; when the code finishes, the connection goes back to the pool instead of being closed. If all connections are busy, new callers wait until one is returned or a timeout expires. The pool also tests, recycles and closes connections in the background, so borrowers always receive working ones.

Why connection pooling is required?

Opening a database connection takes network round trips, often a TLS handshake, authentication and, in PostgreSQL, a new server process. Doing that for every request wastes time and database CPU. More importantly, databases slow down when too many connections run queries at once, and each connection uses memory. Pooling reuses connections and caps how many exist, which keeps the database fast and predictable under load.

Does Django use connection pooling?

Not by default. Django's default CONN_MAX_AGE of 0 opens and closes a connection for each request. Setting it above 0 keeps one persistent connection per thread, which is reuse but not pooling. From Django 5.1, PostgreSQL users with psycopg 3 can enable a real pool through the "pool" option. Large deployments usually add PgBouncer as well, to cap total connections across all servers.

What is connection pool exhaustion?

Pool exhaustion means every connection in a pool is in use and new callers must wait. If none frees up before the acquire timeout, requests fail with errors such as SQLAlchemy's "QueuePool limit reached" or psycopg's PoolTimeout. Common causes are slow queries, long transactions, code that holds a connection while calling another service, connection leaks, and pools sized far below real concurrency.