Connection Pooling

GitHub 2019: a traffic spike caused 'FATAL: remaining connection slots are reserved for non-replication superuser connections'. Every new application request was rejected at the database level. The root cause was a connection pool misconfiguration that allowed too many direct connections to PostgreSQL. The fix was PgBouncer in front of every database cluster - now handling millions of application threads with a fraction of the actual server connections.

• **Supabase**: all 200M+ users share PgBouncer in transaction mode. Each project gets an isolated PostgreSQL instance; PgBouncer translates ~1M application connections into ~5,000 actual server connections.
• **GitLab**: deploys PgBouncer in transaction mode in front of primary and all replicas. Prepared statements are disabled in ActiveRecord to maintain compatibility. PgBouncer handles ~10,000 concurrent connections per database cluster.
• **Shopify**: ProxySQL sits in front of every Vitess MySQL shard, handling connection pooling, read/write routing, and automatic failover detection with sub-second primary switchover.

The Cost of a Database Connection

Opening a database connection is expensive: the client and server perform a TCP handshake, TLS negotiation, authentication (md5 or SCRAM), and session initialization. PostgreSQL spawns a dedicated OS process per connection - each consuming 5-10 MB of RAM regardless of whether it is executing a query. With 10,000 application threads each holding a connection, the database process table alone consumes 50-100 GB of RAM.

AWS RDS PostgreSQL defaults to max_connections based on instance RAM: roughly (RAM in GB * 25). A db.t3.micro with 1 GB RAM gets max_connections=25. Applications connecting directly from Lambda functions hit this limit almost immediately at moderate traffic.

PgBouncer: Three Pooling Modes

PgBouncer is a lightweight PostgreSQL connection pooler that sits between application and database. It maintains a fixed pool of real database connections and multiplexes many application connections onto them. PgBouncer offers three pooling modes with different trade-offs between feature compatibility and connection efficiency.

Transaction-mode PgBouncer is incompatible with server-side prepared statements (PREPARE/EXECUTE). Each transaction may be routed to a different server connection. Applications using ORMs (Django, Rails, SQLAlchemy) must disable server-side prepared statements when using PgBouncer transaction mode.

ProxySQL: MySQL Connection Pooling and Routing

ProxySQL is a high-performance proxy for MySQL and MariaDB that provides connection pooling, query routing, caching, and failover. Unlike PgBouncer (which focuses on connection multiplexing), ProxySQL parses SQL queries and can route reads to replicas and writes to the primary automatically.

Shopify runs ProxySQL in front of every Vitess MySQL shard. ProxySQL handles connection pooling, automatic read/write splitting, and failover detection. During a primary switchover, ProxySQL detects the new primary via health checks and redirects write traffic within seconds, without application restarts.

Connection Limits and Monitoring

Connection pool sizing requires balancing application concurrency with database connection limits. Too few pool connections starve application threads; too many exhaust database memory. The ideal pool size depends on database CPU cores, not application thread count.

Pooler Trade-offs and Failure Modes

Connection poolers introduce their own failure modes and operational complexity. A pooler crash drops all application connections simultaneously. Misconfigured pool limits can cause cascading failures. Session-level features (advisory locks, LISTEN/NOTIFY, temporary tables) require careful handling in pooled environments.

Running PgBouncer as a single instance makes it a single point of failure for the entire database tier. Production deployments use 2-3 PgBouncer instances behind a TCP load balancer (HAProxy or keepalived VIP). The loss of one pooler causes a brief connection reset, not a database outage.

Key Ideas

• **PostgreSQL connections** are expensive: each spawns an OS process consuming 5-10 MB RAM. max_connections beyond 300-500 causes more overhead than throughput gain.
• **PgBouncer transaction mode** is the standard: 10,000 app threads share 50-200 server connections. Incompatible with SET, prepared statements, and advisory locks.
• **ProxySQL** adds read/write query routing on top of pooling for MySQL. SELECT goes to replicas; writes go to primary. SELECT ... FOR UPDATE must be explicitly routed to the primary.
• **Pool size formula**: (CPU cores * 2) + 1 per database, not per application thread. More connections than cores causes context switching overhead.
• **Pooler HA**: PgBouncer must run in a 2+ instance setup behind HAProxy. A single pooler crash drops all application connections simultaneously.

Related Topics

Connection pooling is part of the database infrastructure stack:

• Replication — Connection poolers sit in front of both primary and replicas. ProxySQL routes reads to replicas and writes to primary automatically.
• Caching — A caching layer (Redis, Memcached) reduces total queries hitting the connection pool. Effective caching lowers the required pool size.
• Database Monitoring — Pool health metrics (cl_waiting, avg_wait_time, sv_idle) are critical SLOs. Pool exhaustion is one of the most common database availability incidents.

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

• An application uses LISTEN/NOTIFY for real-time notifications. PgBouncer transaction mode breaks this. What architecture enables real-time notifications without sacrificing connection pooling?
• Supabase shares PgBouncer across all customer projects. What isolation guarantees still hold, and what could be affected by a noisy neighbor project?
• AWS RDS Proxy is a managed connection pooler. What operational advantages does it offer over self-managed PgBouncer, and what are the trade-offs?

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

• os-03-threads