Search
4 results for “connection-pooling”
Search results
Database Connection Pooling
Why transaction pooling gives far better connection reuse than session pooling, and why that same efficiency silently breaks SQL PREPARE, SET, LISTEN, and session-level advisory locks, even though PgBouncer can support protocol-level named prepared statements under transaction pooling when max_prepared_statements is enabled.
What is the practical difference between session pooling and transaction pooling, and why does transaction pooling scale better?
Session pooling assigns one server connection to a client for their entire session, released back to the pool only when the client disconnects, which supports every PostgreSQL feature but means a mostly-idle client still occupies a real server connection the whole time it's connected. Transaction pooling instead assigns a server connection only for the duration of a single transaction, returning it to the pool the moment the transaction ends, so many more clients can share a small, fixed pool of real connections, since a client that isn't actively mid-transaction isn't holding one at all. This is why transaction pooling is the standard choice for applications with many short-lived connections (like a web app's connection-per-request pattern) against a database with a hard connection limit.
An application uses PREPARE to create a reusable prepared statement, then relies on it across multiple requests. What happens if it's deployed behind a transaction-pooled PgBouncer, and why?
It breaks. A SQL PREPARE statement is a session-level feature, it lives on whatever specific server connection issued the PREPARE, but transaction pooling reassigns the underlying server connection to a different client (or the same client's next transaction) as soon as each transaction ends, so there's no guarantee a later request lands on that same server connection where the prepared statement actually exists. This is explicitly documented as one of the session-based features transaction pooling breaks, along with SET/RESET, LISTEN, WITH HOLD cursors, and session-level advisory locks, all of which depend on state tied to one specific, persistent server connection. This is distinct from protocol-level named prepared statements issued via the extended query protocol (what most driver-level "prepared statements" actually are), which PgBouncer can support under transaction pooling when `max_prepared_statements` is set to a non-zero value.
Why would you choose session pooling over transaction pooling even though it scales to fewer concurrent clients per server connection?
Session pooling is the only mode of the two that supports every PostgreSQL feature without exception, prepared statements, session variables set via SET, LISTEN/NOTIFY, session-level advisory locks, because the server connection genuinely stays with the client for as long as their session lasts. If an application depends on any of those session-level features and can't be refactored around them, session pooling is the correct choice despite its lower connection-reuse efficiency, trading raw scalability for full feature compatibility rather than working around broken session state.