Pgpool-II - PostgreSQL Connection Pooler That Actually Works

Pgpool-II is a connection pooler that sits between your application and PostgreSQL databases. Think of it as a bouncer at a club - it manages who gets in, where they go, and kicks people out when they overstay their welcome.

The Reality of Process-Based Architecture

Pgpool-II uses a process-based model like PostgreSQL itself, spawning up to 32 child processes by default. Each process uses about 2MB of RAM baseline, so you're looking at 64MB just for the proxy before it does anything useful. When you hit max connections, it just starts dropping new ones - learned this the hard way during a traffic spike that took down our staging environment for 2 hours.

The connection pooling architecture differs significantly from thread-based poolers like PgBouncer. Each Pgpool process maintains its own pool of backend connections, which can lead to suboptimal resource utilization compared to shared connection pools.

The parent process forks children to handle connections, and here's where it gets fun: if a child process crashes (which happens more often than you'd like with SSL issues), the parent kills all other children and respawns them. So one bad connection can temporarily murder your entire connection pool.

Connection Pooling That Actually Pools

Unlike application-level pooling where your framework pretends to be smart, Pgpool-II maintains persistent connections to your PostgreSQL servers. This saves you from the 50ms handshake overhead on every new connection. In practice, we saw connection establishment time drop from 45ms to under 1ms for pooled connections.

But here's the catch: connections are per-process, not shared. If you have 32 Pgpool processes and 4 backend servers, you could theoretically open 128 connections to your database cluster. Set max_pool = 4 and you're looking at 512 potential connections. Do the math before your DBA murders you.

Query Routing (When It Works)

Pgpool-II can automatically detect SELECT statements and route them to read replicas while sending writes to the primary. This sounds great until you realize it's session-based routing, not statement-based.

The load balancing mechanism analyzes SQL queries to determine routing, but load balancing conditions can be more complex than expected. Enterprise load balancing techniques show that pgpool's query-aware routing has limitations compared to simpler approaches.

If your web app holds connections open (like every single web framework does), your "load balancing" becomes "randomly pick a server and hammer it for the next 10 minutes." We've seen scenarios where one replica gets 80% of the read traffic while others sit idle because of long-lived connections. Load balancing comparisons often highlight this session affinity issue.

Failover: The Part That Actually Matters

When your primary database shits the bed, Pgpool-II can automatically fail over to a replica. The good news: it usually works. The bad news: every client connection gets dropped during failover, so your application better handle ECONNRESET gracefully.

Failover detection uses SELECT pg_is_in_recovery() to identify the primary. With default health check intervals, you're looking at 10-30 seconds of downtime while Pgpool figures out what happened. Set health checks too aggressive and you'll get false positives from network hiccups. PostgreSQL high availability strategies provide broader context for when automatic failover makes sense.

The watchdog feature coordinates multiple Pgpool instances for failover coordination, but production experiences show that watchdog can be complex to configure properly without split-brain scenarios.

SSL Performance Hell

SSL overhead with OpenSSL 3.0.2 will destroy your CPU. We saw 300% CPU utilization on a 4-core box just handling SSL handshakes. Version 4.6.3 finally addresses some of this, but you'll still want to monitor CPU usage carefully if you're terminating SSL at Pgpool.

The SSL performance issues are well-documented in the GitHub repository, where users report 75% CPU utilization with SSL enabled. This is tied to broader OpenSSL 3.0 performance issues that affect many applications.

Pro tip: terminate SSL at a load balancer in front of Pgpool if you can. Your CPU will thank you.

Installation: The Easy Part

Getting Pgpool-II installed is the only part that actually works as advertised. Most package managers have it, and the official installation documentation covers both package and source installation methods:

## Ubuntu/Debian (works)
apt-get install pgpool2

## RHEL/CentOS (also works)
yum install pgpool-II

## Or compile from source if you hate yourself
wget https://www.pgpool.net/download.php?f=pgpool-II-4.6.3.tar.gz
./configure --prefix=/usr/local/pgpool
make && make install  # Takes 20 minutes, fails if missing dev libs

If you're compiling from source and it fails with pg_config not found, you need libpq-dev and postgresql-server-dev-XX. This error message is the first of many that will make you question your life choices. The MyDBOps installation guide covers these dependency issues in detail.

Configuration Files: Welcome to Hell

Pgpool-II uses two main config files that live in /etc/pgpool-II/ (or wherever your package manager decided to put them):

• pgpool.conf - The 200+ parameter nightmare that controls everything
• pcp.conf - Authentication for administrative commands (you'll forget about this until nothing works)

The basic configuration examples from the official docs are a good starting point, but sample configuration files on GitHub show real-world scenarios. Here's a basic config that looks simple but will break in 17 different ways:

## This will fail if DNS hiccups
listen_addresses = '*'
port = 5432

## These numbers are lies until you tune them for your workload
connection_cache = on
max_pool = 4
num_init_children = 32

## Load balancing that works until it doesn't
load_balance_mode = on
master_slave_mode = on

## Backend configuration that assumes everything is perfect
backend_hostname0 = 'db-primary.internal'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = 'db-replica1.internal'
backend_port1 = 5432
backend_weight1 = 1

Authentication: The Part That Always Breaks

Authentication is where Pgpool-II goes from "challenging" to "I'm updating my resume." You need to sync three different auth configurations:

1. PostgreSQL's pg_hba.conf (controls database access)
2. Pgpool's pool_hba.conf (mirrors pg_hba.conf, sort of)
3. The pool_passwd file (password hashes that never match)

The authentication configuration documentation explains the theory, but Stack Overflow authentication issues show the reality of debugging auth problems.

The password file is the real killer. Use the pg_md5 utility or spend hours debugging why connections fail with unhelpful error messages:

## Generate password hash (this command will save your sanity)
pg_md5 -m -u username password

If you get authentication failed for user errors, it's always the password hashes. Every. Single. Time.

SSL Configuration: CPU Death Trap

SSL between clients and Pgpool works fine. SSL between Pgpool and PostgreSQL backends will murder your CPU. We measured 300% CPU utilization on OpenSSL 3.0.2 handling just 50 concurrent connections.

The SSL configuration guide shows proper setup, while TLS connection tutorials provide PostgreSQL SSL context. PostgreSQL security hardening covers the broader security implications.

Version 4.6.3 helps, but if you're stuck on older versions, terminate SSL at HAProxy in front of Pgpool:

## In pgpool.conf - this will peg your CPU
ssl = on
ssl_cert = '/etc/ssl/certs/pgpool.crt'
ssl_key = '/etc/ssl/private/pgpool.key'

## Better: terminate SSL at load balancer, use plaintext to Pgpool

Watchdog Clustering: Split-Brain Guaranteed

The Watchdog feature lets multiple Pgpool instances coordinate for high availability. In theory. In practice, you'll get split-brain scenarios when network connectivity hiccups. The watchdog setup utility helps with initial configuration, and Google Cloud HA architecture guides show enterprise deployment patterns.

## This config assumes perfect network connectivity (spoiler: it's not)
use_watchdog = on
watchdog_hostname = 'pgpool1.internal'
wd_lifecheck_method = 'heartbeat'

## When this fails, you'll have two Pgpool instances fighting over the VIP

Network partitions will cause both Pgpool instances to think they're primary. Your monitoring will show two active VIPs and you'll wonder why half your traffic is disappearing.

The Gotchas That Aren't in the Docs

Health Check Tuning

Default health check intervals (10 seconds) are too conservative for production. Set health_check_period = 5 but prepare for false positives during network congestion.

Connection Limits Math

With 32 child processes, max_pool = 4, and 3 backend servers, you're looking at potentially 384 connections to your database cluster. Your DBAs will not be happy.

Failover Timing

Failover detection can take 30+ seconds with default settings. Applications need to handle connection drops gracefully or users will see errors during database failover.

Log File Growth

Enable query logging for debugging but remember to rotate logs. We filled a 20GB partition in 6 hours during a connection storm.

Production Reality Check

Plan 2-3 days for initial setup and another week for production tuning. The official documentation shows perfect configs that work in tutorials but break under real load.

Enterprise deployments often involve extensive cluster configuration and monitoring setup. The PCP command documentation becomes essential for production operations, though the syntax is cryptic.

For production monitoring, Debian security advisories track CVEs you'll need to patch. Consider Docker deployment patterns for development, but test thoroughly before production use.

Keep your old PgBouncer config handy - you might need to fall back to it while debugging Pgpool at 3am on a Saturday.