The number nobody counted
It was not a count. It was a tally kept by something with no obligation to be right.
No screenshot on this one. The finding is seven.
I was doing the least glamorous job in software, which is taking an inventory before a move. Two piles of data for the same business — one old, one newer — and the only question that matters before you touch either is: what is actually in each, and where do they already agree?
So I asked the database what it had. The tooling has a perfectly good listing that gives you every table with a row count beside it, which is exactly the shape of answer the question wants. One table came back at 141 rows. The old pile had 134 of the same records.
Seven apart. Close enough to look like ordinary drift — a few rows added after the export, somebody fixing a price. Not close enough to call a match. I very nearly wrote “roughly aligned, needs reconciling” next to it and moved on to the ones that were properly broken.
Then I counted it.
count(*) 134
pg_class.reltuples 134
stats collector live tuples 141 <- what the listing reports
rows ever inserted 134
dead rows 0
last vacuum never
The table held 134. It had held 134 since the day it was filled. The two piles did not roughly align — they matched exactly, and they were the only pair in the entire comparison that did.
The seven were never there.
Here is where it comes from, and it is not a bug, which is the part worth sitting with. Postgres keeps a running tally of live rows per table as a side effect of write activity — a counter maintained by the statistics collector, bumped as things happen. It is cheap and it is approximately right, and it is reconciled against reality by housekeeping that runs later. On this table the housekeeping has never run. There was nothing to clean up, so nothing came to clean, so the counter has been sitting seven high since whenever it drifted, patiently, with no reason on earth to correct itself.
Note the second line of that output, because it is the trap inside the trap. There is a different estimate in Postgres — the one the query planner uses — and it is correct. It was corrected, automatically, ten days ago. So if you reach for the obvious fix and go refresh the statistics, you will refresh the number that was already right and the wrong one will not move.
It was not a count. It was a tally kept by something with no obligation to be right.
And I want to be honest about the shape of the near-miss, because “I nearly got a number slightly wrong” is not the story. The story is what the number was for. The whole purpose of that inventory was to find the places where the two piles already agree, because those are the places you do not have to rebuild. A false 141 does not just make one row wrong. It takes the single strongest signal in the survey — this one is done, leave it alone — and disguises it as one more thing on the list of stuff to migrate.
The error was seven. The cost would have been re-extracting a dataset that was already correct, and worse, never learning that the two systems had agreed about anything at all.
For anyone running a business rather than a database: this is the reason a number on a dashboard deserves the question where did you get that? before it gets the question is it good? A count that is generated as a by-product of something else — rows touched, emails sent, calls logged, jobs closed — is usually fine, and it is fine right up until the day you make a decision out of the small difference between two of them. The tally is not lying to you. It was just never promising what you decided to use it for.
Cheapest habit I know, and it cost me one query today: when a number is about to change what you do, count the thing once, by hand, in the most boring way available. Not every number, not every time. The one you are about to act on.
The gap was seven. The table was finished. Go and count it.