JDBC and Connection Pooling (HikariCP)

1. Introduction

Every database connection is an expensive resource — it consumes ~10 MB of PostgreSQL server memory, a process slot, and TCP socket overhead. Connection pooling reuses a small set of persistent connections for many application requests. HikariCP is the industry-standard Java pool, offering sub-millisecond borrow times. PgBouncer is a PostgreSQL-native pooler that sits between the app and database, supporting transaction-level connection sharing for any language.

2. Why It Matters

Without pooling, a web app handling 1,000 concurrent users would open 1,000 database connections, each consuming 10 MB = 10 GB of PostgreSQL memory, plus process scheduling overhead. Connection establishment takes 50-200ms (TCP handshake + SSL + auth). Pooling reduces this to ~0 connections opened per request and sub-millisecond connection borrow times, dramatically improving throughput and latency.

3. Real-World Analogy

A connection pool is like a taxi fleet at an airport. Instead of every passenger buying a new car (opening a new connection), they borrow a taxi from the fleet (pool), use it, and return it. The fleet stays at a manageable size while serving hundreds of passengers per hour.

4. How It Works

• HikariCP (Java): Maintains a configurable pool of JDBC connections. On getConnection(), it borrows an idle connection. On close(), the connection returns to the pool. Key settings: maximumPoolSize (default 10), connectionTimeout, idleTimeout, maxLifetime.
• PgBouncer modes: Session (connection per client session), Transaction (connection per BEGIN/COMMIT block — most efficient), Statement (connection per single query — rare).
• JDBC: Java Database Connectivity — the standard API for connecting Java applications to relational databases. Uses drivers (PostgreSQL JDBC Driver) to translate Java calls to PostgreSQL's wire protocol.

5. Internal Architecture

HikariCP uses a lock-free concurrent bag (ConcurrentBag) for O(1) connection borrowing. It pre-creates minimumIdle connections at startup and lazily creates more up to maximumPoolSize. Idle connections are evicted after idleTimeout. PgBouncer sits as a TCP proxy, multiplexing client connections over a smaller pool of server connections. In transaction mode, it assigns a server connection only during active transactions.

6. Visual Explanation

The diagram shows application threads borrowing connections from a HikariCP pool, which maintains a smaller set of persistent PostgreSQL connections, compared to un-pooled direct connections.

7. Practical Example

8. Common Mistakes

• Pool size too large: More connections than CPU cores causes context switching overhead. Formula: pool_size = (core_count * 2) + effective_spindle_count.
• Not closing connections: Forgetting to close (or use try-with-resources) leaks connections until the pool is exhausted.
• maxLifetime shorter than server timeout: If PostgreSQL's idle_session_timeout kills a connection before the pool rotates it, the pool borrows a dead connection.
• Using PgBouncer transaction mode with prepared statements: PgBouncer discards session state (including prepared statements) between transactions. Use named prepared statements at the driver level or switch to session mode.

9. Quick Quiz

10. Scenario-Based Challenge

A Spring Boot application with 200 concurrent threads exhausts a HikariCP pool of 20 connections, causing 30-second timeouts. PostgreSQL has max_connections = 100. Design a two-tier pooling strategy (HikariCP + PgBouncer) to handle the load without increasing PostgreSQL's max_connections.

11. Debugging Exercise

12. Interview Questions

• Q: Why not just increase max_connections instead of using a pool?
  A: Each PostgreSQL connection consumes ~10 MB RAM and a process slot. 500 connections on a 16 GB server wastes 5 GB on connection overhead. Pooling serves the same throughput with 20-50 connections.
• Q: What is the difference between HikariCP and PgBouncer?
  A: HikariCP is an in-process Java pool (one pool per JVM). PgBouncer is an external proxy pool shared by all clients. They can be used together: HikariCP pools connections to PgBouncer, which pools connections to PostgreSQL.

13. Production Considerations

• Pool sizing formula: Start with connections = ((core_count * 2) + disk_spindles) and tune based on actual throughput.
• Connection validation: HikariCP's connectionTestQuery validates connections before lending them. PostgreSQL JDBC driver's isValid() is faster than a SELECT 1.
• Monitoring: Expose HikariCP metrics to Prometheus/Grafana. Alert on ThreadsAwaitingConnection > 0 for more than 5 seconds.
• Statement caching: Enable cachePrepStmts and useServerPrepStmts in the JDBC URL for prepared statement caching.