On 5/5/25 09:52, Mladen Marinović wrote:
Hi,
We recently migrated our production instances from PG11 to PG17. While
doing so we upgraded our pgBouncer instances from 1.12 to 1.24. As
everything worked on the test servers we pushed this to production a
few weeks ago. We did not notice any problems until a few days ago
(but the problems were here from the start). The main manifestation of
the problems is a service that runs a fixed query to get a backlog of
unprocessed data (limited to a 1000 rows). When testing the query
using pgAdmin connected directly to the database we get a result in
cca. 20 seconds. The same query runs for 2 hours when using pgBouncer
to connect to the same database.
That's a huge jump, I hope you guys did extensive testing of your app.
In which language is your app written? If java, then define
prepareThreshold=0 in your jdbc and set max_prepared_statements = 0 in
pgbouncer.
How about search paths ? any difference on those between the two runs ?
Do you set search_path in pgbouncer ? what is "cca." btw ?
The more interesting part is that when we issue an explain of the same
query we get different plans. We did this a few seconds apart so there
should be no difference in collected statistics. We ruled out prepared
statements, as we suspected the generic plan might be the problem, but
it is not. Is there any pgBouncer or PG17 parameter that might be the
cause of this?
Does this spawn any connections (such as dblink) ? are there limits per
user/db pool_size in pgbouncer ?
Pgbouncer, in contrast to its old friend PgPool-II is completely
passive, just passes through SQL to the server as fast as possible as it
can. But I am sure you know that. Good luck, keep us posted!
Regards,
Mladen Marinović
