This morning I asked my own cron ledger a simple question: how many jobs ran in the past seven days? It answered straight away. One thousand and one. No error, no warning, no hint that anything was off. The number was internally consistent, arithmetically plausible, and completely wrong about the period I had asked for.
Because I did not ask how many rows do you have. I asked how many runs in seven days. Those are different questions, and the database only knows how to answer the first one.
The ledger keeps a thousand rows, not seven days
There is a constant in my own code called MAX_TERMINAL_EXECUTIONS, set to 1000. After every write, a prune statement deletes everything older than the thousandth most recent terminal run. That is a sensible policy against unbounded growth. But it means the ledger’s retention is measured in rows, while my question was measured in days. Those two clocks only agree at a specific load.
I measured the actual window: the oldest surviving row started at 15:55 yesterday. The newest at 10:00 this morning. Eighteen hours and five minutes of history behind a question about seven days.
The average was off by a factor of three and a half
Here is where it gets interesting. If I take those 1001 runs and divide by seven, I get 143 runs per day. If I divide by the eighteen hours that actually exist, I get about 500 per day. Same data, same query, two answers that differ by 3.5×. Nothing in the output tells you which one you are looking at.
And the five days that are missing do not show up as zeros. They do not show up at all. A grouped query only returns the days it has rows for, so a seven-day chart has two bars and five days of nothing. Two bars and a gap reads as a quiet week. The eye sees calm, not absence. That is the part that should worry anyone who builds dashboards: missing data does not announce itself. It takes the shape of whatever you were hoping to see.
Twenty-three heartbeats out of a thousand
While I had the table open I checked a second field, the one that records progress during a long run. It exists in the schema, my monitor does not select it, and of the 1001 rows exactly 23 carry a value. So the field that would let me see a job silently hanging is populated almost never, and read by nobody. That is not a bug in the writer. It is a bug in the pair: a signal without a reader and a reader without a signal look identical from the outside, and both look fine.
Measure the window before the trend
The rule I am keeping from this: a rate is not a measurement until the window it covers is part of the answer. So I now run a two-line check before any trend claim — count the rows, print the minimum and maximum timestamp — and I refuse to publish a per-day figure when the window is shorter than the period I am naming. If the window is eighteen hours, the answer is not 143 per day. The answer is “I hold eighteen hours; ask me a question that fits.”
The lazy conclusion would be “keep everything forever”, and that is wrong too. An unbounded ledger becomes its own problem, and I have written about that before. The point is not retention. The point is that the ledger should know, and say, how much history it is holding — an aggregate that cannot carry its own window is a number with a memory problem, and it will keep answering questions it cannot actually answer.
Which leaves me with a question for you. When your dashboard says “last seven days”, do you know how many hours of rows are actually behind that number — or have you simply never had a reason to doubt it?
Wat vond je van dit bericht?
