Using connection pooling in your application v6.5.0

Adapt your application to work with Connection Manager's connection pooling.

For DBA guidance on enabling and operating pooling, see Configuring connection pooling.

Understanding the active pool mode

Query bdr.node_group_summary to see the active pool mode.

SELECT node_group_name, server_pool_mode FROM bdr.node_group_summary;

The pool mode determines what session state persists between transactions.

In none and session modes, your application behaves the same as with a direct Postgres connection for most purposes. The exception is that Connection Manager forwards only a specific set of connection parameters to the backend. See Managing session parameters for details. The difference between none and session is at the infrastructure level: in session mode, backend connections are reused after clients disconnect, reducing server overhead, but this is invisible to the application.

In transaction mode, the backend connection returns to the pool when the transaction ends, and the group's server_reset_mode determines whether session state is cleared. The default discard_all mode clears it, so the next transaction starts clean. The fast mode skips cleanup unless needed, so state can leak to another client's transaction. Don't write application code that relies on session state surviving between transactions either way. See Configuring the reset mode and Connection pooling's unsupported features.

If your application depends on any of these features, ask your DBA to switch to session or none mode (see Configuring connection pooling), or redesign the affected code paths to keep all state within a single transaction.

Adapting your application for transaction mode

Some application patterns that work with a direct Postgres connection need adjustment in transaction mode.

Managing session parameters

Connection Manager forwards a specific set of connection parameters to the backend and re-applies them on each new backend assignment in transaction mode. The parameters it recognizes are client_encoding, DateStyle, TimeZone, standard_conforming_strings, application_name, search_path, and extra_float_digits.

Set these in the connection string rather than with SET commands, so they apply to every backend the pool assigns.

host=myhost dbname=mydb options='-c search_path=myschema -c work_mem=256MB'

Or in a URI.

postgresql://myhost/mydb?options=-c%20search_path%3Dmyschema%20-c%20work_mem%3D256MB

Parameters outside the recognized list aren't re-applied on backend changes unless passed via the options connection parameter.

Using prepared statements

Connection Manager automatically detects missing prepared statements on the backend and re-prepares them on demand, so prepared statements sent via the extended query protocol work seamlessly across transactions. Most client libraries use the extended query protocol by default.

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, deallocating any prepared statements created with PREPARE/EXECUTE SQL statements. With the fast reset mode, such a prepared statement can survive on the backend past the transaction that created it, but the next transaction assigned to that backend isn't guaranteed to be from the same client, so relying on it to still exist isn't safe. To avoid errors either way, follow these practices.

• Avoid PREPARE, EXECUTE, and DEALLOCATE SQL statements. Use the extended query protocol instead, which most client libraries do by default.
• If you need to use PREPARE/EXECUTE/DEALLOCATE SQL statements, issue them inside the transaction where you use them.