R2DBC pool closes all connections before each query

Question

We're using the latest versions of spring-data-r2dbc, r2dbc-pool and r2dbc-postgresql for connecting to a PostgreSQL database using a connection pool. We noticed some high response times, much higher than the query response times taken from the database itself (query_store.qs_view)

We added a metricsRecorder to the pool and, for debugging purposes, we're only printing when each method is invoked. It seems that before each SQL query, we get as many recordDestroyLatency invocations as there are connections in the pool and the same number of recordAllocationSuccessAndLatency invocations. We assumed that this means that each connection gets closed and reopened before each query. We then compared with the database logs and it proves this is true: there is the same number of could not receive data from client: An existing connection was forcibly closed by the remote host followed by connection received: messages.

Why would this happen? Below is the code we're using for creating the connection factory.

@Configuration
open class DatabaseConfiguration : AbstractR2dbcConfiguration() {

//some variable initialisations

    @Bean
    override fun connectionFactory(): ConnectionFactory {
        val cf = PostgresqlConnectionFactory(
            PostgresqlConnectionConfiguration.builder()
                .host(hostname)
                .database(dbName)
                .schema(dbSchema)
                .username(dbUsername)
                .password(dbPassword)
                .build()
        )
        val cp = ConnectionPoolConfiguration.builder(cf)
            .initialSize(poolInitialSize)
            .maxSize(poolMaxSize)
            .metricsRecorder(DatabaseMetricsRecorder())
            .build()
        return ConnectionPool(cp)
    }
}

As mentioned, the DatabaseMetricsRecorder just prints each operation. For the query itself, we're extending the ReactiveCrudRepository interface. The ConnectionPoolConfiguration is in its simplest form here, we tried adding parameters like maxIdleTime or validationQuery (as we'll have for production) but it doesn't seem to help.

Answer 1

It's a known bug in R2DBC pool, here's the issue. As a workaround, maxLifeTime should be explicitly set, for example I set it to the maximum allowed value in milliseconds (otherwise, if set to a value greater than the maximum allowed value in milliseconds, R2DBC will throw an Exception):

    val cp = ConnectionPoolConfiguration.builder(cf)
        .initialSize(poolInitialSize)
        .maxSize(poolMaxSize)
        .maxLifeTime(Duration.ofMillis(Long.MAX_VALUE))
        .metricsRecorder(DatabaseMetricsRecorder())
        .build()