The API Scaled. The Database Didn’t: A Connection Budget Failure

Photo by Piret Ilver on Unsplash

Code: the complete Docker experiment, API, load generator, sanity test, and raw results are available in [herley-shaori/social-media-articles-code](https://github.com/herley-shaori/social-media-articles-code/tree/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code).

Horizontal scaling is usually presented as the safe response to load. Add API replicas, spread requests across them, and let each instance keep a database connection pool so it does not pay connection setup cost on every request.

Every part of that design can be reasonable in isolation and still fail as a system.

I reproduced the failure with three API instances and PostgreSQL. Each API was configured with a pool of 12 connections. Twelve is not an absurd pool size. The mistake was treating it as an instance-level decision while the database limit was shared by the entire deployment.

The third replica did not add capacity. It arrived after the first two replicas had consumed most of the database’s connection budget, opened only three of the 12 connections it expected, and returned HTTP 503 under the same burst that the first two replicas served successfully.

This is not a beginner’s “forgot to close the connection” bug. Every connection was managed and returned correctly. The professional mistake was failing to assign ownership of a global capacity constraint.

The configuration that looked safe

The Docker experiment configures PostgreSQL with 30 total connections and reserves three for superusers:

postgres:
  command:
    - postgres
    - -c
    - max_connections=30
    - -c
    - superuser_reserved_connections=3

ource repository: [social-media-articles-code — docker-compose.yml](https://github.com/herley-shaori/social-media-articles-code/blob/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code/docker-compose.yml#L28-L36)

PostgreSQL defines max_connections as the maximum number of concurrent server connections. Once active connections reach max_connections minus superuser_reserved_connections, ordinary application roles can no longer connect; the reserved slots remain available for emergency administration [1].

That makes the application’s actual ceiling 27 connections, not 30:

application ceiling = max_connections - superuser_reserved_connections
                    = 30 - 3
                    = 27

Source repository: [social-media-articles-code — experiment calculation](https://github.com/herley-shaori/social-media-articles-code/blob/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code/README.md#connection-budget)

The initial API configuration asked for 12 connections per instance. With one instance, that uses 44% of the application ceiling. With two, it uses 89% but still works. With three, the same configuration asks PostgreSQL for 36 connections.

The dangerous part is the timing. Nothing fails when the first instance starts. Nothing fails when the second starts. The configuration becomes invalid only when horizontal scaling performs exactly the action it was designed to perform.

Reproducing the scale-out failure

The experiment contains PostgreSQL, three small Python API containers, and a load generator. It is connection-bound rather than CPU-bound. Docker Compose limits the complete stack to approximately two CPU cores and one GiB of memory; Docker documents cpus and mem_limit as per-service resource constraints [3].

The API startup is deliberately staggered to make ownership of the final slots visible:

+----------+--------------+-------------+------------------+
| Instance | Desired pool | Opened pool | Startup failures |
+----------+--------------+-------------+------------------+
| API-1    |           12 |          12 |                0 |
| API-2    |           12 |          12 |                0 |
| API-3    |           12 |           3 |                9 |
+----------+--------------+-------------+------------------+

Source: [oversubscribed.json](https://github.com/herley-shaori/social-media-articles-code/blob/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code/results/oversubscribed.json)

PostgreSQL rejected API-3’s remaining connection attempts with the failure that matters:

FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

Source repository: [social-media-articles-code — oversubscribed.json](https://github.com/herley-shaori/social-media-articles-code/blob/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code/results/oversubscribed.json)

The API process remained alive. Its health endpoint could report that it had opened only three of its desired 12 connections, but the container itself was healthy enough to accept traffic. This is an uncomfortable and realistic state: the replica exists, the process responds, and the dependency capacity it assumed does not.

The load generator then sent the same 12-request burst to each API. A request holds one connection for 350 ms. API-1 and API-2 can serve their 12 requests in parallel. API-3 can serve only three at a time. Six requests wait longer than the 600 ms pool-acquisition deadline and receive:

{
  "instance": "api-3",
  "error": "api-3 exhausted its local connection pool"
}

That error becomes HTTP 503. The database is responsive, the queries are deliberately small, and the Mac is not CPU-saturated. The scarce resource is the number of connections the deployment is permitted to hold.

Three correct decisions, one missing owner

A pool of 12 can be justified from local evidence. It may perform well in a single-instance load test. It may be below a library’s default. It may even have months of production history behind it.

The error appears when two independently reasonable controls interact:

• the application team chooses pool size per process;
• the platform scales the number of processes;
• the database team owns one shared connection ceiling.

No individual setting has enough information to guarantee safety. The pool configuration does not know the future replica count. The autoscaler does not know the database connection cost of each replica. PostgreSQL enforces its limit correctly but cannot decide which application instance deserves the final slot.

The missing artifact is a connection budget: a documented allocation of a shared database limit across APIs, workers, migrations, monitoring, and operational access.

The fix: budget for the deployment, not the pod

The corrected calculation starts with the application ceiling, subtracts application headroom, and divides what remains by the maximum — not current — number of replicas:

safe pool per instance
  <= floor((application ceiling - application headroom) / maximum replicas)
  <= floor((27 - 3) / 3)
  <= 8

Source repository: [social-media-articles-code](https://github.com/herley-shaori/social-media-articles-code/blob/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code/README.md#connection-budget)

The three API pools now request 24 connections in aggregate. Three ordinary application slots remain as headroom, while PostgreSQL’s three superuser slots remain untouched for emergency access.

The same 36-request burst produces a very different result:

+----------------+-----------+--------+---------+--------+----------+
| Scenario       | Requested | Opened | Success | Failed | p95 (ms) |
+----------------+-----------+--------+---------+--------+----------+
| Oversubscribed |        36 |     27 |      30 |      6 |   710.30 |
| Budgeted       |        24 |     24 |      36 |      0 |   711.23 |
+----------------+-----------+--------+---------+--------+----------+

Source: [summary.md](https://github.com/herley-shaori/social-media-articles-code/blob/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code/results/summary.md)

The p95 values are nearly identical. That is a useful result rather than a disappointment. The fix did not make the query faster; it removed a capacity failure without materially slowing the successful path. A smaller pool still completed all 36 requests because queued work waited for a connection instead of competing for database slots that did not exist.

Why I did not just increase max_connections

Raising the database limit would make this particular test pass, but it would not fix the ownership problem. The next scale-out event, a new worker fleet, or an unbudgeted migration could cross the new limit in exactly the same way.

It is also not a free change. PostgreSQL sizes some resources directly from max_connections, including shared memory allocation [1]. A higher limit must therefore be treated as database capacity planning, not as an application error toggle.

A connection pooler such as PgBouncer may be appropriate when many client connections perform short transactions. It changes how efficiently physical database connections are shared. It does not remove the need to decide how many database connections the total workload may consume.

Retries are an even weaker answer. Retrying a connection failure while every slot remains occupied adds more waiting clients to a capacity shortage. A retry policy can handle transient recovery; it cannot manufacture connection slots.

What production systems should measure

The experiment exposes both desired and opened pool size from each replica. In production, this needs to become a capacity view rather than a debugging detail:

• aggregate configured pool capacity across the maximum replica count;
• current PostgreSQL backends by database and application name;
• pool acquisition latency and timeout count per application instance;
• rejected connection attempts;
• connection allocations for workers, migrations, monitoring, and operators.

PostgreSQL provides pg_stat_activity with one row per server process and pg_stat_database with database-wide statistics, including current backend counts [2]. These are the server-side facts that should be reconciled with pool metrics from the application.

One nuance matters: automatically failing readiness because an instance opened fewer connections than desired can produce a restart loop without freeing enough global capacity. The condition should be visible and alertable. Whether it should remove the replica from service depends on whether its reduced pool can still serve useful traffic and how the deployment behaves when readiness fails.

Lesson learned

Horizontal scaling multiplies every per-instance reservation: database connections, HTTP connections, file descriptors, caches, queues, and background threads. If the dependency is shared, its capacity must be budgeted against the maximum deployment size.

The pool size was not obviously reckless. The autoscaler was not malfunctioning. PostgreSQL was not unhealthy. The failure came from a boundary between three correctly functioning components that did not share one capacity model.

The practical rule is simple:

Never approve a per-instance pool size without multiplying it by the maximum replica count and accounting for every other consumer of the same database.

That calculation is small. Discovering its absence during a scale-out event is not.

Reproducing the experiment

Run the complete Docker experiment with:

./run.sh

The runner executes the oversubscribed and budgeted scenarios independently, writes their raw JSON results, runs sanity assertions, and removes its containers and volumes when finished.

References

[1] PostgreSQL 17 — Connections and Authentication. https://www.postgresql.org/docs/17/runtime-config-connection.html

[2] PostgreSQL 17 — The Cumulative Statistics System. https://www.postgresql.org/docs/17/monitoring-stats.html

[3] Docker Docs — Define services in Docker Compose. https://docs.docker.com/reference/compose-file/services/

[4] Experiment source and raw results. https://github.com/herley-shaori/social-media-articles-code/tree/513bec1b3d8037e89a7d23fca47f7367c8cdedc1/articles/database-connection-budget/code