Re: Named Prepared statement problems and possible solutions

On 6/8/23 02:15, Konstantin Knizhnik wrote:

There is a PR with support of prepared statement support to pgbouncer:
https://github.com/pgbouncer/pgbouncer/pull/845
any feedback, reviews and suggestions are welcome.

I was about to say that the support would have to come from the pooler as it is possible to have multiple applications in different languages connecting to the same pool(s)

Ideally, support should be provided by both sides: only pooler knows mapping between clients and postgres backends and only server knows
which queries require session semantic and which not (in principle it is possible to make connection pooler to determine it, but it is very non-trivial).

.

I can certainly give this a try, possibly over the weekend. I have a TPC-C that can use prepared statements plus pause/resume. That might be a good stress for it.

By the way, I have done some small benchmarking of different connection poolers for Postgres.
Benchmark was very simple: I just create small pgbench database with scale 10 and then
run read-only queries with 100 clients:

pgbench -c 100 -P 10 -T 100 -S -M prepared postgres

Number of connections to the database was limited in an all pooler
configurations to 10. I have tested only transaction mode. If pooler supports prepared statements, I have also tested them.
Just for reference I also include results with direct connection to Postgres.
All benchamrking was done at my notebook, so it is not quite representative scenario.

 

Direct:
Connections  Prepared     TPS
10           yes       135507
10           no         73218
100          yes        79042
100          no         59245

Pooler: (100 client connections, 10 server connections, transaction mode)
Pooler         Prepared     TPS
pgbouncer      no          65029
pgbouncer-ps   no          65570
pgbouncer-ps   yes         65825
odyssey        yes         18351
odyssey        no          21299
pgagrol        no          29673
pgcat          no          23247