TL;DR — Two screens on a billing platform showed different amounts owed for the same account. One summed a stored
total_duecolumn. The other added up the 21 charge columns that total was supposed to equal. They disagreed because a penalty step updated five of those charge columns and never touched the total. On 108,521 rows the penalty step had never touched, the two agreed exactly. Every row it had touched was off by exactly the penalty. A denormalized total is a second source of truth, and every writer that changes the parts now has to change it too. When one of them forgets, nothing errors; the numbers just fork. I fixed the reader that week. The structural fix, the next time I built a table like this, was aGENERATED ALWAYS AS … STOREDcolumn that no writer can forget.
Key takeaways
- A total column that can be computed from other columns on the same row is a cache. Every writer of the parts is now also responsible for the cache, whether it knows it or not.
- Prove drift with a partition, not a sample: split rows by which code paths have touched them, and the path that forks the total shows up as the only group that disagrees.
- Two screens can disagree for a legitimate reason too. Check whether they define the quantity the same way before you call either one wrong.
- A lookup that writes turns every reader into a writer, so drift grows with traffic.
- In Postgres, a generated column is the version of a stored total that can’t drift, because nothing is allowed to write it.
Two screens, two amounts, one account
The platform is a public-sector billing system. Each bill carries 21 charge heads: the base
charge, several cesses, interest on each of those, notice fees and penalties. There is also
a total_due column on the same row, which is supposed to be their sum. I own the reports
and dashboards. The billing and payment code belongs to another team.
The report that started this was a defaulter list: accounts with money owing, by area. Staff compared it with the payment screen, the one a cashier uses to collect, and the amounts didn’t match.
The first thing worth being precise about is where each number came from:
| Screen | How it computed “owed” | Which bills it counted |
|---|---|---|
| Defaulter list | SUM(total_due) | due date before today (overdue) |
| Payment screen | sum of the 21 heads, each GREATEST(ROUND(head), 0) | bill date on or before today (issued) |
So they differed in two ways at once: which column they trusted, and which bills they counted. Those are separate problems, and only one of them is a bug.
Finding the writer that forks the total
If total_due were always the sum of its heads, the first column of that table wouldn’t
matter. So the question was which code path changes heads without changing the total.
There were three writers to these rows that mattered:
bill lookup (every time a cashier opens a bill)
├─ interest step ──────▶ writes interest heads AND total_due
└─ penalty step ───────▶ writes 5 notice/penalty heads, NOT total_due
daily 07:00 job
└─ penalty step ───────▶ writes 5 notice/penalty heads, NOT total_due
▼ every bill that has ever been opened or swept for penalties
has a total that is short by exactly its penalty
Reading code tells you what a writer can do. To prove what it did, I split production
rows in two areas by which steps had ever touched them, and compared total_due with the
sum of heads in each group:
| Rows | Penalty or interest ever applied? | Drift between total and heads |
|---|---|---|
| 108,521 | No | 0 rows |
| The rest | Penalty applied | Off by exactly the penalty on each row |
That’s the whole proof. Rows only the original bill generator had written were perfect. Rows the penalty step had written were short by precisely what the penalty step added. No other explanation produces a difference that is exactly one column’s value.
The partition matters more than the numbers. A random sample of drifting rows would have told me that totals were wrong. Grouping by write path told me which writer made them wrong, and showed that the others were innocent.
The same week, another interest job went in on the billing side. It runs every 10 minutes
during the day and refreshes the interest heads. It doesn’t update the total either. So by
the time the fix below shipped, there were two writers forking total_due, not one. That
is the general shape of this bug: it doesn’t stay at one writer, because every new writer
has to rediscover the rule.
The read that writes
The penalty and interest steps run inside the bill lookup itself. Opening a bill on the payment screen catches its interest up to today and applies any due penalty, then shows the result.
There is a guard: interest is caught up at most once per bill per day, so calling the lookup ten times doesn’t add ten days of interest. But it means every reader of a bill is also a writer of it. The drift doesn’t grow on a schedule; it grows with how many bills cashiers open. Totals I measured for the same set of bills on two consecutive days had both moved overnight.
RFC 9110 defines safe methods as
ones the client doesn’t expect to change server state. The lookup is a POST, so it isn’t
breaking the letter of that. It’s breaking the spirit: nobody calling “show me this bill”
expects the bill to change, and nobody writing a report expects their numbers to depend on
which bills were looked at today.
A legitimate difference: overdue versus issued
The second column of the first table isn’t a bug. The two screens answer different questions:
- Overdue: bills whose due date has passed. This is what a defaulter list should mean.
- Issued: bills dated today or earlier, due or not. This is what a cashier collects against, because a person paying today can pay a bill that isn’t due yet.
In one area, counting by bill date included about 3.6× as many accounts as counting by due date. Neither number is wrong. They are two definitions of “owed”.
I made the definition a single backend setting and defaulted it to match the payment screen, so the two screens agree. I turned down a request to make it a query parameter. A parameter lets every caller choose its own definition, which is how you get a third screen with a third number.
The fix I shipped, and the one I didn’t
What shipped: the defaulter list stopped reading total_due and sums the 21 heads, each
rounded and floored at zero, exactly as the payment screen does. Both screens now compute
the same figure from the same columns, and the 108,521-row group still agrees, as it
should.
-- Illustrative. BROKEN: trusts a total that some writers don't maintain.
SELECT account_id, SUM(total_due) AS owed
FROM bills WHERE due_date < current_date
GROUP BY account_id;
-- FIXED: computes owed from the parts, the same way the payment screen does.
SELECT account_id,
SUM(GREATEST(ROUND(COALESCE(NULLIF(base_charge, 'NaN'), 0)), 0)
+ GREATEST(ROUND(COALESCE(NULLIF(cess_a, 'NaN'), 0)), 0)
/* … 19 more heads … */) AS owed
FROM bills WHERE bill_date <= current_date
GROUP BY account_id;
The NULLIF(…, 'NaN') is there for a reason that surprised me. Postgres numeric supports
NaN, and the docs
say it sorts as greater than every other value. So WHERE total_due > 0 keeps rows whose
total is NaN. The old report had been counting them as defaulters.
What didn’t ship: fixing the writers. Those belong to the billing team, and changing what
a payment step writes on a live collection system is their call and their release. They’ve
said they’ll fix it at the source. Until they do, any other report that reads total_due
still disagrees, and I would rather say that plainly than imply the reader fix closed it.
That is the uncomfortable trade with a reader fix. It makes your screen right. Every other reader of the column has to make the same fix, or go on being wrong.
Generated column, trigger, compute on read, or reconcile
The structural question is whether total_due should be a column anyone can write at all.
| Option | Can it drift? | What it costs | When to use it |
|---|---|---|---|
| Plain stored column, every writer updates it | Yes, the first time one forgets | Nothing, until it drifts | Only when there is exactly one writer, enforced |
GENERATED ALWAYS AS (…) STORED | No: the database computes it and refuses direct writes | Changing the formula means rewriting the table | The total is a pure function of columns on the same row |
| Trigger recomputes it on every write | Not via normal writes, but a disabled trigger or a bulk load bypasses it | A trigger’s cost on every write | The total needs other rows or tables |
| Compute on read | No | CPU on every read; every reader must use the same formula | Small tables, or one shared view everyone reads |
| Stored + nightly reconcile job | Yes, for up to a day | A job, and a policy for which side wins | Legacy tables you can’t change yet |
The inventory ledger post argues for a materialised balance, and I still think it’s right there. The condition that makes it right is in that post’s heading: the balance changes in exactly one place. Here the total had at least four writers. The rule isn’t “never store a total”. It’s to know how many writers it has, and to stop storing it as a plain column once the answer is more than one.
What I did the next time
On 13 October I built a new table for the same platform: a daily snapshot of the interest and penalty heads. That table exists because the main one is overwritten in place, so there is no way to look back past yesterday. Its totals are generated columns. The migration says why, in a column comment I wrote for whoever touches it next:
“GENERATED … STORED rather than plain columns the loader fills: the database computes them on every insert and update and they cannot be written directly, so they can never drift from the heads. … a silently wrong total is far more expensive than one rewrite.”
-- Illustrative.
CREATE TABLE bill_heads_daily (
bill_id bigint NOT NULL,
snapshot_on date NOT NULL,
base_charge numeric NOT NULL DEFAULT 0,
cess_a numeric NOT NULL DEFAULT 0,
/* … the other heads … */
total_due numeric GENERATED ALWAYS AS (base_charge + cess_a /* + … */) STORED,
PRIMARY KEY (bill_id, snapshot_on)
);
The cost is real: changing the formula means ALTER TABLE and a rewrite. The 21 heads have
been stable, so I’ll pay that once in a while rather than meet this bug again.
Postgres’s generated-columns docs
list the limits: the expression can only use columns on the same row, and only immutable
functions.
What actually broke in production
- Nothing compared
total_duewith its heads. The drift was only visible to someone holding both screens side by side. - Two differences were tangled together. The overdue-vs-issued difference and the stale total both showed up in every comparison. Until they’re separated, the error per account looks random, because it’s the sum of two unrelated effects.
NaNpassed> 0. A report of money owing counted rows whose total wasn’t a number.- The fix is partial by design. The reader is right. The column is still wrong for anyone else who reads it, and a second writer that skips the total arrived during the same week the first was diagnosed.
FAQ
Why would a stored total disagree with the sum of its columns? Because some code path updates one or more of the parts without updating the total. Nothing in a plain column enforces the relationship, so the database accepts both writes and the row quietly holds two different answers. Find the path by grouping rows by which writers have touched them and comparing total with sum in each group.
Should I store a total column or compute it? Compute it, or make it a generated column, unless there is exactly one writer and you can enforce that. A stored plain total is a cache. It’s safe only as long as every writer maintains it, and the number of writers tends to grow over time.
What is a generated column in Postgres?
A column defined as GENERATED ALWAYS AS (expression) STORED. Postgres computes it on every
insert and update from other columns of the same row, and rejects any attempt to write it
directly. It can’t drift from its inputs. The trade is that changing the expression needs
a table rewrite.
How do I prove which code path causes the drift? Partition, don’t sample. Split rows by which steps have touched them and compare the stored total with the computed one in each group. Here, rows no penalty step had touched showed zero drift across 108,521 rows, and touched rows were off by exactly the penalty.
Should I fix the reader or the writer? Both, eventually. The writer fix stops new drift for every consumer. The reader fix makes one screen correct now, and you control it. If the writer belongs to someone else, ship the reader fix, say plainly that other readers are still affected, and hand the writer fix to its owner.
Why is a read endpoint that writes dangerous? It makes every reader a writer. State changes with traffic instead of with business events, reports depend on what was looked at today, and any bug in the write spreads at the rate people open records. Keep lookups free of side effects, and move catch-up work into a job you can monitor.
Why does WHERE amount > 0 include NaN in Postgres?
Postgres numeric treats NaN as greater than every non-NaN value, so it can be sorted and
indexed. That means NaN > 0 is true. Guard money columns with NULLIF(col, 'NaN') or a
CHECK constraint that rejects NaN.
What I’d still improve
- A nightly check of
total_dueagainst its heads, alerting on any mismatch, until the writers are fixed. Drift that nobody is measuring is drift nobody fixes. - Move interest catch-up out of the lookup into the scheduled job, so opening a bill is read-only.
- A
CHECKagainstNaNon the money columns. - Turn the main table’s total into a generated column once the writers stop writing it. That needs the billing team, and it’s the only fix that ends the class of bug rather than this instance of it.
The one idea to take away
If a number can be computed from other columns, every writer is now responsible for keeping two truths in step, and one of them won’t.
The total wasn’t wrong because someone was careless. It was wrong because a rule that lived in one person’s head (“update the total too”) had to be rediscovered by every new writer, and two of them didn’t. A generated column moves that rule into the database, where nobody has to remember it.
I write about backend reliability, Postgres and data correctness from work on food-tech,
healthcare and public-sector platforms. More on what I build and how I work, on
freezing per-order figures so reports don’t rewrite history,
and on reconciling two systems that both change the same data.
If you have a total column, run SELECT count(*) WHERE total <> sum_of_parts today.