I sell a social-data API. Behind it, a cron job checks each route once an hour and writes what it got into a table called checagem_rota. A database function reads that table and computes the uptime percentage per route. I had been reading that table as one row per check. It is one row per attempt, and some rounds make two attempts.
All numbers below were first read on 2026-09-30 between 13:17 and 13:23 UTC, from the production database, and re-run on 2026-10-02 against the same window (rows up to 2026-09-30 13:05 UTC). Every figure reproduced. One sentence about the alerts did not survive the re-read, and it is corrected below. (My code comments are in Portuguese; quotes are my translations.)
What the status function divides
uptime_das_rotas computes, per route:
'perguntas', count(*) filter (where c.cached is not true),
'respondeu', count(*) filter (where c.status = 200 and c.cached is not true),
'pct', round(100.0 * count(*) filter (where c.status = 200 and c.cached is not true)
/ nullif(count(*) filter (where c.cached is not true), 0), 1)
Rows in the denominator. Cached rows are excluded, which is correct: the table's own comment says cached = false is the only state that proves the upstream answered.
The assumption underneath is that one row is one question asked. That is the thing worth checking.
One round, sometimes two rows
Over the whole history of the table in that window (4,245 non-cached attempts across twelve routes, from 2026-09-15 16:16 to 2026-09-30 13:03 UTC, with the monitor's own 401 and 402 responses left out), the hourly rounds hold 4,190 first attempts and 55 second attempts. No round has a third.
The 55 are not scattered at random:
with base as (
select endpoint_id, date_trunc('hour', em) as h, status,
row_number() over (partition by endpoint_id, date_trunc('hour', em)
order by id) as tentativa
from checagem_rota
where cached is not true
and status not in (401, 402)
)
select count(*) filter (where tentativa = 1) as rodadas,
count(*) filter (where tentativa > 1) as extras,
count(*) filter (where tentativa > 1 and status = 200) as extras_ok
from base;
Of the 55 rounds that made a second attempt, the number whose first attempt succeeded is zero. Thirty-seven of the 55 second attempts returned 200. Every second attempt used a different handle from the first.
So the extra row is a retry. It exists only in rounds where something already failed, and it usually works.
What that does to the number
medium/profile is where it shows. Whole history of that route, cached excluded: 378 attempts in 337 rounds, 284 successes.
- Per attempt, which is what the status function returns: 284 / 378 = 75.1%.
- Per round: 284 of 337 rounds got an answer = 84.3%.
The numerator is the same number twice. Only the denominator moves, by the 41 retries. Over the last seven days of that window the same route reads 137 / 189 = 72.5% per attempt against 137 / 152 = 90.1% per round, a gap of 17.6 points.
Across all twelve routes the gap is small, because most routes never retry: 97.1% per attempt against 98.4% per round. Five of the twelve have no second attempt in the whole history, and five have exactly one.
Neither percentage is wrong. They answer different questions. "What share of my requests to Medium got through" is 75.1%. "What share of hours could I have served a Medium profile" is 84.3%. Nothing in the output says which one it is, and I had been reading it as the second.
The trap I nearly walked into
Then I went to compare the three Medium handles the monitor rotates, because that is the interesting question: is the refusal about the address or about the account?
Comparing them over every row is not safe, because the rows come from two populations. A first attempt is an unconditioned sample. A second attempt is conditioned on a failure seconds earlier, from the same address. Pooled by position: 22.3% blocked for first attempts (75 of 337), 36.6% for second attempts (15 of 41). The all-rows figure, 23.8% of 378, describes neither population.
So I restricted the per-handle comparison to first attempts, where the rotation is even: 111, 108 and 118 rounds. There the three read 14.4%, 27.8% and 24.6% blocked (16, 30 and 29 refusals). So the difference between benjaminhardy and the other two survives the correction. It is a real difference. Checking it for the right reason is the only part of this episode I would repeat unchanged.
What I am not claiming
- I did not read the monitor's code for this post. The retry is inferred from the row pattern, not read from a source file. And it is not a rule I can quote:
medium/profilealso has 35 rounds that ended in 451 with no second attempt, so something decides when to retry and I do not know what. - I am not claiming the retry hid an incident. That was my next fear, so I measured it. The route alert, as the function is written today, opens when the hours since the last success exceed the route's tolerance, which for this route is 12. The largest gap without a success in the window is 7.0 hours counting every attempt, and 7.0 hours counting first attempts only. The retries did not move it.
- The four old incidents came from a different rule. My first draft said four incidents were opened for this route between 2026-09-17 and 2026-09-20 and that I could not tell whether they would open today. Re-reading the rows, their text says "3 consecutive checks without upstream success". That is an earlier rule, not the tolerance rule the function runs now. The four opened between 2026-09-17 21:04 and 2026-09-20 08:04 UTC, all closed within four hours, and none has opened since. They tell me nothing about the current rule either way.
- I am not claiming why one handle is refused less often. 14.4% against 27.8%, over 111 and 108 rounds. I have no mechanism to offer.
- I am not claiming anything from the per-handle retry rates. Those samples are 16, 18 and 7 attempts. I gave the pooled figure and left the split out on purpose.
- Nobody was misled, because I have no customers. The only reader of that percentage was me.
- I have not fixed it. I have decided the fix:
checagem_rotaneeds a round identifier and an attempt number, so that whoever reads it picks the unit instead of inheriting mine. Neither column exists today, and the function still divides by rows.
This is a different hole from the one in my uptime monitor that was green 284 times. That post was about routes the monitor never checks. This one is about how the checks it does make get counted. Both belong to the same family as a disabled source and a broken source sharing one row: the table answers a question nobody asked it, and the answer looks fine.
Run this on your own table
Any table that gets a row per attempt has this inside it, and the test needs to know nothing about my schema:
select count(*) as linhas,
count(distinct (grupo, periodo)) as eventos,
count(*) - count(distinct (grupo, periodo)) as tentativas_extra
from sua_tabela;
If the third number is not zero, every percentage computed over that table is per attempt, and your retry logic is voting in the denominator. Then ask the question that settles it: are the extra rows independent of the rest? Retries never are. They exist because something failed, so they carry that failure with them, and averaging them together with first attempts produces a blend of two populations, weighted by how often the first one broke.
The general shape: a rate needs its unit named, and the unit is chosen by whatever wrote the rows, not by whoever divides them afterwards. Mine was chosen by a retry I had forgotten I wrote.