API Endpoint Getting Slower as Data Grows? Find the Cause

When an API endpoint gets slower week by week without a deploy, the cause is almost always one of two things: it reads far more data than it uses (a whole document or every row, just to compute a size or a flag), or it talks to the database once per item in a loop (the N+1 pattern). Watch the endpoint's p95 against a promise rather than its average, open one slow request's trace to see which of the two it is, then fix it by reading only the fields you use and by turning per-item calls into one call for the batch.

Both problems are invisible on day one. With ten items or a small document, the extra work costs a few milliseconds. They grow with your data: the endpoint is fine in testing and slow a month later.

Step 1: Watch p95 Against a Promise, Not the Average

An average hides a slow tail, and a slow tail is where these bugs show first: only the requests that hit a big document or a long list get slow. Track the 95th percentile per route, and give the route a promise such as "95 % of requests finish under 1 second". Count the requests that miss it.

-- per day: requests, p95, and how many missed a 1 s promise (ClickHouse; adapt for your store)
SELECT toDate(Timestamp) AS day,
       count() AS requests,
       quantile(0.95)(Duration / 1e6) AS p95_ms,
       countIf(Duration / 1e6 > 1000) AS missed
FROM otel_traces
WHERE SpanKind = 'Server' AND SpanAttributes['http.route'] = '/api/v1/sessions/replay'
GROUP BY day ORDER BY day

A p95 that climbs while the request count stays flat is the signature: the work per request is growing, not the load. If you need the basics of per-route latency first, see how to find a slow API endpoint.

Step 2: Open One Slow Request and Count the Database Calls

Take one request from the slow tail and look at its trace (OpenTelemetry auto-instrumentation records every database call as a span). You are looking for one of two shapes:

• A few calls, one of them slow and large. The handler reads a big document or many rows. The time goes into transferring and decoding data the handler then mostly throws away.
• Many calls with the same statement. The handler loops over items and queries once per item: 20 items, 20 identical queries with different values.

If you have no tracing, log the number of database calls and the bytes read per request for a day; the shape shows up the same way.

Step 3: Read Only the Fields You Use

The classic case: to enforce a size limit, the handler loads every chunk of a stored document, including the data, just to add up the sizes.

# Before: downloads the whole document (all chunks' data) on every request
doc = coll.find_one({"_id": session_id}, projection={"chunks.data": 1, "chunks.size": 1})
total = sum(c["size"] for c in doc["chunks"])

# After: only the sizes travel; the data is never read here
doc = coll.find_one({"_id": session_id}, projection={"chunks.size": 1})
total = sum(c["size"] for c in doc["chunks"])

The result is identical, and the cost no longer grows with the document. If old records lack the small field (here size), read the big one only for those records instead of for every request. Better still, keep a running total on the document ($inc on each write) so the read is a single number.

The same rule holds in SQL: SELECT * on a table with a large JSON or text column, when you need two small columns, does the same damage.

Step 4: Turn Per-Item Calls Into One Call for the Batch

The N+1 pattern in its usual form, here writing one record per uploaded file:

# Before: one round trip per file (182 files = 182 queries)
for f in files:
    coll.update_one({"project_id": pid, "file": f.name}, {"$set": f.fields}, upsert=True)

Look up what already exists in one query, insert the new items in one call, and update only the few that existed:

# After: two round trips for a typical batch
names = [f.name for f in files]
have = {d["file"] for d in coll.find({"project_id": pid, "file": {"$in": names}}, {"file": 1})}
new = [{"project_id": pid, "file": f.name, **f.fields} for f in files if f.name not in have]
if new:
    coll.insert_many(new, ordered=False)
for f in files:
    if f.name in have:
        coll.update_one({"project_id": pid, "file": f.name}, {"$set": f.fields})

Keep a unique index on the key (project_id, file): if two requests race, the index refuses the duplicate insert and you update that item instead, so the result is the same as before. Reads follow the same idea: one WHERE id IN (...) (or $in) instead of one SELECT per id, or a join, or your ORM's eager loading (selectinload in SQLAlchemy, include in Prisma).

Step 5: Prove the Fix Did Not Change the Result

A performance fix must return exactly what the old code returned. Write the test before you change the code:

• the same output for a normal batch, an empty batch, and a batch with a repeated item;
• the limit still enforced (for a size cap: at the cap, just under, and with old records that lack the small field);
• the number of database calls, counted with a thin wrapper around the collection or a query counter, so the regression cannot come back.

Then deploy and watch the same p95 and missed-promise count from Step 1. The p95 should drop within hours; the missed-promise budget recovers more slowly, because old slow requests stay in the window.

How Relia Found Two of These in Relia

We run Relia on Relia itself. On 27 October, before a release, we asked it through its MCP tools in the editor whether anything needed fixing:

• A speed promise used up. POST /api/v1/sessions/replay promises 95 % of requests under 1 second. 844 of 13,029 requests had missed it, and its p95 had gone from 284 ms to 1,175 ms in a day, with no errors and a server at 10 % CPU. The cause was Step 3: every upload read the whole stored replay just to add up its size. The fix reads only the sizes.
• A repeated query. Right after that release, Relia raised a new finding: POST /api/v1/sourcemaps ran the same MongoDB query up to 182 times per request, about 730 ms per request with 23 % of it spent on the repeats. That was Step 4: one write per uploaded source map. The fix stores a build's maps in two round trips.

Relia keeps a promise on your most important API routes (it proposes them from the traffic it learns, and you can change them), flags a query repeated in one request as an N+1 finding with example traces, and after a deploy watches real traffic to confirm the problem stopped. The fixes were ours to write; Relia never changes your code.

FAQ

Why did the endpoint not get slow right away?

Because the extra work scales with your data, not your traffic. A handler that reads a whole document costs little while documents are small, and N+1 queries cost little while lists are short.

Is an N+1 always worth fixing?

Not always. Three queries per request on a page nobody waits for can stay. Fix it when the repeats grow with data, when they are a real share of the request time, or when the endpoint has a promise to keep.

Should I add a cache instead?

Fix the shape first. A cache in front of a request that reads 8 MB still reads 8 MB on every miss, and it adds a second thing to keep correct.