Hello,
I want to build a small payment system. I want to store all the transactions and cross-check the user balance when he wants to make a purchase by evaluating his balance from all his transactions.
I am using postgres for most of my data storage so I thought about creating a table there with all the transactions and use it to calculate balance, however I am concerned about the performance implications when it comes to multiple users and more transactions.
Is there a better/more standardized way to achieve this?
Postgres can handle a lot. Performance will depend how you design your application, schemas/tables, indices etc.
For calculating balance, if you recalculate every time using all transactions - it will get slow. What is common is to calculate snapshots every now and then. Then to get the current balance you can do: latest_snapshot.balance + sum of all transactions since the last_snapshot
Yeah that is a great idea, I was thinking about this too!
In general I was thinking about calculating the balance on each new transaction, and it can be optimized later to use snapshots like you mentioned if the performance is an issue.
