An endpoint that answers in 400ms during staging and then spikes to 4 seconds under production load is one of the most frustrating problems in backend engineering. The code looks fine. The database dashboard looks healthy. Nothing obvious is broken. But p95 has tripled and users are waiting.
Slow endpoints rarely have a single dramatic cause. They usually have a compounding one: a missing index that is fine with 100 rows but painful at 100,000, an ORM that fires 50 queries to build one response, or an external service that nobody is timing. The bottleneck lives somewhere in the chain, and the only way to find it reliably is to measure the chain.
This guide walks through a repeatable debugging workflow that starts with traces, drills into database queries, and ends with verification. It is written for engineers working on backend APIs in Node.js, Python, or any stack that supports standard observability tooling.
TLDR
- Slow APIs almost always trace to one of four causes: missing indexes, the N+1 query problem, slow external calls, or unthrottled serialization work.
- Start with a distributed trace to find the slow span, then zoom into that single layer.
- Use
EXPLAIN ANALYZEfor queries and eager loading for N+1, then verify the fix under the same load test that exposed it. - Never optimize until a measurement points you at the bottleneck.
What you will accomplish#
By the end of this guide you will be able to:
- Reproduce a slow request reliably using curl or a load tool
- Read a distributed trace to identify which span is slow
- Use
EXPLAIN ANALYZEto understand database query costs - Detect and fix N+1 queries caused by lazy-loading ORMs
- Measure external service latency and set appropriate timeouts
- Verify the fix under realistic traffic before shipping
Prerequisites#
- Access to your backend service logs or an observability backend (Jaeger, Honeycomb, Datadog, or similar)
- A way to run queries against your database directly (psql, DBeaver, or a DB client)
- curl or a simple load testing tool (k6, wrk, or hey)
- OpenTelemetry instrumentation in your service, or a framework with built-in tracing (Django, FastAPI, Rails, Express with OTEL auto-instrumentation)
If you do not have distributed tracing yet, structured logs with timing fields are a workable starting point. Add duration_ms to every request log at minimum.
Measure before you change#
The workflow has one principle: measure before you change. Every step narrows the search space by eliminating layers that are not the problem.
The layers to inspect, roughly in order of likelihood:
| Layer | What goes wrong |
|---|---|
| Database queries | Missing indexes, N+1 queries, row-scanning joins |
| External service calls | Slow third-party APIs, DNS timeouts, missing connection pools |
| Serialization / transformation | Large payloads, deep object traversal, encoding overhead |
| Application logic | Synchronous blocking work, unbounded loops |
| Infrastructure | Cold starts, connection pool exhaustion, CPU throttling |
The trace is your entry point. Read it once and let it route you down the right branch instead of guessing. The decision tree below is the question you are really answering: where in the chain did the latency accumulate?
Start at the trace. It will tell you which layer to look at first.
The debugging workflow, step by step#
Reproduce the Slow Request Reliably
A bottleneck you cannot reproduce consistently is impossible to measure and dangerous to fix. Before touching any code, establish a baseline that you can run repeatedly.
Use curl with timing output to get a raw measurement:
curl -w "\n\nTotal: %{time_total}s\nConnect: %{time_connect}s\nTTFB: %{time_starttransfer}s\n" \
-o /dev/null -s \
-H "Authorization: Bearer $TOKEN" \
"https://api.example.com/v1/orders?user_id=42&limit=50"
If the endpoint is only slow under load, simulate concurrent traffic with k6:
// k6 load test script
import http from 'k6/http';
import { check, sleep } from 'k6';
export const options = {
vus: 20,
duration: '30s',
};
export default function () {
const res = http.get('https://api.example.com/v1/orders?user_id=42&limit=50', {
headers: { Authorization: `Bearer ${__ENV.TOKEN}` },
});
check(res, { 'status 200': (r) => r.status === 200 });
sleep(0.5);
}
Run this and record the p50, p95, and p99 latency. These numbers are your baseline. Every subsequent change should be measured against them.
Read the Distributed Trace
A distributed trace shows the full lifecycle of a request as a tree of spans. Each span represents a unit of work: an HTTP handler, a database query, a cache lookup, or an external API call. The OpenTelemetry tracing specification defines the model. A trace is a collection of spans sharing a trace ID, with parent-child relationships that form a waterfall.
Open your trace for the slow request and look for:
- The longest single span: That is where time is being spent.
- Many small identical spans: A sign of an N+1 query loop.
- A span with no children that is still slow: Usually serialization, external I/O, or CPU work.
- Gaps between spans: Time spent waiting for a connection pool or scheduler.
Request (412ms total)
├── Auth middleware (8ms)
├── DB: SELECT user (3ms)
├── DB: SELECT orders WHERE user_id=42 (6ms)
├── DB: SELECT items WHERE order_id=1 (4ms) ← N+1 begins
├── DB: SELECT items WHERE order_id=2 (4ms)
├── DB: SELECT items WHERE order_id=3 (3ms)
│ ... × 47 more queries
└── Serialize response (9ms)
This pattern (one query for the parent, one per child) is the N+1 problem. The solution is a single JOIN or a batched query with an IN clause.
If your service does not yet have tracing, add OpenTelemetry auto-instrumentation. Most frameworks have a single import that instruments HTTP handlers and common ORMs automatically.
Inspect Database Queries with EXPLAIN ANALYZE
Once you know which query is slow, run it directly with EXPLAIN ANALYZE. The PostgreSQL documentation describes this as executing the statement and collecting actual runtime statistics, unlike plain EXPLAIN which only shows the planner's estimate.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.created_at, o.total_cents
FROM orders o
WHERE o.user_id = 42
AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;
Read the output from the inside out (innermost node first). Watch for:
| Warning sign | What it means |
|---|---|
Seq Scan on a large table | No index is being used |
actual rows >> estimated rows | Stale statistics; run ANALYZE |
Loops=N on an inner node | That operation runs N times |
High Buffers: shared hit=X read=Y with large Y | Disk reads; cache miss |
Sort without an index | Full in-memory sort for ORDER BY |
A common fix is a composite index covering the filter and sort columns:
-- covers: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_user_status_date
ON orders (user_id, status, created_at DESC);
Use CONCURRENTLY in production to avoid table locks. After adding the index, re-run EXPLAIN ANALYZE and confirm the plan changed to Index Scan.
Detect and Fix N+1 Queries
N+1 queries happen when code fetches a list and then queries the database once per item to load related data. With 50 orders, you get 51 database round-trips. With 500, you get 501. The query count scales linearly with data volume, which is why the endpoint feels fine in staging with 10 rows and breaks in production.
The fix is to load related records in a single batched query. In raw SQL that means a JOIN or a second query with IN:
-- Instead of N queries in a loop:
-- SELECT * FROM order_items WHERE order_id = ? -- × N
-- One query:
SELECT oi.*
FROM order_items oi
WHERE oi.order_id = ANY($1::int[])
ORDER BY oi.order_id, oi.position;
In an ORM (Django, Rails, Prisma, TypeORM), use eager loading:
# Django: prefetch_related fires one IN query
orders = Order.objects.filter(user_id=42, status='completed') \
.prefetch_related('items') \
.order_by('-created_at')[:50]
// Prisma: include fetches items in one round-trip
const orders = await prisma.order.findMany({
where: { userId: 42, status: 'completed' },
include: { items: true },
orderBy: { createdAt: 'desc' },
take: 50,
});
Sentry's documentation on N+1 queries notes that this problem frequently goes undetected in development because row counts are small, and only manifests in production where data volumes are realistic.
Check External Service Latency and Timeouts
If the slow span is not a database query, look at external service calls: payment processors, email providers, internal microservices, or third-party APIs. External calls are the most common source of inconsistent latency because you do not control them.
First, measure what is actually happening:
# Time an external API call directly
time curl -s -o /dev/null "https://api.external-service.com/v1/some-endpoint"
Then check whether your code has explicit timeouts. A missing timeout means one slow upstream can hold your connection pool until the process is overwhelmed.
// Node.js with fetch — always set a timeout
const controller = new AbortController();
const timeout = setTimeout(() => controller.abort(), 3000); // 3s max
try {
const response = await fetch('https://api.external.com/data', {
signal: controller.signal,
});
const data = await response.json();
return data;
} finally {
clearTimeout(timeout);
}
Consider whether the external call can be moved out of the request path entirely. If the result is not needed synchronously, push the work into a background job and return a pending status to the client. See designing reliable job queues for the patterns that make this safe.
Measure Serialization and Middleware Overhead
If traces show the slow time is in your handler itself, not in any database or external span, the culprit is usually serialization of a large response, a deeply nested transform, or middleware that runs on every request.
Add span annotations to bracket suspected sections:
// OpenTelemetry manual span
import { trace } from '@opentelemetry/api';
const tracer = trace.getTracer('orders-service');
async function buildOrderResponse(orders: Order[]) {
return tracer.startActiveSpan('serialize-orders', async (span) => {
try {
const result = orders.map(transformOrder);
span.setAttribute('order.count', orders.length);
return result;
} finally {
span.end();
}
});
}
Common fixes include: limiting response size with pagination, removing unused fields from the response shape, and moving expensive transforms to a read model or materialized view.
Verify the fix under load#
After every change, re-run the baseline measurement from Step 1. A fix that looks good in EXPLAIN ANALYZE but does not move the p95 latency in a load test either targeted the wrong problem or introduced a new one.
Check three numbers:
| Metric | Before | After target |
|---|---|---|
| p50 latency | baseline | ≥ 30% improvement |
| p95 latency | baseline | visible drop |
| DB query count per request | N+1 count | 1–3 queries |
If latency improved but query count is still high, you fixed one bottleneck and exposed another. Keep going.
Also monitor error rate and connection pool saturation during your load test. A too-aggressive fix (like removing a necessary check) can improve latency while breaking correctness.
Common mistakes that waste time#
Optimizing without measuring. Adding an index to a column that is not in any slow query plan wastes time and adds write overhead. Let EXPLAIN ANALYZE tell you what to index.
Fixing the ORM and not the query. Switching from lazy loading to eager loading in the ORM is correct, but verify the generated SQL is actually a single query. Some ORM configurations still produce multiple round-trips.
Testing with small datasets. An endpoint that returns 10 rows will never reveal an N+1 problem or a sequential scan. Use a database with production-realistic row counts for performance testing.
Ignoring cold start effects. The first request to a fresh process is slower than subsequent ones due to connection pool warmup and JIT compilation. Exclude cold-start requests from your baseline.
Missing timeouts on external calls. A third-party API that times out after 30 seconds will hold a thread or connection for that entire duration. Set aggressive timeouts and handle errors explicitly.
Production considerations#
Connection pool sizing. Most APIs underperform not because of query cost but because they exhaust the connection pool under load. Set your pool size to match your database's max_connections minus connections used by migrations and admin tools. A common safe formula for web apps is (2 × CPU cores) + disk spindles per replica.
Query observability in production. PostgreSQL's auto_explain module (official docs) can log query plans for statements that exceed a time threshold without requiring manual intervention. Enable it with auto_explain.log_min_duration = 1000 to capture plans for queries taking more than one second.
Read replicas for read-heavy endpoints. If a reporting or listing endpoint is slow because it competes with writes, route it to a read replica. The primary stays free for writes and latency-sensitive mutations.
Cache strategically, not speculatively. Add caching only after profiling confirms the same expensive query fires repeatedly with identical parameters. Premature caching hides bugs and makes debugging harder. For the caching infrastructure side of this, see how CDNs deliver web apps.
Observability as a permanent fixture. The goal of a debugging session is not just to fix the current bottleneck. It is to make the next one easier to find. Keep the traces, keep the query annotations, and set up alerting on p95 latency so regressions surface before users report them. The broader observability story, especially for AI-augmented systems, is covered in observability for AI systems.
Key Takeaways#
- Always reproduce the slow request and record a p95 baseline before changing anything.
- Distributed traces are the fastest way to locate which layer is responsible for latency. Read them before writing any fix.
- The most common database bottlenecks are missing indexes and N+1 queries caused by ORM lazy loading.
EXPLAIN ANALYZEand trace query counts point directly to them. - External service calls need explicit timeouts. A missing timeout converts a slow upstream into a process-level resource leak.
- Verify every fix with the same load test you used to establish the baseline. Latency improvements that do not show up under realistic concurrency are not real improvements.
- Make observability permanent: traces, query-level timing, and p95 alerting reduce the time to diagnose the next bottleneck.
FAQ#
How do I debug a slow endpoint that is only slow in production?
Enable auto_explain on the production database with a threshold of 500ms–1000ms and ship a build with full OpenTelemetry tracing enabled. Sample at 10–20% if volume is high. The difference between staging and production is almost always data volume or concurrency, and trace data plus query plans from a real production request will show you which.
Is adding an index always safe in production?
Use CREATE INDEX CONCURRENTLY to build indexes without locking the table. Monitor disk usage and replication lag during the build on large tables. Index creation increases write overhead slightly because every insert and update must maintain the index. Only add indexes that EXPLAIN ANALYZE confirms the planner will use.
When should I cache instead of fixing the query?
Cache when the same query fires frequently with the same parameters and the data changes slowly. Fix the query first if the problem is correctness. A 5-second full table scan should not be cached, it should be indexed. Caching an unindexed query hides the bug and creates stale-data risk.
My endpoint calls five microservices. How do I know which one is slow?
Distributed tracing is the only reliable answer here. Each service adds a span to the shared trace. The waterfall view immediately shows which downstream span is contributing most to the total duration. If you cannot add tracing to all services, add explicit timing logs at each call site and correlate on a shared request ID passed in headers.
What is a safe p95 latency target for an API endpoint?
It depends on the use case. Interactive UI endpoints that gate page rendering should target under 200ms p95. Background data endpoints that feed dashboards can tolerate 500ms–1000ms. Anything above 2 seconds for a synchronous API will cause visible user-facing problems. Set the threshold based on the user experience, not an arbitrary number.
