PgBouncer, search_path, and a Schema That Vanished

· 6 min read · Jo4 Team
postgres docker devops springboot

Our app lives in a non-default Postgres schema, alertstage. For a long time the app set the search_path itself over the connection, and everything resolved fine. Then we put PgBouncer in front of Postgres in transaction-pooling mode, and unqualified queries started hitting the wrong schema. The pool cutover took prod down.

TL;DR: In transaction-mode PgBouncer, two things break app-side search_path: pgjdbc sends currentSchema as a search_path startup parameter, which PgBouncer rejects outright; and a per-session SET search_path is discarded at every transaction boundary. The fix is to stop configuring search_path from the app entirely and pin it server-side — but use ALTER ROLE ... IN DATABASE, not ALTER DATABASE, or DigitalOcean's managed Postgres silently no-ops it.

The Problem

Two independent failures, both from transaction pooling:

  1. Startup parameter rejected. pgjdbc's currentSchema=alertstage is transmitted as a search_path connection startup parameter. PgBouncer refuses it: unsupported startup parameter: search_path. Connection dies.
  2. Per-session SET discarded. The fallback — a Hikari connection-init SET search_path — sets it per session. But under transaction pooling, sessions are multiplexed across backends and reset at each transaction boundary. Your SET evaporates. Unqualified queries (and none of our entities hardcode @Table(schema=...)) resolve against the default schema, find nothing, and blow up.

The app was doing the right thing for a direct connection and exactly the wrong thing for a pooled one.

The Fix: Pin It Server-Side, Own It in Provisioning

Stop setting search_path over the connection at all. Make the schema and the search_path the responsibility of DB provisioning, applied before the app starts, so every connection — direct or pooled — lands in alertstage with zero client-side configuration. The app just connects and runs Liquibase against a schema that already exists.

The DatabaseConfig shrank from ~120 lines of runtime schema wiring to a plain datasource bean. The load-bearing work moved to bootstrap:

psql ... -c "CREATE SCHEMA IF NOT EXISTS alertstage;"

# Pin server-side so EVERY connection resolves alertstage with NO client-side search_path.
psql ... -c "ALTER ROLE \"$DB_USER\" IN DATABASE \"$DB_DATABASE\" \
             SET search_path TO \"alertstage\", public;" \
  && echo "✅ search_path pinned" \
  || { echo "::error::Failed to pin search_path"; exit 1; }

A server-side default is applied by Postgres on every new backend connect, regardless of pooling mode. Both direct and pooled connections resolve alertstage correctly. No startup parameter, no per-session SET, nothing for PgBouncer to reject or discard.

The Trap: ALTER DATABASE Silently No-Ops

This is the part that actually took prod down. The intuitive command is ALTER DATABASE ... SET search_path. On DigitalOcean managed Postgres, the doadmin user is not a superuser, and DO silently refuses ALTER DATABASE ... SET — it no-ops, no error. A prior deploy ran exactly that, "succeeded," and left prod with no default search_path. The pool cutover then had nothing to fall back on, and everything 500'd.

ALTER ROLE <user> IN DATABASE <db> SET ... works for a non-superuser and does the same job. Two rules learned the hard way:

  1. Use ALTER ROLE ... IN DATABASE, not ALTER DATABASE, on managed Postgres.
  2. Never warn-and-continue on infra like this — fail the build. The old script did || echo "⚠️ could not set...". That warning is exactly what hid the problem until prod. Now it exit 1s, and then verifies the setting actually took on a fresh connection:
EFFECTIVE_SP=$(psql ... -tAc "SHOW search_path;")
case "$EFFECTIVE_SP" in
  *alertstage*) echo "✅ Verified" ;;
  *) echo "::error::search_path did NOT take effect (got: $EFFECTIVE_SP)"; exit 1 ;;
esac

We mirror the same provisioning in local E2E (a postgres initdb script) and integration tests (Testcontainers), so "schema exists and search_path is pinned before the app starts" holds in every environment, not just prod.

The Broader Lesson: Connection State Is a Lie Under Pooling

The specific bug is search_path, but the category is bigger. Transaction-mode pooling multiplexes many logical sessions over a few physical backends, and any per-session state you set — SET statements, session variables, prepared statements, temp tables, advisory session locks — may not survive to the next query, because the next query might run on a different backend. The mental model that "I set it once on the connection and it stays" is simply false under transaction pooling.

The fix pattern generalizes: push connection state out of the application and into something that applies uniformly to every backend. For search_path that's a server-side role/database default. For prepared statements it's disabling server-side prepares in the driver (or using a pooler mode that supports them). For anything session-scoped, ask "would this survive if the very next statement landed on a fresh backend?" — and if the answer is no, don't rely on it.

Verify in Every Environment, Not Just Prod

The reason this bit us in prod specifically is that our lower environments used direct connections, where per-session search_path worked fine — so the bug was invisible until the pooler went live. The lesson: make your local and CI setups mirror the production connection topology. We now provision the schema and pin the search path identically in local Docker (an initdb script) and integration tests (a Testcontainers hook), so "schema exists and search_path is pinned before the app starts" is an invariant that holds everywhere. A bug that can only manifest in prod is a bug you'll meet in prod.

Lessons Learned

  • Transaction pooling changes the rules. Startup params get rejected and per-session state gets discarded. Anything you set "on the connection" is suspect under PgBouncer.
  • Own schema resolution in provisioning, server-side. A pinned server-side default survives every pooling mode; app-side search_path does not.
  • ALTER DATABASE can silently no-op on managed Postgres. Use ALTER ROLE ... IN DATABASE, then verify on a fresh connection. And fail the build — a warning is how this hid until prod.

Been bitten by a "successful" command that silently did nothing? I want to hear it.

Building jo4.io - a URL shortener running Spring Boot, Postgres, and PgBouncer in production.