Connection pool

What it is

A connection pool is a set of open database connections that an application keeps and lends out again, instead of opening a new connection for every piece of work and closing it afterwards. Code checks a connection out of the pool, runs its statements on it and hands it back, and the next caller that needs one gets the same connection, already open. SQLAlchemy’s documentation describes pooling as “a standard technique used to maintain long running connections in memory for efficient reuse”. The same page gives the pool a second job: it manages how many connections the application may use at the same time, so a burst of traffic waits for a free connection instead of opening more.

A pool can live in two places. Inside the application it is part of the database library: every SQLAlchemy Engine comes with one, and Django can keep one in each process for PostgreSQL. Outside the application it is a separate program that sits between the clients and the database server. PgBouncer is such a pooler for PostgreSQL: applications connect to it as if it were the database, and it lends them server connections from pools of its own. The two can be combined, and Django’s database reference has a section on running behind a transaction pooler such as PgBouncer.

Why a connection is worth keeping shows on the server side. PostgreSQL gives each client a process of its own: a supervisor process called the postmaster listens for incoming connections, starts a new server process for every connection request, and each client is connected to exactly one of those processes. Opening a connection therefore means starting a process on the database server, and a pool pays that price once and then reuses the result.

Why it matters

The first reason is the cost of connecting. Django’s documentation makes the case for keeping connections in plain terms: a persistent connection avoids the overhead of establishing a new one in each HTTP request. Its default does the opposite, though. CONN_MAX_AGE is 0, which closes the connection at the end of every request; a positive number of seconds keeps it open for reuse, and None keeps it open without a limit. That is the simplest form of reuse, one connection per thread that outlives the request, and a pool is the general form of it.

The second reason is the limit on the server. PostgreSQL’s max_connections is typically 100 by default, may be lower if the kernel settings will not support it, and can only be changed at server start. Three of those slots are reserved for superusers by default. Raising the number is not free either: PostgreSQL sizes certain resources, shared memory among them, directly from max_connections. Without a pool, every worker holds a connection of its own, and Django’s documentation spells out what follows: because each thread keeps its own connection, the database has to accept at least as many simultaneous connections as there are worker threads. A pool turns that into a number the team chooses and can add up.

The third reason is hygiene. A pool is the one place every connection passes through between two borrowers, so it is where a connection gets cleaned and tested. When a connection comes back, SQLAlchemy’s pool calls rollback() on it, which releases any transactional state, locks included, so the next borrower does not inherit a half-finished transaction. PgBouncer sends its server_reset_query, DISCARD ALL by default, when it releases a server connection and before another client can have it. And a pool can test a connection before lending it out, which matters for connections that have sat idle for a while.

What it looks like in practice

In SQLAlchemy 2.1 every Engine gets a QueuePool by default, with SQLite’s in-memory database as the exception. Its pool_size is 5 and its max_overflow is 10, so one engine can hold up to 15 connections at once, and a caller that finds them all in use waits pool_timeout, 30 seconds by default, before giving up. The settings are keyword arguments to create_engine():

from sqlalchemy import create_engine

# At most ten connections for this engine: ten kept open, none beyond.
engine = create_engine(
    "postgresql+psycopg2://me@localhost/mydb",
    pool_size=10,
    max_overflow=0,
    pool_timeout=10,  # wait ten seconds for a free connection, then give up
    pool_pre_ping=True,  # test each connection as it is checked out
    pool_recycle=3600,  # replace connections that are over an hour old
)

With pool_pre_ping, the pool checks at checkout that the connection is still usable, through the driver’s own ping or a statement equivalent to SELECT 1. pool_recycle is meant for servers that close connections left unused for a certain time: with 3600, any connection open for more than an hour is replaced at its next checkout.

Django 6.1 has a pool of its own for PostgreSQL. It works only with the psycopg driver and needs the psycopg[pool] package, and it is switched on with "OPTIONS": {"pool": True} in the database settings, or with a dictionary of options for psycopg’s ConnectionPool. The pool reuses connections across threads, and each process keeps a separate pool for each database alias. CONN_MAX_AGE must stay at 0 when pooling is on; the lifetime of a pooled connection is set with the pool’s max_lifetime option instead. Under ASGI, Django’s documentation advises turning persistent connections off and using this built-in pool instead.

PgBouncer’s pool_mode decides when a server connection can go to another client. In session mode, the default, the client keeps it until it disconnects. In transaction mode, a client has a server connection only while a transaction runs, so a few dozen server connections can serve many more clients. Statement mode releases the connection after every query and does not allow transactions that span several statements. Each pool holds up to default_pool_size server connections per user and database pair, 20 by default, and PgBouncer accepts up to max_client_conn client connections, 100 by default.

The company’s own notes put the pool early in the order of work. Optimise the database before you shard it has the reader count the active sessions in pg_stat_activity against the CPU cores: when many times more are active than there are cores, the server spends its time switching between them, and a connection pool fixes that before any hardware does. Selling the last ticket exactly once applies the same idea to a flash sale: a hundred thousand buyers should become a few dozen connections through PgBouncer, not thousands of server processes waiting for one lock.

Where it goes wrong

The pools add up to more than the server allows. Each pool counts only its own connections. At SQLAlchemy’s defaults one engine can reach 15, so twelve processes can ask for 180 from a server whose default max_connections is typically 100, three of them reserved for superusers, and Django keeps a separate pool for every database alias in every process. Add up the maximum of every pool that points at the database, across every process of every application and every scheduled job, and keep the sum below max_connections minus the reserved slots. When it cannot fit, put PgBouncer in front and let it hold the number of server connections.

A connection dies while it waits in the pool. A connection that sat idle can be closed on the other side without the pool knowing, and the next borrower then fails on its first statement. Turn on pool_pre_ping in SQLAlchemy or CONN_HEALTH_CHECKS in Django, which tests a reused connection once per request, and set pool_recycle or psycopg’s max_lifetime below the time after which the server closes idle connections. Neither covers a connection dropped in the middle of a transaction: SQLAlchemy’s documentation says plainly that pre-ping does not handle that case, so the application has to retry the work itself.

A forked worker inherits the parent’s connections. A web server or a job runner that creates the engine and then forks its workers leaves every child holding the same pooled connections as the parent. The SQLAlchemy documentation calls it critical that pooled connections are not shared with a forked process, because both processes would then talk over the same network connection. Follow its recommended approach and call engine.dispose(close=False) in each child’s initializer, so the child starts with an empty pool and leaves the parent’s connections alone; the alternatives are NullPool, which opens and closes a connection for every use, or calling engine.dispose() before forking.

Transaction pooling breaks session state. In PgBouncer’s transaction mode, two transactions from the same client can run on different server connections, so anything a client sets up for its session is not guaranteed to be there for its next transaction. PgBouncer’s feature list marks SET, LISTEN, cursors declared WITH HOLD, SQL-level PREPARE and session-level advisory locks as never supported in that mode, and is open that the mode, by its nature, breaks some of the assumptions clients make about the server. Keep session state out of code that goes through a transaction pooler, and send the clients that need it to a pool in session mode. In Django, set DISABLE_SERVER_SIDE_CURSORS to True for a database behind a transaction pooler, or run those queries inside atomic().

Further reading

• Connection Pooling — QueuePool and its defaults, disconnect handling, reset on return and pools under fork in SQLAlchemy 2.1.
• PgBouncer features — the session, transaction and statement modes, and which PostgreSQL features each one supports.
• PgBouncer configuration — pool_mode, default_pool_size, max_client_conn and the timeouts, with their defaults.
• How Connections Are Established — PostgreSQL’s process per user model and the postmaster.
• Connections and Authentication — max_connections and the connection slots reserved for superusers and other roles.
• Databases — persistent connections, the PostgreSQL connection pool and transaction pooling, in Django’s reference.