Re: Named Prepared statement problems and possible solutions

On 08.06.2023 3:43 PM, Jan Wieck wrote:
> 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