Back to articles
AI & Cloud

Optimizing PostgreSQL Database with PgBouncer: Solution for Handling Thousands of Connections in Enterprise Systems

A detailed guide on deploying and optimizing PostgreSQL with PgBouncer to handle thousands of simultaneous connections, enhancing scalability and stability for enterprise systems....

DPTCloud Engineering4 min read
Optimizing PostgreSQL Database with PgBouncer: Solution for Handling Thousands of Connections in Enterprise Systems

Introduction to PgBouncer and Benefits for Enterprise Systems

PostgreSQL is a powerful relational database management system widely used in enterprise environments. As application and user counts grow, handling thousands of simultaneous database connections can overwhelm the database, leading to degraded performance and increased response times. PgBouncer is a lightweight, efficient connection pooler that addresses this issue by managing and reusing connections to PostgreSQL.

In enterprise settings where applications frequently access the database, PgBouncer reduces the load on PostgreSQL by limiting the number of actual connections to the server, instead of allowing each application to open a separate connection. This not only conserves system resources but also improves overall system performance and stability.

Deploying PgBouncer on VPS or Dedicated Server

To deploy PgBouncer, you can use services such as High-RAM Cloud VPS or Dedicated Compute Servers to ensure sufficient resources for both PostgreSQL and PgBouncer.

Prerequisites

  • PostgreSQL installed and running.
  • A Linux-based server (Ubuntu/Debian/RHEL).
  • PgBouncer software installed.

Configuring PgBouncer

The main configuration file for PgBouncer is pgbouncer.ini. Below is a basic configuration example:

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
server_reset_query = DISCARD ALL

[database]
host = 127.0.0.1
port = 5432
database = your_database
username = your_user

Explanation of key parameters:

  • listen_addr and listen_port: The address and port where PgBouncer listens for incoming connections.
  • auth_type: Authentication method (md5, trust, etc.).
  • auth_file: File containing user credentials.
  • pool_mode: Pooling mode (session, transaction, statement).
  • server_reset_query: Query to reset the connection after each transaction.

Starting PgBouncer

After configuration, start PgBouncer with the following commands:

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Testing and Performance Optimization

Testing Connection

To verify PgBouncer is running, use the following command to connect to port 6432 (adjust as per your configuration):

psql -h 127.0.0.1 -p 6432 -U your_user -d your_database

Monitoring Performance

Users can check current connection counts and activity by accessing the status file:

echo "show stats" | pgbouncer -c /etc/pgbouncer/pgbouncer.ini

Alternatively, use:

pgbouncer -c /etc/pgbouncer/pgbouncer.ini -q "show stats"

Tuning Configuration for High Load

To handle high loads, adjust the following parameters in pgbouncer.ini:

  • max_client_conn: Maximum number of client connections allowed.
  • default_pool_size: Default pool size for each connection.
  • reserve_pool_size: Number of connections reserved for critical applications.

Troubleshooting and Preventive Solutions

Common Error Causes

  • Connection overload: Number of connections exceeds configured limits.
  • Insufficient system resources: CPU, RAM, or disk I/O constraints.
  • Incorrect configuration: Misconfigured or missing critical parameters.

Preventive Measures

  • Regularly review and update PgBouncer configuration based on actual load.
  • Monitor system and database performance periodically.
  • Set alerts when connection counts approach limits.
  • Run PgBouncer on a separate server to avoid impacting PostgreSQL.

References

DPTCloud Tier III Infrastructure

High-RAM Cloud VPS Plans Optimized for AI, Databases & Cache

Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.

View 22 VPS Plans
ECO · vn-02Recommended

VPS-4E

297.000 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe
ECO · vn-02

VPS-5E

614.900 ₫ / mo

10% VAT included

  • CPU: 8 vCPU
  • RAM: 16 GB
  • SSD: 100 GB NVMe
ADVANCED · vn-03Recommended

VPS-4T

548.900 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478