Connections and pooling

conceptThe concept

Each backend uses a few megabytes plus whatever work_mem its queries take, and snapshot computation and lock management get slower as connections grow. Many servers do best with active connections around 2-4 times the CPU cores. Microservices with 50 pods times 20 connections each quickly exceed that.

PgBouncer pool modes: session (a server connection per client session; safe, little saving), transaction (a server connection only during a transaction; the usual choice), statement (per statement; no multi-statement transactions). In transaction mode, session state does not survive between transactions: session-level SET, advisory locks held across transactions, LISTEN, WITH HOLD cursors and temp tables need care. Protocol-level prepared statements are supported since PgBouncer 1.21 with max_prepared_statements.

Size pools from the database side: default_pool_size per database and user pair, plus a small reserve, well under max_connections. Application-side pools (HikariCP and others) should be small too.

whyWhy it matters

"Too many connections" outages and servers spending more time context-switching than working are among the most common PostgreSQL production issues, and pooling is the fix.

Use caseReal-world scenario

200 application pods each open up to 10 connections (2,000 clients). PgBouncer in transaction mode serves them with 40 server connections, and the database runs at max_connections = 200 with headroom for admin tools.

Try itExamples

Run and edit these in the interactive lesson.

SELECT state, count(*) AS connections,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY connections DESC;
SHOW max_connections;
SELECT count(*) FILTER (WHERE backend_type = 'client backend') AS client_backends, count(*) AS all_processes FROM pg_stat_activity;

; pgbouncer.ini
[databases]
shop = host=10.0.1.1 port=5432 dbname=shop

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 40
reserve_pool_size = 5
max_prepared_statements = 200
server_reset_query =
server_check_delay = 10

BEGIN;
SET LOCAL statement_timeout = '5s';
SET LOCAL app.customer_id = '42';
SELECT current_setting('statement_timeout') AS timeout_in_tx, current_setting('app.customer_id') AS tenant;
COMMIT;
SELECT current_setting('statement_timeout') AS timeout_after, current_setting('app.customer_id', true) AS tenant_after;

Avoid / preferDo this, not that

max_connections = 5000

max_connections = 300
# PgBouncer in front: thousands of client connections, a few dozen server connections

-- transaction pooling, and the app uses SET search_path and session advisory locks

-- SET LOCAL inside transactions, transaction-level advisory locks,
-- or session pooling for the clients that need session state

PitfallsCommon mistakes

• Raising max_connections to 5,000 instead of pooling.
• Using session-level SET, advisory locks or temp tables across transactions behind a transaction-mode pooler.
• Giving every application instance a large pool, so the sum across instances exceeds what the database can handle.

LimitsWhen not to use it

Transaction-mode pooling does not suit clients that rely on LISTEN/NOTIFY, session temp tables, WITH HOLD cursors or session-level SET. Route those through session mode or directly.

SecuritySecurity notes

Encrypt both legs: client to PgBouncer and PgBouncer to PostgreSQL. With auth_query, use a SECURITY DEFINER lookup function owned by a restricted role instead of letting PgBouncer read pg_authid.

ChallengeTry it yourself

SELECT count(*) AS connections,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity
WHERE backend_type = 'client backend';

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Connection settings
• PgBouncer