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
