Connection Tuning and Connection Pooling

Heroku PostgreSQL: every customer gets PostgreSQL with max_connections = 25. Seems tiny? Heroku ships PgBouncer out of the box. 1000 Rails workers -> PgBouncer -> 25 PostgreSQL connections. An average Rails app with 100K RPM runs perfectly on 25 connections. Understanding connection overhead and pooling is the difference between a $50/month plan and a $500/month plan.

• **Heroku** max_connections=25 on the free tier + PgBouncer: 95% of customers never notice the limit because connection pooling solves the scaling problem
• **Yandex Cloud** Odyssey in production for all managed PostgreSQL: the multi-threaded pooler handles 500K+ connections/sec on flagship instances
• **Supabase** PgBouncer transaction mode for every project: 5000 client connections -> 20 PostgreSQL connections per project, auto-scaling pool

max_connections: How Many Connections PostgreSQL Can Handle

**max_connections** caps the number of concurrent backend processes. Default: 100. Seems small, but each connection is a separate OS process with its own memory. With 1000 connections PostgreSQL spends huge resources on scheduling and IPC. Performance degrades faster than it scales with connection count.

**max_connections > 500 without a connection pooler is an antipattern.** 500 backend processes ~ 3-5 GB RAM just for processes + OS scheduling overhead. Lock table size = O(max_connections^2). Above 500, performance drops. Correct approach: max_connections = 100-200 + PgBouncer.

Connection Overhead: The Cost of Each Connection

**Every PostgreSQL connection is a full OS process** created via fork(), with memory allocation and structure init. Establishing a new connection: 50-100 ms (TCP handshake + auth + fork + init). At 10K queries/sec with a fresh connection per query, latency is worse than the query itself.

**pg_sleep and pgbench:** a benchmark of 1000 connections vs 50 (through a pooler) usually shows a 2 to 4x TPS difference in favor of the pooler, even at the same workload. OLTP with small transactions is especially sensitive to connection overhead.

PgBouncer: Three Pool Modes

**PgBouncer** multiplexes client connections through a small pool of real PostgreSQL connections. Three pool modes: session (1 PG connection per client session), transaction (1 PG connection per transaction), statement (1 PG connection per query). Each mode trades compatibility for efficiency.

Odyssey: An Advanced Pooler from Yandex

**Odyssey** is a connection pooler built by Yandex for Yandex Cloud. It is multi-threaded (unlike single-threaded PgBouncer), so it uses multi-core CPUs better. Supports routing rules, SSL offloading, and detailed metrics. It speaks the same wire protocol as PgBouncer, so it is compatible with any PostgreSQL client.

**PgBouncer vs Odyssey:** PgBouncer is battle-tested, simpler to configure, widely supported (AWS RDS, Heroku). Odyssey is better on multi-core servers (>4 cores), has more flexible routing, and is actively developed by Yandex. For most teams PgBouncer is enough.

Prepared Statements Through PgBouncer

**Prepared statements** in PostgreSQL cache the query plan at the session level. In PgBouncer transaction mode the connection rotates between clients, so a prepared statement created by one client is unavailable to another. This breaks the PREPARE/EXECUTE workflow. Three solutions: session mode, application-level caching, or the new Prepared Statement protocol.

**PgBouncer 1.23+** (2024) added support for prepared statements in transaction mode via statement-level caching. The pooler intercepts PREPARE/EXECUTE and maps them across connections. This removes the main limitation of transaction mode.

Key Ideas

• **max_connections = 100-300** is the practical maximum without a pooler. More than that and scheduling + lock table overhead outweigh the gain
• **PgBouncer transaction mode** is optimal for REST APIs and ORMs. Breaks: PREPARE, SET outside a transaction, advisory locks, LISTEN
• **Odyssey** is multi-threaded, better on multi-core servers, supports routing rules. PgBouncer is simpler and battle-tested
• **Prepared statements through a pooler**: server_reset_query=DISCARD ALL + client without named statements, or PgBouncer 1.23+ with built-in statement cache
• Monitoring: idle in transaction > 0 = problem. Lots of idle connections = wasted backend processes, drop via disconnect

Related Topics

Connection pooling ties closely to HA and application architecture:

• High Availability — PgBouncer in the HA stack: buffers connections during failover so clients see latency instead of errors
• PostgreSQL Architecture — The process-per-connection model is why a pooler is needed. Threads would be more efficient, but Postgres uses processes
• Monitoring — pg_stat_activity is the main connection-monitoring tool: state, wait_event, idle_in_transaction

Вопросы для размышления

• 10 microservices, each with a pool of max=20. Without PgBouncer: 200 PostgreSQL connections. With PgBouncer (pool_size=10 per service): 10 connections. How does PgBouncer multiplex 200 client connections over 10 real ones? What happens if all 200 want a query at once?
• An app uses Django with psycopg2. Someone added `connection.cursor(); cursor.execute('SET search_path=...')`. PgBouncer is in transaction mode. An hour later: random 'relation not found' errors. Why?
• PgBouncer shows wait_time rising. pool_size = 20, client_conn = 500. 80% of connections are idle. What is going on and how do you fix it?

Связанные уроки

• bt-24-connection-pooling