pgPool-II: When You Need Load Balancing, Read/Write Splitting, and HA — Not Just Connection Pooling
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
Session and transaction pooling modes reduce 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.
pgPool-II vs PgBouncer
PgBouncer is faster and simpler but does only connection pooling. pgPool-II adds query routing and HA at the cost of higher complexity and resource use.
| Feature | pgPool-II ✦ | PgBouncer |
|---|---|---|
| Connection pooling | ||
| Automatic read/write query routing | ||
| Load balancing across replicas | ||
| Watchdog HA (self-healing proxy) | ||
| Online recovery of PostgreSQL standbys | ||
| Parallel query execution | ||
| Sub-1ms connection overhead | ||
| Tiny memory footprint (~2MB) | ||
| Serverless / Lambda friendly | ||
| Simple single-binary deployment | ||
| Connection multiplexing | ||
| Transparent to application code |
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
Connection pooling reuses backend connections — temp tables and prepared statements may not survive across requests. Use session_pooling for apps that rely on session state.
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.