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 →
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.