Use Prepared Statements Correctly with Pooling

Why This Matters

Prepared statements are tied to individual database connections. In transaction-mode pooling, connections are shared between requests, causing "prepared statement does not exist" errors.

The Problem

-- Named prepared statement
PREPARE get_user AS SELECT * FROM users WHERE id = $1;

-- In transaction mode pooling, next request may get different connection
EXECUTE get_user(123);
-- ERROR: prepared statement "get_user" does not exist

The Solution

-- Option 1: Use unnamed prepared statements (most ORMs do this automatically)
-- The query is prepared and executed in a single protocol message

-- Option 2: Deallocate after use in transaction mode
PREPARE get_user AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(123);
DEALLOCATE get_user;

-- Option 3: Use session mode pooling (port 5432 vs 6543)
-- Connection is held for entire session, prepared statements persist

Driver Settings

-- Many drivers use prepared statements by default
-- Node.js pg: { prepare: false } to disable
-- JDBC: prepareThreshold=0 to disable