Connection pooling v6.5.0

Configure connection pooling to reuse backend connections across clients, reducing server memory overhead and improving throughput for workloads with many short-lived sessions. Connection Manager's built-in pooling removes the need for external tools such as pgBouncer in most deployments.

See Configuring connection pooling for DBA setup and migration guidance, and Using connection pooling in your application for developer guidance.

Configuring the pool mode

Set the pool mode for a node group using bdr.alter_node_group_option:

SELECT bdr.alter_node_group_option('mygroup', 'server_pool_mode', 'transaction');

Or using the PGD CLI:

pgd group mygroup set-option server_pool_mode transaction

The server_pool_mode group option controls the pooling behavior.

The current pool mode is visible in the server_pool_mode column of the bdr.node_group_summary view.

Configuring the reset mode

Set the reset mode for a node group to control how Connection Manager cleans up a pooled backend connection before returning it to the pool for reuse. This option only takes effect in transaction pool mode. See Configuring the pool mode for session mode's cleanup behavior.

Without cleanup, a backend connection can carry over session state left by the client that just used it, for example an open transaction, prepared statements, SET values, temporary tables, or advisory locks.

Use bdr.alter_node_group_option:

SELECT bdr.alter_node_group_option('mygroup', 'server_reset_mode', '<value>');

Or use the PGD CLI:

pgd group mygroup set-option server_reset_mode <value>

The server_reset_mode option accepts two values. The current reset mode is visible in the server_reset_mode column of the bdr.node_group_summary view.

fast trades the cleanup guarantees of discard_all for lower latency and higher throughput, following the same approach as PgBouncer's transaction pooling default, which skips the reset query rather than running it after every transaction. Enable it only for applications that already avoid the features listed in Unsupported features.

Managing session parameters

Connection Manager forwards a specific set of connection parameters to the backend in all pool modes. The parameters it recognizes are client_encoding, DateStyle, TimeZone, standard_conforming_strings, application_name, search_path, and extra_float_digits. Additional parameters can be included via the options connection parameter using -c name=value syntax.

In transaction mode, where the backend connection can change between transactions, Connection Manager re-applies these parameters each time it assigns a new backend. Because only connection parameters are re-applied, avoid using SET commands to configure session parameters in transaction mode. Changes made with SET aren't preserved when the backend connection changes. See Using connection pooling in your application for connection string examples.

Using prepared statements

Prepared statements sent via the extended query protocol work seamlessly across transactions in all pool modes. Connection Manager automatically detects missing prepared statements on the backend and re-prepares them on demand, transparently managing statement naming between the client and the backend.

In transaction mode with the default discard_all reset mode, DISCARD ALL runs when the backend connection is returned to the pool at the end of each transaction, which deallocates any prepared statements created with PREPARE/EXECUTE SQL statements. With the fast reset mode, a prepared statement created with PREPARE/EXECUTE can survive on the backend past the transaction that created it, but the next transaction using that same backend connection isn't guaranteed to be from the same client, so relying on it to still exist isn't safe. Either way, issue PREPARE/EXECUTE SQL statements inside the transaction where you use them, or use the extended query protocol instead. See Using prepared statements in the developer guide for specific steps.

Reusing connections

In session and transaction modes, Connection Manager returns the backend connection to the pool when a client disconnects or a transaction ends. If a client triggers an unsupported feature or closes the connection with commands still pending, Connection Manager discards that backend connection rather than returning it to the pool. Other clients aren't affected.

Unsupported features

Some Postgres features aren't supported in transaction mode, regardless of server_reset_mode. With discard_all, relying on them fails predictably, since DISCARD ALL clears the state they depend on. With fast, that cleanup is skipped, so the failure isn't predictable, and state can instead persist and leak to another client's transaction. Only enable fast mode for applications that already avoid all of the following.

• SET changes made during a session don't persist in transaction mode. Configure session parameters in the connection string instead. See Managing session parameters for details.
• PREPARE/EXECUTE/DEALLOCATE SQL statements aren't supported in transaction mode. Issue these statements inside the transaction where you use them, or use the extended query protocol instead. See Using prepared statements for details.
• Holdable cursors (WITH HOLD) aren't supported in transaction mode. They persist beyond the transaction boundary, which conflicts with returning the backend connection to the pool when the transaction ends.
• LISTEN isn't supported in transaction mode. A LISTEN subscription persists beyond a single transaction, which conflicts with returning the backend connection to the pool.
• Advisory locks held across transaction boundaries aren't supported in transaction mode.
• Temporary tables accessed across transactions aren't supported in transaction mode.
• Replication connections are rejected in all pool modes.