Auto-reconnect to database with HikariCP after DB crash/restart

[twisti-dev]

What Needs Improvement?

Currently, if the database crashes or restarts, the application using HikariCP fails to automatically reconnect, resulting in a cascade of connection failures until a manual restart or intervention is performed.

Proposed Improvement

Implement automatic reconnection logic in the HikariCP configuration or application-level logic to detect and recover from database disconnections. This could include:

• Enabling HikariCP's validationTimeout and connectionTestQuery
• Adjusting maximumPoolSize, idleTimeout, connectionTimeout, and maxLifetime to handle reconnections better
• Adding a retry mechanism for transient failures
• Monitoring and resetting stale connections

Benefits of the Improvement

• Reduces downtime after a database crash or restart
• Enhances resilience and reliability of the application
• Minimizes the need for manual intervention during outages
• Improves user experience through increased service availability

Additional Context

This issue has been observed in production environments where temporary DB unavailability causes persistent failures in services depending on HikariCP.