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 = 50Execution: 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: falsePhase 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 variables (like SET LOCAL) will behave unpredictably.
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.