PostgreSQL
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.
An app complains about 'too many connections'. max_connections = 100. What is the correct action?
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.
Benchmark: 100 connections x 1 transaction/sec = 100 TPS. Expected result at 1000 connections x 0.1 transaction/sec?
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.
| Mode | Pool efficiency | Compatibility | Use case |
|---|---|---|---|
| session | Low | Full | Legacy apps, LISTEN/NOTIFY, advisory locks |
| transaction | High | Most cases | REST APIs, microservices, ORMs |
| statement | Maximum | Autocommit only | Simple queries without transactions |
An app uses PgBouncer transaction mode. A developer adds `LISTEN events` for real-time notifications. What happens?
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.
PgBouncer handles 100K requests/sec. CPU: 1 core at 100%. Will switching to Odyssey help?
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.
More max_connections = better PostgreSQL performance
Optimal max_connections for most servers: 100-300. Higher values mean scheduling and lock table overhead outweighs the benefit. To scale client count, use a connection pooler
PostgreSQL's lock table stores info about locks per (process, object) pair. Size grows as O(max_connections). At 1000 connections, 1M entries. The OS schedules across hundreds of processes. pgbench shows TPS peaking at 50 to 200 connections, then degrading
TypeORM uses prepared statements (named statements) with PgBouncer transaction mode. The developer sees 'prepared statement X does not exist'. Quick workaround?
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?