Improve Database Connection Pooling

[artntek]

We've seen postgres connection pool starvation from time to time - e.g. when indexing or when doing hashstore conversions; eg:

metacat 20241112-12:37:09: [ERROR]: Cannot save checksums for urn:uuid:3c5a3edf-2f9e-40a4-b9f7-f8686215e1bd
since FATAL: remaining connection slots are reserved for non-replication superuser connections
[edu.ucsb.nceas.metacat.admin.upgrade.HashStoreUpgrader:convert:482]

...from ADC corpus, on a 256-core machine (overwhelming the 200 available DB connections)

Short-term solution was to limit the number of threads requesting connections. However, longer term, it would be nice to do something better - see below Slack posting from @mbjones on 11/12.24:

Matt Jones
Looking to the future, before she went on leave, @jeanetteclark and I had some discussions on connection pooling for metadig, which faces similar issues to the indexer and metacat. The postgres deployment on metadig loads the bitnami pgbouncer chart as a sidecar to postgres, and pretty transparently adds some robust connection pooling features without code changes. The PGBouncer transaction level pooling is pretty magical in enabling connection reuse even when code does a lot of transactions with a single statement. I've wondered whether shifting to something like that rather than managing the connection pools inside metacat would be sensible. Just food for thought.

She and I talked about moving from session to transaction pooling, but decided to leave it at session pooling for now.

Config is here: https://github.com/NCEAS/metadig-engine/blob/main/helm/metadig-postgres/config/pgbouncer.ini

also see: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server