The API didn’t look slow. It looked broken.

The situation

One API served every consumer: the web app bench scientists used, the ML optimisation pipelines, an automated job shipping Parquet to an external pharma partner’s bucket, and CI data-integrity checks. Behind it sat a PostgreSQL-compatible warehouse with thirty typed assay tables, a sequence table of roughly sixteen million rows, and protein records of 170–200KB of nested JSON. A full project response approached a gigabyte.

Every report was a symptom. The app team flagged that narrow filters returned nothing and timed out. A scientist opened a running issue to collect 5xx errors, calling it better than dropping a message in a chat that will soon be forgotten. It accumulated a 502 after 65 seconds, and a gateway timeout that took the API docs down for twenty minutes. A frontend engineer was blocked mid-test by intermittent 502s and 503s. Then the partner delivery pipeline broke outright: a large project returned zero bytes after ninety seconds.

What was actually wrong

Five independent problems across four layers: schema shape, query planning, transport and server concurrency. Each produced the same user-visible symptom.

• Ingestion logic ran on the read path

  The transform layer loaded whole assay tables into dataframes and filtered in Python afterwards. Instrumented on a hundred-protein query: 120,347 rows loaded, five used.

• SQL never saw the page limit

  A limit of ten cost the same as a limit of three thousand. Twenty-eight seconds to return ten rows.

• Intermediate steps were re-evaluated per row

  Three common table expressions were declared without materialisation, so the database recomputed them for every output row. That cost 4.79 million index scans against a single table to produce a 1,547-row result.

• A default was silently not applied

  A role filter was guarded by a condition that only held when a project was specified. Any query without one pulled in a commercial target carrying 954,073 relationship links, which detonated the aggregations downstream.

• The planner had no idea how big anything was

  A columnar scan estimated about 1,700 rows where there were sixteen million. Join selectivity then rounded every downstream step to a one-row estimate, so the planner chose nested loops throughout: one join ran as a filtered cross product, eight top-level joins discarded around seventy-three million rows, and the final sort spilled 215MB to disk.

How it was measured

Four layers, four instruments.

• Query plans against production, read-only. Every headline number came from an EXPLAIN ANALYZE on the production database. The reproduction was committed as two SQL files side by side, so the fix could be proven to return an identical 5,356-row result.
• Estimates against actuals. The decisive observation was every intermediate step reporting one estimated row against tens of thousands actual. Cost estimates pointed the other way: materialised steps looked expensive and ran dramatically cheaper.
• Row accounting on the ingestion layer. Rows loaded against rows used, plus peak memory per stage. Those ratios are what showed the problem was architectural.
• A benchmark in CI against production-shaped snapshots. Built with a colleague, extended here with reproducible snapshot loading. It turned it feels slow into a number anyone on the team could re-run.
• Session inspection for the concurrency faults. A CI job that hung for an hour turned out to be a streaming response whose database session never tore down, holding locks against a competing operation. Found in the database’s own activity view.

What was rejected, and why

Most of these were tried and measured before being discarded. The reasoning matters more than the fix.

• Disable nested-loop joins

  The obvious lever, since the planner kept choosing them. Measured worse, at over 90 seconds: the materialised intermediate steps genuinely need index nested loops against ten-million-row tables.

• Install a planner-hint extension

  Available, and it would have pinned the plan directly. Declined: a permanent operational liability on the production cluster to work around one bad row estimate.

• Rewrite the query builder wholesale

  Designed, branched, and dropped. The existing metadata already drove SQL aggregation, so safety came from allowlisting group-by keys and a closed set of aggregate functions. The rewrite bought nothing.

• Aggregate in Python for the new endpoint

  The Python aggregation pass was the slow path being removed. Adding a second one would have recreated the bug under a new name.

• Return an empty list instead of an error

  The streaming endpoint returned a 500 on an empty result. Masking it would have let the partner delivery job ship an empty dataset silently. Kept fail-loud; filed the server bug separately.

• async handlers everywhere

  Counter-intuitive, and measured. With a synchronous database driver, async handlers produced false concurrency: blocking I/O ran inside the event loop, so requests serialised. Plain handlers on the framework threadpool restored real parallelism.

• Bigger pods

  Raising memory limits and pool size bought time. The actual cause was connection-pool exhaustion from one database engine per schema per pod, so the fix was collapsing those into a singleton.

• More query tuning, at the end

  Once a full-project response was measured at roughly a gigabyte, transfer dominated what was left. The next lever was payload reduction: pagination, streaming, field projection.

What was chosen instead: give the planner true statistics. Materialise the candidate set into an analysed temporary table so downstream steps inherit real cardinalities and the planner picks hash joins on its own. One structural change, read-only, transaction-scoped, and it never writes to the production schema.

What could not break

This is a scientific system of record. Wrong numbers are worse than slow numbers.

• Result identity, proven every build. Every change was gated on returning the same rows, with parity snapshots captured from the main branch before the refactor, so a behaviour change failed the build before it reached a scientist. The partner-delivery rework was verified byte-identical against production before merge.
• Completeness had to increase. The rewrite was only allowed to be faster if it also returned more correct data: 47,985 datapoints against 20,785 from the old path, including 5,000 chromatogram points the previous implementation returned none of.
• An external partner was consuming the output. The delivery job had to stay green throughout, which is also why it moved from full overwrite to an incremental merge with a per-delivery change summary.
• A public API contract under a semver gate. CI rejected any pull request whose schema change did not match its version bump, so every breaking step was deliberate and announced.
• The database-agnostic test path. All warehouse-specific tuning is gated on the dialect, so the 1,388-test unit suite runs unchanged against in-memory SQLite.

Result

The worst offenders went from 30–55 seconds to three seconds or less. Across the endpoint family, end-to-end time dropped by roughly 70%.

That rollup is deliberately conservative. Server-side reductions were 90% and above, but end-to-end includes wire transfer and response construction, which did not improve proportionally. On the largest cohort, moving the payload dominated what remained. The conclusion recorded at the time: query tuning had stopped paying, and the next lever was payload size.