Configuring connection pooling v6.4.0

Enable Connection Manager's connection pooling for a PGD node group to maximize database throughput by recycling server connections across clients, preventing connection spikes from applications with many short-lived connections. Connection Manager's built-in transaction pooling requires no additional software to deploy or maintain.

For guidance on writing applications that work correctly with each pool mode, see Using connection pooling in your application.

Prerequisites

• Connection Manager running on each data node (PGD 6.4 and later). See Monitoring to verify it's running.
• bdr_superuser role or equivalent to run bdr.alter_node_group_option

Enabling connection pooling

Connection pooling is disabled by default, with server_pool_mode set to none. To enable it, set the mode to session or transaction for your node group.

Using SQL:

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

Using the PGD CLI:

pgd group mygroup set-option server_pool_mode transaction

When enabling pooling, existing connections aren't affected and only new connections use the updated mode. When switching away from transaction mode, existing client connections with no open transaction are closed the next time they start a transaction.

Use transaction mode when connections are long-lived but transactions are short, with idle time between them, for example when client applications use their own connection pooling.

Use session mode for short-lived connections, or if your applications rely on session state that persists across transactions (such as SET, temporary tables, or advisory locks). For full details on each mode, see Connection pooling.

Check the active pool mode.

SELECT node_group_name, server_pool_mode FROM bdr.node_group_summary;

Monitoring the pool

Monitoring Connection Manager describes the available metrics and HTTP endpoints for tracking active connections and pool utilization.

Migrating from pgBouncer

Connection Manager provides built-in connection pooling, removing the need for a separate pooler such as pgBouncer. Its pooling modes are similar to pgBouncer's session and transaction pooling modes. Follow these steps to migrate.

1. Set server_pool_mode to match your current pgBouncer pool mode (session or transaction).
2. Set read_write_max_server_connections to a similar value as pgBouncer's pool_size, and read_write_max_client_connections to a similar value as pgBouncer's max_client_conn. As a starting point, set read_only_max_server_connections and read_only_max_client_connections to the same values. Tune all four settings based on your actual workload.
3. Update application connection strings to point to Connection Manager's read-write port instead of pgBouncer.
4. Verify application behavior:
   • In session mode, application behavior is unchanged from a direct Postgres connection.
   • In transaction mode, pay particular attention to any use of SET commands, prepared statements, or WITH HOLD cursors, and adjust as needed. See Using connection pooling in your application.
5. Remove pgBouncer from your infrastructure once migration is verified.

Authentication configuration stays in pg_hba.conf on each PGD node. No separate user list or pooler configuration file is needed. Any user that can connect to Postgres through Connection Manager can use the pool, with no second place to grant or revoke access.

Connection Manager imposes additional restrictions compared to a direct Postgres connection in some scenarios. Test your application thoroughly before completing the migration.