API latency optimization: from 800 ms to 250 ms, step by step 30 questions in 5 chapters

Before changing anything, I run a load test with a tool like k6, JMeter or Locust and record p50, p95, p99 and throughput at a known request rate. Averages hide the slow tail that real users feel, so percentiles are the baseline. Every later optimization is compared against this same test, so I can prove the change actually helped and did not just move the problem.

I open distributed traces from an APM or OpenTelemetry for the slow requests and look at the spans. Each span shows where time went: the database, our own code, a downstream HTTP call, or waiting for a connection. That tells me which layer to fix first, instead of guessing. I focus on the longest span on the critical path, because shortening anything else barely changes total latency.

I use the pg_stat_statements extension to rank queries by total and mean execution time, because a medium query called thousands of times can matter more than one slow query. Then I run EXPLAIN with ANALYZE and BUFFERS on the worst ones. That shows the real plan, actual row counts versus estimates, and how many pages came from cache or disk.

I check the PostgreSQL statistics views for tables with many sequential scans, a lot of dead tuples, lock waits, and a low buffer cache hit ratio. Each points to a different fix: a missing index, vacuum not keeping up, contention between transactions, or not enough memory. A high sequential scan count on a small table is normal, so I always read it together with table size.

In a B-tree composite index, I put the columns used with equality first and the range column last. Then the database jumps straight to one contiguous slice of the index and reads only matching rows. If the range column comes first, it has to scan the whole range and filter the rest. Column order should follow how the query filters, not the table definition.

A partial index only covers rows that match a condition, for example only orders whose status is pending. If most queries hit that small hot subset, the index is much smaller, fits in memory, and is cheaper to maintain on writes. The catch is that the query's WHERE clause must clearly imply the index condition, otherwise the planner cannot use it.

A covering index stores extra columns with the INCLUDE clause, so the database can answer the query from the index alone, which is an index-only scan, without visiting the table. That saves a lot of random reads on hot queries. It only works well when the visibility map is current, so vacuum matters, and every extra column makes the index bigger and writes slower.

The planner chooses plans from table statistics. If they are stale after big data changes, it misjudges row counts and picks bad plans, so I run ANALYZE or let autovacuum do it. Autovacuum also cleans dead rows left by updates and deletes. On busy tables I tune it to run more often, otherwise bloat grows and every scan gets slower.

When reads overload the primary, I send read-only traffic to replicas, accepting a little replication lag for that data. When a single table becomes very large, partitioning by time or key lets queries skip whole partitions and makes deleting old data cheap. Partitioning is not a general speed-up; it helps mainly with very large tables and queries that filter on the partition key.

I select only the columns I need instead of everything. That means less data read, sent over the network and turned into objects, and it allows index-only scans when the selected columns are in an index. It also protects the API from accidentally exposing new columns and from breaking when someone adds a large text or binary column later.

If I wrap an indexed column in a function in the WHERE clause, like lowercasing an email, a normal index on that column cannot be used and the database scans the table. I either rewrite the condition so the column stays bare, for example using a date range instead of extracting the date, or I create an expression index that matches the function exactly.

OFFSET pagination makes the database read and throw away every skipped row, so deep pages get slower and slower. Keyset pagination remembers the last sort key from the previous page and asks for rows after it, using an index. Each page then costs about the same. The trade-off is that users cannot jump to an arbitrary page number, only next and previous.

For a large subquery, EXISTS can stop at the first match per row, while a badly planned IN might build the whole list first. Modern PostgreSQL often plans both as the same semi-join, so I check EXPLAIN instead of assuming. The bigger trap is NOT IN with a subquery that can return nulls, because it silently returns no rows; NOT EXISTS avoids that.

Inserting rows one at a time pays a network round trip and often a commit for each row. A multi-row INSERT or a batch sends many rows in one statement, and COPY is the fastest way to bulk load in PostgreSQL. For large loads I also wrap everything in one transaction, and sometimes drop and recreate indexes afterwards, which the PostgreSQL docs recommend.

The N+1 problem is one query for a list and then one extra query per item, usually from lazy loading in JPA. I spot it in SQL logs or traces as many near-identical queries. I fix it with a fetch join or entity graph for that use case, or with batch fetching so related rows load in groups, and I add a test that checks the query count.

Opening a database connection is slow, so I reuse connections from a pool like HikariCP, which is the Spring Boot default. The pool should be small and sized for the database, not for the number of threads, because too many active connections compete for the same CPU and disk. PgBouncer helps when many application instances together would open too many connections.

For data that is read often and changes rarely, I use cache-aside with Redis: read from the cache, and on a miss load from the database and store it with a time to live. On updates I write to the database and then delete the cache entry. The risks are stale data, a stampede when a hot key expires, and extra moving parts.

If a request calls several services or queries that do not depend on each other, running them in sequence adds their latencies together. Running them in parallel, for example with CompletableFuture or virtual threads, makes the total close to the slowest call. I always add a timeout per call and use a bounded executor, so one slow dependency cannot hold every thread.

I never return unbounded lists. Every collection endpoint is paginated with a sensible maximum page size, and the response includes only the fields the client needs. Smaller responses mean less database work, less serialization, and less network time, which matters most for mobile clients. Field selection or separate summary and detail endpoints keep payloads small without forcing clients to make extra calls.

For larger text responses like JSON, I enable gzip or brotli compression on the server or the load balancer. Text compresses very well, so transfer time drops a lot, especially on slow networks. I set a minimum size so tiny responses are not compressed, because the CPU cost is not worth it, and I skip already compressed content such as images.

For responses that do not change often, I send Cache-Control headers so clients and CDNs can reuse them, and an ETag that identifies the version. The client then sends a conditional request with If-None-Match, and if nothing changed the server replies 304 Not Modified with no body. That saves bandwidth and serialization, though the server may still need to compute the version.

If work like sending emails, generating reports or calling slow partners does not need to finish before the response, I put it on a queue and process it in the background. The API answers fast, often with 202 Accepted and a status link. Background work must be idempotent and retried safely, and the client needs a way to see when it is done.

After the changes, I rerun exactly the same load test, with the same data, request rate and environment, and compare p50, p95, p99, error rate and throughput with the baseline. Only that comparison proves the improvement. If I changed the test, the numbers would not be comparable, so the test setup is treated as part of the experiment.

I keep dashboards for the four golden signals: latency, traffic, errors and saturation, with latency shown as percentiles. Alerts fire on symptoms users feel, like p99 above the target for several minutes, not on every CPU blip. A performance check in CI or before release catches regressions before production, so the latency win does not slowly disappear.

I start by agreeing on a concrete target as percentiles, for example p99 under a set time at a given request rate. Then I write a load test for that target and run it before each release. In production I watch latency, traffic, errors and saturation with alerts on p99. When we miss the target, I use traces to find where time goes before changing anything.

First I ask what changed: a deploy, a traffic spike, or a data change. I check whether it is all endpoints or one, all pods or one. Then I open slow traces to see whether time is in the database, our code, or a downstream call. If a deploy caused it, I roll back first and investigate after, because users come before root cause.

Latency is skewed. A few very slow requests barely move the average, but real users hit them, and one page often makes many calls, so most users see the tail at some point. Percentiles like p95 and p99 show what the slowest users actually experience. An average can look healthy while a meaningful share of requests are painfully slow.

I look at SQL logs or traces and see one query for the list followed by one query per item, which is the N+1 pattern. I fix it with a fetch join or an entity graph for that use case, or batch fetching so related rows load in groups. Then I add a test that asserts the number of queries, so it does not come back.

I add a cache when data is read often, changes rarely, and the database is the measured bottleneck. I use cache-aside with a time to live and delete entries on updates. The risks are stale data, a stampede of requests when a hot key expires, memory growth without eviction, and one more system to run, so I measure the hit rate.

With OFFSET, the database still reads and throws away all the skipped rows, so a deep page means scanning a huge number of rows just to return a few. The fix is keyset pagination: remember the last sort key and ask for rows after it, with an index on the sort columns. Every page then costs about the same.