Hacker News Reader

Comment by rejschaap | Hacker News Reader

Another solution is to not use update statements at all. Obviously this involves some changes to the tables. And it defers the balance calculation to query time.

Analogous to the example from the article:

  create table transaction (user_id integer, amount integer);
  
  insert into transaction values (1, -100);
  
  select sum(amount) as balance from transaction;

View on HN

This approach is good if you do not have to apply some check on the balance before inserting a new transaction. If you do (say verifying that balance does not go negative) then you have at least two options:

1) you make transactions serialized

  BEGIN ISOLATION LEVEL SERIALIZABLE;
  select sum(amount) as balance from transaction where user_id = 1;
  -- do something with balance
  insert into transaction values (1, -100); 
  COMMIT;

2) or you use row level locking on an additional table (say user, while this conflicts with the system table user, but for the sake of the example we assume this is not the case) to serialize the access.

  BEGIN; 
  select user_id from user where user_id = 1 for update; 
  select sum(amount) as balance from transaction where user_id = 1;
  -- do something with balance
  insert into transaction values (1, -100);
  COMMIT;

Now it may be tempting to change this to

  BEGIN; 
  select balance from user where user_id = 1 for update; 
  -- do something with balance
  update user set balance = balance + (-100) where user_id = 1; 
  insert into transaction values (1, -100);
  COMMIT;

Just for clarity: in most cases you should register your transaction event anyway (with a time stamp) for later auditing, but you now will have a risk of introducing inconsistencies between calculated balance and transactions due to the programming errors (that is partially committed transactions).

Also you should obviously create proper primary keys and indexes.

Also you should include proper error/exception handling.

Also if you already do locking, then you could also consider different locking strategies described in the article.

Another solution would be to have a view that calculates the balance from the transactions.

Then your sql code can check the balance without doing a sum (which might get expensive, and often the same value until a transaction changes it) just by joining to that view.

i.e. `WHERE total_view.balance > 0`

I like this idea as I am pro-history keeping and prefer not to make irreversible changes like an update.

What are the space concerns for keeping data in this way? For something like a retail bank (audit issues aside which probably make this necessary anyway) does space suddenly become a big factor?

This is basically the Event Sourcing pattern. You start with an initial state, keep the events relating to it that might change it, and thus the current state can always be found by replaying those events. In the example above you don't even need to replay them in order, although maintaining order is definitely useful in more sophisticated uses of this pattern.

Double-entry book-keeping is done this way. The data certainly grows over time, but generally at a predictable and manageable rate.

Most people using long-lived event-sourced data structures have some kind of snapshot/checkpoint/archive mechanism, which going back to the accounting example might simply be an opening balance.

Accounting systems usually have a "close of day" or "end of year" event at which transactions can be consolidated.

With postgresql and MySQL this can be very limiting performance wise if you need to materialize the total after every transaction on a ledger that recieves a high rate of transactions. Continuous queries ought to help with this and supposedly pipeline DB is refactoring into a postgresql module; should be interesting.

This is also the pattern used by most version control systems, which use several strategies to deal with long histories efficiently. We use them all the time to move backwards in time, and between alternative realities until we eventually merge to a consistent state all users agree on.

The space concerns depend on the usage. But in general I feel people worry too much and too early about this. People also worry about calculating balance being not efficient. You need really large amounts of data before any of this becomes a problem. When it does become a problem it can be solved by caching.

I think the more realistic concern with this approach is that some queries might get complicated when they involve more complex logic based on the balance.

> When it does become a problem it can be solved by caching.

Solved or made worse by caching?

made worse by caching

Caching may relieve some stress (on say a dashboard), but if an application is doing calculations around account balances, and uses event sourcing-like structures in the database, then caching isn't going to help as those calculations are going to still need to be done for each new balance changing event.

So caching is never worse (unless you accidentally used the cached value where you needed the live value), but isn't necessarily going to relieve a lot of DB stress.

Since it's append-only, can't you "cache" or snapshot the calculation in pieces? e.g., "Today @ 00:00 all events on this account summed up to 100." Then you just replay today's events. You don't have to recalculate anything unless you're changing your transformations. Make your checkpoints whatever interval is necessary to make on-demand event replay acceptably performant.

A retail bank would already keep tabs of ALL changes to an account anyway, if not in this format, then in separate logging tables. They don't just keep only a final tally.

Typically you do both, keep a log and keep a current table (you wouldn't recalculate every time. As for space, you archive in a regular basis (say every year). That's why you cannot check you're bank statement from 2 or 3 years ago.

Especially banks really need to maintain the transaction log. One could also argue that keeping the aggregated balances could be some DB side trigger on update operations for separate "balances" table or generated with some kind of hourly/daily batch job to not put too much load on the operational DB with every customer browsing the online bank.

Triggers get a lot of hate because of separation from application level version control etc., but these are also long discussions to get into.

edit: This article's contents are good to be included with all the most junior level application design/development training material.

You can check triggers, or any db changes, into version control. Usually is more a migration script like Active Record. And sometimes its more like a log of changes than a total current state, but its definitely doable.

Yes, I just see this kind of arguments coming from quite senior level engineers every now and then. I see the reasoning that application/system level logic should be kept separate from the database, but aggregations are a good example in my opinion of things that database engines are good at, and also as the aggregated data does not insert any new operative data/transactions this should be mostly fine.

In practice, loaded OLTP systems use a combined approach. Balances are computed at regular points in time (at the beginning of each month or quater), and final balance is efficiently calculated from the value at the nearest point and changes from the transaction log.

Khmm select sum(amount) as balance from transaction where user_id = 1;

Reply on news.ycombinator.com