Spring Boot 2 with pgBouncer pooling

Question

I have a PostgreSQL 11 server with pgBouncer pooling set up and enabled.

I'd like to use its pooling mechanism rather than default Hikari and Tomcat pooling that come with the Spring Boot's spring-boot-starter-data-jpa. I've disabled HikariCP and tomcat-jdbc from the project however, I'm not sure what I'd need to set up further in order to launch the Spring app successfully.

I guess my question is how to set up a Spring application so that it doesn't use any pooling mechanism to communicate with the db as it will be handled by pgBouncer on the db side?

I have looked at a variety of questions and answers to somewhat similar questions which led to me disabling HikariCP to start with. However, I was unable to find a concise tutorial/example of how I could make this work in my case scenario.

Any help would be really appreciated.

So disapibting. All answers are yalking about disabling Hikari, bur nobody told a rhing about prepared statements and transactions. I guess all of present were using pgBouncer in srssion node, not transaction mode. Which have no sense. — Bogdan Mart, Mar 11 '23 at 10:51

score 5 · Answer 1 · answered Jun 04 '20 at 21:53

I had similar problem, and after looking at the spring source codes I found an easier way that does not require any code or pom changes.

spring:
  datasource:
    type: org.springframework.jdbc.datasource.DriverManagerDataSource
    driver-class-name: org.postgresql.Driver

This datasource will simply replace hikari and create a new connection every time instead of pooling.

score 2 · Answer 2 · answered May 11 '20 at 13:15

Have to exclude it from dependency

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
        <exclusions>
            <exclusion>
                <groupId>com.zaxxer</groupId>
                <artifactId>HikariCP</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

score 1 · Accepted Answer · answered Apr 05 '20 at 19:08

Turns out I needed to define a DataSource, so I created a configuration class like this:

@Configuration
@ConfigurationProperties("spring.datasource")
public class DatabaseConfig {

    @Value("${spring.datasource.url}")
    private String uri;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Bean
    public DataSource dataSource() throws SQLException {
        SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
        dataSource.setDriver(DriverManager.getDriver(uri));
        dataSource.setUrl(uri);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }
}

score -1 · Answer 4 · answered Apr 05 '20 at 16:03

-1

You can disable Hikari's connection pool by setting maximumPoolSize as 1

This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Basically this value will determine the maximum number of actual connections to the database backend

 spring.datasource.hikari.maximumPoolSize=1

answered Apr 05 '20 at 16:03

Ori Marko

56,308
23
131
233

1

Does this limit the concurrent database activities that application can do to 1? – Alireza Aug 01 '20 at 14:50
@Alireza It limits the application code that uses hikari to 1 concurrent activity – Ori Marko Aug 03 '20 at 05:22
So I guess this is not a good answer to the original question – Alireza Aug 04 '20 at 02:03
@alireza OP doesn't want any pooling in code *set up a Spring application so that it doesn't use any pooling mechanism to communicate with the db as it will be handled by pgBouncer on the db side* – Ori Marko Aug 04 '20 at 04:02
3

Please correct me if I'm wrong: setting pool size to 1 does not disable pooling, it limits the concurrent database interactions to 1. Unless this setting to 1 has some side effects that I'm not aware of, like allowing the application to use non-pooled connection. Or, are you suggesting this as an addition to one of the other answers? If you can address these confusions in the answers it would be great. – Alireza Aug 04 '20 at 04:26
@alireza pool with 1 connection is the same as 1 connection without pool – Ori Marko Aug 04 '20 at 05:05

Spring Boot 2 with pgBouncer pooling

4 Answers4

Linked