Connections and Pooling

Why connections are expensive

Each PostgreSQL connection is a process using several megabytes of memory. A small server with max_connections = 100 can be exhausted easily: 3 web workers × 4 threads × a pool of 10 = 120 possible connections, before counting background jobs and your own psql session.

The error looks like this, and it takes the whole site down:

FATAL: sorry, too many clients already

Do the arithmetic

Total possible connections = (processes) × (pool size + overflow per process), summed over every service that connects.

PandaDev reads DB_POOL_SIZE and DB_MAX_OVERFLOW from the environment. On a Render Starter instance with 1 Gunicorn worker: pool 5 + overflow 5 = at most 10 connections. On a 2 GB instance with 3 workers: 3 × (8 + 10) = 54. Both fit comfortably under a managed database's limit, but check yours:

SHOW max_connections;
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

Pool settings that matter

In SQLAlchemy:

create_engine(url, pool_size=5, max_overflow=5, pool_pre_ping=True, pool_recycle=1800)

• pool_pre_ping checks a connection before use, avoiding errors after the database restarts or a firewall drops idle connections.
• pool_recycle replaces connections periodically.

PgBouncer

PgBouncer sits between apps and PostgreSQL, letting thousands of client connections share a few dozen real ones.

; /etc/pgbouncer/pgbouncer.ini
[databases]
pandadev = host=127.0.0.1 port=5432 dbname=pandadev

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 500

Transaction pooling gives the biggest win but breaks features that rely on session state between transactions (session-level SET, advisory locks held across transactions, some prepared statement setups). Test your app with it before switching. Many managed providers offer a built-in pooler endpoint.

Finding connection leaks

SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY 5 DESC;

Many idle in transaction connections from the app point to code that opens a transaction and never commits or rolls back.

Check yourself

• Calculate the maximum connections for 2 workers with pool 5 and overflow 5.
• What does pool_pre_ping prevent?
• What does transaction pooling break?