Batch your lookups: 14x the requests/s from the same Postgres

I had a service that needed one user per request. It did what everybody does: one SELECT … WHERE key = $1 per request. On my laptop that stopped at about 18,000 requests per second. Sixty-four clients, a thousand clients, four thousand clients: 18,000.

Postgres was not busy. Every request paid a full round trip for one row, and on this laptop each round trip also went through Docker Desktop's port forwarding. The round trips were the limit, and they were more expensive here than they would be on a Linux host.

When I sent the requests that happened to be waiting at the same moment as one query, the same database answered 252,000 requests per second with 4,096 clients, and the p99 dropped from 271 ms to 28 ms.

This post is about keeping the call simple and letting something else do the batching. Disclosure: the library in my numbers is mq-bridge, which I wrote. The pattern itself is much older. In GraphQL, this is DataLoader, so I compare against one. I also compare against pipelining in the driver, which removes the waiting without changing the query.

The setup

• A Postgres table with 1 million users. Each request fetches one of them by primary key.
• N client tasks in the same binary. Each one sends its next request when the last one is answered. This is a closed loop: the latencies are what these clients saw, not what a fixed arrival rate would see. The HTTP section below has an open-loop run.
• The baseline is sqlx 0.9 with a pool of 64 connections and one WHERE path = $1 per request.
• Everything runs on one MacBook (M1, 8 cores, 8GB RAM), Postgres 17.6 in Docker Desktop.
• Every number is the median of three runs.
• The benchmark checks every answer against its key, so a fast wrong answer doesn't count.

The code is in
mq-bridge/benches/lookup/caller.

The code

All versions return the same struct:

#[derive(sqlx::FromRow, serde::Deserialize)]
struct User { path: String, id: i64, name: String, tier: String }

This is the baseline:

let user: User = sqlx::query_as("SELECT path, id, name, tier FROM users WHERE path = $1")
    .bind(&key)
    .fetch_one(&pool)
    .await?;

And this is the batched version, as the caller sees it:

let user: User = users.call([("path", &key)]).await?;   // Option<User> if it may be missing

One call in, one answer out. The batching SQL lives in a route, set up once at startup:

let users = Caller::from_str(r#"
batch_size: 256
concurrency: 4
input:
  middlewares:
    - lookup:
        from:
          sqlx:
            url: "postgres://localhost:55432/bench"
            table: users
            max_connections: 8
            lookup_query: "SELECT path, id, name, tier FROM users WHERE path IN (${payload:path})"
        into: $
    - parallel: { max_in_flight: 16 }
"#, "users").await?;

Calls that arrive together are read as one batch, and lookup answers the whole batch with one query. On Postgres it runs IN (${payload:path}) as path = ANY($1), so every batch size shares one prepared statement.

parallel keeps up to 16 batches in flight. The lanes share one pool of 8 connections, so at most 8 queries wait for Postgres at once. concurrency: 4 only covers handing the answers back to the callers. The lookups run in the input, so parallel is what overlaps them.

What happened

requests/s	one query per request	batched, 16 in flight	factor
1 client	3,095	3,245	1.0x
16 clients	16,002	21,113	1.3x
64 clients	17,849	49,253	2.8x
256 clients	18,075	138,141	7.6x
1,024 clients	18,038	206,690	11x
4,096 clients	18,289	252,161	14x

p50 / p99	one query per request	batched, 16 in flight
1 client	0.30 / 0.64 ms	0.30 / 0.55 ms
1,024 clients	54 / 99 ms	4.6 / 14 ms
4,096 clients	222 / 271 ms	16 / 28 ms

One query per request flattens out at 64 clients. Adding clients after that only makes the queue for the connection pool longer, which is why its latency grows with every row of the table.

With a single client, batching changes nothing. A batch of one is one query, and it costs about 0.3 ms either way.

parallel is what keeps it going. Without it, each batch waits for the previous one, and the route stops at about 60,000. Lanes only start for batches worth a query of their own, so the extra lanes cost little at 16 clients: 21,000 with them, 22,000 without.

The batches are only as full as the number of requests waiting at that moment. That is the ceiling for this kind of traffic, and it is why the single-request numbers stop around 14x.

And pipelining?

The problem is the waiting, and a driver can stop waiting without touching the SQL. tokio-postgres pipelines queries that are issued concurrently on one connection: it sends the next query before the previous answer is back. The sqlx pool doesn't do that; each connection runs one query at a time. I ran the same one-query-per-request load on 16 and on 64 tokio-postgres connections, round robin.

requests/s	one query per request	pipelined, 16 connections	pipelined, 64 connections	batched, 16 in flight
1 client	3,095	3,401	3,213	3,245
16 clients	16,002	26,085	23,482	21,113
64 clients	17,849	38,367	31,850	49,253
256 clients	18,075	47,641	37,502	138,141
1,024 clients	18,038	56,255	44,431	206,690
4,096 clients	18,289	55,109	47,882	252,161

Pipelining helps, by 2–3x, and with 16 clients it is the fastest of all. Fewer connections did better than more, because each pipeline gets deeper. Then it levels off around 55,000, with a p99 of 375 ms at 4,096 clients. Pipelining removes the waiting, not the work: Postgres still executes and answers one statement per key. Batching sends one statement for up to 256 keys.

If you can't change the query, pipelining is the cheaper fix. If you have many concurrent requests, batching goes further.

And DataLoader?

I ran async-graphql's DataLoader on the same 64-connection pool: max_batch_size 256, delay 0, one WHERE path = ANY($1) per batch. Its cache is off, as in the route. This post is only about combining requests.

requests/s	one query per request	DataLoader	batched, 16 in flight
1 client	3,095	585	3,245
16 clients	16,002	9,476	21,113
64 clients	17,849	37,348	49,253
256 clients	18,075	109,165	138,141
1,024 clients	18,038	191,580	206,690
4,096 clients	18,289	222,924	252,161

p50 / p99	one query per request	DataLoader	batched, 16 in flight
1 client	0.30 / 0.64 ms	1.9 / 2.7 ms	0.30 / 0.55 ms
1,024 clients	54 / 99 ms	4.3 / 19 ms	4.6 / 14 ms
4,096 clients	222 / 271 ms	14 / 85 ms	16 / 28 ms

DataLoader does the same thing and lands in the same place. From 1,024 clients up, the route was 8–13% ahead, and DataLoader's p99 at 4,096 clients was higher, 85 ms against 28 ms.

Below that, the difference is mostly how this DataLoader waits. Even with delay 0, async-graphql collects a batch behind a Tokio timer, which fires on the next millisecond tick. That costs about 1.5 ms per batch, and it is why it does 585 requests/s with one client. A DataLoader that only yields would not pay that. So I would not read the low-client rows as "batching in a route is faster than DataLoader". They show that waiting for a timer is expensive when nobody else is coming. I did not test other implementations, such as the dataloader crate.

How each batch asks Postgres mattered more than I expected. One = ANY($1) with an array is one prepared statement for every batch size. IN ($1, …, $n) is a different statement for each n. At 64 clients, the same DataLoader with an IN list did 27,000 instead of 37,000.

If you write Rust and have one database, a Loader is about fifteen lines and nearly as fast. What a route adds is that the batching is configuration instead of code: the same lookup works against MySQL, SQLite, MongoDB, ClickHouse or HTTP, and the same Caller is available from Python, Node and C.

On ClickHouse the effect is the same. A point lookup there reads a whole granule. In a short file-to-file run against 1 million rows (ClickHouse 25.8 in Docker), one query per message did about 1,100 rows/s and batches of 1,000 keys did about 19,500, roughly 17x.

Behind an HTTP endpoint

The same lookup behind an HTTP endpoint (mq-bridge's http input → lookup → response, oha as the client), one lookup per request on a pool of 64 against batched with 16 lanes. At 20,000 requests/s offered, the batched route kept up with a p99 of 7.9 ms. One lookup per request managed about 12,400 and fell behind, with waits of almost two seconds.

Numbers

Load (HTTP, local)	per request: req/s	per request: p99	batched: req/s	batched: p99
1 client	2,513	0.89 ms	2,645	0.64 ms
64 clients	10,974	10 ms	31,752	4.2 ms
20,000 req/s offered	12,391	1,894 ms	19,989	7.9 ms
40,000 req/s offered	12,201	3,451 ms	36,539	430 ms

At 40,000 req/s offered, the batched route was past its limit as well.

Caveats

This all ran on one laptop. The clients, mq-bridge and Postgres fought over the same 8 cores.

The 18,000 queries/s ceiling includes Docker's port forwarding on macOS. A pool of 192 connections did not raise it. With a faster path to the database that number goes up, and every factor in this post goes down. Over a real network it goes the other way: each round trip gets more expensive, and one query per request falls further behind.

The baseline and the DataLoader use 64 connections, pipelining 16 or 64. The batched route uses 8. Another driver or pool size would move the numbers. I only tested primary-key lookups with small rows, on Postgres 17.6; I did not test Postgres 18.

Most cells varied by less than 5% between the three runs, a few by up to 16%.

Try it

cd benches/lookup
docker compose up -d --wait
export PGUSER=bench PGPASSWORD=bench   # the compose database's credentials
cd caller && cargo run --release        # args: requests per cell, variants, runs (default 50000, all, 3)

It prints the tables above for your machine, plus a few more variants. The HTTP runs are described in benches/lookup/README.md. Let me know what kind of numbers you have.