Why is a DB connection pool bounded by the database's max_connections rather than by app load?

package com.bank.config;

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

public class CorrectPoolSizing {

    // Database server: 4 CPU cores, SSD
    // Formula: pool_size = (cores * 2) + effective_spindle_count
    // pool_size = (4 * 2) + 0 = 8

    public DataSource createPool() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
        config.setUsername("nbi_app");
        config.setPassword("secret");
        config.setMaximumPoolSize(8);  // NOT 100, NOT 1000
        config.setConnectionTimeout(3000);
        return new HikariDataSource(config);
    }
}

-- Check the limit
SHOW max_connections;  -- Default: 100

-- Check current usage
SELECT count(*) FROM pg_stat_activity;  -- Currently open connections

-- The budget math:
-- max_connections = 100
-- Reserve for admin (DBA, monitoring, replication): 10
-- Available for apps: 90
-- Number of app instances: 6
-- Per-instance pool size: 90 / 6 = 15

package com.bank.config;

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

public class BudgetedPoolConfig {

    // 6 instances × 15 per instance = 90 total
    // max_connections = 100, admin reserve = 10
    // 90 <= 100 - 10 = 90 ✓

    public DataSource createPool() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://db.nbi.internal:5432/nbi_ledger");
        config.setMaximumPoolSize(15);  // Derived from budget, not from load
        config.setConnectionTimeout(3000);
        return new HikariDataSource(config);
    }
}

package com.bank.service;

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

public class ConnectionFailureDemo {

    private final DataSource dataSource;

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

    public void demonstrateFailure() {
        try (Connection conn = dataSource.getConnection()) {
            // When max_connections is exceeded, this throws:
            // PSQLException: FATAL: too many clients already
            conn.prepareStatement("SELECT 1").executeQuery();
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
            // Output: Connection failed: FATAL: too many clients already
        }
    }
}

package com.bank.config;

public class PoolSizeBudget {

    public static int calculatePoolSize(
            int maxConnections,
            int adminReserve,
            int numberOfInstances) {

        int availableForApps = maxConnections - adminReserve;
        int perInstanceSize = availableForApps / numberOfInstances;

        // Never exceed cores * 2 per instance (diminishing returns)
        int cpuOptimal = Runtime.getRuntime().availableProcessors() * 2;
        return Math.min(perInstanceSize, cpuOptimal);
    }

    public static void main(String[] args) {
        // National Bank of India production setup
        int poolSize = calculatePoolSize(
                200,   // max_connections
                20,    // admin reserve
                12     // app instances
        );
        System.out.println("Per-instance pool size: " + poolSize);
        // Output: Per-instance pool size: 15
        // 12 × 15 = 180 <= 200 - 20 = 180 ✓
    }
}

Alternatives to a bigger pool:

1. Faster queries
   - A pool of 8 with 1ms queries = 8000 qps
   - A pool of 8 with 100ms queries = 80 qps
   - Fix the query, not the pool

2. Read replicas
   - Route SELECT queries to replicas
   - Primary handles only writes
   - Each replica has its own max_connections

3. External pooler (PgBouncer)
   - Multiplexes 1000 logical connections onto 40 real ones
   - One central choke point for the entire fleet

4. Connection-per-transaction pattern
   - Hold the connection only during the query
   - Not during external HTTP calls or business logic

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 EfficientConnectionUsage {

    private final DataSource writeDataSource;  // Primary
    private final DataSource readDataSource;   // Replica

    public EfficientConnectionUsage(DataSource writeDs, DataSource readDs) {
        this.writeDataSource = writeDs;
        this.readDataSource = readDs;
    }

    // Read queries go to the replica, saving primary connections
    public BigDecimal getBalance(String accountId) throws SQLException {
        try (Connection conn = readDataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "SELECT balance FROM accounts WHERE account_id = ?")) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
            }
        }
    }

    // Write queries go to the primary
    public void transfer(String from, String to, BigDecimal amount) throws SQLException {
        try (Connection conn = writeDataSource.getConnection()) {
            conn.setAutoCommit(false);
            // ... debit and credit ...
            conn.commit();
        }
    }
}

-- Increasing max_connections beyond what the hardware supports:
ALTER SYSTEM SET max_connections = 500;
-- But: 500 connections × 10 MB = 5 GB just for connection overhead
-- Less memory for shared_buffers, work_mem, and caching
-- Performance degrades due to context switching

-- Better approach: keep max_connections moderate
-- and use PgBouncer to multiplex