PgBouncer with Spring Boot 4: Setup, Gotchas, and the Proof

Every Postgres connection is a process on the server. That's a deliberate design choice — it makes Postgres simple and robust — but it also means connections are expensive to open and there's a hard ceiling on how many can exist at once (max_connections, 100 by default). An application that opens-and-closes connections per request, or that just runs a lot of instances, each with their own pool, can hit that ceiling long before the database itself is under any real load.

PgBouncer sits between your application and Postgres and pools connections at the proxy: your app can open as many client connections to PgBouncer as it wants, and PgBouncer multiplexes them onto a much smaller number of real Postgres connections. This post walks through adding it to a Spring Boot 4 / Kotlin app, step by step, using the spring-demo-pgbouncer project as the running example.

1. Add PgBouncer to compose.yml

Postgres and PgBouncer as two services, PgBouncer pointed at Postgres by its Compose service name:

services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_DB: demo
      POSTGRES_USER: demo
      POSTGRES_PASSWORD: demo
    ports:
      - "5432:5432"
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -U demo -d demo" ]
      interval: 5s
      timeout: 5s
      retries: 10

  pgbouncer:
    image: edoburu/pgbouncer:latest
    environment:
      DATABASE_URL: postgres://demo:demo@postgres:5432/demo
      POOL_MODE: transaction
      MAX_CLIENT_CONN: "1000"
      DEFAULT_POOL_SIZE: "20"
      AUTH_TYPE: scram-sha-256
    ports:
      - "6432:6432"
    depends_on:
      postgres:
        condition: service_healthy

A couple of details are worth noting:

• edoburu/pgbouncer listens on port 5432 inside the container by default — it's built to be a drop-in replacement that your app talks to as if it were Postgres. If you want it on the conventional PgBouncer port (6432, so it's obvious at a glance which one you're hitting), you need to set LISTEN_PORT: "6432" explicitly. Skip this, and the container starts, prints its config, and then just sits there listening on the wrong port while your ports: mapping (or a Testcontainers wait strategy) times out waiting for 6432 to open.

• AUTH_TYPE: scram-sha-256 matches modern Postgres's default auth method. The image parses DATABASE_URL, writes the credentials into /etc/pgbouncer/userlist.txt on startup, and generates pgbouncer.ini from the environment variables — you don't hand-write either file for a setup this simple.

2. Pick a pool mode

POOL_MODE: transaction is the one that actually delivers the connection-multiplexing benefit. PgBouncer supports three:

• session — a client connection gets a server connection for its entire session. Safest (works with everything Postgres supports, including session-level SET/prepared statements), but doesn't help with connection ceilings — it's a 1:1 mapping, just proxied.

• transaction — a server connection is only checked out for the duration of a transaction, then returned to the pool the instant it commits/rolls back. This is the mode that lets MAX_CLIENT_CONN=1000 sit on top of DEFAULT_POOL_SIZE=20 real Postgres connections.

• statement — checked out per statement, even more aggressive, but breaks multi-statement transactions. Rarely what you want for an application backend.

Transaction pooling has one consequence worth knowing before you flip it on: because a given client connection might get a different backend server connection for its next transaction, session-level state doesn't survive — including the JDBC driver's server-side prepared statement cache. The fix on the Spring/Hikari side is one property:

# application-pgbouncer.properties
spring.datasource.url=jdbc:postgresql://localhost:6432/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.datasource.hikari.data-source-properties.prepareThreshold=0

prepareThreshold=0 tells the PostgreSQL JDBC driver never to switch to server-side prepared statements, which avoids ERROR: prepared statement "..." already exists / "does not exist" errors that show up under transaction pooling once the driver starts trying to reuse a statement name against a connection that PgBouncer has quietly swapped out from under it.

3. Point the application at PgBouncer instead of Postgres

The demo app keeps this as a separate Spring profile rather than baking it into the default config, specifically so you can flip between the two for comparison:

# application.properties (default — direct to Postgres)
spring.datasource.url=jdbc:postgresql://localhost:5432/demo

# application-pgbouncer.properties (routed through PgBouncer)
spring.datasource.url=jdbc:postgresql://localhost:6432/demo

./gradlew bootRun --args='--spring.profiles.active=pgbouncer'

Everything else about the application — the entities, the repositories, Hikari itself — is unchanged. That's the point of PgBouncer being a proxy: it speaks the Postgres wire protocol, so from the JDBC driver's point of view it is Postgres.

4. Benchmark it

The proof, as they say, is in the pudding. The demo project's PgBouncerConcurrencyBenchmarkTest starts a real Postgres and a real PgBouncer in Testcontainers (on a shared Docker network, PgBouncer addressing Postgres by its network alias), opens a Hikari pool against each, and runs the same concurrent workload — 80 workers × 25 short SELECT queries each — against both:

private fun runLoad(dataSource: HikariDataSource): LoadStats = runBlocking(Dispatchers.IO) {
    val totalNanos = measureNanoTime {
        val perWorkerLatencies = (1..CONCURRENCY).map {
            async {
                (1..QUERIES_PER_WORKER).map {
                    measureNanoTime {
                        dataSource.connection.use { connection ->
                            connection.createStatement().use { statement ->
                                statement.executeQuery("SELECT id FROM benchmark_probe WHERE id = 1").use { it.next() }
                            }
                        }
                    }
                }
            }
        }
        // ...collect percentiles from perWorkerLatencies
    }
}

A captured run, logged by the test itself:

PgBouncer concurrency benchmark completed, concurrency=[80] queriesPerWorker=[25]
directTotalMs=[127] directP50Ms=[0] directP95Ms=[2] directMaxMs=[114]
pgbouncerTotalMs=[110] pgbouncerP50Ms=[0] pgbouncerP95Ms=[3] pgbouncerMaxMs=[103]

At 80 concurrent clients, they're a wash — both comfortably under Postgres's default max_connections=100, so direct connections never queue and PgBouncer's extra network hop costs essentially nothing (a millisecond or two at p95). That's actually the right takeaway for this concurrency level: PgBouncer isn't a magic performance multiplier, it's insurance.

An insurance that pays off at the point where direct connections start queuing or getting rejected. Push the same benchmark's CONCURRENCY past Postgres's max_connections (100 by default, minus a handful reserved for superusers) and the direct-to-Postgres run starts throwing FATAL: sorry, too many clients already while PgBouncer — sitting on its fixed pool of 20 real backend connections — just queues the excess client connections for a few milliseconds and keeps going. That's the actual value proposition: PgBouncer doesn't make each query faster, it lets your application scale its client-side concurrency (more app instances, more threads, bursty traffic) far past what the database's connection limit would otherwise allow, without you having to provision Postgres for a peak max_connections it only needs for a few seconds a day.

Summary

• Transaction-mode PgBouncer sits between your app and Postgres, pooling a small number of real backend connections behind a much larger number of client connections.

• It costs a small, consistent per-query latency overhead (low single-digit milliseconds at p95 in this benchmark) — not free, but cheap.

• Its actual job is absorbing client-side concurrency that would otherwise hit Postgres's max_connections ceiling directly — the benefit doesn't show up at moderate concurrency, it shows up when you'd otherwise be paged.

• Watch for prepared-statement caching under transaction pooling (prepareThreshold=0on the JDBC datasource) and the edoburu/pgbouncer image's default listen port (LISTEN_PORT) — both are easy to trip over on a first setup.

See the full project at spring-demo-pgbouncer for the complete Spring Boot 4 application, this benchmark runs inside, including thePgBouncerConcurrencyBenchmarkTest source in full.