Re: SQL query latency when using pgPool (v4.6.2)

Hi,

Sorry for the late response.
We are aware of the issue, and are currently looking into whether improvements can be made.

We will provide an update on the GitHub issue (https://github.com/pgpool/pgpool2/issues/130) as soon as the patch is ready.

Best regards,

Bo Peng
________________________________________
差出人: Bob Ross <bob(dot)ross(dot)19821(at)gmail(dot)com>
送信: 2025 年 11 月 2 日 (日曜日) 18:42
宛先: Bo Peng <pengbo(at)sraoss(dot)co(dot)jp>
Cc: pgpool-general(at)lists(dot)postgresql(dot)org <pgpool-general(at)lists(dot)postgresql(dot)org>
件名: Re: SQL query latency when using pgPool (v4.6.2)

Hi Bo, Do you have any updates regarding this case? Have you been able to reproduce it? In the last week I have created a new set of VMs in European and Australian datacenters (220ms latency), and some very simple SELECT SQL statements routed locally (with remote weights set to 0) still experience the same latency issue. The more statements I add to the transaction, the bigger the gap gets (e.g. 5ms vs 1100ms for 5 simple SQLs). Thanks, BobOn Wednesday, October 22, 2025, Bob Ross <bob(dot)ross(dot)19821(at)gmail(dot)com> wrote:++  pgpool-general(at)lists(dot)postgresql(dot)org as missed it by accident.On Wed, Oct 22, 2025 at 11:40 AM Bob Ross <bob(dot)ross(dot)19821(at)gmail(dot)com> wrote:Hi Bo,I was able to narrow down the issue and reported it as a bug: https://github.com/pgpool/pgpool2/issues/130All required information regarding versions is in the link. Affected pgPool is colocated with Postgres in the local (primary) region.See our pgpool.conf attached but it's quite irrelevant as this issue occurs with any parameter combination, as long as there are both local and remote nodes configured as backends.Logs are clean, the only difference is the delay between SQLs when remote nodes are attached (~20ms delay) and detached (~1ms delay).For us this is a blocker - we are unable to use pgPool effectively in multi-region Postgres deployment.Please let us know in case of any further questions.Thanks,BobOn Fri, Oct 17, 2025 at 9:42 AM Bo Peng <pengbo(at)sraoss(dot)co(dot)jp> wrote:Hi,Thank you for your question.Could you please share the cluster configuration?(For example, the number of nodes, Pgpool-II/PostgreSQL versions, and whether pgpool and PostgreSQL are running on the same host.)Also, could you please share your pgpool.conf file and the pgpool logs?---Bo Peng <pengbo(at)sraoss(dot)co(dot)jp>SRA OSS K.K.TEL: 03-5979-2701 FAX: 03-5979-2702Mobile: 080-7752-0749URL: https://www.sraoss.co.jp/________________________________________差出人: Bob Ross <bob(dot)ross(dot)19821(at)gmail(dot)com>送信: 2025 年 10 月 8 日 (水曜日) 15:30宛先: pgpool-general(at)lists(dot)postgresql(dot)org <pgpool-general(at)lists(dot)postgresql(dot)org>件名: SQL query latency when using pgPool (v4.6.2)Hello,I am currently testing SQL query performance using PgPool (v4.6.2) as a load balancer, and I am observing approximately 18–20 ms latency between SQL statements within the same transaction. Execution time on the PostgreSQL backend servers is less than 0.5 ms.It’s important to note that both the application and database servers are located in the same cloud region, with network latency below 1 ms. PgPool is colocated with the PostgreSQL backend servers.For example, when executing a transaction containing 10 SQL statements, the total execution time through pgPool is about 200 ms, as confirmed by both application and pgPool logs. The delay consistently occurs between the “Execute” and “Parse” phases. When connecting directly to the PostgreSQL backends, the same transaction completes in about 10 ms, with roughly 1 ms between SQL statements.We have tested the following configurations:- Switched from the extended query protocol to the simple query protocol — for some queries this reduced inter-query latency to 2–3 ms, but for others it increased to 30–40 ms due to additional parsing overhead.- Adjusted various PgPool parameters related to load balancing and connection pooling (e.g., load_balance_mode, statement_level_load_balance, serialize_accept, disable_load_balance_on_write) and enabled debug logging, but none of these changes brought a significant improvement.Could you please advise whether there are additional pgPool parameters or tuning approaches that could help improve throughput and reduce latency in extended query protocol? Thank you,Bob