The pgbouncer-safe defaults cannot connect through PgBouncer: jit and search_path are startup parameters it rejects, or silently drops

[AlexeyShalaev]

Measured on 0.2.1 against PostgreSQL 17 and PgBouncer 1.25.2 in transaction mode, PgBouncer at its defaults. create_async_session_manager(BasePostgresConfig(...)) with nothing but host, credentials and an application name:

--- sqlalchemy-foundation-kit, its pgbouncer-safe settings ---
  PgBouncer transaction mode, defaults     ok=0    errors={'ProtocolViolationError': 800}
      first error: unsupported startup parameter: jit

Every connection is refused. BasePostgresConfig.jit defaults to "off" and create_async_session_manager sends it as an asyncpg server_settings entry, which arrives at PgBouncer as a startup parameter, and PgBouncer only passes through the handful it tracks (client_encoding, datestyle, timezone, standard_conforming_strings, application_name). The configuration guide says jit="off" # Required for pgbouncer; measured, it is the one setting that makes the connection impossible through PgBouncer.

The usual admin-side workaround makes it worse. With ignore_startup_parameters = jit,search_path in PgBouncer the connections succeed, and PgBouncer drops both parameters on the floor:

  direct to PostgreSQL                                  jit='off' search_path='app'            application_name='probe'
  PgBouncer, ignore_startup_parameters=jit,search_path  jit='on'  search_path='"$user", public' application_name='probe'

So db_schema is silently not applied and every query lands in public. That is the dangerous half: a setting the library accepted and the database ignored.

For context, the reason this configuration existed is gone on current PgBouncer. Plain SQLAlchemy + asyncpg with the driver's default statement cache runs 800 of 800 statements through PgBouncer 1.25 in transaction mode without an error, because max_prepared_statements has defaulted to 200 since 1.22. Forcing max_prepared_statements=0 brings the classic failure back (320 of 800: prepared statement "__asyncpg_stmt_346__" does not exist), so the zero statement caches and AsyncCConnection still earn their place for older PgBouncers; jit never did.

What I think it needs: jit should default to None and never be sent as a startup parameter unless asked for; db_schema should not be delivered as a search_path startup parameter either, because under transaction pooling the only places a per-role setting can live are ALTER ROLE ... SET / ALTER DATABASE ... SET on the server, or PgBouncer's track_extra_parameters (1.18+), and inside a transaction as SET LOCAL. The unit of work owns the transaction, so SET LOCAL search_path at the start of each transaction() / query() block is the one form that is correct through a transaction pooler; that or schema-qualified metadata, documented as the choice. The configuration guide's PgBouncer section needs rewriting around what was measured: the statement caches, the connection class, and the parameters PgBouncer will and will not carry.

Scripts: pgbouncer_lab.py and jit_probe.py in https://github.com/bedrock-python/bedrock-python.github.io/tree/docs/production-python-series/docs/blog/lab/2026-09-07-pgbouncer-async-sqlalchemy.

[AlexeyShalaev]

Read the source against the numbers here and measured the alternatives before picking one; this is what I intend to ship.

The startup parameters go. BasePostgresConfig.jit defaults to None, and create_async_session_manager puts jit into server_settings only when it is set explicitly, at which point it is still a startup parameter and the docs say so: through PgBouncer that means track_extra_parameters, or ALTER ROLE ... SET jit = off on the server instead. db_schema stops being delivered as search_path in the startup packet altogether.

For applying db_schema I tried three places. A connect-event SET search_path is per server connection, which through a transaction pooler is exactly the leak the session_state_leak case in the lab shows, so that is out. SET LOCAL inside open_session() is correct but covers only the unit of work: manager.get_session(), get_transaction(), a raw engine.connect(), and the statements after a session.commit() inside managed_session() would all run in public, and it needs db_schema threaded into both DI integrations' UoW providers. What I am going with is one level down: a begin listener on the engine that issues SELECT set_config('search_path', $1, true) (the function form of SET LOCAL, so the value is a bind parameter) as the first statement of every transaction. Under the asyncpg adapter BEGIN is sent lazily with the first statement, so this lands inside the transaction, after PgBouncer has pinned the server connection. Verified on PostgreSQL 17: engine.connect(), a session's autobegin (the query() and get_session() shape), session.begin(), an explicit isolation level, managed_session() before and after a commit(), and inside and after a savepoint all show search_path = app; the listener fires once per transaction, not per savepoint. The one shape it cannot cover is a statement run with isolation_level="AUTOCOMMIT", which has no transaction to scope to, and that is equally true of any per-connection state through a transaction pooler; the docs will say so.

Where it lives: AsyncSessionManager(..., search_path=None), with attach_search_path(engine, search_path) beside attach_metrics in session.manager, .with_search_path() on the builder, and create_async_session_manager mapping db_schema onto it. Nothing is attached when no schema is configured, so the cost for everyone else is zero; with one it is one extra round-trip per transaction. The statement caches at 0 and AsyncCConnection stay. ALTER ROLE ... SET, ALTER DATABASE ... SET, track_extra_parameters and schema-qualified metadata get documented as the alternatives, and the PgBouncer section of the configuration guide is rewritten around what was measured here.

Tests: the default config yields server_settings holding only application_name; an explicit jit="off" still lands there; integration tests on PostgreSQL 17 for the schema in every shape above plus a control with no schema. Then both lab scripts against the branch, output in the PR. I will file it as feat:, since two defaults change in a way a direct-to-PostgreSQL user can observe (JIT follows the server default now; the schema is per transaction rather than per connection) and the minor bump is the honest signal.

If you would rather keep search_path inside the unit of work only and out of the engine, say so before the PR goes up.

[AlexeyShalaev]

PR is up: #24.

What shipped: jit defaults to None and is sent only when set, still as a startup parameter then. db_schema never goes as a startup parameter; the manager applies it with SET LOCAL (set_config('search_path', $1, true)) as the first statement of every transaction, from an engine begin listener rather than from the unit of work, so get_session(), get_transaction(), a raw engine.connect() and the statements after a commit() inside managed_session() are covered along with transaction() and query(). AsyncSessionManager(search_path=...) and with_search_path() expose the same mechanism to people who do not go through create_async_session_manager. Caches at 0 and AsyncCConnection stay; the configuration guide has a PgBouncer section built around your numbers, and the agents page follows.

Your two scripts against the branch: both kit rows 800 ok through a default PgBouncer, and the probe reads search_path='app' through PgBouncer and jit='on' on the direct row, since nothing is sent for it now. Full output and the reasoning for the engine-level listener are in the PR.

Nothing to change on your side unless you want JIT off on a direct connection, which is now jit="off" explicitly, or the schema without the per-transaction round-trip, which is ALTER ROLE … SET search_path with db_schema unset.