Severely degraded performance of pgPool in geo-distributed configurations

[rotomasz]

Summary
In a geo-distributed environment with multi-region setup, pgPool contacts all configured backends on every SQL statement, including nodes with backend_weight=0 that will never receive traffic. This adds significant cross-region latency to each SQL statement. Removing or detaching remote (standby) backends from the config eliminates the overhead.

Environment

• pgPool version: 4.6.2
• PostgreSQL version: 17.5
• PostgreSQL topology: streaming replication, two region setup with three PostgreSQL nodes in each

Snippet of the configuration file causing performance issues

# local (primary) region
backend_hostname0 = 'db-local-1'
backend_weight0   = 1
backend_hostname1 = 'db-local-2'
backend_weight1   = 1
backend_hostname2 = 'db-local-3'
backend_weight2   = 1

# remote (standby) region - weights are set to 0
backend_hostname3 = 'db-remote-1'
backend_weight3   = 0
backend_hostname4 = 'db-remote-2'
backend_weight4   = 0
backend_hostname5 = 'db-remote-3'
backend_weight5   = 0

Current behavior
pgPool contacts all backends on every SQL statement, including the remote (standby) nodes with weight 0, even though the primary database and potential read-only candidates remain within the same region.

Impact
In a cross-region setup with e.g. ~20 ms round-trip latency, each SQL statement in a transaction incurs a ~20ms delay even though PostgreSQL server-side execution time is <1ms per statement. For a transaction containing 10 SQLs, we consistently observe ~200ms of total execution time via pgPool vs. actual execution time on PostgreSQL side of ~10ms. This causes a 20x performance degradation

Workaround
There’s no viable workaround if we need to maintain a highly performant geo-distributed PostgreSQL setup with pgPool.

Proposed fixes
The following behaviors could made configurable via pgPool parameters:

• Do not contact backends with backend_weight=0 during per-SQL eligibility checks; if communication with preferred nodes fails, consider the weight-0 backends as a fallback
• Instead of performing an eligibility check for every SQL statement, defer this to the Pgpool-II health check mechanism
• Perform the eligibility check once at the start of each transaction rather than for every statement

pgPool users could choose to keep the current behavior or enable any of the new options (which are not mutually exclusive).

[tatsuo-ishii]

Do not contact backends with backend_weight=0 during per-SQL eligibility checks; if communication with preferred nodes fails, consider the weight-0 backends as a fallback

This is not possible because backend_weight=0 node could be accessed if database_redirect_preference_list is set to something like:

mydb[0-4]:1
backend_weight1=0

Instead of performing an eligibility check for every SQL statement, defer this to the Pgpool-II health check mechanism

Probably you misunderstand when the "eligibility check" is performed. It's not performed every SQL statement. The data for "eligibility check" is cached in shared memory. It's updated when failover is performed.

I think the source of the latency are:

• application_name setting when client connects to pgpool. This is not problematic for applications to keep connection to pgpool for long time. But for applications that connect to pgpool every SQL statement or a few ones. Fortunately this has been already fixed by the commit above.

• Currently pgpool issues "Sync" message to all PostgreSQL nodes. Sync message is a messages used in extended query protocol, typically used by JDBC driver. This has not been fixed yet. Currently I am looking for good design for this.

[rotomasz]

Hi @tatsuo-ishii, thank you for the explanation. I see that the first bugfix was released as part of v4.6.4 — much appreciated. Do you have an ETA for the second bugfix? I can also confirm that we use JDBC and extended query protocol.

We’ve effectively been blocked from using pgPool in multi-region deployments over the last few months. Thanks for all your efforts.

[tatsuo-ishii]

I plan to make it into the next major release 4.8, which is scheduled to be released by the end of next year (2026).
The reasons include:

• It will need none trivial and invasive changes. Not suitable to be applied to stable branches.
• It will take long time because it's hard to implement.