Is postgres a good choice for transaction operations?

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.