3 ms·
I briefly dabbled in financial ledgers in a previous job. We made plans to do what you seem to be describing, which is to store the transactions themselves and
by allknowingfrog 3y ago
I briefly dabbled in financial ledgers in a previous job. We made plans to do what you seem to be describing, which is to store the transactions themselves and calculate a balance as needed, but we frankly lacked expertise. I always wondered what would happen if we scaled to millions of transactions in that system.
Is there a standard strategy for optimizing balance queries against a series of transactions? I assumed that we would eventually have to cache some kind of snapshot for each account to avoid the overhead of totaling every transaction on every balance check.
- jen20 3y agoThere are quite a few standards - "closing the books" on some period (monthly, daily, hourly, depending on volume/regulatory requirements) is usually the way. There's also a good, sadly now out of print, book named "Analysis Patterns" by Martin Fowler which has a large section about account representation strategies from an object modelling perspective which also translates reasonably well to functional programming. It's annoying it's out of print, since it contains timeless information rather than the fad-du-jour!
- allknowingfrog 3y agoI'm not surprised to learn that Martin Fowler has a relevant book. Thank you for the recommendation.
- mrkeen 3y ago> Is there a standard strategy for optimizing balance queries against a series of transactions? Nope! But it's not terribly hard to design something. I demote the DB down to a caching layer, and promote the ledger as the source of truth. All writes are appended to the ledger. Something listens to the ledger and updates the DB. Reads are against the DB.
- allknowingfrog 3y agoHa. I guess finding your own solution is the joy of software. It's good to know I wasn't way off on my caching instincts.