skip to content

How would you set SQLAlchemy's pool_size and max_overflow for an API of four worker processes plus a nightly batch job against a PostgreSQL server allowing 100 connections?

level: principalimportance: should knowfreq 30%

answer

  1. one pool per Engine per process
  2. multiply, then add
  3. deploys double the processes
  4. overflow is headroom, timeout is backpressure

basics

~20 s

Each process has its own pool, so the database sees processes x (pool_size + max_overflow) per engine, plus the batch job, deploy overlap and admin connections. Size pool_size to steady concurrency, use max_overflow as headroom, keep the sum under 100.

solid answer

~40 s

I start from the arithmetic: a SQLAlchemy pool lives in one Engine in one process, so the defaults give four workers 4 x (5 + 10) = 60 and the batch job another 15. Then I add what people forget: a rolling deploy that briefly runs eight workers, any second Engine in a process, migrations, monitoring and the server's reserved slots. Within that, `pool_size` matches the threads that hold a connection at once, `max_overflow` is burst headroom (or 0 for an exact ceiling), and a shorter `pool_timeout` turns exhaustion into fast `TimeoutError`s rather than slow requests. The batch job gets its own small engine or `NullPool`. I write the budget down, export `checkedout()`, and revisit it whenever worker counts change.

go deeper

for a junior

Recall that every process creates its own Engine and its own pool, so the connection count multiplies with the number of worker processes.

for a middle

Do the arithmetic: processes times pool_size plus max_overflow, for every Engine, and compare it with the server's connection limit.

for a senior

Include the hidden consumers in the sum, deploy overlap, second engines, migrations and monitoring, and tune pool_timeout so exhaustion shows up quickly.

for a principal

Own the budget as a written team decision: per-process limits or central pooling, burst headroom or exact ceilings, and who recalculates it when the topology changes.

## Start from the arithmetic A SQLAlchemy pool belongs to **one Engine in one process**. Nothing is shared between processes, so the ceiling the database sees is: **sum over every process of (pool_size + max_overflow) for each Engine it creates** For this service: | consumer | engines | default ceiling | |---|---|---| | API, 4 worker processes | 1 each | 4 x (5 + 10) = **60** | | nightly batch job | 1 | 15 | | total | | **75 of 100** | That looks safe, but the table leaves out several consumers that are easy to forget: - **Deploys.** A rolling restart briefly runs old and new workers side by side. Eight processes at 15 is 120, more than the server allows. - **Second engines.** A process that creates another Engine (a read replica, an audit database, a migration check at startup) has another pool with its own ceiling. - **Everyone else.** Migrations, an operator's shell, monitoring and the database's own reserved slots all need connections from the same 100. ## The knobs and what each one decides - **`pool_size`** should match the **steady concurrency of one process**: how many threads or tasks in a worker actually hold a connection at the same time. Connections above that sit idle and still count against the server. - **`max_overflow`** is **burst headroom**. It is the part of the budget that is usually unused, and the part that multiplies badly during a deploy. `max_overflow=0` makes the ceiling exact and predictable; a positive value buys tolerance for spikes. - **`pool_timeout`** is **backpressure**. At 30 seconds a request waits a long time before failing. Lowering it makes an exhausted pool fail fast with `TimeoutError`, which frees the caller and shows up in metrics. - **The batch job** gets its **own engine** with a small pool, or `NullPool`, so it cannot take connections the API needs, and it can be scheduled away from peak load. ## Shapes a lead might choose 1. **Fixed per-process budget.** For example `pool_size=10, max_overflow=0` per worker: 40 for the API and 5 for the batch job; even a full deploy overlap (80 + 5) stays under 100 and leaves 15 for migrations, operators and reserved slots. Predictable, no bursts. 2. **Small core, measured burst.** `pool_size=5, max_overflow=5`: 40 at the API's peak, 20 in steady state. Tolerates spikes, but the deploy overlap has to be counted against the peak. 3. **Central pooling.** An external connection pooler in front of the database with `NullPool` in each process moves the budget into one place. That trades per-process limits for one more component to run, and its settings belong with the pooler, not with SQLAlchemy. None of these is right in general. The choice depends on the deploy method, how bursty traffic is, and whether the team prefers requests to wait, fail fast, or overload the database. ## Threads, sessions and the per-process number `pool_size` works as a steady-state figure only if it covers the threads or tasks in one process that hold a connection at the same moment. A `Session` holds its connection for the whole transaction, from the first statement to `commit()` or `rollback()`, not just while a query runs, and code that keeps a transaction open around a slow remote call holds the connection for that time too. Shortening transactions can therefore lower the budget more than any pool setting. If a worker runs 8 threads that all use the database, `pool_size=5, max_overflow=10` covers them; `pool_size=5, max_overflow=0` makes three of them wait. ## What not to do - **Do not raise `max_overflow` to -1** to silence timeouts; it removes the only limit the process has. - **Do not size the pool to the server's limit.** A larger pool is not faster once the database is busy; the trade-off of database-side concurrency is covered with connection pooling in general. - **Do not assume the default 15 per process** is someone's decision. Write the budget down, with the deploy overlap counted. ## Keep it observable - Export `engine.pool.checkedout()` per process and alert when it sits at the ceiling. - Count `sqlalchemy.exc.TimeoutError` as a capacity signal, not as a generic error. - Recalculate the budget whenever the worker count, replica count or the database's `max_connections` changes.

  • What does max_overflow=0 change about a SQLAlchemy pool's behaviour under a spike?
    The pool never opens more than `pool_size` connections, so the ceiling equals the steady-state count and the budget is exact. Under a spike, extra checkouts wait up to `pool_timeout` and then raise `TimeoutError` instead of opening more connections. You trade burst tolerance for predictability.
  • Why count a rolling deploy in a SQLAlchemy connection budget?
    During a rolling restart old and new worker processes run at the same time, each with its own pool. If all workers overlap, the ceiling briefly doubles: eight processes at 15 is 120 against a 100-connection server. Either size for the overlap or replace workers a few at a time.

  • Worker processes share one SQLAlchemy pool, so the defaults give 15 in total
  • Each worker's pool_size can be set to the server's full max_connections
  • A bigger pool always means a faster API
  • max_overflow=-1 is a sensible production default
  • The batch job can reuse the API's pool settings without thought