Our service was slow, and the database was bored

A service I measured stopped at about 18,000 requests per second. Adding more concurrent clients didn't move it: 64 clients, 1,000 clients, 4,000 clients, all 18,000. The obvious suspects were the database and the code. Neither was the problem. The database was mostly idle.

After one change, the same database on the same laptop answered about 252,000 requests per second, and the slowest requests got about ten times faster. This post explains that change without code, and when it's worth asking your team about it.

Where the time went

Each request needed one record, say one customer. It asked the database for it, waited for the answer, and went on.

Finding one record is very fast for a database. What costs time is everything around it: the request travelling to the database, the database reading the question, and the answer travelling back. That round trip is the same whether you ask for one record or for a hundred.

Think of a delivery service that sends a separate van for every parcel to the same street. The vans are fast, and the warehouse isn't busy. The limit is how many trips the road can take.

With thousands of requests at once, they all queued up for their own trip. The database waited for questions; the requests waited for their turn.

The change: one trip for everyone who is waiting

At any moment, many requests are waiting for a record. Instead of sending a trip for each, the service now collects whoever is waiting right now and asks for all of their records in a single question. When the answer comes back, each request gets its own record and continues.

Two things make this practical:

• Nobody waits on purpose. The service doesn't hold requests back to fill a batch. It takes whoever is waiting at that moment. When traffic is low, a "batch" is one request and nothing changes. When traffic is high, batches fill up by themselves.
• The code that handles a request doesn't change. Each request still asks for one record and gets one record. The batching happens in one shared component.

What it bought

Measured on one laptop with a Postgres database:

In budget terms: the database work per request goes down, so the same database carries more traffic before anyone has to scale it up.

Please don't take "14x" as a promise for your system. A laptop is not production. On a faster path to the database, the gain is smaller; over a real network, where each trip costs more, it tends to be larger. The full measurement, with all caveats, is
here.

When it's worth it, and when it isn't

Worth asking about when:

• many requests at the same time each fetch a small piece of data by its ID (a user, a product, a price, a permission);
• the database looks underused while the service is at its limit;
• latency gets much worse as traffic rises, even though nothing else changes.

Not worth it when:

• traffic is low. With nobody to share a trip with, there is nothing to gain;
• each request runs a large, different query. Batching helps many small identical lookups;
• the time is spent elsewhere, in computation or in another service.

What it costs:

• One more component that every lookup goes through. It's small, but it has to be right.
• A failed query now fails every request in that batch, not just one. Usually that's the same outcome (the database is down for everyone), but it changes how errors look in monitoring.
• Someone on the team has to understand it. The step-by-step animation is a good place to start.

Questions for your team

1. Where do we fetch single records by ID in a hot path?
2. Under peak load, is the database busy, or are our services waiting for it?
3. Do we already batch anywhere? (GraphQL services often do, under the name DataLoader.)

Disclosure: the numbers come from mq-bridge, a library I wrote. The pattern is much older than that and works in any language. A developer can build a small version in an afternoon, and that is a fine first step to see whether it pays off for you.