Why an API Is Slow in Production but Fast Locally

An API that is slow in production but fast locally is not running different code. It is running the same code against different data, different statistics, a different network, and different concurrency. The endpoint that returns in 40 ms on your laptop and 3 seconds in production is telling you which of those differences matters. This guide shows how to find out which one it is.

The fastest way to lose a day is to guess. The second fastest is to run the query locally, see that it is fast, and conclude the database is fine. The workflow below starts with a trace of one slow production request and narrows from there.

Why is an API slow only in production?

Request latency is the sum of the work your code does and the time it spends waiting. On a laptop, almost all of it is work: the database is on localhost, tables hold a few hundred rows, and you are the only user. In production the work often takes about the same time, but the waiting grows. The request waits for a connection from the pool, waits on a lock another transaction holds, waits on a network round trip for each of fifty queries, and waits on a dependency that is itself under load.

Some of the work changes too. A query plan that reads 1,000 rows locally can read millions in production, because the table is bigger and because the planner chose a different strategy for it. That is the classic “database query slow in production” case, and it is one of several.

Questions about an API slow in production but fast locally or a query slow in production but not local are common on Stack Overflow, and the answers vary because the causes do. No single setting fixes all of them. The evidence tells you which one you have.

What actually differs between local and production?

Before you look at any code, list the differences between the two environments. Each row below is a known source of “works on my machine” latency.

Most production-only slowdowns come from the first five rows. Check them in order.

Why is a query slow in production but not local?

Take a common endpoint: the latest 20 orders for a tenant.

SELECT id, status, total, created_at
FROM   orders
WHERE  tenant_id = $1
ORDER BY created_at DESC
LIMIT  20;

The table has an index on created_at and another on tenant_id. Locally the table has 1,000 rows. The planner reads the whole table, keeps the top 20 matches in a small in-memory sort, and returns in well under a millisecond:

Limit  (actual time=0.402..0.407 rows=20 loops=1)
  ->  Sort  (actual time=0.400..0.403 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (actual time=0.011..0.290 rows=212 loops=1)
              Filter: (tenant_id = 42)
              Rows Removed by Filter: 788
Execution Time: 0.431 ms

In production the table has 50 million rows and the planner chooses a different strategy. It walks the created_at index backwards, newest first, and throws away rows from other tenants until it has 20 for tenant 42. The planner expects that to be quick because it assumes tenant 42’s rows are spread evenly over time. They are not. Tenant 42 is an older customer whose recent activity is low, so the scan walks millions of index entries first:

Limit  (actual time=2871.402..2903.118 rows=20 loops=1)
  Buffers: shared hit=112043 read=389210
  ->  Index Scan Backward using orders_created_at_idx on orders
        (actual time=2871.399..2903.101 rows=20 loops=1)
        Filter: (tenant_id = 42)
        Rows Removed by Filter: 4812330
        Buffers: shared hit=112043 read=389210
Planning Time: 0.214 ms
Execution Time: 2903.170 ms

Two things went wrong at once, and neither shows up locally. The data volume made a wasted scan expensive. The data distribution made the planner’s estimate wrong, because it assumes the columns are independent. Rows Removed by Filter is the tell: the plan did millions of rows of work to return 20. The high read count in Buffers shows that most of it came from disk rather than memory.

The fix gives the planner an index that answers the question directly. Build it without blocking writes:

-- Serves the filter and the sort together; no filter-and-discard
CREATE INDEX CONCURRENTLY orders_tenant_created_idx
    ON orders (tenant_id, created_at DESC);

-- Expected plan afterwards:
-- Limit -> Index Scan using orders_tenant_created_idx (rows=20)

Missing, invalid, and unused indexes

Before blaming the plan, confirm the index you expect exists in production. Schema drift is common: an index created by hand during a past incident, a migration that failed halfway, or a CREATE INDEX CONCURRENTLY that was interrupted and left an invalid index the planner will not use. Compare the production schema with the migrations rather than trusting that they match.

-- Indexes on the table, and whether each is valid
SELECT c.relname AS index_name, i.indisvalid, pg_get_indexdef(i.indexrelid)
FROM   pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE  i.indrelid = 'orders'::regclass;

-- Is the index actually used?
SELECT indexrelname, idx_scan
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

Stale statistics, generic plans, and parameter sniffing

The planner does not look at your data when it plans a query. It looks at statistics about your data: row counts, the most common values, histograms, and the number of distinct values. When those statistics are wrong, the plan can be wrong even with the right indexes in place.

Stale statistics

PostgreSQL refreshes statistics through autovacuum’s analyze step, which runs after a fraction of the table has changed. After a bulk import, a backfill, or a period of fast growth, statistics can describe a table that looks nothing like the current one. Locally, you typically reset and reseed the database, so statistics are always fresh.

SELECT relname, n_live_tup, n_mod_since_analyze,
       last_analyze, last_autoanalyze
FROM   pg_stat_user_tables
WHERE  relname = 'orders';

-- If n_mod_since_analyze is large relative to n_live_tup, refresh:
ANALYZE orders;

If the estimated rows in a plan node differ from the actual rows by orders of magnitude, suspect statistics first. For skewed columns, raising the per-column statistics target (ALTER TABLE ... ALTER COLUMN ... SET STATISTICS) gives the planner a more detailed picture. For correlated columns, PostgreSQL’s extended statistics (CREATE STATISTICS) can help with some estimates.

Generic plans and parameter sniffing

Production code usually runs queries as prepared statements with bound parameters. Locally you paste the SQL with a literal value into a console. Those are not the same execution path.

In PostgreSQL, a prepared statement is planned with the actual parameter values (a custom plan) for its first several executions. After that, the server may switch to a generic plan that ignores the specific value if the generic plan’s estimated cost is not much worse. A generic plan built for an average tenant can be a poor plan for your largest tenant. See the PREPARE documentation for the rules and the plan_cache_mode setting.

SQL Server has the mirror-image problem, called parameter sniffing: the plan is compiled for the parameter values of the first execution and cached, so a plan compiled for a small customer is reused for a large one. The symptom is the same: the query is fast when you run it by hand and slow when the application runs it.

PREPARE latest_orders(bigint) AS
  SELECT id, status, total, created_at FROM orders
  WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 20;

-- Force each mode and compare
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE latest_orders(42);

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE latest_orders(42);

If the generic plan is the slow one, the durable fix is usually an index that makes both plans good. Forcing custom plans for the session or role is a workaround with a planning-time cost on every execution.

N+1 queries: why round trip latency matters only in production

An N+1 query pattern runs one query to fetch a list and then one query per item to fetch related data. ORMs make this easy to write by accident through lazy loading. Locally it is almost free, for two reasons: the database is on localhost, and your seed user has three orders, not fifty.

List<Order> orders = orderRepo.findTop50ByTenantIdOrderByCreatedAtDesc(tenantId);
for (Order o : orders) {
    // customer is LAZY: each access issues its own SELECT
    views.add(new OrderView(o, o.getCustomer().getName()));
}

@EntityGraph(attributePaths = {"customer"})
List<Order> findTop50ByTenantIdOrderByCreatedAtDesc(Long tenantId);

The equivalents elsewhere are JOIN FETCH in JPQL, select_related and prefetch_related in Django, includes in Rails, and eager loading options in SQLAlchemy. The same pattern applies to HTTP: calling a pricing service once per line item inside a loop is an N+1 over the network.

The numbers above are illustrative, but the shape is not. Latency grows with the number of round trips times the cost of each. Every request also holds a pool connection for the whole loop, which feeds directly into the next section. A trace makes N+1 obvious: dozens of identical short database spans stacked one after another under a single request span.

Contention: why production concurrency changes the answer

Locally you send one request at a time. Nothing queues. In production, a small increase in per-request time can turn into a large increase in latency, because requests start waiting for shared resources.

Connection pool contention

Little’s law gives the average number of connections in use: arrival rate × time each request holds a connection. If an endpoint handles 400 requests per second and holds a connection for 5 ms, about 2 connections are busy on average. If a plan regression pushes the hold time to 50 ms, it needs about 20. With a pool of 10, the extra requests wait in line, and the wait adds to every request, including requests to endpoints that did not change.

The HikariCP timeout post in this series covers how to tell a slow query from a leak or an undersized pool using pool metrics. For latency debugging, the key metric is connection acquire time. If acquire time is a large share of request time, the endpoint is waiting, not working, and the cause is whatever holds connections longest.

Lock contention

Row and table locks only conflict when two transactions touch the same data at the same time, which never happens on a single-user laptop. A long transaction that updates a hot row (an account balance, a counter, a tenant settings row) makes every other writer to that row wait. A migration that takes a stronger lock on a busy table can stall reads and writes behind it. In PostgreSQL, find who is blocking whom:

SELECT pid,
       pg_blocking_pids(pid)   AS blocked_by,
       wait_event_type, wait_event,
       now() - query_start     AS waiting_for,
       left(query, 80)         AS query
FROM   pg_stat_activity
WHERE  cardinality(pg_blocking_pids(pid)) > 0;

CPU throttling and resource limits

In Kubernetes, a container CPU limit is enforced by the Linux CFS quota: once the container uses its quota within a scheduling period, its threads are paused until the next period. Average CPU usage can look moderate while individual requests stall in bursts. Garbage collection and JSON serialization of large responses are common triggers. The Kubernetes resource management docs describe how limits are applied. With cAdvisor metrics in Prometheus, check the throttled ratio:

sum by (pod) (rate(container_cpu_cfs_throttled_periods_total{container="api"}[5m]))
  /
sum by (pod) (rate(container_cpu_cfs_periods_total{container="api"}[5m]))

Memory limits have a related effect: a JVM or Node process sized close to its container limit spends more time in garbage collection, and each pause adds to request latency. The OOMKilled post in this series covers how to tell the two apart.

Network, TLS, cold caches, and downstream dependencies

Not every production-only slowdown is in the database.

• New connections per request. An HTTP client created per call, or a client with keep-alive disabled, pays a DNS lookup, a TCP handshake, and a TLS handshake each time. That is several round trips before the first byte of the request. Locally, against http://localhost, those costs are close to zero.
• DNS. Slow or failing resolution inside a cluster adds latency to every uncached lookup. It shows up as a gap at the start of client spans.
• Cold caches. After a deploy or a failover, the database buffer cache, the OS page cache, application caches, and the JIT compiler are all cold. The first minutes are slower than steady state. If only the first requests are slow, warm up before taking traffic rather than tuning queries.
• Working set larger than memory. Locally the whole database fits in RAM. In production, a query that touches old data reads from disk. In EXPLAIN (ANALYZE, BUFFERS), a high read count relative to hit shows this.
• Downstream dependencies. A mocked payment or pricing service answers instantly. The real one is under its own load, may retry internally, and may be in another region. A missing client timeout lets its worst latency become yours. Retries on top can amplify it, which is the subject of the retry storm post.

A systematic workflow for an API slow only in production

Start with the request, not the query. A distributed trace of one slow request shows where the time went, and that decides which of the causes above to investigate. If you do not have tracing, OpenTelemetry instrumentation for your HTTP server, HTTP client, and database driver gets you most of the picture.

Once you have a trace, the diagnosis is a short sequence of questions. Ask them in order, because the earlier ones are cheaper to answer and more often the cause.

The steps

1. Confirm the slowdown is server-side and quantify it. Compare server latency percentiles (p50, p95, p99) by endpoint with what clients see. Averages hide the tail where production-only problems live.
2. Trace one slow request. Split its time into waiting (pool acquire, locks, throttling), database, downstream calls, and your own CPU.
3. Find the statements that dominate database time. Use pg_stat_statements in PostgreSQL, or the slow query log and Performance Schema in MySQL.
4. Get the production plan with real parameters. Run EXPLAIN (ANALYZE, BUFFERS) on production or a production-sized copy, using the parameter values from the slow requests, and through a prepared statement if the application uses one.
5. Check what the planner knows. Compare estimated and actual rows, statistics freshness, index validity, generic versus custom plans, and settings such as work_mem.
6. Count round trips per request. Look for repeated identical spans, which point to N+1 queries or per-item HTTP calls.
7. Check contention and the environment. Pool acquire time, lock waits, CPU throttling, dependency latency, and whether slowness is limited to the minutes after a deploy.
8. Reproduce at production scale, fix, and verify. Reproduce with production-sized data and realistic concurrency, apply the fix for the cause you found, and confirm with the same metrics that showed the problem.

SELECT calls,
       round(total_exec_time)       AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows / NULLIF(calls, 0)       AS rows_per_call,
       shared_blks_read,
       left(query, 80)              AS query
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT  15;

Sort by total time as well as mean time. A 2-second query that runs twice an hour matters less than a 4 ms query that runs 50 times per request. A very high calls count relative to request volume is the database-side signature of an N+1 pattern.

Fixes, matched to the cause

How Tomosu helps

Most of the causes in this guide enter the codebase through an ordinary pull request. A PR adds a filter and an ORDER BY to a repository method. Another adds a lazy association to a list view, or a call to a pricing service inside a loop. Each change passes review and passes tests, because the tests run against a small database with one request at a time. The first environment with realistic data and concurrency is production.

Tomosu analyzes the standing codebase and each change for this kind of production reliability risk. For production-only latency, the useful output connects the changed code to where it will run:

• Changed query paths: new or modified filters, sorts, and joins, the tables they touch, and whether an index in the schema and migrations serves them. That makes a query that works on a 1,000-row dev table easy to question before it meets a 50-million-row one.
• Round-trip patterns: database calls and HTTP calls inside loops, lazy associations read in list views, and per-item calls that turn into N+1 patterns as data grows.
• Shared resource impact: changed code that holds connections longer, calls out while a transaction is open, or updates hot rows, so the effect on the pool and on other endpoints is visible.
• Blast radius: which endpoints, jobs, and callers reach the changed path, so a slower query on the checkout path is weighed differently from one in an admin report.
• The evidence to collect: which plans, metrics, and traces would confirm or rule out the risk before or after release.

These findings roll up into the Production Reliability Index. The Fragility Index and Runtime Signals capture the risk in the code path and what production telemetry says about it. For the broader practice, see How to Assess the Blast Radius of a Code Change and Pre Merge Reliability Analysis. For why dashboards alone arrive too late for this class of problem, see Production Reliability vs Observability.

Scan your repository with Tomosu →

Key takeaways

• An API that is slow in production but fast locally is running the same code on different data, plans, networks, and concurrency. Find out which one changed.
• Start with a trace of one slow request. Split its time into waiting, database, downstream calls, and CPU before looking at any single query.
• Get the production plan with EXPLAIN (ANALYZE, BUFFERS), real parameter values, and the prepared-statement path. Rows Removed by Filter and estimate errors are the tells.
• Round trip latency is a multiplier. N+1 patterns that cost 15 ms locally can cost several times that in production, and N grows with the data.
• Under concurrency, slower queries hold connections longer, and every request pays the queue.
• Reproduce with production-scale, production-shaped data and concurrent load, or the repro will say everything is fine.

Frequently asked questions

A query that is fast locally has only been tested against a database that does not exist in production. Tomosu connects a changed query or call path to the data, concurrency, and callers it will meet after merge. Assess your repository →