What does an external connection pooler like PgBouncer add on top of the application-side pool?

package com.bank.config;

public class FleetConnectionMath {

    public static void main(String[] args) {
        // Without PgBouncer:
        int services = 5;          // account-service, loan-service, etc.
        int instancesPerService = 4;
        int poolPerInstance = 15;
        int totalConnections = services * instancesPerService * poolPerInstance;
        System.out.println("Total connections without PgBouncer: " + totalConnections);
        // Output: 300 connections demanded

        int maxConnections = 200;
        System.out.println("Exceeds max_connections? " + (totalConnections > maxConnections));
        // Output: true → connections rejected!

        // With PgBouncer:
        int pgBouncerPoolSize = 40;
        System.out.println("PgBouncer real connections: " + pgBouncerPoolSize);
        // 40 real connections serve 300+ logical connections
    }
}

Total connections without PgBouncer: 300
Exceeds max_connections? true
PgBouncer real connections: 40

Transaction pooling timeline:

Time 0.000s: Client A starts transaction
             PgBouncer assigns real connection R1 to Client A
Time 0.020s: Client A commits
             PgBouncer takes R1 back to the shared pool

Time 0.021s: Client B starts transaction
             PgBouncer assigns R1 to Client B (reused!)
Time 0.045s: Client B commits
             PgBouncer takes R1 back again

Between transactions, clients hold NO real connection
R1 served both Client A and Client B in 45ms
With 40 real connections, thousands of clients are served
because most clients are idle between transactions

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class TransferService {

    // JDBC URL points to PgBouncer, not directly to PostgreSQL
    // jdbc:postgresql://pgbouncer.nbi.internal:6432/nbi_ledger
    private final DataSource dataSource;

    public TransferService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void transfer(String from, String to, BigDecimal amount) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            try {
                // PgBouncer assigns a real connection for this transaction
                execute(conn, "UPDATE accounts SET balance = balance - ? WHERE account_id = ?",
                        amount, from);
                execute(conn, "UPDATE accounts SET balance = balance + ? WHERE account_id = ?",
                        amount, to);
                conn.commit();
                // PgBouncer reclaims the real connection here
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }

    private void execute(Connection conn, String sql, BigDecimal amount, String id)
            throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, id);
            ps.executeUpdate();
        }
    }
}

PgBouncer pooling modes:

1. Session pooling
   - Client keeps its real connection for the whole session
   - Behaves like a simple proxy
   - All PostgreSQL features work
   - Minimal multiplexing benefit

2. Transaction pooling (recommended)
   - Real connection assigned per transaction
   - Maximum multiplexing: 40 real connections serve 1000s
   - Breaks session-state features spanning transactions

3. Statement pooling
   - Real connection assigned per statement
   - Even more aggressive multiplexing
   - Breaks multi-statement transactions
   - Rarely used

package com.bank.service;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;

public class TransactionPoolingCaveats {

    private final DataSource dataSource;

    public TransactionPoolingCaveats(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // BREAKS: Session-level prepared statements
    // Transaction 1 prepares a statement on real connection R1
    // Transaction 2 may run on real connection R2
    // The prepared statement does not exist on R2

    // BREAKS: SET session variables
    public void setTimezoneAndQuery() throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            conn.createStatement().execute("SET timezone = 'Asia/Kolkata'");
            // Transaction commits, PgBouncer reclaims connection
        }
        try (Connection conn = dataSource.getConnection()) {
            // May get a DIFFERENT real connection!
            // timezone is NOT 'Asia/Kolkata' on this connection
            conn.createStatement().executeQuery("SELECT now()");
        }
    }

    // BREAKS: Advisory locks held across transactions
    // BREAKS: LISTEN/NOTIFY channels

    // FIX: Disable server-side prepared statements in the JDBC driver
    // jdbc:postgresql://pgbouncer:6432/nbi_ledger?prepareThreshold=0
}

; /etc/pgbouncer/pgbouncer.ini

[databases]
nbi_ledger = host=pg-primary.nbi.internal port=5432 dbname=nbi_ledger

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction          ; transaction pooling
max_client_conn = 2000           ; accept up to 2000 client connections
default_pool_size = 40           ; only 40 real connections to PostgreSQL
reserve_pool_size = 5            ; extra connections for burst
reserve_pool_timeout = 3         ; wait 3s before using reserve
server_lifetime = 3600           ; recycle real connections after 1 hour
server_idle_timeout = 600        ; close idle real connections after 10 min

package com.bank.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class PgBouncerDataSourceConfig {

    public DataSource createPool() {
        HikariConfig config = new HikariConfig();
        // Point to PgBouncer, not directly to PostgreSQL
        config.setJdbcUrl("jdbc:postgresql://pgbouncer.nbi.internal:6432/nbi_ledger"
                + "?prepareThreshold=0");  // Disable server-side prepared statements
        config.setUsername("nbi_app");
        config.setPassword("secret");
        config.setMaximumPoolSize(10);  // Small local pool for efficiency
        config.setConnectionTimeout(3000);
        return new HikariDataSource(config);
    }
}

Senior checklist for PgBouncer:
1. Use transaction pooling mode (not session)
2. Disable server-side prepared statements (prepareThreshold=0)
3. Keep local HikariCP pools small (10 per instance)
4. Monitor PgBouncer with SHOW STATS / SHOW POOLS
5. Set up health checks (pgbouncer admin console)
6. Consider active-passive pair for HA
7. Test failover: reconfigure PgBouncer to new primary
8. Watch for session-state features that break