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