Configuring pgpool-II Connection Pooling and Load Balancing

This guide is part of PgBouncer vs RDS Proxy vs pgpool-II. pgpool-II is chosen for one reason — it can send reads to streaming replicas and writes to the primary without the application choosing an endpoint — and it is then usually configured as if it were PgBouncer. It is not. pgpool-II serves each client with a pre-forked child process, and the number of those processes, not a pool size, is its client concurrency limit. When every child is busy, new clients do not get an error; they wait in the kernel’s listen queue, and the application sees connection attempts that simply hang:

psql: error: connection to server at "pgpool.internal" (10.2.0.14), port 9999 failed: timeout expired
pgpool[2211]: LOG:  child process with pid: 3190 exits with status 256
pgpool[1807]: FATAL:  Sorry, too many clients already

The objective is a configuration where client concurrency, backend connections and max_connections agree, where a full pgpool-II fails fast instead of hanging, and where load balancing never reads from a replica that is too far behind.

Rapid incident diagnosis

Connect through pgpool-II and use its SHOW commands, which are answered by pgpool itself rather than forwarded to PostgreSQL.

-- Which children are busy, and on which database and user
SHOW pool_processes;

-- Backend health, role, load-balance weight and replication delay
SHOW pool_nodes;

-- Cached backend connections per child, per slot
SHOW pool_pools;

If every row in SHOW pool_processes has a non-empty database and username, every child is serving a client and new connections are queuing. If you cannot connect at all to run the command, use the PCP interface, which listens on its own port:

pcp_proc_count -h 127.0.0.1 -p 9898 -U pcpadmin -w
pcp_node_info  -h 127.0.0.1 -p 9898 -U pcpadmin -w -n 1 --verbose

Then separate the three causes of “clients cannot connect”:

  • Children exhausted. All num_init_children processes are occupied by clients, many of them idle. Application pools in front of pgpool-II are holding sessions open, and each open session occupies a child for its whole life.
  • Backends refusing. Children are free, but the log shows FATAL: sorry, too many clients already coming from PostgreSQL. num_init_children × max_pool exceeds the backend’s max_connections.
  • Node detached. SHOW pool_nodes shows a backend with status down after a failed health check, and all traffic has shifted to the remaining nodes, which then saturate.

For reads returning stale data, the same command answers the question: replication_delay in SHOW pool_nodes is the lag pgpool-II measured at its last streaming-replication check.

pgpool-II process model and the listen queue Clients connect to a listen socket. Each is accepted by one of num_init_children pre-forked children, each of which caches up to max_pool backend connections. When all children are busy, further clients wait in the listen backlog. client 33 client 34 client 35 Listen backlog waiting, no error until client timeout num_init_children = 32 32 pre-forked children all busy one client each accept PostgreSQL max_pool 4 per child reserved_connections = 1 turns the silent wait into an immediate "too many clients" error
Each client occupies one child for the life of its session. Once all children are occupied, new connections queue in the kernel with no error until the client's own connect timeout fires.

Mathematical sizing or parameter formula

pgpool-II has two independent limits, and both must fit.

Backend connections. Each child caches up to max_pool connections, one per distinct combination of user, database and protocol options it has served. The worst case the backends must accept is:

num_init_children × max_pool ≤ max_connections − superuser_reserved_connections   (per backend node)

Because load balancing sends traffic to every node, the inequality must hold on the primary and on each replica.

Client concurrency. num_init_children is the maximum number of simultaneous client sessions. Size it from the number of sessions that exist at once, which, with application pools in front, is the sum of their maximum sizes:

num_init_children ≥ Σ over app instances (pool_max)

Worked example. Twelve application replicas each run a pool of 8: clients need 96 children. If the application only ever connects as one user to one database, each child needs only one cached backend connection, so max_pool = 1 and the backend needs 96 × 1 = 96 slots. With max_connections = 200 and 3 superuser slots, that fits with room to spare. Leaving max_pool at its default of 4 would make the worst case 96 × 4 = 384 — which only materialises if other users or databases connect through the same pgpool-II, but then it materialises all at once.

Memory is the third constraint. Each child is a full process; at a typical 5–10 MiB resident per child, 96 children cost under 1 GiB, but a num_init_children of 2,000 does not fit on a small proxy host. When client sessions number in the thousands, put PgBouncer in front of pgpool-II or reconsider whether query routing needs to happen in a proxy at all.

Backend connection requirement for two max_pool settings With 96 children, max_pool 1 needs 96 backend connections, within a usable budget of 197. The default max_pool 4 needs 384, nearly double the budget. Backend connections needed with num_init_children = 96 max_pool = 1 96 max_pool = 4 (default) 384: exceeds the budget usable budget 197 per node the inequality must hold on the primary and on every replica
Leaving max_pool at its default multiplies the backend requirement by four. For a single application user and database, one cached connection per child is enough.

Exact remediation & configuration

The following pgpool.conf fragment is for pgpool-II 4.2 or later with streaming replication, a primary and two replicas, and a single application role:

# --- Topology ---
backend_clustering_mode = 'streaming_replication'
backend_hostname0 = 'pg-primary.internal'
backend_port0 = 5432
backend_weight0 = 0            # writes only; no read load-balanced here
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'pg-replica-a.internal'
backend_port1 = 5432
backend_weight1 = 1
backend_hostname2 = 'pg-replica-b.internal'
backend_port2 = 5432
backend_weight2 = 1

# --- Client concurrency and backend cache ---
num_init_children = 96          # Σ application pool maxima
max_pool = 1                    # one user, one database
reserved_connections = 1        # reject with an error instead of queuing silently
listen_backlog_multiplier = 1
serialize_accept = on           # avoid thundering herd when children are idle

# --- Lifetimes ---
child_life_time = 300           # recycle an idle child after 5 minutes
child_max_connections = 0       # 0 = no per-child session cap
connection_life_time = 1800     # close cached backend connections after 30 minutes
client_idle_limit = 0           # application pools manage their own idle sessions

# --- Load balancing ---
load_balance_mode = on
disable_load_balance_on_write = 'transaction'
statement_level_load_balance = off

# --- Replication delay guard ---
sr_check_period = 5
sr_check_user = 'pgpool_check'
delay_threshold_by_time = 1000  # milliseconds; stop reading from a replica more than 1 s behind

# --- Health checks ---
health_check_period = 5
health_check_timeout = 10
health_check_max_retries = 3
health_check_retry_delay = 2

Three of these settings change failure behaviour rather than performance:

  • reserved_connections = 1 makes pgpool-II refuse a client with Sorry, too many clients already when only one child remains free, instead of leaving clients in the listen backlog. Applications see an immediate, retryable error.
  • disable_load_balance_on_write = 'transaction' keeps every statement after the first write in a transaction on the primary, so a transaction never reads data it has not yet replicated. Use 'trans_transaction' to extend that to later transactions in the same session, at the cost of fewer reads on replicas.
  • delay_threshold_by_time (4.4 and later; delay_threshold in bytes on older releases) removes a lagging replica from read routing until it catches up.

Apply changes with a reload where possible. Most load-balancing and health-check parameters reload with pcp_reload_config; num_init_children and max_pool require a restart, so change them behind a second pgpool-II instance or during a maintenance window:

pcp_reload_config -h 127.0.0.1 -p 9898 -U pcpadmin -w

In front of pgpool-II, keep application pools small and let them close idle connections, because an idle application connection occupies a child. With HikariCP, an idleTimeout of two minutes and a minimumIdle of 2 return children to the free set during quiet periods.

Read routing decisions in pgpool-II A SELECT outside a write transaction is load-balanced across replicas by weight, but only to replicas within the delay threshold. After a write in a transaction, following SELECTs go to the primary. SELECT arrives from a client session Write earlier in this transaction? Primary (weight 0) reads after a write Replica A · lag 120 ms weight 1: receives reads Replica B · lag 3.4 s over 1 s threshold: skipped yes no delay_threshold_by_time = 1000 removes B until it catches up
Load balancing is decided per statement, but a write pins the rest of the transaction to the primary, and a replica past the delay threshold receives no reads at all.

Validation & verification

Routing. Enable statement logging on the backends briefly, or use SHOW pool_nodes counters, which report select_cnt per node. Run a read-heavy load and confirm reads land only on replicas:

pgbench -h pgpool.internal -p 9999 -U app -S -c 32 -T 120 appdb
psql -h pgpool.internal -p 9999 -U app appdb -c "SHOW pool_nodes;" \
  | awk -F'|' 'NR>2 && NF>5 {print $2, "role="$6, "select_cnt="$9}'

The primary’s select_cnt should stay near zero with backend_weight0 = 0.

Read-your-writes. Inside one transaction, insert a row and immediately select it. With disable_load_balance_on_write = 'transaction' the select must always find the row, across hundreds of iterations:

BEGIN;
INSERT INTO probe(id, at) VALUES (gen_random_uuid(), now()) RETURNING id \gset
SELECT count(*) FROM probe WHERE id = :'id';   -- must be 1
COMMIT;

Fail-fast admission. Open num_init_children idle sessions from a test client, then attempt one more. With reserved_connections = 1, the extra attempt must fail within milliseconds with Sorry, too many clients already, not hang until the client timeout.

Delay guard. On a staging replica, pause replay with SELECT pg_wal_replay_pause();, generate writes on the primary, and watch replication_delay rise in SHOW pool_nodes. Once it passes one second, select_cnt on that node must stop increasing. Resume with pg_wal_replay_resume().

Backend budget. On each node, SELECT count(*) FROM pg_stat_activity WHERE usename = 'app'; must never exceed num_init_children × max_pool, and the server must keep free slots for superusers.

Running pgpool-II highly available

A single pgpool-II instance is a single point of failure for every connection it serves, so production deployments run at least two. pgpool-II’s watchdog coordinates them: instances heartbeat each other, elect a leader, and the leader holds a virtual IP that clients connect to. When the leader fails, a standby takes over the address.

# --- Watchdog (pgpool-II 4.2+ style, identical on each node except pgpool_node_id file) ---
use_watchdog = on
hostname0 = 'pgpool-a.internal'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'pgpool-b.internal'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'pgpool-c.internal'
wd_port2 = 9000
pgpool_port2 = 9999

delegate_ip = '10.2.0.50'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'

wd_lifecheck_method = 'heartbeat'
heartbeat_hostname0 = 'pgpool-a.internal'
heartbeat_hostname1 = 'pgpool-b.internal'
heartbeat_hostname2 = 'pgpool-c.internal'
failover_when_quorum_exists = on
failover_require_consensus = on

Three nodes, not two, so a network partition cannot leave two instances each believing it is the leader. failover_require_consensus makes backend failover a majority decision, which prevents one instance with a broken network path from detaching a healthy primary for everyone.

Connection sizing changes with more than one instance. Only the leader normally receives traffic through the virtual IP, but during a switchover clients reconnect to the new leader while the old one may still hold backend connections until its children exit. Budget the backend for both:

backend_connections_during_switchover ≤ 2 × num_init_children × max_pool

With num_init_children = 96 and max_pool = 1, that is 192 connections per backend node during a switchover, which must still fit max_connections minus superuser slots. If it does not, lower child_life_time so the old leader’s idle children exit quickly, or accept a short period where backends refuse connections during leader changes.

On Kubernetes, the watchdog and virtual IP are usually replaced by a Deployment of several pgpool-II pods behind a Service, with the watchdog disabled. In that layout every pod receives traffic, and the backend budget becomes replicas × num_init_children × max_pool at all times, not just during switchover — size num_init_children per pod accordingly.

Layout Traffic-serving instances Backend connections to budget
Watchdog with virtual IP 1 (leader) 1× normally, 2× during switchover
Kubernetes Deployment behind a Service all replicas replicas × always
Single instance 1 1×, with no failover

Frequently Asked Questions

Can pgpool-II replace the application connection pool?
No, and trying makes it worse. Without an application pool, every request opens a TCP connection to pgpool-II, which occupies a child and runs authentication each time. Keep a small application pool; pgpool-II’s cache is about reusing backend connections per child, not about multiplexing many clients onto few children.
Why do reads go to the primary even with replicas configured?
Common causes are functions pgpool-II cannot classify as read-only (listed with write_function_list or read_only_function_list), SELECT statements inside explicit transactions after a write, prepared statements whose parse was routed to the primary, and a replica excluded by the delay threshold. SHOW pool_nodes shows which node was chosen as load_balance_node for the session.
Is statement-level load balancing better?
statement_level_load_balance = on picks a replica per statement rather than per session, spreading load more evenly for long-lived application connections. It adds a routing decision to every query and makes debugging harder, because one session’s reads can land on different replicas with different lag.
Should pgpool-II also manage failover?
It can, with failover_command and the watchdog for its own high availability, but that is a separate and substantial commitment. If your platform already manages PostgreSQL failover — Patroni, a managed service — let it, and configure pgpool-II only to detect the new primary.
Does pgpool-II support SCRAM-SHA-256 authentication?
Yes, from pgpool-II 4.0, using pool_passwd with AES-encrypted or SCRAM-hashed entries and enable_pool_hba. Clients authenticate to pgpool-II, which authenticates to each backend with the stored credentials, so every backend must accept the same method.