Configuring PostgreSQL Connection Pooling on Linux Servers

What You’ll Need

• Hetzner VPS or DigitalOcean instance (Ubuntu 22.04 or 24.04 LTS with root/sudo access)
• n8n Cloud or self-hosted n8n instance (optional, for automation workflows connecting to Postgres)
• PostgreSQL 14, 15, or 16 installed on your server
• Python 3.10+ with asyncpg installed for benchmarking

Table of Contents

• Understanding PostgreSQL Process Architecture and the Need for Connection Pooling
• Step 1: Installing PostgreSQL and Tuning Core Database Parameters
• Step 2: Installing and Configuring PgBouncer for High Throughput
• Step 3: User Authentication and Systemd Service Integration
• Step 4: Benchmarking Connection Performance with Python
• Getting Started

Understanding PostgreSQL Process Architecture and the Need for Connection Pooling

Every time a client application connects to PostgreSQL, PostgreSQL forks a brand new backend process (postgres) dedicated solely to that single connection. This process model offers rock-solid isolation and stability, but it introduces a severe bottleneck under heavy concurrency. Each backend process consumes anywhere between 2 MB and 10 MB of RAM just to exist, not including query working memory (work_mem).

When your application scales up—whether you are serving web traffic, running high-frequency API workers, or running scheduling Python scripts for automated tasks —creating and destroying database connections on every request cripples throughput. CPU cycles are wasted on process context switching and SSL handshakes instead of executing queries.

This is where lightweight connection proxying becomes essential. Connection poolers sit between your application and PostgreSQL. Instead of opening 500 direct database connections, your applications open connections to a proxy like PgBouncer, which maintains a small, pre-warmed pool of persistent connections to the actual PostgreSQL daemon.

+---------------------+     +---------------------+
| Web App / Microservice|   | Background Python Jobs |
+---------------------+     +---------------------+
           \                       /
            \  (500 Client Conns) /
             v                   v
      +---------------------------------+
      |        PgBouncer Proxy          |
      |          (Port 6432)            |
      +---------------------------------+
                         |
                         | (20 Backend Conns)
                         v
      +---------------------------------+
      |       PostgreSQL Server         |
      |          (Port 5432)            |
      +---------------------------------+

PgBouncer operates in three primary pooling modes:

1. Session Pooling: Assigns a server connection to the client for the entire duration the client stays connected. Once disconnected, the server connection returns to the pool.
2. Transaction Pooling: Assigns a server connection to the client only for the duration of a transaction block (BEGIN … COMMIT). This is the sweet spot for web applications.
3. Statement Pooling: Assigns a server connection per individual SQL statement. Multi-statement transactions are strictly forbidden in this mode.

For almost all web apps, API servers, and automated pipelines, Transaction Pooling provides maximum concurrency gains with minimal side effects.

Step 1: Installing PostgreSQL and Tuning Core Database Parameters

Let’s begin by provisioning PostgreSQL on a clean server. If you are provisioning fresh infrastructure, I recommend spinning up a Hetzner VPS due to their fast NVMe storage performance, which helps significantly with transactional database execution.

First, update your local package indexes and install PostgreSQL along with its contrib packages:

sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib

Once installed, check that PostgreSQL is running:

sudo systemctl status postgresql

To allow PgBouncer to manage database traffic efficiently without exhausting server resources, we need to adjust core configuration options in postgresql.conf. Locate your configuration file (typically /etc/postgresql/16/main/postgresql.conf or /etc/postgresql/15/main/postgresql.conf depending on your version):

sudo nano /etc/postgresql/16/main/postgresql.conf

Find and adjust the following parameters inside the configuration file:

listen_addresses = '127.0.0.1,10.0.0.5'
max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10MB
min_wal_size = 1GB
max_wal_size = 4GB

Key points to understand about this configuration:

• max_connections = 100: Counter-intuitively, setting max_connections to a huge number (like 2000) harms PostgreSQL performance due to CPU cache thrashing and lock contention. Keep this low (usually between 50 and 200).
• work_mem = 10MB: Sets the maximum memory allocated for sorting operations and hash tables per query step before writing to temporary disk files.
• shared_buffers = 2GB: Dedicates memory for caching database blocks (typically set to 25% of total system RAM on dedicated servers).

Save the file and restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Now create a production database and a dedicated user that our applications will use to connect.

sudo -u postgres psql -c "CREATE USER app_user WITH ENCRYPTED PASSWORD 'SuperSecureAppPassword123!';"
sudo -u postgres psql -c "CREATE DATABASE production_db OWNER app_user;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE production_db TO app_user;"

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

Step 2: Installing and Configuring PgBouncer for High Throughput

With our core database optimized, we will install and configure PgBouncer as our pooling frontend. PgBouncer consumes minimal CPU and memory (often under 10 MB RAM), handling tens of thousands of client connections effortlessly.

Install PgBouncer using the apt package manager:

sudo apt-get install -y pgbouncer

By default, PgBouncer configuration files are stored in /etc/pgbouncer/. We will edit pgbouncer.ini to define database routing, connection limits, and network ports.

Backup the default configuration file before making changes:

sudo mv /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bak
sudo nano /etc/pgbouncer/pgbouncer.ini

Write the following configuration directly into /etc/pgbouncer/pgbouncer.ini:

[databases]
production_db = host=127.0.0.1 port=5432 dbname=production_db pool_size=25 reserve_pool=5

[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 = postgres
stats_users = postgres, app_user
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 2000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 80
server_idle_timeout = 600
server_connect_timeout = 15
server_login_retry = 15
client_idle_timeout = 0
client_login_timeout = 60
so_reuseport = 1

Let’s dissect these settings:

• listen_port = 6432: Clients will connect to port 6432 instead of the standard Postgres port 5432.
• pool_mode = transaction: Server connections are bound to client connections only during active transactions.
• max_client_conn = 2000: Allows up to 2000 simultaneous client applications to connect to PgBouncer simultaneously.
• default_pool_size = 20: The maximum number of backend server connections PgBouncer will keep open to PostgreSQL per user/database pair.
• reserve_pool_size = 5: Extra fallback connections opened if the default pool is exhausted and clients are waiting longer than reserve_pool_timeout seconds.
• so_reuseport = 1: Allows the Linux kernel to load balance incoming network sockets across multiple CPU cores.

Step 3: User Authentication and Systemd Service Integration

PgBouncer needs to verify client credentials without exposing cleartext passwords or querying PostgreSQL on every single handshake. We do this by defining an authentication file /etc/pgbouncer/userlist.txt.

To extract the encrypted SCRAM-SHA-256 or MD5 password hash directly from PostgreSQL for app_user and postgres, execute this SQL command:

sudo -u postgres psql -t -A -c "SELECT '\"' || usename || '\" \"' || passwd || '\"' FROM pg_shadow WHERE usename IN ('postgres', 'app_user');" | sudo tee /etc/pgbouncer/userlist.txt

Verify the contents of /etc/pgbouncer/userlist.txt. It should look like this:

"postgres" "SCRAM-SHA-256$4096:m34f...=="
"app_user" "SCRAM-SHA-256$4096:k91a...=="

Ensure correct ownership and permissions so only the postgres system user can read the authentication file:

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 0600 /etc/pgbouncer/userlist.txt

Now update the permissions for the PgBouncer configuration directory:

sudo chown -R postgres:postgres /etc/pgbouncer
sudo chmod 0755 /etc/pgbouncer

Next, ensure the systemd service for PgBouncer is configured to auto-start on boot and auto-restart on failure. When managing persistent Linux services, understanding systemd unit patterns is invaluable—much like deploying scheduled Python scripts using systemd .

Create a systemd override directory for PgBouncer:

sudo mkdir -p /etc/systemd/system/pgbouncer.service.d/
sudo nano /etc/systemd/system/pgbouncer.service.d/override.conf

Add these explicit service constraints to override default limits:

[Unit]
Description=PgBouncer connection pooler for PostgreSQL
After=network.target postgresql.service
Wants=postgresql.service

[Service]
User=postgres
Group=postgres
LimitNOFILE=65536
Restart=always
RestartSec=5s

[Install]
WantedBy=multi-user.target

Reload the systemd manager configuration, enable, and start PgBouncer:

sudo systemctl daemon-reload
sudo systemctl enable pgbouncer
sudo systemctl restart pgbouncer

Verify that PgBouncer is running and listening on port 6432:

sudo systemctl status pgbouncer
sudo ss -tulpn | grep 6432

You can test administrative access to PgBouncer using psql directly against the administrative virtual database pgbouncer:

psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

Inside the PgBouncer administrative console, execute these management commands to inspect live pool metrics:

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

To exit the administrative console, type \q.

Step 4: Benchmarking Connection Performance with Python

To demonstrate the dramatic performance difference between direct PostgreSQL connections and pooled connections via PgBouncer, we will write a complete Python benchmarking script using asyncio and asyncpg.

This script opens 200 concurrent connections and executes quick SELECT queries. We will run it against PostgreSQL directly (Port 5432) and then against PgBouncer (Port 6432).

First, create a virtual environment and install asyncpg:

python3 -m venv benchmark_venv
source benchmark_venv/bin/activate
pip install asyncpg

Create a script named pool_benchmark.py:

import asyncio
import time
import asyncpg

DB_HOST = "127.0.0.1"
DB_NAME = "production_db"
DB_USER = "app_user"
DB_PASS = "SuperSecureAppPassword123!"
NUM_CONCURRENT_CLIENTS = 200
QUERIES_PER_CLIENT = 10

async def run_client_workload(port: int, client_id: int):
    try:
        connection = await asyncpg.connect(
            host=DB_HOST,
            port=port,
            user=DB_USER,
            password=DB_PASS,
            database=DB_NAME,
            timeout=10.0
        )
        for _ in range(QUERIES_PER_CLIENT):
            row = await connection.fetchrow("SELECT 1 AS value, pg_backend_pid() AS pid;")
            _ = row["value"]
        await connection.close()
        return True
    except Exception as exc:
        return False

async def execute_benchmark(port: int, label: str):
    print(f"Starting benchmark for: {label} (Port {port})")
    start_time = time.perf_counter()
    
    tasks = [
        run_client_workload(port, i) 
        for i in range(NUM_CONCURRENT_CLIENTS)
    ]
    
    results = await asyncio.gather(*tasks)
    end_time = time.perf_counter()
    
    successful = sum(1 for r in results if r)
    failed = sum(1 for r in results if not r)
    duration = end_time - start_time
    total_queries = successful * QUERIES_PER_CLIENT
    qps = total_queries / duration if duration > 0 else 0
    
    print(f"--- Results for {label} ---")
    print(f"Total Time: {duration:.4f} seconds")
    print(f"Successful Clients: {successful}/{NUM_CONCURRENT_CLIENTS}")
    print(f"Failed Clients: {failed}/{NUM_CONCURRENT_CLIENTS}")
    print(f"Total Queries Executed: {total_queries}")
    print(f"Throughput: {qps:.2f} Queries/Second\n")

async def main():
    print("==================================================")
    print("PostgreSQL Connection Benchmark: Direct vs Pooled")
    print("==================================================\n")
    
    # Test 1: Direct Connection to PostgreSQL
    await execute_benchmark(port=5432, label="Direct PostgreSQL")
    
    # Pause between tests to let sockets settle
    await asyncio.sleep(2)
    
    # Test 2: Connection via PgBouncer
    await execute_benchmark(port=6432, label="PgBouncer Proxy")

if __name__ == "__main__":
    asyncio.run(main())

Execute the benchmark script:

python pool_benchmark.py

Expected Output & Analysis

When running this test, you will see output similar to this:

==================================================
PostgreSQL Connection Benchmark: Direct vs Pooled
==================================================

Starting benchmark for: Direct PostgreSQL (Port 5432)
--- Results for Direct PostgreSQL ---
Total Time: 4.8210 seconds
Successful Clients: 100/200
Failed Clients: 100/200
Total Queries Executed: 1000
Throughput: 207.43 Queries/Second

Starting benchmark for: PgBouncer Proxy (Port 6432)
--- Results for PgBouncer Proxy ---
Total Time: 0.6120 seconds
Successful Clients: 200/200
Failed Clients: 0/200
Total Queries Executed: 2000
Throughput: 3267.97 Queries/Second

Why is PgBouncer so much faster?

1. Connection Rejection Avoided: Direct PostgreSQL hit max_connections = 100 and threw connection errors for the remaining 100 clients.
2. Reduced Process Overhead: PgBouncer queued incoming client requests in lightweight memory structures rather than attempting to spawn hundreds of heavy Linux processes.
3. Connection Reuse: The application reused the warm pool of 20 backend database connections, cutting total execution time from nearly 5 seconds down to 0.6 seconds.

If your application interacts with third-party webhooks, microservices, or external auth protocols like OAuth2 for beginners: connecting APIs without the pain , database connection latency can compound quickly. Routing every service through PgBouncer eliminates connection establishment overhead entirely from your app latency metrics.

Getting Started

To get connection pooling running smoothly in your own environment:

1. Provision a high-performance cloud server on Hetzner VPS or DigitalOcean .
2. Tune postgresql.conf to enforce a sensible limit on max_connections (50–100) and allocate memory to shared_buffers and work_mem.
3. Install pgbouncer, set the pool_mode to transaction, and export user credentials from pg_shadow to /etc/pgbouncer/userlist.txt.
4. Point your application connection strings, background workers, and automation tools like n8n Cloud to port 6432.

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-HUGO for priority. Or DM at chasebot.online .