Rails PgBouncer: Transaction Pooling, Prepared Statements, and Connection Sizing in Production

The Rails app had been fine for two years. Then traffic doubled over a quarter and Postgres started rejecting connections during the morning peak. The CTO’s first instinct was to scale Postgres vertically — bigger instance, more max_connections. I asked how many Puma workers they were running across how many dynos. Sixteen dynos, five workers, five threads. Eight hundred potential database connections from the app alone. Postgres was capped at 300.

We did not scale the database. We put PgBouncer in front of it in transaction pooling mode, dropped Rails’ connection count to a realistic number, and the morning peak became a non-event. Total cost: one afternoon and roughly forty dollars a month for a small connection pooler instance.

After nineteen years of Rails, PgBouncer with transaction pooling is the single highest-leverage piece of database infrastructure I add to growing Rails apps. It is also the piece most likely to be misconfigured in ways that work for a year and then explode at three in the morning.

Why Rails Apps Need PgBouncer Transaction Pooling

Postgres connections are expensive. Each connection is a forked process with its own memory — usually 5 to 15 megabytes of resident memory once warmed up. A Postgres instance with max_connections = 300 is reserving somewhere between 1.5 and 4.5 gigabytes just for connection slots, before any actual query work.

Rails makes this worse because every Puma worker has its own connection pool. A modest deployment of ten dynos times five workers times five threads is 250 connections at full saturation, even though most of those connections sit idle most of the time. The connections are held open across requests via Rails’ ActiveRecord::ConnectionAdapters::ConnectionPool, and they are rarely fully utilized.

PgBouncer in transaction pooling mode breaks this 1:1 mapping. The Rails app connects to PgBouncer, PgBouncer maintains a much smaller pool of real Postgres connections, and a Postgres connection is borrowed only for the duration of a single transaction. A typical Rails app with 250 client connections to PgBouncer can run comfortably against 20 to 40 real Postgres connections, because at any given moment only a fraction of clients are actually executing SQL.

This is not a marginal optimization. It is the difference between a Postgres instance that uses two gigabytes of RAM for connections and one that uses two hundred megabytes.

The Three PgBouncer Pooling Modes

PgBouncer offers three modes. Pick the wrong one and you will either lose connection efficiency or break your application in subtle ways.

Session pooling assigns a Postgres connection to a client for the entire duration of the client’s connection. The pool sizes are 1:1 with client connections, which means you have not really gained much. Session pooling is useful when you need session-level features (temp tables, SET LOCAL, prepared statements managed by the server) but it is not the mode that saves your bacon under load.

Transaction pooling assigns a Postgres connection to a client only for the duration of a transaction. The moment the transaction commits or rolls back, the connection returns to the pool and may be handed to a different client. This is the high-leverage mode. It also breaks several things Rails depends on by default, which is why most “we tried PgBouncer and it broke our app” stories end here.

Statement pooling returns the connection after every single statement, even within a transaction. Statement pooling is essentially incompatible with Rails because Rails wraps everything in transactions and assumes statements within a transaction execute on the same backend.

For Rails apps, transaction pooling is the answer. The rest of this post is about making transaction pooling work without breaking your application.

Prepared Statements: The First Trap

When Rails issues a parameterized query, it asks Postgres to prepare a statement, then executes that prepared statement with the actual parameters. The prepared plan lives on the Postgres backend that prepared it. The next time Rails issues the same query, it executes the previously prepared statement by name — saving a parse and plan cycle.

This is a wonderful optimization in normal operation and a disaster behind PgBouncer transaction pooling. The prepared statement lives on a specific Postgres backend connection. The next query from the same Rails worker may be routed to a different backend that has never heard of that statement. You get ERROR: prepared statement "a3" does not exist and a confused engineer paged at 3am.

Two ways to fix this. The right one depends on your PgBouncer version.

If you are on PgBouncer 1.21 or newer, enable server-side prepared statement support and leave Rails’ prepared statements on:

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

PgBouncer will track prepared statements per client and replay them on whichever backend it routes to. This is the modern answer and it is the one I default to for new deployments.

If you are on an older PgBouncer (managed services often lag here — check your provider’s documentation), disable prepared statements on the Rails side:

# config/database.yml
production:
  adapter: postgresql
  prepared_statements: false
  advisory_locks: false

prepared_statements: false makes Rails send the query text every time. You lose a small performance optimization. You gain not getting paged. For most apps the trade is obvious.

advisory_locks: false matters for the same reason as prepared statements — Rails uses advisory locks for schema migrations and with_advisory_lock blocks, and PostgreSQL session-level advisory locks live on a specific backend connection. Transaction-scoped advisory locks (pg_advisory_xact_lock) work fine through PgBouncer because they release at transaction end. Session-scoped advisory locks do not.

Sizing the Pools: Math That Actually Works

Connection pool sizing is where most teams either over-provision and waste connections or under-provision and queue requests at the pooler. The math is not complicated but it requires understanding both layers.

The variables:

• N: number of Rails processes (dynos × Puma workers)
• T: threads per Puma worker
• C: ActiveRecord pool size per process
• P: PgBouncer default_pool_size per database+user
• M: Postgres max_connections

Rails will open up to C connections per process. It needs C ≥ T or threads will queue waiting for connections — you will see this as ActiveRecord::ConnectionTimeoutError. I set C = T + 1 as a starting point and adjust if I see timeouts.

PgBouncer’s per-database pool size P is the number of real Postgres backends serving that database+user combination. The right number is empirically derived from your actual transaction profile, but a good starting point is P = 20 for most Rails apps. You can be aggressive here — under transaction pooling, twenty backends can comfortably serve hundreds of client connections if your transactions are short.

The constraint that bites is N × C ≤ PgBouncer max_client_conn and P × (number of databases × users) ≤ M. With one database and one user, P ≤ M − (reserved for admin, replication, etc.).

Worked example for the app I described in the opening:

• 16 dynos × 5 Puma workers = 80 processes
• 5 threads per worker, so C = 6, total potential client connections = 480
• PgBouncer max_client_conn = 1000 (room to grow)
• PgBouncer default_pool_size = 25
• Postgres max_connections = 100 (down from 300)
• 25 backends served 480 clients comfortably; the pool wait time stayed under 5ms at peak

The most counterintuitive lesson here: you almost always want fewer Postgres connections than you think. Postgres is a tuple-at-a-time system; more concurrent connections past your CPU count means more context switching, more lock contention, and more memory pressure. A modern Postgres instance with 8 CPU cores is happier serving 40 active connections than 200.

Health Checks and Statement Timeouts

Transaction pooling assumes transactions are short. If a transaction holds a connection for thirty seconds, that connection cannot serve other clients for thirty seconds, and your effective pool size drops. One long-running migration or a single stuck transaction can starve the entire app.

Set timeouts at every layer:

; pgbouncer.ini
query_wait_timeout = 60    ; how long a client waits for a server connection
server_idle_timeout = 600  ; idle backends are closed
server_lifetime = 3600     ; recycle backends after an hour

# config/database.yml
production:
  variables:
    statement_timeout: 30000              # 30s
    lock_timeout: 5000                    # 5s
    idle_in_transaction_session_timeout: 60000  # 60s

idle_in_transaction_session_timeout is the one that saves you from the “I started a transaction in a Rails console two hours ago and walked away” scenario. Without it, a forgotten transaction holds locks indefinitely and you find out about it when writes start queuing.

For long-running operations — batch jobs, migrations, ETL — connect directly to Postgres bypassing PgBouncer, or use a separate PgBouncer pool configured in session mode for that specific purpose.

LISTEN/NOTIFY and PgBouncer

This one trips up teams using ActionCable with the Postgres adapter, or any gem that relies on Postgres pub/sub. LISTEN registers a listener on a specific Postgres backend. Under transaction pooling, the next call from the same Rails worker may land on a different backend that is not listening, and the NOTIFY never reaches your subscriber.

Three options:

1. Use Redis for pub/sub instead of Postgres LISTEN/NOTIFY. This is what I do by default for ActionCable in production. The Postgres adapter is fine for development; Redis is fine for production.
2. Use Solid Cable which uses polling instead of LISTEN/NOTIFY and works through PgBouncer without ceremony.
3. Bypass PgBouncer for the connection that needs LISTEN. Connect directly to Postgres on a separate database URL just for pub/sub clients. This is what I recommend if you have a non-Rails consumer that genuinely needs push semantics.

The wrong answer is “leave LISTEN/NOTIFY on through PgBouncer and hope.” It works in testing because there is only one backend in the pool. It fails in production when the pool grows.

Deployment Patterns I Trust

For Heroku-style platforms, the managed pgbouncer buildpack runs PgBouncer as a sidecar in each dyno. The Rails app connects to localhost, PgBouncer forwards to Postgres. This is the simplest deployment and the one I reach for first when migrating an existing app.

For Kubernetes, run PgBouncer as a sidecar in each Rails pod, not as a shared deployment. Sidecar PgBouncer keeps connection routing local and avoids a network hop between Rails and the pooler. The downside is more total PgBouncer processes, but each one is tiny and they coordinate fine because they all point at the same Postgres.

For VPS / bare metal, a single PgBouncer instance per Postgres primary, sitting on the same network as the database, is the standard. Add a second PgBouncer for read replicas if you are using read replicas.

For any deployment, log PgBouncer stats every minute and alert on these:

• cl_waiting > 5 for more than a minute (clients queuing for a connection means your pool is too small)
• sv_active consistently at default_pool_size (your pool is fully saturated; transactions may be slow)
• avg_wait_us > 1000 (clients waiting more than a millisecond is a smell)

I have a Datadog dashboard template I drop into every Rails-with-PgBouncer setup that tracks these three. It catches problems weeks before they become incidents.

When PgBouncer Is the Wrong Answer

Not every Rails app needs PgBouncer. If you are running fewer than 50 total connections from Rails, the operational cost of running PgBouncer is not worth the savings. If your transactions are long (analytics workloads, long aggregations), transaction pooling provides less benefit because you are holding backends for the same duration anyway.

If you have so few connections that Postgres’ built-in pooling is fine, leave PgBouncer out. Add it when you cross the threshold where Postgres connection slots become the constraint — somewhere around 100-200 active connections from Rails workers — not before.

The other case where I avoid PgBouncer is when an app uses heavy session-level state: custom prepared statements managed by the application, session-level advisory locks for coordination, or LISTEN/NOTIFY as a core pattern. The cost of working around those is sometimes higher than the cost of scaling Postgres connections directly. Know what you are giving up before you add the pooler.

FAQ

Does PgBouncer work with Rails prepared statements?

Yes, but only on PgBouncer 1.21 or newer with max_prepared_statements set. On older PgBouncer versions you must disable Rails prepared statements with prepared_statements: false in database.yml. Otherwise you will see ERROR: prepared statement does not exist because the prepared plan lives on a specific Postgres backend that the next query may not be routed to.

What pool size should I use for PgBouncer with Rails?

Start with default_pool_size = 20 for most Rails apps. The right number is empirically derived from your workload, but twenty backends can comfortably serve hundreds of client connections under transaction pooling because Rails transactions are typically short. Counterintuitively, smaller Postgres connection counts often perform better than larger ones because Postgres is a tuple-at-a-time system that suffers from too much concurrency.

Why does my Rails app get “prepared statement does not exist” errors with PgBouncer?

This happens when Rails prepared statements are enabled and PgBouncer is running in transaction pooling mode on a version older than 1.21. The prepared statement lives on a specific Postgres backend connection; the next query may be routed to a different backend that has never seen that statement. Fix by either upgrading PgBouncer and setting max_prepared_statements, or by setting prepared_statements: false in database.yml.

Can I use ActionCable with PgBouncer in transaction pooling mode?

Not with the default Postgres adapter, because LISTEN/NOTIFY requires a persistent session on a specific backend. Use Solid Cable which polls instead of using LISTEN/NOTIFY, switch to Redis for ActionCable pub/sub, or bypass PgBouncer for the specific connection that needs LISTEN.

Need help sizing a Rails Postgres deployment or wrangling a connection pool that is misbehaving in production? TTB Software specializes in Rails performance and infrastructure. We have been doing this for nineteen years.