Ecto Repo prepare option and PgBouncer transaction pooling: which setting belongs in production config

Question

An Ecto application connecting to PostgreSQL works in local development but is being deployed behind PgBouncer in transaction pooling mode (as is common with managed Postgres such as RDS Proxy, Supabase, or Cloud SQL). The relevant documented feature is the Postgrex prepare option on the Repo, which defaults to :named.

Named prepared statements are bound to a database session. Under transaction pooling, a client connection can be reassigned to a different server session between transactions, so a named statement prepared earlier may no longer exist on the session handling the next query. The documented alternative, prepare: :unnamed (available since Ecto 3.5 / Postgrex 0.15), avoids this but gives up server-side plan caching across transactions, which may add planning overhead for repeated complex queries. Assume Ecto 3.10+ and Postgrex 0.16+; behavior should be verified against the versions actually in use.

There is also uncertainty about scope: the setting is per Repo, and ecto.migrate runs on its own connection that honors the same option, so migrations in a transaction-pooled staging environment may behave differently from runtime queries.

Should prepare: :unnamed be set unconditionally for any deployment behind transaction pooling, or only after measuring plan-cache impact? Does the migration connection need separate treatment? Is there a supported way to detect pooling mode at boot rather than relying on manual config?

Tasadduq Burney · Answer

Answer
Set prepare: :unnamed in your Repo configuration for any production deployment that uses PgBouncer (or similar) in transaction‑pooling mode.
Why this is the safe default

Named prepared statements are tied to a PostgreSQL server session.
In transaction pooling a client connection can be reassigned to a different server session between transactions, making a previously named statement unavailable and causing “prepared statement not found” errors.
prepare: :unnamed tells Postgrex to use the unnamed statement portal, which lives only for the duration of a single query and is not pooled across sessions, eliminating the error.

When you might keep :named
If you have measured that the loss of server‑side plan caching does not noticeably increase latency for your workload, you may keep the default :named. Without that measurement, the safest choice is :unnamed.
Steps to apply the setting

Open your config file (e.g., config/prod.exs or config/config.exs).
Ensure the Repo configuration includes the option:
config :my_app, MyApp.Repo,
  username: System.get_env("DB_USERNAME"),
  password: System.get_env("DB_PASSWORD"),
  hostname: System.get_env("DB_HOSTNAME"),
  database: System.get_env("DB_DATABASE"),
  prepare: :unnamed   # <-- add this line

Alternatively, set via an environment variable:
# config/prod.exs
prepare: {:system, "ECTO_PREPARE", :unnamed}

Run your migrations; they use the same Repo, so they inherit :unnamed automatically. No separate configuration is needed.
Verify in a staging environment behind PgBouncer:
  
    Run a representative query twice.
    Check the application logs for any “prepared statement not found” messages.
    If none appear, the setting works.

Detecting pooling mode at boot
Ecto/Postgrex does not provide a built‑in way to query whether the connection is behind a transaction‑pooler. The setting must be supplied explicitly via config or an environment variable.

Ecto Repo prepare option and PgBouncer transaction pooling: which setting belongs in production config

1 answer

Answer

Why this is the safe default

When you might keep `:named`

Steps to apply the setting

Detecting pooling mode at boot

Your perspective belongs here.

0 question comments