Prepared statements vs transaction pooling

Prepared statements vs transaction pooling
Type: incident simulation · Topic: Connections & Pooling · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-03/06-prepared-statements-vs-pooling

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the app used server-side prepared statements (PREPARE in one
transaction, EXECUTE in another) through PgBouncer in transaction pooling mode.
A prepared statement lives on the specific server connection that created it;
the next transaction may land on a different backend, so EXECUTE fails with
"prepared statement does not exist" (or a re-PREPARE hits "already exists").
The SQL is fine — it's a session-state-vs-pooling mismatch.

The fix (pooling-safe): don't rely on cross-transaction prepared statements —
issue the query directly.

Lesson: prepared statements are session state and don't survive transaction
pooling. Disable server-side prepared statements in the driver/ORM, use the
simple query protocol where appropriate, or use session pooling for that
workload. It is not an indexing, ANALYZE, or connection-count problem.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The errors are about prepared statements ('prepared statement "..." does not exist' / 'already exists'), not about the SQL itself. Under PgBouncer transaction pooling, a prepared statement created on one server connection isn't there on the next.
2. Prepared statements are session state. Transaction pooling spreads your statements across server connections, so a PREPARE in one transaction won't be visible to an EXECUTE that lands elsewhere.
3. Use a pooling-safe pattern: run the query directly (no session-bound PREPARE/EXECUTE across transactions). The real fix is to disable server-side prepared statements in the driver or use session pooling for that workload.