How did you set up and configure PgBouncer to optimize database connections?

PgBouncer is a lightweight connection pooler for PostgreSQL that helps reduce the overhead of establishing connections to the database.

To set up PgBouncer, I usually:

1. Install PgBouncer on a server close to the application.
2. Configure the pgbouncer.ini file with connection parameters, max connections, and pool mode (session, transaction, or statement).
3. Set parameters like pool_mode = transaction for optimal transaction handling.
4. Set limits on max_client_conn and timeout.
5. Restart PgBouncer and check logs for debugging.

Example of pgbouncer.ini:

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

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