Your connection pool is bigger than your database

A startup in Lisbon called me in after their database started rejecting connections at random intervals. The error was familiar. “FATAL: remaining connection slots are reserved for non-replication superuser connections.” Postgres had hit its max_connections limit. The database was a db.r6g.large instance with 200 connections configured. They had ten services talking to it. Each service had a connection pool of 20. Ten times 20 is 200. The database was full, and every pool thought it was being reasonable.

The on-call engineer did what most engineers do. He bumped max_connections to 400. The errors stopped for a week. Then they came back, because two services had been scaled from two pods to four pods each, and the math changed. Ten services, some with four pods, each pod with a pool of 20. The total had quietly climbed past 400. He bumped it to 600. The database started running out of memory, because every Postgres connection is a forked process with its own memory overhead. At 600 connections on an 8GB instance, the connection overhead alone was consuming 2.5GB. Query performance degraded. The database was now slow because of connections, not because of queries.

This is the pattern I see at almost every startup that has moved to a microservice architecture. Each team sizes their connection pool for their service. Nobody sizes the database for the sum of all pools. The connection pool is a local decision with a global consequence.

How connection exhaustion actually happens

The math is not complicated. It is just never done. A Postgres instance has a max_connections setting. Every service that connects to it opens a pool. The pool size is configured per pod. If you have three pods running a service with a pool of 20, that service holds 60 connections. Multiply across services, add the connection overhead of the ORM, the migration tool, the monitoring agent, the admin console, and the total exceeds max_connections.

Here is the calculation I ask every team to do. It takes five minutes and nobody has done it.

Service A: 3 pods x pool 20 = 60 connections
Service B: 4 pods x pool 15 = 60 connections
Service C: 2 pods x pool 25 = 50 connections
Service D: 5 pods x pool 10 = 50 connections
Service E: 3 pods x pool 20 = 60 connections

Subtotal: 280 connections

Migration runner:         5 connections
Monitoring agent (Datadog): 10 connections
Admin console (pgAdmin):   3 connections
PgBouncer admin:           2 connections

Total: 300 connections

Postgres max_connections: 200
Result: 100 connections rejected at peak

This team had 300 connections worth of pools pointing at a 200-connection database. The database was oversubscribed by 50%. It worked fine at low traffic because pools are not always full. It failed at peak traffic because pools fill up under load, and that is exactly when you cannot afford to fail.

The worst part is that most of those connections are idle. A service with a pool of 20 might use 4 connections under normal load and 20 under a traffic spike. The other 16 sit idle, holding a Postgres backend process open, consuming memory, and counting against max_connections. The pool is sized for the worst case, but the worst case across all services never happens simultaneously. The database is paying for capacity that is never used.

Why your ORM’s pool is not helping

Every ORM has a connection pool. SQLAlchemy calls it a pool. HikariCP calls it a pool. Prisma calls it a pool. They all do the same thing. They open N connections to the database at startup and reuse them across requests. This is better than opening a connection per request, which is a disaster. But the pool size is a local configuration that does not know about the database’s global limit.

The pool also does not know about the connection lifecycle. A connection opened by the pool stays open for the lifetime of the application process. If the process is a Kubernetes pod, the connection stays open until the pod restarts. In a deployment with rolling updates, old pods and new pods are both running simultaneously, both with their pools open. During a deploy of Service A from 3 pods to 4 pods, you briefly have 7 pods, each with 20 connections. That is 140 connections for a service that normally uses 60. The deploy itself can cause connection exhaustion.

Here is the SQLAlchemy default that most Python services ship with.

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@db.internal:5432/app",
    pool_size=20,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=1800,
)

The pool_size=20 means 20 persistent connections. The max_overflow=10 means 10 more can be opened under load, for a total of 30 per process. The pool_recycle=1800 recycles connections every 30 minutes, which is good. But the total per pod is 30 connections, not 20. The team that configured pool_size=20 thinks they are using 20 connections per pod. They are using 30.

I have never audited a service where the engineer knew about max_overflow. They configure pool_size, ignore max_overflow, and the database sees 50% more connections than expected.

The Java side is worse. HikariCP’s default pool size is 10, which sounds conservative. But Spring Boot applications often have multiple DataSources, one for the primary database, one for a read replica, one for a separate schema. Three pools of 10 per pod. Scale to four pods and you have 120 connections for one service.

// Spring Boot application.yml
spring:
  datasource:
    primary:
      hikari:
        maximum-pool-size: 10
    replica:
      hikari:
        maximum-pool-size: 10
    audit:
      hikari:
        maximum-pool-size: 10
// 30 connections per pod, 4 pods = 120 connections for one service

The engineer who set this up configured 10 connections per pool because the HikariCP documentation says 10 is a good default. It is a good default for one pool. It is not a good default when you have three pools per pod and four pods per service.

The real cost of a Postgres connection

A Postgres connection is not a lightweight handle. It is a forked process. Every connection spawns a backend process that allocates memory for query parsing, planning, execution, and the work_mem area for sorting and hashing. The default work_mem is 4MB. A connection doing a sort can allocate 4MB. At 200 connections, the potential memory overhead from work_mem alone is 800MB. On an 8GB instance, that is 10% of total memory, before any actual data is touched.

The process overhead is roughly 5-10MB per connection on a modern Postgres, depending on the queries being run. At 200 connections, that is 1-2GB of overhead for processes that are mostly idle. At 600 connections, which is where the Lisbon team ended up, it is 3-6GB on an 8GB instance. The database is spending more memory on connection processes than on caching data.

This is why bumping max_connections is not a solution. It is a deferral. The connections come back. The memory pressure increases. The query performance degrades because the buffer cache is smaller. The database gets slower. The services open more connections because their pools are not filling fast enough. The cycle accelerates.

The correct response is to reduce the number of connections to the database, not to increase the database’s capacity for them.

PgBouncer: the band-aid that works

PgBouncer is a connection pooler for Postgres. It sits between your services and the database. Your services connect to PgBouncer, and PgBouncer maintains a smaller set of real connections to the database. The math changes.

Without PgBouncer:
  10 services x 3 pods x 20 pool = 600 connections to Postgres

With PgBouncer:
  10 services x 3 pods x 20 pool = 600 connections to PgBouncer
  PgBouncer to Postgres: 50 connections

PgBouncer has three pooling modes. Session mode keeps a real connection for the duration of a client session. It does not reduce the number of database connections. It is useless for this problem. Transaction mode assigns a real connection for the duration of a transaction and returns it to the pool when the transaction ends. This is what you want. Statement mode does it per statement, which breaks prepared statements and session state. Do not use statement mode.

Transaction mode is the one that solves connection exhaustion. Here is the configuration I install.

[databases]
app = host=db-primary.internal port=5432 dbname=app user=appuser password=...

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 50
max_user_connections = 50
server_idle_timeout = 600
query_wait_timeout = 30

The key settings. max_client_conn = 1000 means PgBouncer accepts up to 1000 client connections. These are lightweight. They are not Postgres processes. They are file descriptors in a single process. default_pool_size = 25 means PgBouncer maintains 25 real connections to the database per user/database pair. max_db_connections = 50 is the hard cap. Even if every pool is full, PgBouncer will not open more than 50 connections to Postgres.

Your services connect to PgBouncer on port 6432 instead of Postgres on 5432. Their pools still exist, but the pool connections terminate at PgBouncer, not at the database. The database sees 50 connections, not 600.

The configuration on the service side changes. The pool size becomes less important because PgBouncer is multiplexing. But you still need a pool, because the network round-trip to PgBouncer is not free.

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@pgbouncer.internal:6432/app",
    pool_size=10,
    max_overflow=0,  # No overflow. PgBouncer handles the rest.
    pool_timeout=30,
    pool_recycle=1800,
    connect_args={"prepared_statement_cache_size": 0},  # Required for PgBouncer transaction mode
)

The max_overflow=0 is deliberate. With PgBouncer in transaction mode, there is no benefit to overflow connections. PgBouncer is already multiplexing. The pool of 10 is enough to handle the concurrency of the service, and PgBouncer handles the rest.

The prepared_statement_cache_size = 0 is the one that catches everyone. PgBouncer in transaction mode does not support prepared statements across transactions, because the connection assigned to the next transaction might be a different backend. SQLAlchemy and asyncpg both cache prepared statements by default. If you do not disable the cache, you get errors like “prepared statement does not exist” or, worse, a prepared statement from one session executing in the context of another session. Set it to zero.

What PgBouncer does not fix

PgBouncer solves connection exhaustion. It does not solve connection misuse. I have watched teams install PgBouncer and then treat it as a license to open as many connections as they want. The pool sizes stay at 20. The max_overflow stays at 10. The services still hold connections they do not need. PgBouncer absorbs the waste, but the waste is still there.

PgBouncer in transaction mode also breaks certain Postgres features. Session-level settings (SET) do not persist across transactions. Advisory locks do not work. LISTEN/NOTIFY does not work. Temporary tables do not persist. If your application uses any of these, and many do, you need to know before switching to transaction mode. The errors are not always obvious. A SET statement might work in testing and fail in production because the connection assigned to the next transaction did not carry the setting forward.

The other thing PgBouncer does not fix is the connection lifecycle during deploys. When a pod starts, it opens its pool. When a pod terminates, the pool connections are closed. But in a rolling update, the old pods do not close their connections until the new pods are ready and traffic has switched. During this window, both old and new pods have open pools. PgBouncer helps here because the database connections are capped, but PgBouncer itself can hit its client connection limit if the deploy overlaps too many pods.

The fix is to configure your deployment to be less aggressive with rolling updates, and to drain connection pools on shutdown.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: api
spec:
  replicas: 4
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 0
  template:
    spec:
      terminationGracePeriodSeconds: 60
      containers:
        - name: api
          lifecycle:
            preStop:
              exec:
                command:
                  - /bin/sh
                  - -c
                  - "sleep 15 && python -c 'from app.db import engine; engine.dispose()'"

The preStop hook disposes the connection pool before the pod terminates. The sleep 15 gives the load balancer time to stop sending traffic. The terminationGracePeriodSeconds: 60 gives the pool disposal time to complete. Without this, pods terminate abruptly, connections are reset, and PgBouncer sees a flood of disconnections that can momentarily reduce the available pool.

What I configure instead

PgBouncer is the right answer for most startups, but it is not the only answer. The better approach is to reduce the number of connections at the source. Here is what I configure, in order.

Size pools to actual concurrency, not to a round number. A service doing 50 requests per second with 200ms average query time needs approximately 10 concurrent connections. That is Little’s Law. The throughput divided by the latency gives the concurrency. 50 requests per second times 0.2 seconds per request equals 10 concurrent connections. A pool of 20 is double what is needed. A pool of 10 is correct.

# Little's Law: concurrency = throughput * latency
# 50 req/s * 0.2s = 10 concurrent connections needed
pool_size = throughput * avg_query_latency
# Set pool_size to this value, max_overflow to 0

Most teams set pool_size to 20 because it is a round number. The actual concurrency needed is usually 5-15. The extra connections are waste. Measure your throughput and latency, calculate the concurrency, and set the pool to that number. If you do not know your throughput and latency, you should not be sizing a pool.

Scale down, not up. If the database is at 180 of 200 connections and you need headroom, reduce pool sizes. Cut pool_size from 20 to 10 on services that do not need 20. The services that need 20 are the ones where concurrency is above 10, and you can identify them by measuring. Most services do not need 20. They have it because someone copied the config from a tutorial.

Use a single pool per pod, not per DataSource. If a service reads from a primary and a replica, use one pool with read/write splitting at the driver level, not two pools. Two pools doubles the connection count for no benefit. The read replica is there for load distribution, not for connection multiplication.

Install PgBouncer in transaction mode. Even with right-sized pools, PgBouncer provides a backstop. If a deploy causes a spike in connections, or if a service gets an unexpected traffic burst, PgBouncer caps the database connections at a safe level. It is defense in depth. The pools are sized correctly, and PgBouncer is there in case the sizing is wrong.

The Lisbon fix

The Lisbon team had 300 connections of pools pointing at a 200-connection database. The fix took a day. We installed PgBouncer in transaction mode with a default_pool_size of 25 and max_db_connections of 50. We right-sized the service pools based on measured throughput and latency. Service A was doing 40 req/s with 150ms average query time. Concurrency needed: 6. Pool was 20. We cut it to 8. Service B was doing 80 req/s with 100ms query time. Concurrency needed: 8. Pool was 15. We cut it to 10. Service C was doing 20 req/s with 500ms query time. Concurrency needed: 10. Pool was 25. We cut it to 12.

The total across all services went from 300 connections to 120. PgBouncer capped the database at 50. The database went from 200 connections (oversubscribed) to 50 (underutilized). Memory overhead from connection processes dropped from 1.5GB to 400MB. Query performance improved because the buffer cache had more memory to work with. The connection errors stopped.

The team asked why nobody had done this before. The honest answer is that connection pooling is invisible until it breaks. The pool size is a config file value that looks reasonable. The max_connections setting is a database parameter that looks generous. The math between them is never done because it crosses a boundary that nobody owns. The service team owns the pool. The database team owns max_connections. Nobody owns the relationship between them.

PgBouncer is the bridge. It makes the relationship explicit. The database has a hard cap. The services can open as many connections as they want. PgBouncer manages the gap. But PgBouncer is not an excuse for oversized pools. Right-size the pools, install PgBouncer as a backstop, and do the math. Five minutes of arithmetic saves you from the 3am page that starts with “FATAL: remaining connection slots are reserved.”