The query plan never changed: three seconds became 200 milliseconds. The fix was to stop computing the number on read.
The endpoint returned a balance. Balances feel like stored data, but in a ledger they are a conclusion: sum every entry for the account, filter by state, subtract holds. That scan grows with account history, so response time becomes a function of how long someone has been a user. No index fixes that. You are paying for correctness on every request.
So the work moves to write time. The balance becomes a stored row, updated inside the same transaction that appends the entry. The read turns into a primary key lookup.
The cost is the honest part. A materialized number can drift: a backfill, a manual correction, a job that writes entries outside the main path. The pattern is only safe with three things. The entry and the update sit in one transaction, not two. The id or version of the last entry gets stamped on the materialized row, so you can tell what it reflects. And a recompute job re-derives from entries and alarms on a mismatch instead of quietly repairing it.
That last point is probably where most of these optimizations rot. Teams ship the cache and skip the audit, and a year later nobody can say whether the fast number is the true one.
I treat speed like this as a trade and not a win. You swap a per-request scan for an invariant you now defend forever. Worth it for balances, order book totals, catalog aggregates: numbers read far more often than they change. Not worth it for anything a human reconciles by hand.
I wrote up the drift-detection side in more detail here: https://polycratia.com/c/8a61f4a6
Where you draw that line is what I'd want to know: which numbers you still compute on read because you don't trust a stored one...