pgBouncer, and the search_path bug that corrupted data

Every Postgres connection costs real memory, and Postgres has a hard limit on how many it'll accept. A Node backend under real concurrent load — especially several instances each holding their own pool — will happily try to open more than that. pgBouncer sits in front, keeps a smaller pool of real connections, and multiplexes many client connections onto it. The app thinks it has its own connection; it doesn't.

I didn't build this in from day one. I added it once the platform's actual expected scale was concrete, after starting with just a backend, a frontend and a database. Provisioning for scale you haven't confirmed is its own mistake — I add the piece when the need is real.

DATABASE_URL=postgresql://...@pgbouncer:6432/db   # runtime, through the pooler
DIRECT_URL=postgresql://...@postgres:5432/db      # migrations, straight to Postgres

Transaction pooling — the mode that actually gives you the multiplexing benefit — doesn't support prepared statements or session-level state, and DDL migrations need both. So migrations bypass the pooler entirely via DIRECT_URL.

The part that bit me

Transaction pooling reuses one real connection across different logical client sessions, sometimes mid-sequence. If one session runs SET search_path and pooling then hands that same connection to a different client, the setting doesn't reset. The next client inherits an unexpected schema context and silently reads or writes the wrong schema.

This wasn't theoretical. It corrupted test and seed data during development — no error, no crash, just data landing in the wrong place. That's alarming enough that I now keep a permanent CI safeguard: a smoke test that runs the whole stack through Docker Compose and specifically hammers queries through pgBouncer, not against a direct connection.

The fix in the code: don't rely on session-level SET search_path behind a transaction-pooling proxy at all. Bake the schema into the connection string, or fully qualify every table reference. Treat search_path as something that must never depend on connection reuse being clean, because it won't be.

Test through the pooler from the start. A local setup talking straight to Postgres will never surface this class of bug — it only shows up with real pooled connections under load, which usually means it first appears somewhere shared or production-like unless you went looking for it on purpose.