Hacker News Reader

Comment by jen20 | Hacker News Reader

If you're storing a balance, rather than a ledger of the transactions which caused that balance to be the case, you're probably already off to a bad start when it comes to design.

Just imagine if you called your bank and asked them why my balance was $500, and they said "I don't know, hit F5 and see if it changes"...

View on HN

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.

There 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!

I'm not surprised to learn that Martin Fowler has a relevant book. Thank you for the recommendation.

> 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.

Ha. 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.

When it comes to this topic, almost all examples don't match how things work in the real world where they are eventually consistent.

For example, my bank lets me go negative and then if I don't settle by the end of the day, overdraft protection will kick in for a little more than that negative amount.

Indeed - seeing account balance used as an example of database transactions is a red flag to me, and would absolutely cause me to dig deeper into whether the author/speaker understands how banking actually works.

Hello, I wrote the post.

I don’t claim to be a banking expert, but I did work at a payment processor that handled real money, and we used “select for update” extensively.

We also (obviously) used a ledger, but “just let everyone overdraft whenever” wasn’t something that the executives of the company thought was a good idea. So we did try and prevent insufficient funds errors as much as possible.

I personally witnessed several scenarios where the company lost a notable amount of money, (not only rated to concurrency bugs of course) and it’s a pretty bad feeling.

Anyway, I’m legitimately curious to know from people who worked at larger banks perhaps. How is weak transaction isolation not a problem for banks?

Can you elaborate? Does your bank allow you to overdraft on $50k for example? I think it’s more complicated than “eventual consistency solves all problems.”

I assume there's some point where they will start declining transactions and it could be some fixed number or some complex calculation based on all the data the bank has about my finances.

My point is that these examples are bad ones because they don't match the real world, which is messy and complex and inconsistent.

I hear what you're saying, the real world is often complex. However, I don't agree that means banking examples aren't useful, because they do lay out the problem that all banks have to consider, one way or another.

There are many ways of solving the consistency problem, but understanding what consistency and serializability is means that you'll be better educated in coming up with solutions.

Also, I believe that all else being equal, you would want serializability. It simplifies everything. There's a new [financial database under development named TigerBeetle](https://docs.tigerbeetle.com/design/consistency). They chose to implement strict serializability by default because:

> Strict consistency guarantees (at the database level) simplify 1) application logic and 2) error handling farther up the stack.

So the banks of today may have to deal with inconsistency, but that doesn't mean the banks of tomorrow want to.

Reply on news.ycombinator.com