jdbchikaricpquestdb

How to use HikariCP with QuestDB without resetting the connection all the time?


I'm trying to use HikariCP JDBC pool with QuestDB instead of PostgreSQL like this:

public static void main(String[] args) {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl("jdbc:postgresql://localhost:8812/qdb");
    config.setUsername("admin");
    config.setPassword("quest");
    config.setMaxLifetime(60000);
    config.setMaximumPoolSize(10);

    // Create the DataSource
    HikariDataSource dataSource = new HikariDataSource(config);

    while (true) {
        // Use the DataSource to get a connection
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT now()")) {

            // Process the result
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Os.sleep(10000);
    }
}

My dependencies are

<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
  <version>6.0.0</version>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.3</version>
</dependency>

It seems working fine, I can query the db but I see in my QuestDB logs that something closes all the pooled connections after 59 seconds like this

2024-09-24T11:09:26.276729Z I pg-server connected [ip=127.0.0.1, fd=2241972929959]
...

2024-09-24T11:10:24.950787Z I pg-server scheduling disconnect [fd=2241972929959, reason=15]
2024-09-24T11:10:24.951148Z I pg-server disconnected [ip=127.0.0.1, fd=2241972929959, src=queue]

What is the way to use HikariCP with QuestDB so that the pooled connections stay opened and only the closed/invalid are evicted from the pool?


Solution

  • It does this, because you configured config.setMaxLifetime(60000);, which restricts the lifetime of a connection to 60 seconds. That means that a connection that was created 60 or more seconds ago, and is not currently checked out of the pool (i.e. in use), will be automatically closed. For contrast, the default value is 1800000 (30 minutes).

    In other words, remove the setMaxLifetime setting, or at least set it a lot higher, or set it to 0 if you want to disable such eviction (which is BTW not recommended).

    See also the documentation of maxLifetime (emphasis from original):

    maxLifetime
    This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. We strongly recommend setting this value, and it should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. The minimum allowed value is 30000ms (30 seconds). Default: 1800000 (30 minutes)