Connection pool size with postgres r2dbc-pool

Question

I'm not able to open more than 10 connections with spring-webflux and r2dbc (with r2dbc-pool driver 0.8.0.M8). My config looks like:

@Configuration
public class PostgresConfig extends AbstractR2dbcConfiguration {

  @Override
  @Bean
  public ConnectionFactory connectionFactory() {
    ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
        .option(DRIVER, "pool")
        .option(PROTOCOL, "postgresql")
        .option(HOST, host)
        .option(USER, user)
        .option(PASSWORD, password)
        .option(DATABASE, database)
        .build());
    ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(connectionFactory)
        .maxIdleTime(Duration.ofMinutes(30))
        .initialSize(initialSize)
        .maxSize(maxSize)
        .maxCreateConnectionTime(Duration.ofSeconds(1))
        .build();
    return new ConnectionPool(configuration);
  }
}

When I'm specifying more than 10 connections I get errors like:

org.springframework.dao.DataAccessResourceFailureException: 
Failed to obtain R2DBC Connection; nested exception is 
java.util.concurrent.TimeoutException: 
Did not observe any item or terminal signal within 1000ms in 'lift' 
(and no fallback has been configured)
    at org.springframework.data.r2dbc.connectionfactory.ConnectionFactoryUtils
    .lambda$getConnection$0(ConnectionFactoryUtils.java:71)

Moreover, number of connections remain the same as initial size. New connections are not created.

Maybe you should increase the `maxCreateConnectionTime` then? You're hitting a timeout after all. — Kayaman, Sep 17 '19 at 09:28

score 9 · Answer 1 · answered Nov 28 '20 at 22:58

Spring boot (at least 2.3.4) have a tricky "gotcha" regarding the pool size when set by properties/yaml. If you include "pool" in your database url, then the size set (initial size or max size) won't have any effect and the defaults for the r2dbc pool will be used, 10 and 10.

This is due to PooledConnectionFactoryCondition in ConnectionFactoryConfigurations.java failing when both spring.r2dbc.pool.enabled=true, which it is if the r2dbc-pool dependency is on the classpath, and "pool" being part of the spring.r2dbc.url property.

From the PooledConnectionFactoryCondition docs:

Condition that checks that a ConnectionPool is requested. The condition matches if pooling was opt-in via configuration and the r2dbc url does not contain pooling-related options.

This in turn does lead to the ConnectionPool bean not being created.

Skip the "pool" keyword in the r2dbc url property and have the r2dbc-pool dependency, then you will get a correctly configured pool.

This is a very important comment! I also bumped into this ambiguity with `spring.r2dbc.pool.enabled` prop and having `:pool:` in the URL. However I had a different and very peculiar result - if the aforementioned `PooledConnectionFactoryCondition` fails then Connection Pool is created anyway but from a different source (namely `io.r2dbc.pool.PoolingConnectionFactoryProvider#create`). This looks like Spring Boot can create a Connection Pool basing on the `PooledConnectionFactoryCondition` but if it fails delegates its creation to `io.r2dbc.pool.PoolingConnectionFactoryProvider.class` — fyrkov, Dec 16 '20 at 07:43

score 5 · Accepted Answer · answered Sep 17 '19 at 10:34

Ok, the MAX_SIZE should be also specified for ConnectionFactoryOptions. Otherwise connection pool size still remains 10.

import static io.r2dbc.pool.PoolingConnectionFactoryProvider.MAX_SIZE;

    ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
        .option(DRIVER, "pool")
        .option(PROTOCOL, "postgresql")
        .option(HOST, host)
        .option(USER, user)
        .option(PASSWORD, password)
        .option(DATABASE, database)
        .option(MAX_SIZE, maxSize)
        .build());

score 3 · Answer 3 · answered Aug 25 '20 at 08:02

3

Note that, you can use the release version 0.8.4.RELEASE (which is the latest) https://mvnrepository.com/artifact/io.r2dbc/r2dbc-postgresql/0.8.4.RELEASE which does not require you to instantiate a ConnectionFactory

answered Aug 25 '20 at 08:02

Pierre CARUYER

201
1
3

score 1 · Answer 4 · answered Aug 07 '20 at 15:36

1

If you are using spring-boot-starter-data-r2dbc, then the min and max is configurable in application.properties

spring.r2dbc.pool.initialSize=2
spring.r2dbc.pool.maxSize=2

See org.springframework.boot.autoconfigure.r2dbc.R2dbcProperties class

answered Aug 07 '20 at 15:36

krishnakumarp

8,967
3
49
55

Ady · Answer 5 · 2021-07-07T11:39:53.243

1

Below is my configuration for spring-boot-starter-data-r2dbc Check if that helps you:

spring:
  r2dbc:
    url: r2dbc:postgresql://127.0.0.1:5432/test?schema=public
    username: postgres
    password: postgres
    pool:
      name: TEST-POOL
      initial-size: 1
      max-size: 10
      max-idle-time: 30m

edited Jul 07 '21 at 11:39

answered Jul 06 '21 at 12:50

Ady

584
8
16

Connection pool size with postgres r2dbc-pool

5 Answers5

Linked