Full-Featured PostgreSQL Proxy
pgPool-II: When You Need Load Balancing, Read/Write Splitting, and HA — Not Just Connection Pooling
pgPool-II is an open-source, Layer 7 middleware that sits between applications and PostgreSQL clusters. It inspects SQL statements to provide automatic read/write query splitting, connection pooling, and load balancing across replicas. Its Watchdog mechanism delivers high availability with virtual IP failover, while online recovery re-attaches failed standbys without application downtime.
pgPool-II sits between your application and PostgreSQL to provide connection pooling, automatic read/write query routing to standbys, load balancing across replicas, and watchdog high availability — all in a single component. It is the right choice when PgBouncer alone is insufficient.
What pgPool-II Does That PgBouncer Cannot
pgPool-II parses every SQL query to determine if it is a write (routed to primary) or a read (routed to a standby replica). This query-level intelligence is what sets it apart from PgBouncer's transport-level pooling.
Automatic Read/Write Splitting
pgPool-II inspects each SQL statement. SELECT queries are load-balanced across standby replicas. INSERT/UPDATE/DELETE go to the primary. Your application uses a single connection string.
Load Balancing Across Replicas
Round-robin or weight-based load balancing distributes read queries across multiple standbys. Prevents any single replica from becoming a bottleneck in read-heavy workloads.
Watchdog HA for the Proxy
Watchdog runs multiple pgPool-II instances in active/standby mode with a virtual IP. If the active pgPool-II dies, standby takes over the VIP automatically — no SPOF.
Online Recovery
pgPool-II can automatically clone a failed standby back into the cluster using the online recovery feature — executing pg_basebackup in the background and promoting the node when ready.
Connection Pooling
Per-child connection reuse (connection_cache with max_pool cached connections per pre-forked child) reduces the number of PostgreSQL backend processes. Works alongside the load-balancing features simultaneously.
Query Cache (optional)
In-memory query result caching for SELECT queries — results are served from cache until the underlying table is modified. Reduces database load for repetitive read patterns.
Resilience Engineering
pgPool-II Production Failure Modes
Critical proxy, routing, and Watchdog quorum failure scenarios analyzed and remediated by JusDB DBREs to prevent silent write routing to read-only standbys, split-brain VIP binding, and child process starvation.
Silent Standby Write Routing via Missing write_function_list Definitions
Custom PL/pgSQL functions, sequence increments (nextval), or write operations inside stored procedures not declared in write_function_list are misclassified as read-only queries by pgPool-II and routed to read-only standby nodes, triggering transaction abortion with 'cannot execute UPDATE in a read-only transaction'.
Perform complete AST schema auditing of all application stored procedures, functions, and ORM triggers, compiling exhaustive write_function_list and read_only_function_list declarations within pgpool.conf.
Watchdog Quorum Split-Brain and Duplicate VIP Takeover
Transient inter-proxy network latency or misconfigured trusted_servers IP addresses cause partitioned pgPool-II Watchdog nodes to independently elect themselves master, binding duplicate delegate_ip virtual interfaces and dropping client TCP packets.
Configure trusted_servers to probe dedicated upstream network gateways rather than database hosts, enforce odd-numbered Watchdog quorum nodes (minimum 3), and integrate STONITH fencing scripts.
Child Process Starvation and Backend Connection Cascades
Surging client connections exceeding num_init_children force new TCP requests into an unbounded backlog queue, causing client connection timeouts while idle child processes hold persistent PostgreSQL backend connections without multiplexing.
Calibrate num_init_children against PostgreSQL max_connections, implement aggressive child_life_time recycling, and configure front-end load shedding thresholds to prevent queue saturation.
Proxy Telemetry
Production pgPool-II Diagnostic Runbooks
Non-blocking inspection commands executed via the PCP administrative interface and PostgreSQL query port to inspect backend node health, replication lag thresholds, and Watchdog cluster states.
Inspects live backend node states, replication lag byte delays, connection pool allocations, and primary/standby roles.
# Query backend node status, role, and replication delay via PCP pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool_admin -w -v 0 pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool_admin -w -v 1 # Inspect active child processes and pooled connection allocation pcp_pool_status -h 127.0.0.1 -p 9898 -U pgpool_admin -w | grep -E "(total_pools|num_init_children)" # Inspect live cluster status directly via PostgreSQL query port psql -h 127.0.0.1 -p 9999 -U postgres -d postgres -c "SHOW POOL_NODES;"
Audits Watchdog quorum state, heartbeat ping latencies, delegate IP ownership, and trusted server reachability.
# Query live Watchdog cluster membership and coordinator status psql -h 127.0.0.1 -p 9999 -U postgres -d postgres -c "SHOW POOL_WATCHDOG;" # Inspect network interface binding for delegate virtual IP (VIP) ip addr show to 10.0.1.99/32 # Query Watchdog heartbeat ping latency and quorum health via PCP pcp_watchdog_info -h 127.0.0.1 -p 9898 -U pgpool_admin -w -v
pgPool-II vs PgBouncer vs HAProxy vs Direct
Evaluate architectural trade-offs between intelligent Layer 7 SQL routing, lightweight transaction pooling, raw TCP streaming proxies, and direct application connections.
| Evaluation Vector | JusDB Pgpool-II DBRE | PgBouncer / HAProxy | Direct / In-House |
|---|---|---|---|
| Query Inspection & Read/Write Splitting | Intelligent SQL parsing routes SELECT to read replicas and INSERT/UPDATE/DELETE to primary without application code changes or dual connection pools | PgBouncer does no SQL parsing; HAProxy requires dual listening ports and application-level routing logic across distinct database endpoints | Manual dual-datasource configurations in application ORMs prone to replica write exceptions and stale connection pool exhaustion |
| Connection Pooling & Process Multiplexing | Pre-forked pool tuning (num_init_children, max_pool) with connection cache reuse, protecting PostgreSQL from backend fork storms | PgBouncer excels at lightweight sub-millisecond transaction pooling but lacks read balancing; HAProxy maintains raw TCP streams with zero pooling | Unconstrained client connections spawning hundreds of heavy postgres OS backend processes, causing severe memory bloat and context switching |
| Watchdog HA & Quorum VIP Management | Multi-node Watchdog clustering with virtual IP (delegate_ip) failover, trusted server ping health checks, and automated split-brain avoidance | Requires external Keepalived or cloud load balancers with separate health check daemons and uncoordinated failover logic | Single-node proxy instances creating single points of failure, or uncoordinated Watchdog nodes fighting over duplicate virtual IPs |
| Automated Standby Online Recovery | Integrated recovery_1st_stage and 2nd_stage commands leveraging pg_basebackup to automatically reattach failed standbys with zero cluster downtime | No built-in standby recovery; requires separate orchestration tooling (pgBackRest, Patroni, or manual shell scripts) | Manual DBA intervention required to rebuild failed replicas, leading to extended periods of reduced read capacity during outages |
| In-Proxy Query Caching & Throttling | Selective shared-memory query caching for high-frequency read statements with automatic table-invalidation triggers and delay threshold gating | Neither PgBouncer nor HAProxy supports query caching; requires separate Redis/Memcached layers with app cache invalidation complexity | Every repeated query directly hits PostgreSQL shared buffers, causing cache churn, lock contention, and high CPU usage under spikes |
| 24/7 Production DBRE & Sub-15m P1 SLA | Continuous pcp command audits, pool node health monitoring, Grafana dashboards, senior DBRE on-call 24/7, and guaranteed <15m P1 response | Generic infrastructure tickets with multi-hour turnaround and no deep Pgpool-II protocol-level diagnostic expertise | Engineers struggling to decipher Watchdog consensus logs and write_function_list errors at 2 AM during customer-facing outages |
Only need connection pooling? PgBouncer is lighter and faster →
pgpool vs pgbouncer: Performance & Resource Profile
Pure pooling throughput numbers from the same hardware (16 vCPU, 32 GB RAM, PostgreSQL 15) running pgbench at 200 concurrent clients. pgbouncer wins on raw throughput; pgpool-II wins once you factor in features like read/write splitting that would otherwise force the app to implement routing itself.
| Dimension | pgpool-II | pgbouncer |
|---|---|---|
| TPS (pgbench, pooling only) | ~38,000 | ~52,000 |
| Per-connection memory | ~5 MB (pre-forked) | ~2 KB |
| Total RAM (200 connections) | ~1 GB | ~20 MB |
| Median connection-acquisition latency | ~0.8 ms | ~0.1 ms |
| Cold-start to ready | ~5 s (forks num_init_children) | <1 s |
| Read/write split (no app changes) | ✅ built-in | ❌ application or external proxy |
| Proxy HA | ✅ watchdog + VIP | ❌ external (HAProxy/keepalived) |
Numbers are indicative — your workload (read ratio, query complexity, statement size) will shift these. pgpool-II's TPS overhead comes from SQL parsing per request; pgbouncer simply multiplexes connections.
pgpool-II Configuration Walkthrough
A minimal production pgpool.conf for read/write splitting with watchdog HA — the three settings that matter most arebackend_*,load_balance_mode, anduse_watchdog.
# Backend nodes — primary + 2 read replicas backend_hostname0 = 'pg-primary.internal' backend_port0 = 5432 backend_weight0 = 0 # do not load-balance reads to primary backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'pg-replica-1.internal' backend_port1 = 5432 backend_weight1 = 1 backend_flag1 = 'ALLOW_TO_FAILOVER' backend_hostname2 = 'pg-replica-2.internal' backend_port2 = 5432 backend_weight2 = 1 backend_flag2 = 'ALLOW_TO_FAILOVER' # Pooling num_init_children = 100 # pre-forked child processes max_pool = 4 # connections per child connection_cache = on child_life_time = 300 # Read/write splitting load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 10 delay_threshold = 1000000 # bytes — fall back to primary if replica lags write_function_list = 'nextval,setval,nextval_simple' # Watchdog HA (active/standby + VIP) use_watchdog = on wd_hostname = 'pgpool-1.internal' wd_port = 9000 delegate_ip = '10.0.1.99' heartbeat_destination0 = 'pgpool-2.internal' heartbeat_destination_port0 = 9694 trusted_servers = '10.0.1.1' # Health check health_check_period = 10 health_check_timeout = 5 health_check_user = 'pgpool_health'
Note write_function_list — any stored procedure that writes but is not listed here will be classified as a read and routed to a replica. This is the single most common pgpool-II misconfiguration in production.
Common pgpool-II Misconfigurations (and How We Catch Them)
Writes routed to a standby
A custom function not declared in write_function_list is classified as a read. Symptom: 'cannot execute UPDATE in a read-only transaction'. Audit every PL/pgSQL function during setup.
Watchdog split-brain
Two pgpool-II nodes can both think they own the VIP if trusted_servers isn't reachable. Always pin trusted_servers to a dedicated network device, not the database host.
Excessive num_init_children
Pre-forking 1000 child processes consumes ~5 GB RAM and rarely improves throughput. Size to peak concurrent app connections + 20% headroom.
delay_threshold too high
Default is 0 (disabled). Without a threshold, pgpool-II load-balances reads to replicas regardless of replication lag. Set to ~1 MB for read-after-write consistency.
Sticky session breakage
pgpool-II's connection_cache reuses pre-forked backend connections per child — temp tables and prepared statements may not survive across requests. For apps that rely on session state, keep clients pinned to a child (avoid aggressive child_life_time/child_max_connections recycling) rather than assuming PgBouncer-style transaction pooling.
Health-check user without password
pgpool-II's health_check_user must authenticate via pg_hba.conf trust or explicit password (health_check_password). A missing password causes pgpool to mark all backends as down.
When pgPool-II Beats PgBouncer
Choose pgPool-II when…
- • Read-heavy workload — you want SELECTs automatically routed to standbys
- • Multiple read replicas and you want load balancing across them without app changes
- • You need the proxy itself to be HA (watchdog active/standby)
- • You want automatic standby recovery via pgPool-II's online recovery
- • Read query caching is useful for your workload pattern
- • You prefer a single component managing pooling + routing + HA
Use PgBouncer instead when…
- • You only need connection pooling — no read/write split required
- • Serverless or microservices with many short-lived connections
- • Minimal resource overhead is critical
- • You use Patroni + HAProxy for HA separately
- • Application already handles read/write routing at the ORM level
JusDB pgPool-II Implementation
Architecture Design & Sizing
Determine backend node count, num_init_children, max_pool, and connection_life_time based on your workload and PostgreSQL configuration.
Read/Write Split Configuration
Configure load_balance_mode, write_function_list, and backend weights for intelligent query routing to primary vs standbys.
Watchdog HA Setup
Deploy two pgPool-II nodes in watchdog active/standby mode with a virtual IP (delegate_ip), configure heartbeat interfaces, and test automatic VIP failover.
Online Recovery Configuration
Set up recovery_1st_stage_command and recovery_2nd_stage_command scripts so pgPool-II can automatically re-attach and sync failed PostgreSQL standbys.
Monitoring & Metrics
Expose pgPool-II pool status via SHOW POOL_NODES. Integrate with Prometheus using pgpool2_exporter for real-time Grafana dashboards.
Performance Tuning
Tune connection_cache, num_init_children, and child_max_connections. Identify and resolve query-routing misclassification that sends writes to standbys.
FAQ
Need read/write splitting for PostgreSQL?
JusDB designs and deploys pgPool-II with watchdog HA, correct query routing, and full monitoring — avoiding the common misconfiguration pitfalls.