Pgpool-II for PostgreSQL Load Balancing: Deep Dive

Pgpool-II load balancing is decided by three settings, and getting any one of them wrong makes the other two do nothing. Set all three explicitly:

load_balance_mode = on          # without this, backend_weight is inert
backend_weight0 = 1             # relative share for node 0
backend_weight1 = 1             # relative share for node 1
sr_check_user = 'pgpool_check'  # defaults to '' -- Pgpool-II cannot find the primary without it

Do not go looking for the Pgpool-II admin UI. An earlier version of this page devoted a long section to a “convenient web-based administration interface” for Pgpool-II. That was pgpoolAdmin, and the Pgpool Global Development Group ended it: maintenance stopped on 31 December 2023 and “pgpoolAdmin for Pgpool-II 4.3 or later will not be released”. Its repository has had no commit since June 2021 and no tag above V4_2_0, so there is no build that works with a supported Pgpool-II. The guides that still tell you to install it — including two on this site, now corrected — put it behind an Apache block with no authentication. Use SHOW POOL_NODES and the PCP tools instead.

The measurements

Run on 10 August 2026: macOS 26.6 (arm64), Pgpool-II 4.7.2 from Homebrew, backend_clustering_mode = 'streaming_replication', in front of a PostgreSQL 17.10 primary on port 55611 and a streaming standby on 55612. Each run restarts Pgpool-II with a fresh counter, opens 100 separate connections, runs one SELECT count(*) on each, and reads select_cnt from SHOW POOL_NODES.

### load_balance_mode=on backend_weight0=1 backend_weight1=1 -- 100 SELECTs
node 0 port=55611 role=primary lb_weight=0.500000 select_cnt=55
node 1 port=55612 role=standby lb_weight=0.500000 select_cnt=45

### load_balance_mode=on backend_weight0=1 backend_weight1=9 -- 100 SELECTs
node 0 port=55611 role=primary lb_weight=0.100000 select_cnt=16
node 1 port=55612 role=standby lb_weight=0.900000 select_cnt=84

### load_balance_mode=off backend_weight0=1 backend_weight1=9 -- 100 SELECTs
node 0 port=55611 role=primary lb_weight=0.100000 select_cnt=100
node 1 port=55612 role=standby lb_weight=0.900000 select_cnt=0

The first two runs are randomised, so they land differently each time; a second run of the same script produced 50/50 and 10/90. The third run does not vary. backend_weight1 = 9 is accepted, SHOW POOL_NODES reports lb_weight = 0.900000 for the standby, and the standby serves zero queries. Weights are displayed whether or not they are used. If you are debugging a cluster where “the weights are set but nothing is balanced”, check load_balance_mode first.

The version trap in load_balance_mode

The default changed. The Pgpool-II 4.2 documentation for load_balance_mode says “Default is off”; the 4.3 documentation says “Default is on”. Whether an unset load_balance_mode balances anything therefore depends on which package your distribution carries. On 4.7.2, with the setting deleted from pgpool.conf entirely, the running server reports:

$ psql -p 55699 -U postgres -Atc "PGPOOL SHOW load_balance_mode"
on

Set it explicitly rather than inheriting a default that has already moved once.

Balancing is per session, not per query

The 100 SELECTs above each used a new connection. That is why they split. Within one session the target is fixed: the node “is decided at the session start time and will not be changed until the session ends unless statement_level_load_balance is specified”, and that setting defaults to off on 4.7.2. An application holding a small pool of long-lived connections pins each one to a node at connect time and never moves it.

Writes are not balanced, and neither are reads that follow one

With the 1:9 weighting above — 90% of reads going to the standby — an INSERT through Pgpool-II still lands on the primary, because Pgpool-II routes by statement type. Sent directly to the standby, the same statement is rejected:

$ psql -p 55699 -U postgres -d hw_lb_demo -c "INSERT INTO t VALUES (100001,'via-pgpool')"
INSERT 0 1
$ psql -p 55612 -U postgres -d hw_lb_demo -c "INSERT INTO t VALUES (100002,'direct')"
ERROR:  cannot execute INSERT in a read-only transaction

Inside an explicit transaction that has written, later SELECTs stop being balanced as well. Three SELECT count(*) statements after an INSERT, in one BEGIN/COMMIT block, with the standby weighted at 0.9:

before: node 0 select_cnt=0   node 1 select_cnt=0
after:  node 0 select_cnt=3   node 1 select_cnt=0

That is disable_load_balance_on_write, which defaults to transaction on 4.7.2. It exists so that a transaction never reads its own uncommitted writes from a replica that has not seen them. Read-heavy code that wraps everything in a transaction with a single write at the top gets no load balancing at all, and this is the usual reason a balanced-looking cluster sends everything to the primary.

sr_check_user is not optional

All of the above depends on Pgpool-II knowing which backend is the primary, which is what the streaming-replication check does. Its user defaults to empty. Per the documentation: the user “must have LOGIN privilege and exist on all the PostgreSQL backends. Moreover the user must be a PostgreSQL super user or in ‘pg_monitor’ group.”

GRANT pg_monitor TO pgpool_check;

Leave it blank and the role column of SHOW POOL_NODES cannot be trusted, which means write routing cannot be trusted either.

Managing it without a web panel

psql -h 127.0.0.1 -p 9999 -U youruser -c "SHOW POOL_NODES;"
pcp_node_info -h 127.0.0.1 -p 9898 -U pcpuser
pcp_detach_node / pcp_attach_node    # take a node out of rotation and put it back

SHOW POOL_NODES is where every number in this article came from: status, role, weight, SELECT count and replication delay per backend. For the same data inside SQL, install the pgpool_adm extension, which exposes the PCP commands as SQL functions — the supported way to build a UI.

Two smaller corrections

The old page was right that Windows is unsupported — the docs are blunt: “Windows is not supported” — but it also listed “CitusDB” as an alternative load balancer. Citus is a sharding extension, not a load balancer, and it is free software, licensed AGPL-3.0. Which tool fits which of the three separate problems people call load balancing is worth settling before configuring any of them.

What was not tested

No failover. Nothing here killed a backend, so health_check_period, watchdog and automatic promotion are untested and are omitted rather than described. This also ran on macOS, which is not on Pgpool-II’s list of verified platforms — the routing behaviour measured is Pgpool-II’s own, but a two-node cluster on one laptop shares a kernel, a disk and a clock with itself. Test failover in staging.

Reproducing this

The script verify-pgpool-ii-load-balancing.sh builds the primary, the streaming standby and the Pgpool-II instance from scratch on scratch ports, re-runs all four load-balancing experiments, re-reads the defaults from the running server, and deletes everything on exit. It reports 24 passed, 0 failed, 2 skipped on the machine above; the documentation quotes are checked against pgpool.net over the network and report SKIP when offline. Installing Pgpool-II itself is covered separately in installing Pgpool-II on Ubuntu.

Check this yourself. Every command and every block of output on this page is reproduced by /verify/pgpool-ii-load-balancing.sh. Download it and run it: it creates its own scratch files, prints one line per claim, cleans up after itself, and exits non-zero if any claim here turns out to be wrong. If it disagrees with this page, the page is wrong.