Other commentor brings up a good point though - how do we efficiently get the balance of a certain account at a certain date time? Does using a ledger require constantly summing up columns? What happens when we have 1M, 10M, or 100M+ records?
Maybe the ledger would need to be broken into separate "completed" or "sealed" groups once we verify the balance is zero, so like the other commenter mentioned, we could have "checkpoints" where the balance is known to be zero and can calculate from that point forward, instead of from record 0.
This depends on how you implement the ledger. For pgledger, I store the balance in every entry, so to find a historical balance, you just need to find the most recent entry before that time:
https://github.com/pgr0ss/pgledger/blob/df5541dcf25f416a6a24...
I have a mental TODO to add a query function to make this simpler.
Generally, the most practical way to do is to "finalize" a number periodically (monthly and annually) when the books for the prior period have been "closed" (locked from further edits) and then sum only transactions taking place since the last closed balance. Most major ERPs do this.
And you are certain about going 1 month (or period) going back that at a particular date, at he last entry the balance was something.
Same as in paper books. Just see page total of that particular date....
Is this a good abstraction?
GianFabien•7mo ago
rwmj•7mo ago
jgalt212•7mo ago
accrual•7mo ago
But I could see this being a performance hotspot - looping over rows and tallying a running sum across many accounts seems like a waste of cycles.
What's the alternative to the "what was the balance at date/time" in non-ledger based systems?
em-bee•7mo ago
_boffin_•7mo ago
HelloNurse•7mo ago
pgr0ss•7mo ago
HelloNurse•7mo ago
Consider the example database state in the article:
Transfer ID Description ┃ user receivables available 1 order created ┃ -$10 $10 2 payment received ┃ -$10 $10 3 partial refund ┃ $5 -$5
A real application cannot go very far with this kind of "description": every type of transaction needs specific data, whose complexity dwarfs the double-entry ledger mechanism.
For example "order created" transactions need a reference to an order with details of what has been ordered, "payments" need a reference to a collections of debts that they are meant to pay and details of how they have been paid, "refunds" are actually many types and need a reference to what they are refunding.
Real accounts are also much more complex than a history of changes and a computed balance: for example, some of them need to be included in accounting calculations and reports in various roles, with largely arbitrary relationships and accounting criteria, while some are linked to real-world persons.
Comparing the two ledger implementations that the article references is instructive: Pgledger has simple ID columns for accounts and transfers, and it is up to the application to use them as foreign keys from the interesting tables, while TigerBeetle also offers some generic "user data" and "code" columns with arbitrary data and suggests having a separate "control plane" database.
pgr0ss•7mo ago