Static pool_size vs. aggressive queue_timeout for transient spikes

Question

Managing database connection pools in Elixir via Ecto and DBConnection requires balancing application responsiveness against database server resource limits. When a system experiences sudden, short-lived concurrency spikes, the pool can become exhausted, leaving processes to wait in the queue.

One approach is to increase the pool_size to accommodate peak loads, which reduces queuing but consumes more memory and process slots on the database server. Alternatively, maintaining a conservative pool size while implementing a strict queue_target and short timeout forces a fail-fast mechanism to prevent request piling and linear latency growth.

Given a constraint where database server RAM is limited, but application availability must be maintained during transient bursts:

Does increasing the pool size provide better stability than a fail-fast queue strategy when the database is the primary bottleneck?
  At what threshold does the risk of database-side resource exhaustion outweigh the benefit of reduced application-side queuing?

Muntasir · Answer

Answer
When the database is the primary bottleneck, a conservative pool_size combined with a low queue_target and short queue_timeout (fail‑fast) gives better overall stability than simply raising the pool size. A larger pool only moves the queue from the application to the database and can exhaust DB‑side memory or max_connections.

Why the fail‑fast queue wins when the DB is the bottleneck

Each extra connection consumes RAM and a file‑descriptor slot on the DB server.
  If the DB cannot sustain the higher concurrent connection count, it will hit max_connections or OOM, causing connection‑refusal spikes that cascade to the app.
  A small queue_target (e.g., 10 ms) and queue_timeout (e.g., 200 ms) forces excess requests to fail immediately, keeping DB load predictable and preventing linear latency growth.

Threshold where larger pool becomes risky
The tipping point is reached when:
pool_size × avg_per_connection_memory  ≳  DB_available_RAM × safety_factor
or
pool_size  ≳  max_connections − admin_reserve
Beyond this point, adding connections raises the probability of DB‑side resource exhaustion more than it reduces application‑side queuing.

Practical tuning steps

Set pool_size to handle baseline load (≈ 1.5× average concurrent requests).
  Configure queue_target ≈ 10 ms and queue_timeout ≈ 200 ms to match your latency SLA.
  Run a staged load test that produces a 3× spike for ~30 s; record DB connection count, memory usage, wait events, and app‑side error rates.
  If queue‑timeout errors exceed the SLA, increase pool_size by +2 and re‑measure. Stop when DB utilization reaches ~80 % of max_connections or memory, or when error rates fall within target.

One diagnostic you need
What is the current max_connections setting and the average per‑connection memory footprint on your database server? This determines the exact ceiling for safe pool_size increases.

Verification command (run on the DB host)
# PostgreSQL example
psql -c "SHOW max_connections;"
psql -c "SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;"
# For per‑connection memory estimate, consult your DB docs or monitor via:
# SELECT * FROM pg_stat_activity;  -- observe active connections

Static pool_size vs. aggressive queue_timeout for transient spikes

1 answer

Answer

Why the fail‑fast queue wins when the DB is the bottleneck

Threshold where larger pool becomes risky

Practical tuning steps

One diagnostic you need

Verification command (run on the DB host)

Your perspective belongs here.

0 question comments