Postgres too many clients: budget the whole fleet

Postgres “too many clients already” means the endpoint has no connection slot available to that caller. Budget the maximum connections from every application process, including overlapping deployments, against the slots ordinary roles can use. Keep operational reserves separate. A pool limit that looks modest in one process can exceed the database ceiling across the fleet. ^[1]

Scenario: a deployment works until replacement API replicas start beside the old ones. Requests fail while the database still has spare CPU. The team has no evidence of a connection leak; its pool settings were reviewed only against the normal replica count. We will follow that fictional deployment through a rejected budget, a smaller candidate pool and a failover case that breaks the candidate again.

Why can a quiet database reject connections?#

A connection slot is an admission ticket, not a CPU reading. PostgreSQL checks a configured connection ceiling; spare CPU does not create another connection slot. In the PostgreSQL 18 documentation used here, max_connections is set at server start, and increasing it increases allocations for some resources, including shared memory. Treat a ceiling increase as a capacity change with a restart and resource review, rather than a free emergency switch. ^[1]

The ceiling is also not entirely available to an ordinary application role. PostgreSQL distinguishes the final superuser reserve from slots reserved for roles with pg_use_reserved_connections. A monitoring role that can read statistics does not automatically qualify for either admission reserve. Confirm the actual role and the provider’s privileges before an incident; a dashboard account that cannot connect is a poor emergency plan. ^[1][6]

Use an ordinary-client budget with explicit deductions: configured maximum, protected slots, other ordinary-client commitments, then deliberate unused headroom. The commitments might include administration tools, migration jobs and another service that connects directly. Do not subtract an arbitrary count of background processes from pg_stat_activity: that view includes server processes beyond ordinary client backends. ^[1][2]

Does the rollout fit the same budget?#

Scenario: the database allows 120 connections. Keep 3 superuser slots and 2 additional reserved slots protected, allocate 15 slots to other ordinary clients, and leave 10 slots unused as operational headroom. The application budget is 90. The reserve and headroom values are assumptions for this worked case, not PostgreSQL defaults or universal recommendations.

Scenario: the API normally runs 4 replicas with one pool-owning process in each replica. Each API pool can open 12 connections. Three worker replicas each have a pool of 8, and a migration process may hold 2. The potential ordinary application demand is 74. During a deployment, assume 6 API replicas can coexist while old processes drain, pushing the potential demand to 98.

Scenario: reducing the API pool ceiling to 10 brings overlap demand to 86. That arithmetic establishes that the stated commitments fit; it says nothing yet about request latency. It also assumes the previous API processes have the new limit. A mixed rollout containing old larger pools and new smaller pools needs its own sum, rather than applying the new setting retroactively to every live process.

Count the object that actually owns a pool. A replica may contain several application worker processes, and each may create its own pool. A separate read connection string can create another pool in the same process. A job runner may keep its pool alive between jobs. Inventory those objects explicitly instead of assuming the deployment replica count equals the pool count.

Scenario: if each of the 6 overlapping API replicas starts 2 pool-owning processes, the repaired configuration can commit 146 connections after the workers and migration process are included. The fixture below rejects it. An application-server process-count change is therefore a database capacity change even when Kubernetes replicas and the per-pool setting stay unchanged.

Choose the overlap bound from deployment behavior, not just the desired steady count. Ask how many old processes may still hold sockets when replacements are ready, whether another rollout or autoscaler can run concurrently, and when shutdown actually closes pools. Record the bound as an input that the release owner must revisit. A theoretical ceiling cannot protect you if the fleet can exceed its own declared shape.

What does the database actually see?#

Use an authorized read-only connection for these PostgreSQL 18 inspection queries. They change no settings or sessions. Managed providers may restrict visibility; request only the privileges needed for this investigation. ^[1][2][6]

SHOW server_version;
SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW reserved_connections;

SELECT datname, usename, application_name, state,
       count(*) AS client_backends
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, application_name, state
ORDER BY client_backends DESC, datname, usename;

The filter deliberately counts client backends, across databases on this server. An unfiltered count is a server-process count, not a precise inventory of application pool usage. An idle client backend still represents a connected client; “idle” does not mean its connection slot is free. Conversely, an active backend may be waiting on an event rather than doing useful CPU work. Read state and wait information together when investigating service time. ^[2]

Set a useful application_name in each workload’s connection configuration and verify it appears in the inventory. Keep the naming stable enough to aggregate, with deployment identity available in adjacent telemetry when needed. This is our operating recommendation: the name is only a label, so do not treat it as authenticated ownership or assume it proves which replica created every session.

The query is a snapshot of what exists, whereas the budget describes what may exist. A quiet snapshot below the budget does not disprove a startup burst; a count above an owner’s declared ceiling suggests a missing pool, stale deployment or incorrect assumption worth investigating. Include the inspection session itself in ordinary-client commitments where applicable. Do not repeatedly open fresh diagnostic connections into an already exhausted endpoint.

If the inventory is dominated by sessions stuck inside transactions, fix that mechanism rather than disguising it with another allowance. The separate idle-in-transaction investigation follows transaction ownership and blocking evidence. If requests are retrying after lock conflicts, inspect the deadlock and transaction retry boundary before increasing concurrency again.

Turn the rollout assumptions into assertions#

Store the assumptions next to deployment configuration and evaluate every endpoint independently. The small checker below multiplies replicas, pool-owning processes and per-process maxima. It intentionally has no database driver: its job is to reject an inconsistent proposed fleet, not estimate throughput or discover an application library’s hidden pools.

from dataclasses import dataclass, replace
from collections import defaultdict

@dataclass(frozen=True)
class Pool:
    name: str
    endpoint: str
    replicas: int
    processes: int
    pool_max: int

def evaluate(pools, budgets):
    totals = defaultdict(int)
    for pool in pools:
        if pool.endpoint not in budgets:
            raise ValueError("unbudgeted endpoint: " + pool.endpoint)
        counts = (pool.replicas, pool.processes, pool.pool_max)
        if any(type(n) is not int or n < 0 for n in counts):
            raise ValueError("pool counts must be nonnegative integers")
        totals[pool.endpoint] += (
            pool.replicas * pool.processes * pool.pool_max
        )
    return {endpoint: (total, total <= budgets[endpoint])
            for endpoint, total in sorted(totals.items())}

budget = 120 - 3 - 2 - 15 - 10  # Scenario assumptions
steady = [Pool("api", "writer", 4, 1, 12),
          Pool("workers", "writer", 3, 1, 8),
          Pool("migration", "writer", 1, 1, 2)]
overlap = [replace(steady[0], replicas=6), *steady[1:]]
repaired = [replace(overlap[0], pool_max=10), *overlap[1:]]
assert evaluate(steady, {"writer": budget})["writer"] == (74, True)
assert evaluate(overlap, {"writer": budget})["writer"] == (98, False)
assert evaluate(repaired, {"writer": budget})["writer"] == (86, True)
reports = Pool("reports", "writer", 2, 1, 10)
assert evaluate([*repaired, reports], {"writer": budget})[
    "writer"] == (106, False)

Scenario: a reporting workload normally uses a separate reader endpoint. If failover policy redirects that workload to the writer, its 20 connection commitment joins the repaired writer’s 86, producing 106. The fixture rejects that policy. Keeping the reporting endpoint unavailable, shedding those jobs or allocating a smaller tested report pool are separate decisions; “fail over everything” is not a complete capacity plan.

The arithmetic fixture accompanying this article was run locally. Its assertions cover steady state, unsafe overlap, the smaller candidate pool, isolated reader traffic, reader traffic redirected to the writer and duplicate API processes. Invalid counts and an unbudgeted endpoint also fail. No production database, throughput benchmark or live failover was exercised. The SQL and pooler inspection commands remain examples to adapt in an authorized environment.

Make the checker consume the same values as the deployment before treating it as a guard. Give every new endpoint an owner and an explicit budget. A hand-maintained file that disagrees with live replica, process or pool settings can pass while the deployment remains unsafe.

Does PgBouncer change the arithmetic?#

PgBouncer creates a second accounting boundary: application clients connect to the pooler, while pooler server connections consume the database’s capacity. Do not add application client ceilings to pooler backend ceilings as if they were separate database consumers. Count direct bypass connections separately, then budget the server connections from every pooler instance that can reach the same database endpoint. ^[3][4]

The important scope is per user/database pair. PgBouncer’s default_pool_size is not a global database limit, and reserve_pool_size can permit additional connections. Database or user overrides can change those limits. max_db_connections constrains a PgBouncer database entry; aliases that point to the same PostgreSQL database need explicit accounting rather than assuming their limits combine automatically. ^[3]

Scenario: consider an alternative design with 2 PgBouncer instances, each serving 2 user/database pairs. A pool ceiling of 20 plus an additional reserve pool of 5 allows a simple upper bound of 100 server connections across the poolers, before a 2 connection direct migration allowance. That 102 does not fit the same 90 slot application budget. This is an alternative topology, not another allowance to add to the direct-pool case above.

Scenario: if both pooler instances enforce a 40 server-connection cap for the same single configured database entry, their combined cap plus the 2 direct migration connections is 82. That fits this budget, provided no alias, other pooler replica or bypass consumer was omitted. The cap may make clients wait. It does not create extra database capacity or establish an acceptable waiting time.

Inspect every pooler instance rather than querying whichever replica a load balancer happens to choose. SHOW POOLS exposes waiting clients, active server connections and idle server connections. cl_waiting counts clients that have sent queries but have not obtained a server connection; maxwait describes the oldest waiting client. SHOW DATABASES helps compare the configured database entry with its current server connection count. These are PgBouncer administrative commands, not ordinary PostgreSQL SQL. ^[4]

-- Run on each authorized PgBouncer administrative connection.
SHOW POOLS;
SHOW DATABASES;

Transaction pooling can serve more client connections with fewer server connections by returning a server connection when a transaction finishes. That changes the application contract. Session-level advisory locks, LISTEN and other session-dependent features need review; named prepared-statement behavior also depends on the pooler configuration. Do not switch modes solely to make a budget fit without checking the documented feature matrix and the actual driver. ^[3][5]

Keep migration behavior explicit too. A direct migration connection is another ordinary-client commitment; it should not be hidden inside emergency headroom. A resumed concurrent index build needs its own completion checks and connection semantics. Reserve an intentional path for that work rather than discovering during deployment that the general traffic pool cannot support it.

When is a smaller pool the wrong repair?#

A smaller pool is the wrong complete repair when the workload cannot finish its accepted work within the required deadlines at that concurrency. The budget check proves a ceiling, not a latency result. Measure application checkout waiting, request outcomes and transaction duration under a representative workload; for a pooler, include its queue indicators. Set acceptance bounds from the service’s needs before the trial so a slow success is not mistaken for a repair.

Use application-supported acquisition deadlines and bounded admission to keep waiting consistent with the request budget. Those controls are library-specific; there is no universal pool setting in this article. If retries immediately re-enter the same saturated queue, they can increase offered work without increasing service capacity. Decide which work may wait, which may be shed and which requires a different capacity plan.

Consider more server capacity after measuring resource use and confirming that the workload benefits from more concurrent backends. Removing unnecessary pools or processes may be simpler. Transaction pooling changes the session contract, while shorter transactions address service time. Record the evidence that makes each candidate appropriate. ^[1][5]

A deployment decision worth keeping#

Scenario: approve the smaller API pool only as a candidate with a recorded overlap bound, known direct consumers and a passing writer ceiling. Hold the reporting-to-writer fallback because its combined commitment fails. Keep the old configuration available for an operational rollback, but recognize that restoring a larger pool would restore the original unsafe overlap unless deployment shape changes too.

• Inventory: each endpoint, role, pool-owning process, replica bound and direct bypass has an owner. Protected access is tested through the provider-approved operational path.
• Release evidence: compare the live connection inventory with declared owners, then rehearse the greatest permitted overlap in a controlled environment. Include old processes that are still draining.
• Acceptance: preserve the protected reserve and planned headroom while meeting predefined checkout-wait and request-outcome limits. Observe both the application and each pooler replica.
• Stop condition: reject an unknown pool owner, an endpoint without a budget, an overlap ceiling above its allocation or queueing beyond the agreed service limit. Recalculate after topology or failover-policy changes.

Save the accepted pool ceilings beside the release settings so the next process-count change has to amend them. The next on-call engineer should be able to count the possible clients without reconstructing last month’s deployment. It remains separate from “how much work can this database finish?”, which requires measurements the arithmetic cannot supply.

Does an idle connection still count against the budget?

Yes. An idle client backend is still a connected client; it is waiting for another client command. Count it in the connection inventory. Distinguish that state from idle in transaction, which can introduce additional transaction and locking consequences requiring separate investigation. ^[2]

Should every application use the same pool maximum?

No. Allocate capacity by each workload’s process count, overlap behavior and service requirements. A shared setting is convenient but can give a rarely used background worker the same ceiling as a latency-sensitive API. Review the resulting total and test waiting before approving the allocation.

Does max_client_conn protect the PostgreSQL connection ceiling?

Not by itself. It limits clients accepted by PgBouncer, while the database sees the pooler’s server connections plus direct clients. Review server pool limits, reserve pools, database or user overrides and every pooler replica reaching the endpoint. Maintain both boundaries explicitly. ^[3][4]

Can a monitoring role use the reserved connection slots?

Only if its actual privileges qualify for the relevant reserve and the provider permits that access. Reading monitoring statistics and using reserved_connections are separate capabilities. Confirm the operational role in advance rather than granting ordinary application traffic access to the emergency reserve. ^[1][6]

What should change after adding a reader endpoint?

Give the reader its own budget, then document where its clients go during failure. If they reconnect to the writer, recompute the combined writer commitment. A passing steady-state calculation for separate endpoints does not prove that the consolidated topology fits or meets the same response-time requirements.