How does database connection pooling work, and how do you size a pool correctly?

#Why pooling exists at all

Opening a TCP connection to a database, negotiating TLS, authenticating, and having the database fork or allocate a backend process/thread for that session is not free — it typically costs single-digit to tens of milliseconds, and on Postgres? specifically each connection is a full OS process with its own memory overhead, so idle connections are not "free to keep around" the way a socket file descriptor might suggest. If you open and close a fresh connection on every request, you pay that setup cost on the hot path of every single request, and you can exhaust the database's max-connection limit under load long before you exhaust its actual CPU or I/O capacity for running queries.

A connection pool amortizes this: it opens a fixed set of connections once, at application startup, and hands them out to threads/requests that need one, returning them to the pool instead of closing them when the work is done. The application borrows a connection, uses it, and gives it back — the connection itself stays open to the database the whole time.

#A concrete config: HikariCP

HikariCP (the default pool in Spring Boot?) is a good reference because its defaults are deliberately conservative and its parameters map cleanly onto the concepts every pool shares:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20        # hard ceiling on concurrent DB connections
      minimum-idle: 5              # keep at least this many warm, even when idle
      connection-timeout: 30000    # ms to wait for a free connection before throwing
      idle-timeout: 600000         # ms an idle connection above minimum-idle may live
      max-lifetime: 1800000        # ms before a connection is retired and replaced
      leak-detection-threshold: 60000  # warn if a connection is checked out this long

Every field earns its place: maximum-pool-size caps how much load you can put on the database from this one application instance; connection-timeout decides what happens when every connection is busy (fail fast with a clear error, rather than let requests queue silently forever); max-lifetime forces periodic connection recycling so a load balancer or database failover in front of the DB can actually route you to a new node instead of you holding a stale TCP session forever; and leak-detection-threshold catches the single most common pool bug — code that borrows a connection and forgets to return it (usually a missing close() in a try/finally, or an exception path that skips cleanup).

#Sizing the pool: bigger is not better

The instinctive move is to set maximum-pool-size high — "more connections, more throughput." That's backwards past a fairly small number, because a database connection maps to a real OS thread/process on the server, and beyond the number of CPU cores actually available to execute queries, additional connections just create contention for the same execution resources: more context switching, more lock contention, no more actual parallel query execution. HikariCP's own sizing guidance, echoing the "PostgreSQL connection pooling" community wisdom, gives a widely-cited starting formula:

pool_size = ((core_count * 2) + effective_spindle_count)

For an 8-core database server with SSD storage (effective spindle count ≈ 1), that's roughly (8 * 2) + 1 = 17, which is why HikariCP's own default of 10 is deliberately modest rather than "as high as possible." The formula is a starting point to measure from, not gospel — the right number depends on how much time each query actually spends waiting on I/O versus burning CPU, which is workload-specific.

The second, often-missed part of sizing: N application instances × pool size per instance must stay under the database's max_connections, with headroom for admin/replication connections and other services. Ten horizontally-scaled app pods each opening a pool of 20 is 200 connections aimed at a database that might cap out at 100 — the fix there usually isn't shrinking every pod's pool by hand, it's putting an external pooler (PgBouncer in transaction mode, for example) between the fleet and the database, so the database sees a small, stable number of real connections while thousands of application-side "connections" multiplex through a much smaller set of PgBouncer's actual backend connections.

#What actually goes wrong in practice