How are connections to the db managed?

[TimoGlastra]

I'm curious how Askar manages connections to the database.

When switching from creating a separate DB per wallet to using profiles, I now get the error in CI for some tests:

      Backend error
      Caused by: pool timed out while waiting for an open connection

This seems to be an sqlx error if you create more connections than the allowed max_connections. Question now is: is this within a store? I thought a benefit of using profiles is that you can reuse the same connection to the database, even for different profiles. Is that indeed the case?

Tests are running with sqlite. Not sure if this behvaiour is different between postgres and sqlite?

[andrewwhitehead]

The maximum number of connections applies to a Store instance; each gets one connection pool. The connection string allows for a max_connections parameter to set the maximum number of pooled connections.

I thought a benefit of using profiles is that you can reuse the same connection to the database, even for different profiles. Is that indeed the case?

Yes, it's possible to access different profiles within the same Store instance, using the same connection pool. As long as the connections are being returned to the pool (sessions are not being left open) this shouldn't cause a pool timeout.

Tests are running with sqlite. Not sure if this behvaiour is different between postgres and sqlite?

The way the pool is managed is the same, but the maximum number of connections might be different. For sqlite, the default setting is to use the available parallelism (ie. available CPU cores) for the maximum number of connections, with a minimum of 2.

Sqlite has an additional restriction that only one writer can be active at a time, so transactions do need to be kept short to avoid conflicts between concurrent tasks.