Long lived DB connections, and unable to apply pgbouncer

[matteius]

Describe the bug

In Digital Ocean Manage Postgres database you are limited to a number of connections that is related to the number of cores. I have found that overtime fief is taking up these connections with what appears to be long idle long running queries:

Note that these are "idle" connections and there is not active queries going on.

The logs indicate that when using pg_bouncer connection that its not recommended and to use something else (sorry, I forget the name and I switched back to the normal connection) but that thing is not available in Digital Ocean managed databases.

Perhaps one thing I could do is not scale up the fief instances to as many, but I'd like to find some configuration that would work better with pg_bouncer.

To Reproduce

Have fief connect to a pg_bouncer managed connection configured in either transaction or session mode.

Expected behavior

To reduce the number of database connections.

Configuration

• Cloud or self-hosted: self-hosted
• If self-hosted, Fief version: v0.27.0 but possibly same for earlier versions

Additional context

n/a

[frankie567]

Hey @matteius 👋

This behavior is actually kinda expected: SQLAlchemy maintains a pool of connections, so we always have a ready-to-use connection to perform queries.

The default of SQLAlchemy is 5 connections at most. So, depending on the number of processes you run (both in terms of server and worker), they could multiply pretty fast.

There is currently no available option in Fief to tweak this behavior (or maybe even change the type of connection pooling applied by SQLAlchemy). That should probably be something to add.