How to Use Connection Pooling in PostgreSQL for Efficient Database Access

Use pgbouncer or pgpool-II as external connection poolers to manage PostgreSQL connections efficiently. Configure your application to connect to the pooler instead of directly to PostgreSQL, which reduces overhead and improves performance.

Syntax

Connection pooling in PostgreSQL is not built into the database server itself but is handled by external tools like pgbouncer or pgpool-II. You configure these tools with a configuration file specifying connection limits, authentication, and pooling modes.

Typical configuration parts include:

• [databases]: Defines which databases the pooler connects to.
• [pgbouncer]: Settings for pool size, authentication, and timeouts.
• pool_mode: Controls how connections are reused (session, transaction, statement).

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

Example

This example shows how to set up pgbouncer to pool connections for a PostgreSQL database named mydb. The application connects to pgbouncer on port 6432 instead of directly to PostgreSQL on port 5432.

This reduces the number of active connections to PostgreSQL and improves performance under load.

bash

# 1. Install pgbouncer (example for Ubuntu):
sudo apt-get install pgbouncer

# 2. Configure /etc/pgbouncer/pgbouncer.ini with:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

# 3. Create userlist file with credentials:
# Format: "username" "md5passwordhash"
# Use psql or pg_md5 tool to generate md5 hash

# 4. Start pgbouncer:
sudo systemctl start pgbouncer

# 5. Connect your application to pgbouncer:
psql -h 127.0.0.1 -p 6432 -U youruser -d mydb

Output

psql (13.4)
Type "help" for help.

mydb=> SELECT 1;
 ?column? 
----------
        1
(1 row)

Common Pitfalls

• Connecting directly to PostgreSQL instead of the pooler: This bypasses pooling and defeats the purpose.
• Incorrect authentication setup: The pooler needs a proper userlist file with correct password hashes.
• Choosing wrong pool_mode: For most apps, transaction mode is best; session mode holds connections longer, reducing pooling benefits.
• Not tuning pool sizes: Default sizes may be too small or too large for your workload.

bash

# Wrong: Application connects directly to PostgreSQL
psql -h 127.0.0.1 -p 5432 -U youruser -d mydb

# Right: Application connects to pgbouncer pooler
psql -h 127.0.0.1 -p 6432 -U youruser -d mydb

Quick Reference

Setting	Description	Typical Value
listen_addr	IP address pgbouncer listens on	127.0.0.1
listen_port	Port pgbouncer listens on	6432
auth_type	Authentication method	md5
auth_file	File with user credentials	/etc/pgbouncer/userlist.txt
pool_mode	Connection reuse mode	transaction
max_client_conn	Max client connections to pooler	100
default_pool_size	Max server connections per pool	20

Key Takeaways

Use external tools like pgbouncer or pgpool-II for PostgreSQL connection pooling.

Configure your application to connect to the pooler, not directly to PostgreSQL.

Set pool_mode to 'transaction' for efficient connection reuse in most cases.

Ensure authentication files and credentials are correctly set up for the pooler.

Tune pool sizes based on your application's workload to avoid connection bottlenecks.