Moving to async: the connection pool sets the ceiling, not the execution model

Eight windows and a queue

Picture a bank branch. There are eight windows and as many customers as turn up.

You could hire a hundred greeters to politely walk customers to the windows. The queue will not move any faster for it: eight windows still do the serving. A hundred greeters buy exactly one thing — a more orderly queue.

This is what happens when an application is rewritten as asynchronous code in the hope of speeding up its database work. Coroutines are the greeters. The windows are connections to the database, and there are as many of them as were set up in advance.

The row of buttons labelled "connections in the pool" is the windows. The row labelled "coroutines in flight" is the customers. Change the second without touching the first: not a single extra customer gets served at the same time.

Below that is how long the same batch of calls takes for three different ways of reaching the database. The names are not the point: what matters is that all three bars are nearly the same length, and they only shorten when you add windows.

Why there are so few windows

A database connection is not "a line in a config file". On the server side, each connection gets its own worker process: its own memory, its own place in the queue for the processor.

So windows cannot be handed out to everyone who wants one. The server has an overall limit — around a hundred by default, shared by everyone talking to it. And well before that limit something else begins: the more concurrent processes, the more the server spends on switching between them instead of working.

The documentation of the library Python uses to reach PostgreSQL answers the question "how many connections should I keep" with unusual candour: "Big question. Who knows. However, probably not as large as you imagine."

The surprise: asynchronous code turned out more expensive

If there are few database calls and they run one after another, there is nothing to speed up — nothing to wait for in parallel. What remains is the price of asynchrony itself: every time the code waits for something, it hands control back to a dispatcher, and the dispatcher picks what to do next. That work is not free.

The measurement: the same short database call, three ways of making it.

The ordinary synchronous way turned out cheapest. The difference is tens of microseconds — almost nothing for a single request. But it goes the other way from what people expect of a rewrite.

Where asynchrony wins

None of this means it is useless. It means its benefit lies elsewhere.

Picture not a bank but a helpline where thousands of people are holding the line, waiting for news. There is almost no work — the line simply has to be held. Here the difference is enormous: holding four thousand waits with threads — separate workers the operating system creates, each needing memory of its own — costs two seconds from the first to the last and sixty megabytes. With coroutines the same work costs a third of a second, of which 200 ms is the waiting itself, and practically no extra memory.

So the question to ask before rewriting is: how many simultaneous waits do you have? If the application holds a couple of dozen database connections, the answer is twenty, and there is nothing to win. If it is a gateway with ten thousand connected clients, the answer is ten thousand, and the win is enormous.

About "a heavy operation freezes everything"

There is a well-known warning: if an asynchronous program starts computing something for a long time, it stops responding entirely — the dispatcher is busy and nobody can interrupt it. That is true, and the Python documentation says so in plain words.

The conclusion usually drawn from it is that ordinary threads have no such problem, because the operating system will preempt them.

We checked. The conclusion is wrong: ordinary threads suffer no less, and in the measurement slightly more. The reason lies in how Python itself is built — it does not let two threads compute at the same time either, and there is a separate article about that. The cure is not choosing between threads and coroutines but moving the heavy computation into a separate process.

What follows from this

Before rewriting, it is worth finding out what the program is actually hitting. There are three cases, and asynchrony helps with exactly one of them:

• it hits the number of database connections — connections, caching and fewer queries will help, a rewrite will not;
• it hits computation — separate processes will help;
• it hits the number of simultaneous waits — this is what asynchrony is for.

Why this matters

"Let's rewrite it in async, it will be faster" gets said in projects about as often as "let's add an index". The difference is that an index comes with a query plan, while async comes with a feeling.

The feeling is simple: synchronous code waits, asynchronous code does not, therefore the second is faster. The first half is true; the second is not, and it is settled by measurement rather than argument. Below are five measurements against a real PostgreSQL, and three of them show that the rewrite buys nothing, while one shows it makes things worse.

The practical conclusion is not "do not move to async". It is that you have to find out what you are hitting first, and only then choose an execution model. There are exactly three ceilings, and async lifts one of them.

The model to keep in your head: three ceilings

The path of a request from arrival to the database hits three different limits, and they are independent. The mistake is almost always fixing the wrong one.

They differ by symptom:

• Connections. Throughput does not grow however many threads or coroutines you add; only the time spent queueing does.
• CPU. The cores are busy while the database answers quickly and idles.
• Waits. The cores are free, the database idles, and memory is consumed by thousands of connections each of which does almost nothing.

Async does not move the first two ceilings at all. It moves the third, and by a lot.

First: the pool sets the ceiling

Take one workload: 600 queries, each waiting exactly 5 ms on the server. Waiting, not computing — precisely the case async exists for. Only the execution model and the pool size change.

The three models coincide on pools from one to sixteen — not "look similar" but coincide: with a pool of eight that is 0.43 / 0.45 / 0.43 seconds against a theoretical minimum of 0.38. At a pool of 32 the agreement ends, and the next section says why. The number of coroutines does not affect the time at all: the measurement uses 600 of them against pools of 1, 2, 4, 8, 16 and 32 connections, and the result follows the pool.

The reason is not Python but what a PostgreSQL connection is. It is not a descriptor and not a row in a table — it is a separate process on the server. The documentation says so outright: “To achieve this it starts (“forks”) a new process for each connection”, and then “the client and the new server process communicate without intervention by the original postgres process”.

So a pool has no way to hand out a ninth connection out of eight. When none is free, what happens is exactly what the psycopg documentation describes: "if no connection is available, the client is put in a queue, and will be served a connection once one becomes available". A queue is a queue, whether the things standing in it are threads or coroutines.

How many connections to keep

The obvious temptation: if the ceiling is the pool, raise the pool. It runs into two limits at once.

The first is the server. max_connections defaults to "typically 100 connections, but might be less if your kernel settings will not support it", and that is for the whole server, not for one application. Ten instances with a pool of thirty each come to three hundred — three times over the limit. A hundred holds three such instances, not ten.

The second is that every connection costs a process. More processes means more context switching and more memory on the server, and past a certain count throughput starts falling rather than rising. The psycopg documentation answers the sizing question with the only honest sentence available, worth quoting in full: "Big question. Who knows. However, probably not as large as you imagine."

Our measurement shows it on the last row: with a pool of 32 the time stops dividing by the pool size — 0.16 s instead of the theoretical 0.09. The two cores of the machine the measurement ran on no longer keep up with that many concurrent connections, and the ninth, sixteenth and thirty-second connection each bring less than the one before.

Second: async pays more per operation

Now remove concurrency entirely. Queries run one at a time on one connection. There is nothing to speed up here — nothing to wait for in parallel.

Synchronous psycopg is cheaper than either async model: 59 microseconds against 70 and 68 on a primary-key lookup, and 51 against 67 and 64 on select 1. That is 16 to 33 per cent on top — for returning to the event loop on every wait and for the scheduler's own work.

This is the second half of the claim this article started from: moving to async can slow you down. Not "in theory", but on the most ordinary code — a handler that runs two or three short primary-key lookups and returns a response. If concurrency is modest and fits in the pool, the rewrite takes those per cent and returns nothing.

Note the order of magnitude: we are talking about tens of microseconds. A missing index costs milliseconds — hundreds of times more. The overhead of the execution model matters exactly when everything else is already in order; in a project where the query does a sequential scan, nobody will notice these per cent.

Third: a synchronous driver inside async

The most common way to "move to async without rewriting database access" is to keep the synchronous driver and wrap every call in loop.run_in_executor. From the outside you get an await; on the inside it is the same thread.

async def get_user(pool, user_id: int):
    loop = asyncio.get_running_loop()
    # A coroutine on the outside, a thread from the executor pool inside.
    return await loop.run_in_executor(executor, fetch_user_blocking, pool, user_id)

People say both "never do this, it is the worst of both worlds" and "you must do this or the loop will stall". The measurement, on the same workload — 600 queries, a pool of eight connections:

These four rows come from a single run, which is what makes them comparable with each other; they do not match the previous section to the last digit, because that was a different run, and the gap in the hundredths is the price of noise.

The wrapper costs one and a half per cent: 0.434 against 0.428. All four models land between 0.98× and 1.06× of plain threads — so the price of any of them disappears against the same ceiling: as many queries run as there are connections in the pool, however you launch them.

Hence a practical conclusion that is rarely stated: if you already have synchronous code and you need an await at the boundary — because the framework is asynchronous, say — run_in_executor is neither a disgrace nor an optimisation. It is a way to move the boundary without changing the price.

Fourth: where async genuinely wins

Three measurements in a row showed that the execution model does not decide anything. It is easy to conclude "async is pointless" — and that would be wrong. The difference is real; it is just not in the speed of one wait but in the price of holding many waits at once.

Four thousand concurrent waits cost threads two seconds from creating the first to finishing the last — while each wait itself is 200 ms. The same work costs coroutines a third of a second, which is almost exactly the waiting time and nothing on top, plus zero additional megabytes by RSS against sixty for threads.

The figure has a third toggle: memory as tracemalloc counts it. That is where the trap sits — for those same four thousand threads the counter reports 10.7 MB instead of the 60.7 that RSS reported, and 3.9 for coroutines. The gap drops from “sixty megabytes against nothing” to “two and a half times”, which is not a figure anyone goes and investigates. The reason is that a thread's stack is allocated by the operating system, and Python's allocator never sees it.

The question to ask before rewriting is: how many concurrent waits do you have? If the application holds a pool of twenty database connections, the answer is twenty, and the figure above does not apply to you. If it is a gateway with ten thousand open websockets, the answer is ten thousand, and it applies to nobody else.

Fifth: computation next to the queries

An application is not made of waiting alone. Next to a query there is always CPU work: serialising the response, a template, parsing the body, hashing a password.

There is a familiar argument about this case, and it sounds convincing. The documentation supports it, plainly and without hedging: "Blocking (CPU-bound) code should not be called directly. For example, if a function performs a CPU-intensive calculation for 1 second, all concurrent asyncio Tasks and IO operations would be delayed by 1 second." The conclusion people then draw is that threads survive such a situation, because the operating system preempts them.

So we measured it.

The first half of the argument holds: the event loop does stall, and the scenario with one 191 ms block shows it — the async tail is tight, everyone waiting is pushed out by exactly the length of the block, up to 184 ms.

The second half did not hold. In the same scenario, threads have the worse maximum latency — 212 ms against 184. And in the "often and a little" scenario threads are worse on all four numbers at once: a median of 54.6 ms against 42.9, p99 of 102.3 against 75.6.

The reason is the subject of a separate article on the GIL: pure-Python computation under the GIL is serialised in either model. A thread does not compute in parallel with another thread — it waits its turn on the very same processor, and pays for context switches that coroutines never incur. The sys.setswitchinterval documentation warns about this directly: "The interpreter doesn't have its own scheduler."

The zero-point trap: which moment latency is counted from

The first version launched every task at once and measured time from the moment the task started. The numbers came out pretty and in favour of threads: a median of 5.5 ms against 94.4 for async, a maximum of 6.8 against 178.3.

They were wrong. In the async version all 240 coroutines were created at once and each started its clock at time zero — so its latency included time spent queueing for a connection. In the thread version a task only began to exist once a worker freed up, and the queue never entered the measurement. Two different quantities were being compared.

The fix is a scheduled stream of requests: one every 2 ms, with latency counted from the scheduled time rather than from the moment the request was picked up. After that both models get the same stream and the same right to fall behind — and the numbers flip.

The rule carries over to any measurement of your own: when comparing two execution models, the zero point is the easiest place to lie to yourself. Before trusting a number, check which moment the clock starts from in each of the two models — especially when the result confirmed exactly what you expected to see.

What to do about it

An order of operations in which each step only makes sense after the one before:

1. Find out what you are hitting. The three ceilings in the first figure differ by symptom. Until it is clear which one is yours, choosing an execution model is guesswork.
2. If it is connections — count the pool and the queries instead of rewriting the client. Fewer queries per operation, caching, batching, an external pool. Raise the pool carefully and with an eye on max_connections.
3. If it is CPU — processes. Neither threads nor coroutines add cores, and under the GIL computation is serialised anyway.
4. If it is the number of concurrent waits — this is what async is for, and the gain will be measured in multiples rather than per cent.
5. If you do rewrite — rewrite all the way down to the driver. Async code with a synchronous driver inside run_in_executor performs exactly like threads; that is acceptable, but there is nowhere for a gain to come from.
6. Move computation out of the handler — into processes. This holds for async and for threads alike, and the second half is the one usually forgotten.

One last thing. Every number here was taken against a local database where a query takes a fraction of a millisecond. The further away the database and the longer the query, the larger the share of waiting and the less visible the model's overhead — but the pool ceiling does not move with distance. It moves only with the number of connections.

What measured this

The numbers in this article come from these scripts. Each one opens from here, together with the record of the run: what it was measured on, what came out, and with what spread.

• bench/async-vs-sync/blocking.py
• bench/async-vs-sync/env.py
• bench/async-vs-sync/executor.py
• bench/async-vs-sync/latency.py
• bench/async-vs-sync/pool.py
• bench/async-vs-sync/waiting.py