Debugging SLA Fails in a Big Data API

Mar 15, 2026 | Work

Background

So for our client, I built a big data APIs to handle millions of weather forecasts and observations. This data is used to model and forecast water flows in the US. There are countless lives and dollars of property that depend on the availability of that data.

To meet this challenge, a cloud native microservice architecture was used. Containerized, this architecture is deployed in the cloud using Kubernetes, in this case Amazon AWS EKS. There are three main components to the architecture, the API which publishes the data on a public URL, loaders which load data from S3 buckets (filled regularly by regional forecast centers), and the database. There are other containers: one that prunes the data store to 10 days and a utility container that contains a base implementation for the loaders and additional tools for support.

We have a 12 hour SLA, so that data that arrives, must be available via the API in 12 hours. This sounds and was pretty straightforward — and the loaders were able to exchange, load and transform the input data well within the SLA.

An additional requirement was then placed on the API, that quantile sets be calculated during the load.. Quantiles, in the forecasting sense, are statistical “averages” for values. A hydrologic quantile is a value below which a statistical distribution of forecasted values fall. A 90% quantile’s value is the number below which 90% of forecasts fall. For a river, for example, a 90% quantile of 10ft tells you that 9 out of 10 times, you are going to see a flood of 10ft or less.

So to calculate the hydrograph quantile set, you have to pull in all of the forecasted values, and 1) construct the time-aligned member data series, 2) compute the quantiles for the members for each timestep in the data series, and 3) do the math to get the metadata, quantile set values, the min/max envelopes for the probabilistic hydrograph. The map quantile sets are similar. This has to be done on a server and requires Python Pandas, and SciPy libraries. It takes time.

With this in mind, how to optimize?

Approach

The first step is to work your optimizations in order of effort. Always read the code, if it is available.

For this architecture (20M+ rows in the main data table, AWS RDS) and code set (Django REST Framework in EKS pods), the order would be:

Database queries and indexes — simplest, fastest and high reward performance wise.
Caching, is there common data between system components and their code — usually a matter of defining good keys and a few lines of wrapper code.
Rewrite the quantile code — lots of work, incremental w/testing, maybe Pandas and SciPy need changes, data structures optimized, etc. days of work, cognitive debt on support.
Get the quantile calculations out of the main process altogether — muti-process, you need to track processor thread availability, Pandas/SciPy, the GIL, memory pressure, file synchronization, process synchronization, the list is large. More days or work and even more cognitive debt on support.

Metrics

Approach is the first step, now you need to a way for evidence based improvement, enter metrics.

I branched the code, popped in a few timers to create log entries. Then wrote a harness to put inline with the docker log command to figure that (the key was the flush, since the docker log -f is spitting out a stream, notice the filter as well):

import sys,re
tr=tq=0.0
c=0
for line in sys.stdin:
    if 'Processing metrics' not in line:
        continue
    mr=re.search(r\"'run_time':\s*([0-9.]+)\", line)
    mq=re.search(r\"'quantile_time':\s*([0-9.]+)\", line)
    if mr and mq:
        tr+=float(mr.group(1))
        tq+=float(mq.group(1))
        c+=1
        print(f'entries={c} total_run_time={tr:.2f} total_quantile_time={tq:.2f}', flush=True)

This is how the metrics below are collected.

Baseline

Now we fire up the dev stack, wrap the quantile call in monotonic and log it, and run the loader…

...
entries=1780 total_run_time=2746.26 total_quantile_time=1984.32
entries=1781 total_run_time=2748.57 total_quantile_time=1986.21
entries=1782 total_run_time=2750.89 total_quantile_time=1988.09
entries=1783 total_run_time=2751.67 total_quantile_time=1988.70
...

So this tells us a few things: it takes about 1.5 seconds to process an ensemble series, about 72% of that time is in quantiles.

This tells us: quantiles are the best place for improvement, any other ideas would be optimizing the 28%¹.

Database

Quantile sets start by looking up the forecasts, collecting the data for those forecasts — are all pretty moderate database queries. For example, this database query the collects the relevant headers data. It then pulls in all the series members, the appropriately groups and orders those members. So, with the right indexes, can happen reasonably fast.

Quick check, all the queries are covered by the indexes. This is even a bit speeder, as Blocked Range INdexes (BRIN) are used. BRIN indexes are good for lots of rows (there are millions) and when there is some sort of natural order (data is ingested and rows are created mostly in chronological order).

BRINs are fast, small, and in use. An index fully covers the query, so not much performance to be gained here.²

Therefore: Database will not be the high-gain answer b/c indexes cover all queries.

Cache

Looking at the data source, a quantile set is calculated based on a set of parameter values. These values are used to create queries for the quantile source data. These parameters are the same for the rest of that series. There are about 6 parameters, they are usually sequentially calculated.

This tells a few things. 1) you can create a cache key, 2) that query can be cached at the first call, and used for all subsequent quantiles in that series (all 32 or 64), and 3) the quantiles for a series are all sequentially calculated, so the cache life does not need to be more than a couple of minutes. This cache would cut 31 or 63 database roundtrips a series.

This cache approach was implemented. The cache key was a bit tricky, due to the date formats and that we wanted it to be universal, that way the backend cache could be changed without breaking the code. Letting that run…

...
entries=2111 total_run_time=1002.56 total_quantile_time=400.97
entries=2112 total_run_time=1002.85 total_quantile_time=401.07
entries=2113 total_run_time=1003.14 total_quantile_time=401.17
entries=2114 total_run_time=1003.43 total_quantile_time=401.29
...

Now this tells us: it takes about 0.47 seconds to process an ensemble series, and about 40% of that time is in quantile sets. So caching the quantile queries resulted in:

~3× faster overall processing (≈1.5s → ≈0.47s per ensemble series).
Quantile set computation reduced nicely (≈1.1s → ≈0.19s per series).
Quantile calculation is a smaller share of runtime (72% → ~40%), meaning other processing stages are now a larger proportion of total time.

So we are looking at a ~3x improvement on the data processing side of the data loading. In AWS, this will likely be diluted a bit as it will be run in the full architecture — but given that we were missing SLA by only 10%, we can stop here. The two other optimizations; full refactor of the contributed quantile set code and moving the quantile set calculation to it’s own process; are heavier code changes, and these results get us in SLA without a lot of code/testing time spend.

AWS Results

To collect the AWS results, two steps were required:

Apply a patch to the code set that enabled the quantile calculation metrics to be collected
Implement the updated quantile set caching

After step 1 was deployed in the development environment and allowed to stabilize, a sample set of 50 random logs were collected from Cloud Watch and metrics calculated.

The next day, the second step was deployed in the development environment and metrics were calculated the same way over the same sample size. Here are the quantile results:

Metric	Uncached Quantile Process	Cached Query Quantile Process
Raw Average	5.78 Seconds/Quantile Set	2.14 Seconds/Quantile Set
Spread	2.5 – 36 Seconds	1.1 – 3.0 Seconds
Outliers	Wide	Tight
Relative Stability (distribution)	Less	More

This is a ~2X gain over the prior code (it was suspected that the 3X gains seen in a local system would be diluted in AWS — mostly more latency).

Overall, the system processes around 1.2M quantiles sets a day, and the amount of time it takes to ELT incoming data and calculated quantiles, is reduced around 43%. This moves all data sources to well within the SLA’s, and gives some space for future data growth. We are done.

Results

System performance in a big data API was missing the Service Level Agreement thresholds. A performance improving metrics based methodology was defined for the system. Metrics were implemented in the code set and collected.

Based on collected metrics, the bottleneck was identified, and the performance improvement methodology applied. Caches were implemented around the common quantile input data database queries. This was tested locally in development and yielded significant gains (~3x).

A two step process was used to test this in the full AWS hosted API system in the development VPC. The first step yielded base performance values, and the second the new performance values.

Overall, data loading time was reduced ~43%, exceeding all SLA thresholds, with some space to spare, in the AWS environment.

Even worse, that 28% is mostly database time (already optimized), AWS processes, and third party package calls — very hard to get anything more than marginal gains from well developed packages. ↩︎
Truth be told, there was a prior system issue with the quantile query missing a parameter, which made it miss the database index. This choked the db when quantiles were calculated at data load. Reviewing the source code solved that in minutes, pretty much jumped out. This problem was masked in the earlier architecture, as the quantiles were done out of band, avoiding the database rush seen with the higher speed loaders. Moral? Step 1 should always be step 1. 😉 ↩︎

← Few-Shot Prompting