BLUF (Bottom Line Up Front): Legacy Rails applications utilizing Sidekiq and Puma often exhaust the PostgreSQL max_connections limit, causing ActiveRecord::ConnectionTimeoutError. The solution is deploying PgBouncer in transaction mode. This multiplexes thousands of incoming Rails connections onto a small pool of actual PostgreSQL connections, drastically reducing database memory overhead and preventing connection drops.
Phase 1: The Connection Exhaustion Problem
Glossary entry: Connection Pool Exhaustion.
ActiveRecord maintains a persistent connection pool per Ruby process. If you have 20 web servers running Puma (with 16 threads each) and 10 Sidekiq workers (with 25 threads each), your application demands over 500 persistent database connections.
Synthetic Engineering Context: The Crash
When traffic spikes, the database server runs out of connection slots, and your Rails logs fill with fatal errors.
# Rails Production Log
FATAL: remaining connection slots are reserved for non-replication superuser connections
ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 secondsIncreasing max_connections in postgresql.conf is a trap. Each Postgres connection consumes roughly 10MB of RAM. Allowing 2000 connections will starve the database of memory needed for query caching (shared_buffers).
Phase 2: Implementing PgBouncer
PgBouncer acts as a proxy. Rails connects to PgBouncer thinking it is the database. PgBouncer then intelligently shares a small number of real database connections among the Rails processes.
Execution: PgBouncer Configuration
You must configure PgBouncer to use transaction mode. In this mode, a server connection is assigned to a client only for the duration of a single transaction, rather than the entire session.
# /etc/pgbouncer/pgbouncer.ini
[databases]
# Map the virtual database to the actual PostgreSQL instance
myapp_production = host=127.0.0.1 port=5432 dbname=myapp_production
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
# CRITICAL: Must be transaction mode for Rails
pool_mode = transaction
# Maximum number of real DB connections PgBouncer will open
max_client_conn = 2000
default_pool_size = 50
# Emergency headroom when the default pool is saturated
reserve_pool_size = 5
reserve_pool_timeout = 3Sizing the Pool
default_pool_size is the number of REAL PostgreSQL connections per database/user pair, not the number of clients. A reasonable starting point is the classic formula (2 * cpu_cores) + effective_spindle_count on the database server; for a typical 8-core Postgres box that means a pool of 20-30, not 500. If that sounds too small, remember: in transaction mode a single server connection can serve hundreds of mostly-idle Rails threads, because it is only held for the milliseconds a transaction is actually open.
The trap is long transactions. A Sidekiq job that opens a transaction and then calls a slow external API inside it will pin a server connection for the entire API call. Before lowering the pool size, find offenders with log_min_duration_statement or by grepping for transaction do blocks that wrap network calls.
Execution: Rails Configuration
You must update your database.yml to point to PgBouncer’s port (6432) and disable prepared statements. Prepared statements rely on session state, which breaks in PgBouncer’s transaction mode.
# config/database.yml
production:
adapter: postgresql
encoding: unicode
host: pgbouncer.internal
port: 6432
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: myapp_user
password: <%= ENV['DATABASE_PASSWORD'] %>
# CRITICAL: Disable prepared statements
prepared_statements: false
# CRITICAL: Advisory locks are session-scoped and break in transaction mode
advisory_locks: falseBoth flags matter. Prepared statements (PREPARE/EXECUTE) live in the session; in transaction mode consecutive transactions from the same Rails thread land on different server connections, so the second one fails with PG::InvalidSqlStatementName: prepared statement "a1" does not exist. Advisory locks are the sneakier one: Rails uses pg_advisory_lock to serialize migrations across nodes. With advisory_locks: true behind PgBouncer, the lock is taken on one server connection and released on another, so rails db:migrate can deadlock or silently no-op the protection.
Execution: Monitoring the Pool
PgBouncer exposes a virtual pgbouncer database with admin commands. Connect with psql -p 6432 pgbouncer and watch two numbers:
SHOW POOLS;
-- cl_active | cl_waiting | sv_active | sv_idle
-- 412 | 0 | 38 | 12cl_waiting above zero means clients are queueing for a server connection: your pool is too small or a long transaction is hogging connections. SHOW STATS gives per-database avg_query_time and avg_xact_time; a rising gap between the two is the signature of transactions doing non-database work inside the block.
Phase 3: Next Steps & Risk Mitigation
Running PgBouncer in transaction mode without disabling prepared statements in Rails will result in prepared statement does not exist SQL errors. Furthermore, any application logic relying on session-level state will behave unpredictably: SET (session-wide) breaks, while SET LOCAL inside a transaction remains safe. Audit the codebase for execute("SET ..."), LISTEN/NOTIFY, and long-lived cursors before the cutover; all three are session-scoped features that transaction pooling silently sabotages.
One more compatibility note: if your PostgreSQL uses scram-sha-256 authentication (the default since Postgres 14), you need PgBouncer 1.14+ and matching SCRAM secrets in userlist.txt, or auth_query delegation. The old md5 examples that dominate blog posts will fail the handshake.
Need Help Stabilizing Your Legacy App? Connection pooling is critical for scaling Rails monoliths. Our team at USEO configures robust database infrastructure, including PgBouncer and Pgpool-II, to handle massive concurrency without downtime.