Five separate problems behind one API’s timeouts

The situation

One API served every consumer: the web app the bench scientists used, the ML optimisation pipelines, an automated job that shipped Parquet files to an external pharma partner’s bucket, and the data-integrity checks in CI. Behind it was a PostgreSQL-compatible warehouse with 30 typed assay tables and a sequence table of roughly 16 million rows. A single protein record was 170–200KB of nested JSON, and a full project response came close to a gigabyte.

The problems arrived as separate bug reports. The app team found that narrow filters returned nothing and timed out. One scientist started a running issue to collect 5xx errors, saying it was better than dropping a message in a chat that will soon be forgotten. That issue picked up a 502 after 65 seconds, and a gateway timeout that took the API docs down for 20 minutes. A frontend engineer got blocked in the middle of testing by intermittent 502s and 503s. Then the partner delivery pipeline failed outright, when a large project returned zero bytes after 90 seconds.

What was wrong

There were five separate problems, spread across the schema, the query planner, transport and server concurrency. From the outside they all looked the same.

• Ingestion logic ran on the read path

  The transform layer loaded whole assay tables into dataframes and filtered them in Python afterwards. I instrumented a query for 100 proteins. It loaded 120,347 rows and used 5.

• The page limit never reached SQL

  Asking for 10 rows cost the same as asking for 3,000. It took 28 seconds to return 10 rows.

• Three CTEs were recomputed for every row

  Three common table expressions were declared without materialisation, so the database recomputed them for each output row. Producing a 1,547-row result took 4.79 million index scans against a single table.

• A default filter wasn’t being applied

  A role filter sat behind a condition that was only true when a project was specified. Any query without a project pulled in a commercial target with 954,073 relationship links, which the downstream aggregations then had to process.

• The planner had no idea how big anything was

  A columnar scan was estimated at about 1,700 rows when the table had 16 million. Join selectivity then rounded every later step down to an estimate of one row, so the planner picked nested loops everywhere. One join ran as a filtered cross product, eight top-level joins discarded around 73 million rows, and the final sort spilled 215MB to disk.

How I measured it

• Query plans from production. Every headline number comes from running EXPLAIN ANALYZE against the production database, read-only. I committed the reproduction as two SQL files side by side, which is how I could show that the fixed query returned the same 5,356 rows as the original.
• Estimated rows against actual rows. What gave the problem away was every intermediate step reporting an estimate of one row when the actual count was in the tens of thousands. The cost estimates were misleading in the other direction. The materialised steps looked expensive on paper and ran far cheaper.
• Row accounting in the ingestion layer. I logged rows loaded against rows used, and peak memory per stage. Those ratios showed that the problem was architectural.
• A CI benchmark on production-shaped snapshots. A colleague and I built the harness, and I added reproducible snapshot loading to it. When someone said it feels slow, anyone on the team could re-run the benchmark and get a number.
• Database session inspection. A CI job that hung for an hour turned out to be a streaming response whose database session never closed, so it kept holding locks that another operation was waiting on. I found it in the database’s own activity view.

What I rejected, and why

Most of these I tried and measured before dropping them.

• Disable nested-loop joins

  The planner kept choosing nested loops, so turning them off was the obvious thing to try. It made things worse, at over 90 seconds, because the materialised intermediate steps do need index nested loops against the ten-million-row tables.

• Install a planner-hint extension

  It was available, and it would have pinned the plan directly. I decided against it because it would have been a permanent operational liability on the production cluster, taken on to work around one bad row estimate.

• Rewrite the query builder wholesale

  I designed this and got as far as a branch before dropping it. The existing metadata already drove SQL aggregation, so I could get the safety I wanted by allowlisting group-by keys and keeping a closed set of aggregate functions. A rewrite wouldn’t have added anything.

• Aggregate in Python for the new endpoint

  The Python aggregation pass was the slow path I was removing. Adding a second one for the new endpoint would have brought the same problem back.

• Return an empty list instead of an error

  The streaming endpoint returned a 500 on an empty result. If I had masked that, the partner delivery job could have shipped an empty dataset without anyone noticing. I kept it failing loudly and filed the server bug separately.

• async handlers everywhere

  This goes against the usual advice, so I measured it. With a synchronous database driver, async handlers only looked concurrent. The blocking I/O ran inside the event loop, so requests were serialised. Plain handlers on the framework threadpool gave real parallelism back.

• Bigger pods

  Raising memory limits and the pool size bought some time. The real cause was connection-pool exhaustion, because each pod created one database engine per schema. The fix was to collapse those into a singleton.

• More query tuning, at the end

  Once I had measured a full-project response at roughly a gigabyte, transfer accounted for most of the time that was left. The next thing worth doing was payload reduction, meaning pagination, streaming and field projection.

What I did instead: gave the planner accurate statistics. The candidate set is materialised into an analysed temporary table, so every downstream step inherits real cardinalities and the planner chooses hash joins on its own. It’s a single structural change, read-only and transaction-scoped, and nothing is written to the production schema.

What couldn’t break

This is a scientific system of record, so returning a wrong number would have been worse than returning a slow one.

• Same results, checked on every build. Every change had to return the same rows. I captured parity snapshots from the main branch before starting the refactor, so a change in behaviour failed the build before it could reach a scientist. The partner-delivery rework was verified byte-identical against production before it merged.
• More complete data. The rewrite was only acceptable if it also returned more correct data than the old path. It returns 47,985 datapoints against the old path’s 20,785, including 5,000 chromatogram points the old implementation didn’t return at all.
• An external partner consuming the output. The delivery job had to stay green the whole time. That’s also why I moved it from a full overwrite to an incremental merge with a change summary per delivery.
• A public API contract behind a semver gate. CI rejected any pull request where the schema change didn’t match the version bump, so every breaking change was deliberate and announced.
• A test path that doesn’t depend on the warehouse. All the warehouse-specific tuning is gated on the SQL dialect, so the 1,388-test unit suite still runs unchanged against in-memory SQLite.

Result

Requests that took 26–54 seconds now take 3.4 seconds or less, and the one that timed out returns in 2.7. Across the endpoint family, end-to-end time dropped by roughly 70%.

The 70% figure is conservative on purpose. Server-side time fell by 90% or more, but end-to-end time also includes wire transfer and building the response, and those didn’t improve by as much. On the largest cohort, moving the payload was most of what remained. What I wrote down at the time was that query tuning had stopped paying off, and that payload size was the next thing to reduce.