What is the best practice database design for transactions aggregation?

Question

I am designing a database which will hold transaction level data. It will work the same way as a bank account - debits/credits to an Account Number.

What is the best / most efficient way of obtaining the aggregation of these transactions.

I was thinking about using a summary table and then adding these to a list of today's transactions in order to derive how much each account has (i.e their balance).

I want this to be scalable (ie. 1 billion transactions) so don't want to have to perform database hits to the main fact table as it will need to find all the debits/credits associated with a desired account number scanning potentially a billion rows.

Thanks, any help or resources would be awesome.

Answer 1

(Have been working in Banks for almost 10years. Here is how it is actually done).

TLDR: your idea is good.

Every now and then you store the balance somewhere else ("carry forward balance"). E.g. every month or so (or aver a given number of transactions). To calculate the actual balance (or any balance in the past) you accumulate all relevant transactions going back in time until the most recent balance you kept ("carry forward balance"), which you need to add, of course.

The "current" balance is not kept anywhere. Just alone for the locking problems you would have if you'd update this balance all the time. (In real banks you'll hit some bank internal accounts with almost every single transactions. There are plenty of bank internal accounts to be able to get the figures required by law. These accounts are hit very often and thus would cause locking issues when you'd like to update them with every transaction. Instead every transactions is just insert — even the carry forward balances are just inserts).

Also in real banks you have many use cases which make this approach more favourable:

• Being able to get back dated balances at any time - Being able to get balances based on different dates for any time (e.g. value date vs. transaction date).
• Reversals/Cancellations are a fun of it's own. Imagine to reverse a transaction from two weeks ago and still keep all of the above going.

You see, this is a long story. However, the answer to your question is: yes, you cannot accumulate an ever increasing number of transactions, you need to keep intermediate balances to limit the number to accumulate if needed. Hitting the main table for a limited number of rows, should be no issue.

Make sure your main query uses an Index-Only Scan.