Add configuration support for PgBouncer

[WoodyWoodsta]

We've noticed hatchet can very quickly consume many connections to the DB. This means we've run into situations where the migrations will not run, or operators which manage the postgres instances are not able to connect to the DB.

The best practice solution we've implemented is to use PgBouncer, however, it appears as though Prisma requires extra configuration to support pgbouncer.

Is it possible for us to be able to indicate whether or not the database URL is a pgbouncer url, and for hatchet to configure the connection for prisma correctly in those cases?

[abelanger5]

Hey @WoodyWoodsta, we're actually removing Prisma -- v0.51.0 will be the last release with a dependency on Prisma. We haven't used Prisma on the engine-side for a while, but on the API side it's still been a requirement.

(I'd be happy to go into the many reasons for this decision)

To address the issues:

Is it possible for us to be able to indicate whether or not the database URL is a pgbouncer url, and for hatchet to configure the connection for prisma correctly in those cases?

The restriction here is from pgx and pgxpool as it uses prepared statements internally. I believe we need to set a database flag statement_cache_mode=describe which we can easily add support for in v0.51.0.

We've noticed hatchet can very quickly consume many connections to the DB

This is configurable via the DATABASE_MAX_CONNS flag - is Hatchet consuming more connections than what's set for this value?

[WoodyWoodsta]

Ah yes, sorry. I do remember now that migrations have moved to use a different tool.

If there is a way for us to not need to pool connections outside of hatchet, that would be preferable. I didn't realise that config was available, but I see the default is 5.

Right now, we have 1 frontend, 1 engine and 2 api, and the numbackends is 45 for hatchet application: SELECT numbackends FROM pg_stat_database WHERE datname = 'hatchet';. This seems higher than it should be.

I've seen the number jump up to ~80, but as mentioned, at times this can exceed the postgres default of 100.