Stitching Outages Across Midnight
A monitor's 90-day history needs more than uptime per day: it needs every outage, when it started and how long it lasted, including the ones that run past midnight. Summarising that per day without losing a single outage took the query from 866 ms to 40 ms, and we checked it against 392 real outages before trusting it.
In the last post I described how we stopped counting thirty days of raw check results every time somebody opened the mobile app, by summarising each finished day once and reading the summary instead. That fixed the list views, which only need a percentage per day. It deliberately left one query alone: the 90-day history on a monitor's detail page, which is also what draws the uptime bars on public status pages. That one doesn't just want to know how much of each day a monitor was up. It wants every outage, with the minute it started and how long it lasted, and outages have the inconvenient habit of not respecting midnight.
How slow, and how often
Before changing anything I measured it in a production shell with the cache bypassed, on the twelve monitors in the workspace we use to watch WatchDock itself. Each 90-day history read about 126,000 raw check results, and the query took a median of 866 ms with a worst case of 8.5 seconds on the first, cold run. The access logs told the same story from the user's side: most loads hit the ten-minute cache and came back in about 60 ms, but one in twenty took more than a second over the previous six days, and the slowest took eleven. One in twenty isn't a crisis, but those are the loads that make a status page feel broken to somebody who's already worried enough to be looking at it.
The shortcut that wasn't worth it
My first idea was to avoid summarising outages at all. An outage can only appear on a day that had at least one failed check, and the daily summary already knows which days those are, because they're the ones where the number of successful checks is lower than the total. So clean days could come straight from the summary and only the failure days, plus the day after each one in case an outage closed just past midnight, would be read raw. It would give exactly the same answer by construction, which is a nice property for a change to a page customers show their own users.
Then I counted. Those twelve monitors had at least one failed check on a median of 11 days out of 90, and up to 17. Most of those failures are single blips that recover on the next check, but they still mark the day as one that has to be read raw, and with the day after added in, the shortcut would still read about a quarter of the rows. That's three or four times faster, which is a decent result for a small change and nowhere near what the list views got. So the outages had to go into the summary too.
What a day needs to remember
The rule for an outage stays exactly as it was: it opens at the first failed check, it closes at the next successful one, and it's counted on the day it closed. One that's still open when you look closes at that moment, on today. The difficulty is that a day's outages depend on what happened before it. If a monitor started failing at 23:58 on Tuesday and recovered at 00:04 on Wednesday, Wednesday's summary has to report an outage that started on Tuesday, and Tuesday's summary must not report it at all.
The useful observation is how little of a day actually depends on the day before. Only the run of failures at the very start of a day can belong to an outage that began earlier. Everything after that day's first successful check is decided by the day's own checks. So a finished day can be reduced to a handful of facts computed from its own rows, with nothing borrowed from its neighbours. Names are simplified here, but this is the shape of it:
1@dataclass2class DayFacts:3 total: int4 up: int5 first_at: datetime6 first_ok: bool7 lead_end: datetime | None # first success8 inner: list[Outage] # opened and closed after it9 trail: datetime | None # still open at midnight
Reading the history becomes a walk through the days in order, carrying at most one open outage from each day into the next. If a day was down from its first check to its last, the outage simply carries on. Otherwise its leading failures, or the outage carried in, close at the day's first success, the outages that opened and closed after that are added as they are, and whatever is still open at midnight becomes what gets carried into tomorrow.
1def add_day(self, day, f):2 stats = self.days[day]3 stats.total += f.total4 stats.up += f.up5 if f.lead_end is None: # down all day6 self.open = self.open or f.first_at7 return8 start = self.open or (None if f.first_ok else f.first_at)9 if start:10 stats.outages.append((start, f.lead_end))11 stats.outages.extend(f.inner)12 self.open = f.trail
The same walker also accepts raw checks one at a time, using exactly the old rule, so a single history can mix the two. A 90-day window opens part way through a day, so that partial first day is read raw, the finished days in between come from the summary, and today is read raw because it isn't finished. That's under 3,000 rows for a monitor checked every minute, instead of 126,000. A day summarised before outage detail existed is flagged as such, and the reader goes back to raw checks from that day onwards, which meant the change could be deployed before the backfill had rerun without ever showing a wrong answer.
Proving it before believing it
A summary that's subtly wrong about outages is worse than a slow one, because nobody notices until a customer's status page disagrees with their own memory of last Tuesday. So the bar was that the new path had to give exactly the same answer as reading every raw check, down to each outage's start time and its duration rounded to a tenth of a minute.
The first test generates histories at random: monitors checked every minute or every hour, bursts of failures, days with no checks at all, days that are down from start to finish, outages that start late in the evening and end the next morning. Each one is walked twice, once entirely raw and once with a random run of days in the middle replaced by their summarised facts, and the two results have to be identical. It runs four hundred of those on every build. A second test runs the real query against the database with every edge case placed deliberately, and compares it with the same query forced to read raw.
Neither of those is production data, so after deploying and re-running the backfill with outage detail, I ran both paths on all twelve real monitors, side by side, and compared the output.
| 90-day history, no cache | Before | After |
|---|---|---|
| Median query | 866 ms | 40 ms |
| Worst query | 8.5 s | 215 ms |
| Rows read per monitor | ~126,000 | under 3,000 |
| Outages, summary vs raw | 392 of 392 identical |
Every day on every monitor matched, across 392 real outages. The 215 ms was a first run against a cold disk; warm runs were between 21 and 46 ms.
What it cost
The hourly job that writes the summary used to run one grouped count per day and finish in a fraction of a second. Finding outages means reading each monitor's checks in order, so a day now takes between one and three seconds to summarise across every workspace. That's a trade I'm happy with: it happens once per day, in the background, instead of on every page load. The backfill of ninety days took about five minutes, a good part of it the pause I put between days so the shared database host stayed comfortable.
The thing I'd underline from this one is that the work wasn't the summary table, which already existed, but finding the smallest description of a day that still lets you reconstruct its neighbours exactly, and then refusing to trust it until a randomised test and the real data both agreed. Outages that cross midnight are a small fraction of all outages, and they're precisely the ones a hand-written test forgets, which is why the tests generate hundreds of histories instead of a few examples and why the final check ran on production data rather than fixtures. The detail page and every public status page now load their 90-day history in tens of milliseconds, and the rule for what counts as an outage still lives in exactly one place.