DEV Community

Cover image for Setting Up PostgreSQL Connection Pooling with PgBouncer
Raizan
Raizan

Posted on • Originally published at chasebot.online

Setting Up PostgreSQL Connection Pooling with PgBouncer

#postgres #database #devops #pgbouncer

What You'll Need

  • Hetzner VPS or Contabo VPS running Ubuntu 22.04 or 24.04 LTS
  • DigitalOcean as an alternative cloud infrastructure host
  • n8n Cloud or self-hosted n8n for workflow automation pipelines
  • Namecheap if configuring a custom domain for your database routing infrastructure

Table of Contents

Understanding PostgreSQL Connection Overhead and Pooling Modes

PostgreSQL uses a process-per-connection architecture. Every time a client connects to PostgreSQL, the database engine forks a new backend memory process. Each connection costs approximately 2MB to 10MB of RAM, in addition to CPU cycles consumed purely by process creation, context switching, and connection handshakes.

When scaling backend API workers, microservices, or serverless functions that frequently connect and disconnect, PostgreSQL quickly runs out of resources. Attempting to run 1,000 direct database connections on a standard instance will swamp your CPU with context switches and exhaust host memory, severely degrading query latency.

This is where PgBouncer comes in. PgBouncer is a lightweight, single-threaded connection pooler designed specifically for PostgreSQL. It sits between your application and PostgreSQL, presenting itself as a PostgreSQL server to your application while maintaining a fixed, small pool of reusable connections to the underlying database engine. Proper pooling is essential when optimizing database performance for developers, as it stabilizes hardware utilization under extreme load bursts.

PgBouncer operates in three primary pooling modes:

  1. Session Pooling: PgBouncer assigns a backend server connection to the client for the entire duration the client stays connected. When the client disconnects, the server connection is returned to the pool. This mode supports all PostgreSQL features, including temporary tables and prepared statements, but provides minimal connection relief during high-concurrency spikes.
  2. Transaction Pooling: PgBouncer assigns a backend connection to a client only for the duration of a single database transaction. Once COMMIT or ROLLBACK executes, the backend connection returns to the pool. This is the recommended mode for most microservices and web application workloads. It allows thousands of concurrent clients to be served by a tiny pool of underlying connections.
  3. Statement Pooling: PgBouncer breaks connections after every single SQL statement. Multiple-statement transactions (BEGIN ... COMMIT) are prohibited in this mode. It is useful for pure read-heavy reporting or key-value style access patterns, but too restrictive for standard transactional backend applications.

Installing and Configuring PgBouncer

Let's walk through deploying PgBouncer on an Ubuntu machine, such as a Hetzner VPS.

First, update your local package list and install PgBouncer alongside PostgreSQL client tools:

sudo apt-get update
sudo apt-get install -y pgbouncer postgresql-client
Enter fullscreen mode Exit fullscreen mode

After installation, the primary configuration file is located at /etc/pgbouncer/pgbouncer.ini. We will replace the default configuration with a production-ready configuration tailored for transaction pooling.

Open /etc/pgbouncer/pgbouncer.ini using your editor of choice:

sudo nano /etc/pgbouncer/pgbouncer.ini
Enter fullscreen mode Exit fullscreen mode

Write the following full configuration. Do not leave out parameters, as implicit defaults in PgBouncer can cause unexpected authorization or resource exhaustion issues:

[databases]
app_db = host=127.0.0.1 port=5432 dbname=app_production user=app_user password=supersecretpassword

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer_admin
stats_users = pgbouncer_admin, monitor_user

pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 5000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 50

dns_max_ttl = 15
dns_zone_check_period = 0

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
Enter fullscreen mode Exit fullscreen mode

Next, configure authentication. PgBouncer requires a user authentication list mapping database usernames to their hashed credentials or plain text passwords. Create or edit /etc/pgbouncer/userlist.txt:

sudo nano /etc/pgbouncer/userlist.txt
Enter fullscreen mode Exit fullscreen mode

Insert the database usernames and matching passwords (or SCRAM-SHA-256 password hashes) formatted as double-quoted strings:

"app_user" "supersecretpassword"
"pgbouncer_admin" "adminsecretpassword"
"monitor_user" "monitorpassword"
Enter fullscreen mode Exit fullscreen mode

Set appropriate ownership and strict security permissions on both files so system users cannot inspect database credentials:

sudo chown postgres:postgres /etc/pgbouncer/pgbouncer.ini
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 0600 /etc/pgbouncer/pgbouncer.ini
sudo chmod 0600 /etc/pgbouncer/userlist.txt
Enter fullscreen mode Exit fullscreen mode

Now configure local PostgreSQL settings to work safely with PgBouncer. Log into PostgreSQL using psql to set up the dedicated administration account and matching application roles:

CREATE USER pgbouncer_admin WITH PASSWORD 'adminsecretpassword';
ALTER USER pgbouncer_admin WITH SUPERUSER;

CREATE USER app_user WITH PASSWORD 'supersecretpassword';
CREATE DATABASE app_production OWNER app_user;
Enter fullscreen mode Exit fullscreen mode

Verify your PostgreSQL postgresql.conf file (typically located at /etc/postgresql/16/main/postgresql.conf) has optimal base limits. Ensure max_connections in postgresql.conf is configured to comfortably handle the maximum server connections configured inside PgBouncer:

max_connections = 100
shared_buffers = 2GB
work_mem = 16MB
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL to apply configuration changes:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

💡 Fast-Track Your Project: Don't want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-DEVTO.

Configuring Systemd and Managing PgBouncer Administration

To ensure high availability, PgBouncer should be managed as a persistent system daemon managed by Systemd. If you are familiar with scheduling Python scripts with systemd on Linux, managing network services through Systemd units follows the exact same operational discipline.

Check the status of the native PgBouncer service:

sudo systemctl status pgbouncer
Enter fullscreen mode Exit fullscreen mode

If you need a custom systemd unit file to handle high file descriptor limits for thousands of simultaneous incoming sockets, inspect or create /etc/systemd/system/pgbouncer.service.d/override.conf:

sudo mkdir -p /etc/systemd/system/pgbouncer.service.d/
sudo nano /etc/systemd/system/pgbouncer.service.d/override.conf
Enter fullscreen mode Exit fullscreen mode

Add explicit socket resource overrides to prevent running out of file descriptors during traffic spikes:

[Service]
LimitNOFILE=65536
Restart=always
RestartSec=3s
Enter fullscreen mode Exit fullscreen mode

Reload Systemd configuration, enable the daemon on boot, and start PgBouncer:

sudo systemctl daemon-reload
sudo systemctl enable pgbouncer
sudo systemctl restart pgbouncer
Enter fullscreen mode Exit fullscreen mode

Verify that PgBouncer is listening on port 6432:

sudo ss -tulpn | grep 6432
Enter fullscreen mode Exit fullscreen mode

Navigating the PgBouncer Administration Console

PgBouncer exposes an internal administrative pseudo-database that you can connect to using standard psql. Connect to the administrative console by specifying the port 6432 and administrative database pgbouncer:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer
Enter fullscreen mode Exit fullscreen mode

When prompted, enter adminsecretpassword. Once inside the administrative interface, you can issue custom control commands to monitor performance and alter operational state.

To view real-time connection pooling status across all active databases:

SHOW POOLS;
Enter fullscreen mode Exit fullscreen mode

This command outputs a matrix displaying active client connections (cl_active), waiting client connections (cl_waiting), active backend server connections (sv_active), and idle backend server connections (sv_idle).

To inspect individual active client connections:

SHOW CLIENTS;
Enter fullscreen mode Exit fullscreen mode

To view traffic statistics, byte transfers, and query duration metrics:

SHOW STATS;
Enter fullscreen mode Exit fullscreen mode

If you alter user passwords or modify /etc/pgbouncer/pgbouncer.ini, apply changes without dropping active client connections by running:

RELOAD;
Enter fullscreen mode Exit fullscreen mode

If you need to perform database maintenance on the underlying PostgreSQL server without disconnecting front-end application instances, temporarily pause server connections:

PAUSE;
Enter fullscreen mode Exit fullscreen mode

Once maintenance is complete, resume connection flow:

RESUME;
Enter fullscreen mode Exit fullscreen mode

Exit the administration console using standard psql termination syntax:

\q
Enter fullscreen mode Exit fullscreen mode

Benchmarking Connection Concurrency with Python

To demonstrate the concrete architectural benefits of PgBouncer, let me show you a complete, standalone stress-testing script written in Python using asyncpg. This script simulates bursty, concurrent application access patterns—such as those generated when building Telegram bots for workflow management or handling high-volume webhooks where hundreds of parallel handlers hit the database at once.

Save this script as pool_test.py:

import asyncio
import time
import asyncpg

DATABASE_HOST = "127.0.0.1"
DB_NAME = "app_production"
DB_USER = "app_user"
DB_PASS = "supersecretpassword"

CONCURRENT_TASKS = 200
TOTAL_QUERIES_PER_TASK = 10

async def execute_queries(target_port: int, task_id: int):
    connected = False
    while not connected:
        try:
            conn = await asyncpg.connect(
                host=DATABASE_HOST,
                port=target_port,
                user=DB_USER,
                password=DB_PASS,
                database=DB_NAME,
                timeout=10.0
            )
            connected = True
        except Exception as err:
            await asyncio.sleep(0.05)

    try:
        for _ in range(TOTAL_QUERIES_PER_TASK):
            row = await conn.fetchrow("SELECT pg_backend_pid(), clock_timestamp();")
            await asyncio.sleep(0.01)
    finally:
        await conn.close()

async def run_benchmark(target_port: int, label: str):
    print(f"--- Starting Benchmark against {label} (Port {target_port}) ---")
    start_time = time.perf_counter()

    tasks = [
        asyncio.create_task(execute_queries(target_port, i))
        for i in range(CONCURRENT_TASKS)
    ]

    results = await asyncio.gather(*tasks, return_exceptions=True)

    failures = sum(1 for r in results if isinstance(r, Exception))
    elapsed = time.perf_counter() - start_time
    total_queries = CONCURRENT_TASKS * TOTAL_QUERIES_PER_TASK
    qps = total_queries / elapsed

    print(f"Label: {label}")
    print(f"Total Time: {elapsed:.2f} seconds")
    print(f"Total Successful Queries: {total_queries - (failures * TOTAL_QUERIES_PER_TASK)}")
    print(f"Failed Worker Tasks: {failures}")
    print(f"Throughput: {qps:.2f} Queries/Second\n")

async def main():
    print("Initializing Stress Test Framework...\n")

    # Run test directly against PostgreSQL primary port
    try:
        await run_benchmark(target_port=5432, label="Direct PostgreSQL")
    except Exception as e:
        print(f"Direct PostgreSQL benchmark failed: {e}")

    # Run test through PgBouncer connection pooler port
    try:
        await run_benchmark(target_port=6432, label="PgBouncer Middleware")
    except Exception as e:
        print(f"PgBouncer benchmark failed: {e}")

if __name__ == "__main__":
    asyncio.run(main())
Enter fullscreen mode Exit fullscreen mode

Execute the benchmark script after installing asyncpg:

pip install asyncpg
python3 pool_test.py
Enter fullscreen mode Exit fullscreen mode

Interpreting Benchmark Results

When running 200 concurrent tasks directly against PostgreSQL (Port 5432), PostgreSQL is forced to spawn 200 backend server processes. You will observe substantial latency spikes, high CPU usage from process management, and connection rejection errors if max_connections is exceeded.

When running the exact same workload through PgBouncer (Port 6432) in transaction mode:

  • All 200 concurrent tasks establish connection sockets instantly to PgBouncer.
  • PgBouncer routes all incoming queries through the set of underlying backend server connections configured by default_pool_size = 20.
  • Memory consumption on the host server remains completely flat.
  • Query execution throughput increases, while query response latency drops drastically.

Getting Started

Ready to deploy transaction pooling and elevate your database throughput? Here are the recommended cloud platforms to deploy host servers and orchestration workers:

  • Hetzner VPS — Highly cost-effective virtual servers with dedicated NVMe storage ideal for self-managed PostgreSQL and PgBouncer nodes.
  • Contabo VPS — Excellent compute-to-price ratio for hosting high-memory database workloads.
  • DigitalOcean — Developer-friendly cloud infrastructure offering high-performance Droplets and Managed Databases.
  • n8n Cloud — Powerful workflow automation tool that pairs seamlessly with pooled database connections for serverless data pipelines.

Outsource Your Automation

Don't have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr — mention SYS3-DEVTO for priority. Or DM at chasebot.online.

Originally published on Automation Insider.

Top comments (0)