Architecting Heroku Postgres Connection Pooling with PgBouncer

Requirements

When a Heroku application scales to multiple dynos, each dyno may open many database connections. Heroku Postgres plans enforce a hard limit on concurrent connections (e.g., 20 connections on the hobby‑dev plan, 120 on standard‑0). If the application uses a multi‑threaded or concurrent worker model, the sum of connections across dynos can exceed this limit, resulting in "too many connections" errors and failed requests.

Smallest suitable design

The minimal architecture that satisfies the requirement is:

1. Application dynos connect to a PgBouncer instance instead of directly to Postgres.
2. PgBouncer maintains a small pool of actual Postgres connections (configured via its pool_size setting).
3. Each dyno can open many virtual connections to PgBouncer; PgBouncer multiplexes them onto the limited Postgres connections.
4. Use PgBouncer’s transaction pooling mode so a connection is returned to the pool as soon as a transaction ends.

This design adds only one network hop (app → PgBouncer → Postgres) and does not require changes to the application code beyond updating the connection string.

Trust and data boundaries

Trust boundaries remain within Heroku’s trusted network:

• Connections between the dyno and PgBouncer are SSL‑encrypted (Heroku enforces TLS for all internal service links).
• PgBouncer to Postgres traffic also travels over Heroku’s private network with TLS.
• No data leaves the Heroku trust zone; the only new component is the PgBouncer process, which runs in the same trusted environment as the add‑on.

Operational checks

After deploying PgBouncer, verify the setup with the following steps:

1. Confirm the connection string points to PgBouncer:

   heroku config:get DATABASE_URL -a <APP_NAME>
   # Expected: postgres://user:pass@pgbouncer-host:5432/dbname
   

   Run this in the Heroku CLI (requires account access to the app).
2. Check Postgres connection limit:

   heroku pg:info -a <APP_NAME>
   # Look for "Max connections" under the Postgres add‑on output.
   

3. Inspect PgBouncer pool status via its admin console:

   psql "$(heroku config:get DATABASE_URL -a <APP_NAME>)" -c "SHOW POOLS;"
   # Output shows client connections, active server connections, and waiting clients.
   

   This requires the psql client and network access to the PgBouncer host (provided by the add‑on).
4. Monitor for clogged pools: Watch the waiting column in the SHOW POOLS output. A consistently non‑zero value indicates the pool size may be too low for the current traffic.

Failure modes

• Pool undersizing: If pool_size is set lower than the peak number of concurrent transactions, PgBouncer will queue requests, increasing latency and potentially causing application timeouts.
• Long‑running transactions: In transaction pooling mode, a connection is held until the transaction ends. A transaction that stays open for seconds or minutes ties up a Postgres connection, reducing the effective pool size and leading to "too many connections" errors.
• Incompatible session features: Prepared statements, LISTEN/NOTIFY, or session‑level SET commands do not survive across transactions and will fail or behave incorrectly unless PgBouncer is configured in session pooling mode (which uses more Postgres connections).

Conditions that would change the design

Re‑evaluate the architecture if any of the following occur:

• The application requires session‑level state (e.g., temporary tables, SET search_path) that must persist across multiple statements; switch to session pooling or consider connection limits per dyno instead of pooling.
• Observed latency spikes correlate with a high number of waiting clients in PgBouncer; increase pool_size or upgrade the Heroku Postgres plan to raise the connection limit.
• Application profiling reveals inefficient queries that hold connections long; optimize the queries or reduce transaction scope before adding more pooling capacity.
• Heroku introduces a native connection‑pooling feature for the chosen Postgres plan that eliminates the need for an extra hop; in that case, remove PgBouncer to simplify the architecture.