Configuring ActiveRecord with PgBouncer Transaction Mode

This guide is part of Rails ActiveRecord Connection Pool. Rails is one of the frameworks that does not work behind transaction-mode pooling out of the box, and the reasons are specific and fixable. Three defaults break, one multi-tenant pattern is actively dangerous, and migrations need a separate path. This guide covers all four.

The first failure usually appears within minutes of switching pool_mode, and it is intermittent — which is what makes it confusing:

ActiveRecord::StatementInvalid (PG::UndefinedPsqlStatement: ERROR:
  prepared statement "a12" does not exist):
  app/models/order.rb:44:in `by_customer'

It appears under concurrency and not in testing, because at low concurrency PgBouncer often hands the same client the same backend and the prepared statement happens to be there. Raising concurrency makes reassignment likely and the error constant.

Rapid Incident Diagnosis

Three checks confirm that transaction pooling, rather than something else, is the cause.

1. Confirm the pool mode. On the PgBouncer admin console, SHOW CONFIG; and read pool_mode. If it is transaction or statement, cross-transaction backend affinity does not exist and every item below applies.

2. Confirm the error class. prepared statement "..." does not exist (SQLSTATE 26000) is the prepared-statement failure. relation "..." does not exist for a temporary table is the temp-table failure. Data appearing under the wrong tenant is the search_path failure and the most serious of the three.

3. Confirm it scales with concurrency. Run the same request path at one concurrent client and at fifty. If the error appears only at fifty, backend reassignment is the mechanism — which is exactly what transaction pooling does.

The Three Settings

# config/database.yml — behind PgBouncer in transaction mode
production:
  adapter: postgresql
  host: pgbouncer.internal
  port: 6432
  database: orders_production
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 }.to_i + 1 %>
  checkout_timeout: 3

  prepared_statements: false    # 1 — named statements cannot survive reassignment
  advisory_locks: false         # 2 — migrations must use a direct connection instead

  variables:
    application_name: orders-web        # constant per process — safe here
    statement_timeout: 5000             # server-side bound; also constant

prepared_statements: false is the setting that removes the loud failure. The cost is that PostgreSQL re-parses and re-plans each statement, which for the short, parameterised statements ActiveRecord generates is typically a fraction of a millisecond — measurably less than the network hop the proxy itself adds. PgBouncer 1.21 and later can track prepared statements and replay them onto each backend, which preserves plan reuse at the cost of proxy memory; if that is enabled with a non-zero max_prepared_statements, this setting can stay true.

advisory_locks: false stops ActiveRecord attempting to take a session-level advisory lock around migrations. That lock is what prevents two concurrent deploys running the same migration, so disabling it removes a real safety property — which is why migrations should run through a direct connection that bypasses the proxy entirely, where the lock works as intended.

The variables: block applies its settings at connection establishment. That is safe for values that are constant for the process, and unsafe for anything tenant-specific. This is the distinction the next section is about.

The Multi-Tenant Hazard

Schema-per-tenant applications set search_path to select the tenant. Doing that on the connection is the natural implementation and the dangerous one.

# DANGEROUS behind transaction pooling: the setting survives the commit
# and the next transaction on that backend inherits it.
ActiveRecord::Base.connection.execute("SET search_path TO #{tenant.schema}")

# Correct: scoped to the transaction, reverts automatically at COMMIT.
ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute(
    ActiveRecord::Base.sanitize_sql(["SET LOCAL search_path TO %s", tenant.schema])
  )
  yield
end

The failure mode of the first form has no error and no log line. Tenant A sets its schema, commits, and the connection returns to the proxy still carrying that setting. Tenant B is assigned the same backend, issues no SET of its own, and reads tenant A’s data — a correct-looking response containing the wrong customer’s records.

Gems that implement schema-based multi-tenancy generally use the per-connection form because it is correct without a proxy. Behind transaction pooling they need configuration or replacement, and the check is worth doing explicitly rather than assuming.

The alternative that avoids the question entirely is row-level tenancy with a tenant column and a scope, which requires no session state at all. For an application about to adopt transaction pooling, it is worth weighing.

Migrations Need a Direct Path

Migrations require session-scoped behaviour that transaction pooling cannot provide — the advisory lock above, and CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block at all.

Give them their own connection specification pointing at the database directly:

production:
  primary:
    <<: *pooled_defaults
    host: pgbouncer.internal
    port: 6432
  primary_migration:
    <<: *pooled_defaults
    host: postgres.internal        # direct, bypassing the proxy
    port: 5432
    pool: 2
    advisory_locks: true           # works correctly here
    migrations_paths: db/migrate
    database_tasks: true

Running migrations against primary_migration restores every session-scoped guarantee they depend on, and it costs two connections during a deploy rather than for the life of the application. The same reasoning applies to any maintenance task using LISTEN, session advisory locks, or long-lived temporary tables.

Validation and Verification

Test at concurrency, not at rest. A single client frequently gets the same backend and passes trivially. Force reassignment by setting default_pool_size below the test’s concurrency:

; pgbouncer.ini — for the verification run only
[databases]
orders_production = host=postgres.internal pool_size=3

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
query_wait_timeout = 10

With three server connections and twenty concurrent clients, every transaction is reassigned. A test suite that passes under those conditions has exercised the path that matters.

Verify tenant isolation explicitly. The search_path failure produces no error, so it needs a deliberate assertion:

# Interleave two tenants across many transactions and assert isolation.
20.times do
  Tenant.find_each do |tenant|
    with_tenant(tenant) do
      assert_equal tenant.schema,
        ActiveRecord::Base.connection.select_value("SHOW search_path").split(",").first.strip
      assert Order.all.all? { |o| o.tenant_id == tenant.id }
    end
  end
end

Confirm the proxy is actually multiplexing. SHOW POOLS on the admin console: cl_active should greatly exceed sv_active. If the two are close, something is pinning sessions and the proxy is adding a hop for no benefit.

Common Failure Patterns & Remediation

The row worth re-reading is the second. It is the only entry in the table whose failure produces no error, no log line and no failed request — just a correct-looking response containing another customer’s data. Everything else on this page can be found by watching error rates; that one has to be tested for deliberately.

Frequently Asked Questions

Related

• Rails ActiveRecord Connection Pool — the parent guide on pool sizing and mechanics.
• Fixing ActiveRecord ConnectionTimeoutError in Puma — diagnosing exhaustion before reaching for a proxy.
• PgBouncer Transaction vs Statement Pooling — the pooling modes and what each one breaks.
• Using Prepared Statements with PgBouncer Transaction Mode — the prepared-statement problem across all drivers.