Thirty Days of Uptime in 28 Milliseconds

The home screen of our mobile app waited on one request that took up to 27 seconds on a cold cache, because it counted every raw check result from the last 30 days on every load. Summarising each finished day once brought the same query to 201 ms cold and 28 ms warm.

Watchdock Engineering·7 min read
27 s → 0.2 sworst cold load
2.5 s → 28 mswarm query
90 daysbackfilled in about 4 minutes

The first screen in our mobile app is called Pulse, and its whole job is to tell an on-call engineer in two seconds whether production is fine. This week it was taking a lot longer than two seconds, often enough that I noticed it myself while testing something unrelated, and an on-call app that makes you stare at a skeleton is not one anybody keeps installed. Pulse fires four requests in parallel when it opens: the monitor list, a strip of recent check results, recent incidents, and thirty days of daily uptime for every monitor in the workspace. The screen waited for all four before drawing anything, so it was exactly as slow as the slowest of them, and the nginx logs were very clear about which one that was.

One request out of four

The access log on our API records request time and upstream time for every request, so I pulled the last few hundred hits for each of the four endpoints and sorted them. The monitor list and the strips sat around 200 ms at the median, which is not fast but is mostly the network and authentication. Incidents were between half a second and a second. The thirty-day uptime endpoint had a median of 33 ms and a p90 of 20.4 seconds, with a worst case of 27.1 seconds. A distribution that bimodal always means a cache, and this one was a ten-minute cache in front of a query that was expensive enough to need one. Most requests got the cached copy, and roughly every ten minutes somebody was the unlucky one who paid for a fresh computation while Pulse sat there.

I had been in this code before. In August the same aggregation was spilling its GROUP BY sort to disk on our busiest workspace, about 266,000 rows over thirty days, and raising work_mem for that one transaction took it from 9.6 seconds to 1.2. I wrote at the time that if it got slow again the real fix was a daily rollup table, and then did what everyone does with a note like that, which is forget about it until it got slow again.

What the query was actually doing

Every uptime check we run writes a row: which monitor, when, whether it was up, the status code and the response time. A monitor checked every minute produces 1,440 of those a day and 43,200 over thirty days. To draw a thirty-day uptime bar, the endpoint selected every one of those rows for every monitor in the workspace, truncated each timestamp to a UTC date, grouped by monitor and day, and counted the total and the successful ones. The work grew with check volume, so it grew every time a customer added a monitor or shortened an interval, and it was the same work every time because twenty-nine of those thirty days were already over. A day that has finished will never get another check result. Counting its rows again on every request is like re-adding last month's receipts every time somebody asks what you spent.

So the plan was to count each finished day once and keep the answer. Names are changed for illustration, but the summary is one row per monitor per UTC day with two numbers on it:

python
1class DailyUptime(models.Model):
2 workspace = models.ForeignKey(Workspace, on_delete=CASCADE)
3 monitor_name = models.CharField(max_length=255)
4 day = models.DateField()
5 total = models.PositiveIntegerField()
6 up = models.PositiveIntegerField()

It's keyed by workspace and monitor name rather than a foreign key to the monitor, because that's how the raw results are keyed, and the summary has to agree with what it summarises. If renaming a monitor leaves its old results under the old name, its old summary rows should stay there too, not follow it to the new name.

Never showing half a day

The part I spent the most time on was what a reader should do with a day that hasn't been summarised yet. That situation exists the moment the code is deployed, because the table starts empty and the backfill takes a while, and it happens again any time the hourly job misses a run. If the reader treats a missing summary row as "no checks that day", the uptime bar shows a gap until the job catches up. If it treats a missing row as "go and count it", then a day where a monitor genuinely had no checks looks identical to a day nobody has summarised yet, and you're back to scanning raw rows to find out which.

The way out was a second, tiny table that records which days are finished for everyone. Summarising a day happens in one transaction: count that day's results for every workspace in a single grouped query, delete whatever summary rows the day already had, insert the new ones, and mark the day as done. Readers only trust summary rows for days that are marked, and count anything else live from the raw results.

python
1window = last_n_utc_days(30)
2done = rolled_days(window[:-1]) # today is never finished
3counts = summary_counts(monitors, days=done)
4first_live = next(d for d in window if d not in done)
5counts |= live_counts(monitors, since=start_of(first_live))

That makes the system boring in the right ways. It can be deployed before a single day is summarised and the numbers don't change, only the speed does. A day is either entirely summarised or entirely counted live, and a reader can never catch one halfway through being rewritten. Once the backfill is done, the live part is just today, which is at most 1,440 rows per monitor instead of 43,200.

The hourly job and the edges

A scheduled task runs every hour and looks back seven days for anything that isn't marked, which covers a worker that was down or a deploy that landed at the wrong moment. It also summarises yesterday one more time after two hours have passed since midnight, because a result can be written a little after the day it belongs to when a check is slow or the queue is backed up, and I'd rather pay for one extra grouped query a day than have yesterday permanently short by a handful of checks.

Two smaller things came out of writing the tests. Deleting a monitor now deletes its summary rows in the same transaction, because a new monitor created later with the same name would otherwise inherit thirty days of somebody else's uptime. The summary is also pruned with the same retention cutoff as the raw results, so it can never claim to know about days whose underlying data has already been deleted. Day boundaries are pinned to UTC everywhere, which is a lesson from the August change: TruncDate uses the project's TIME_ZONE unless you tell it otherwise, and ours isn't UTC, so the summary and the live count would have disagreed about which day a check near midnight belonged to.

Backfilling on a shared box

Our production database shares a four-core machine with a few other services, so the backfill had to be polite. It's a management command that walks the last ninety days oldest first, one day at a time, and sleeps two seconds between days. It skips days that are already marked, so stopping it halfway and starting it again costs nothing. Each day took between 0.1 and 0.4 seconds to summarise across every workspace, and the whole thing finished in about four minutes, most of which was the sleeping.

Then I timed the endpoint's query directly in a production shell, bypassing the cache, on the workspace we use to monitor WatchDock itself, which has twelve monitors.

Thirty days of uptimeTime
Old: counting raw check results2,484 ms
New: summary plus today, first run201 ms
New: summary plus today, after that28 ms

The 2.5 seconds for the old query was measured on a quiet machine with warm caches. The 20 to 27 seconds in the nginx log was the same query competing with everything else on the box, which is the case that actually matters. The new version's cost depends on the number of monitors times thirty, plus today's results, so it stays flat as checks pile up instead of growing with them.

What it doesn't cover yet

The per-monitor uptime view, the one in the monitor drawer and on public status pages, still reads raw results. It shows more than a percentage: when each outage started and how long it lasted, including outages that start one day and end the next. That's doable on top of a daily summary, but it means carrying open-outage state across midnight, and I didn't want to fold that into the same change as the part that was hurting. The other half of the fix is in the app. Pulse no longer waits for its slowest request: it draws from the last copy it saved on the phone the moment the tab opens, then fills each tile in as its data lands, so even a slow network shows something immediately.

None of this is a new idea. Pre-aggregating immutable history is about the oldest trick in analytics, and I'd written down that we'd need it three months before we did. What made it worth a post is how small the reliable version turned out to be once the question of missing days was settled: one table for the numbers, one table for which days can be trusted, a reader that falls back to counting anything it can't trust, and a job that's allowed to be late. The query went from growing with every check we run to growing with the number of monitors, and the screen it was slowing down no longer waits on it.

Keep reading
One email when we publish. Nothing else.Postmortems and engineering notes, roughly twice a month.