(Postgres) pgbouncer in transaction mode
we want to use pgbouncer to limit the number of concurrent connections to the database. Postgres documentation recommends no more than 800 simultanious connections. For optimial sharing of the connections we would like to use transaction mode in pgbouncer.
Documentation is here:
With transaction mode we get errors because named prepared statements are not possible in this mode. This is documented and quite sensible:
- using autocommit pgbouncer could switch connections between preparing and executing a statement.
We made tests with unnamed prepared statements without Unidac. These are usable with pgbouncer and transaction mode. This is corresponding to libpq function PQexecParams.
You can see the difference in postgres log: if statement logging is enabled in Postgresql database then we have prepare and execute with named statement here:
Code: Select all
2020-09-03 16:34:15.569 CEST 5f50fee1.948 22 LOG: Ausführen PRSTMTST557682082/PORTALST557682082: Update AL_TEST set CharText = $1 where xkey = $2 2020-09-03 16:34:15.569 CEST 5f50fee1.948 23 DETAIL: Parameter: $1 = 'ABCDefgh', $2 = '3' 2020-09-03 16:34:15.569 CEST 5f50fee1.948 24 LOG: Dauer: 0.159 ms
Code: Select all
2020-09-03 16:32:36.293 CEST 5f50fe81.ad4 11 LOG: Ausführen <unnamed>: Update AL_TEST set CharText = $1 where xkey = $2 2020-09-03 16:32:36.293 CEST 5f50fe81.ad4 12 DETAIL: Parameter: $1 = '\x4142434465666768', $2 = '3' 2020-09-03 16:32:36.293 CEST 5f50fe81.ad4 13 LOG: Dauer: 0.562 ms
Is it possible to use unnamed prepared statements with Unidac like PQexecParams so that pgbouncer can be used in transaction mode?