Full support for a database connection pooler like pgbouncer

[999eagle]

Is your feature request related to a problem? Please describe. To minimize the number of actual database connections required for hydra, I'd like to be able to use pgbouncer in transaction pool mode as the "database" in the dbi connection string. While this works fine, hydra-update-gc-roots then fails because DBD::Pg defaults to prepared statements unless explicitly turned off and pgbouncer doesn't support prepared statements when running in transaction pool mode.

Describe the solution you'd like I'd like to have an option to turn off prepared statements for the hydra-update-gc-roots service (and potentially for the other services as well should they start using prepared statements in the future). This could be implemented by passing pg_server_prepare in the connect_info setting. Ideally this option would be available in the NixOS module.

Describe alternatives you've considered It's possible to use a dbi connection string like dbi:Pg(pg_server_prepare=>0):dbname=hydra;... to set that option, but this makes hydra-evaluator and hydra-queue-runner fail because they only check for fixed prefixes instead of parsing the actual string. I'm currently setting this explicitly using systemd.services.hydra-update-gc-roots.environment.HYDRA_DBI = lib.mkForce ... but this overrides the automatic setting of application_name.

[SuperSandro2000]

When reading the postgres doc, it seems like this comes with a potential hefty performance cost https://www.postgresql.org/docs/current/sql-prepare.html

[999eagle]

Potentially yes, but at least from what I've observed the performance impact is mostly negated by requiring fewer actual connections to PostgreSQL and thus fewer processes that need to be started when using a connection pooler. That's exactly why I'd like to have this as an option instead of a default.