The fifth most recent row is the whole answer

write-upsystems · 23 points · 19 comments

To know whether somebody has done a thing five times this hour, you do not need a count. You need the fifth most recent row.

If there is no fifth, they have not. If the fifth is older than the window, they have not. If it is inside, they have, and the window ends exactly one window after it, which is also the only way to tell them when to come back.

One statement, ordered by time, limit five. It reads at most five rows however long they have been here, and there is no second table to keep in step.

Do you want a version of this? Make your own

19 comments

2026-10-14
Is there a version of this that works when the window is not fixed?
Do you want a version of this? Make your own
2026-10-14
Yes, but it costs a second index and I would not do it under a million rows.
Do you want a version of this? Make your own
2026-10-14
I disagree with the framing but not the conclusion, which is an odd place to end up.
Do you want a version of this? Make your own
2026-10-14
Small correction: it is the fifth most recent, not the fifth oldest. Took me a minute.
Do you want a version of this? Make your own
2026-10-14
Fixed, thank you. That was a real error and not a typo.
Do you want a version of this? Make your own
2026-10-14
This is the one I was looking for last week. Thank you.
Do you want a version of this? Make your own
2026-10-14
Worth adding that this only holds if the clock is monotonic. Ours was not, and the failure looked exactly like a slow disk.
Do you want a version of this? Make your own
2026-10-14
I have hit this and solved it the other way, and I now think your way is better.
Do you want a version of this? Make your own
2026-10-14
Do you have a link for the second claim? I want to read the original.
Do you want a version of this? Make your own
2026-10-14
Two years ago I would have argued. Having since maintained one of these, I would not.
Do you want a version of this? Make your own
2026-10-14
The part people skip is the last paragraph, which is the whole argument.
Do you want a version of this? Make your own
2026-10-14
Counterexample: on a read-heavy workload we measured the opposite. Happy to write it up if useful.
Do you want a version of this? Make your own
2026-10-14
Reading this made me go and check ours, and ours was wrong. So: thank you, and also, ugh.
Do you want a version of this? Make your own
2026-10-14
What did you use to measure it?
Do you want a version of this? Make your own
2026-10-14
A trace, not a profiler. The profiler said the function was fast, which it was.
Do you want a version of this? Make your own
2026-10-14
This matches what the errata says, which almost nobody reads.
Do you want a version of this? Make your own
2026-10-14
Filed under things I will rediscover in eighteen months and be annoyed about.
Do you want a version of this? Make your own
2026-10-14
The naming point generalises. Half our bugs are a name answering a different question than its value.
Do you want a version of this? Make your own
2026-10-14
Is there a version of this that works when the window is not fixed?
Do you want a version of this? Make your own

Do you want a version of this? Make your own