Improve resiliency on Database connection issues

[Sanne]

Description At high level, the intent is to minimize the problem with connecting to databases in the cloud; in practice, often they aren't ready, or are being reconfigured shortly after the application is booted.

Implementation ideas

validation-query-sql

I'm wondering if we should set a sensible default for quarkus.datasource.validation-query-sql; often we know the database vendor by inspecting the driver, so we could have an adequate default for each of them.

Integrate with health metrics

I believe this was done already, but perhaps something is missing? Would be good to be able to test what happens when a DB is restarted/reconfigured, I guess it's possible we're not aggressive enough in the state switch.

[loicmathieu]

I also notice that if a Database is not ready (at least with Postgres) when the applicaiton is started, the application crash and needs to be restatred.

This add a hard dependency between the database and the application, if both are containers this can be challenging.

With MongoDB, the application can start without a database and connect to it later, maybe this should be implemented like this for RDBMS ...

The current healthcheck for RDBMS (implemented at Agroal level) is a readiness check (it uses the isValid(0) method of the datasource) so if deploying to kubernetes a database check failure will not restart the application but prevent any request to be send to it.

[Sanne]

N.B. that by default the Datasource connection pool is started lazily. So you only have such a problem if you have something that's triggering the need for a connection during start; for example if you have Hibernate ORM to validate/update the schema: disable that and you'd have the same as what you describe having with MongoDB.

The current healthcheck for RDBMS (implemented at Agroal level) is a readiness check (it uses the isValid(0) method of the datasource) so if deploying to kubernetes a database check failure will not restart the application but prevent any request to be send to it.

funny, I hadn't reviewed the healthcheck. Ironically that might be the actual cause of eager initialization.

I guess we need a way for the readyness check to be more "lenient", and include Hibernate's schema validation into such a lenient check phase.

[Sanne]

@loicmathieu I guess the MongoDB extension should do the same? I wouldn't claim it's technically "ready" if you haven't checked the capability to connect to it, no?

[loicmathieu]

N.B. that by default the Datasource connection pool is started lazily

I use Flyway, it must be the cause ...

Ironically that might be the actual cause of eager initialization.

Maybe. And maybe we can do something inside the health check to only call isValid() if the datasource is started.

I guess the MongoDB extension should do the same?

MongoDB readiness check do the same as Agroal one (and we implement the same for almost all supported database/message broker). There is no liveness check done for any database/message broker in any extension as a failing database/message broker should not trigger the restart of the application but only provides them to send request (so we only implement readiness checks).

[barreiro]

@Sanne we should setup the exception sorter feature in Agroal in order to purge stale connections ASAP. Should not be too hard since we have control of the JDBC driver as well.

[nimo23]

I also notice that if a Database is not ready (at least with Postgres) when the applicaiton is started, the application crash and needs to be restatred.

@loicmathieu I think this is related to https://github.com/quarkusio/quarkus/issues/5865 as it was said: Quarkus boots faster than the database and there is no possibility to coordinate such process..