CODEWITHSUNDEEP
sqldatabasealgorithmdata-structures
Farhad
Farhad

Reputation: 324

Efficient algorithm for real-time account balance calculation from transactions without storing balance

I'm working on a wallet management system where I need to calculate an account's balance in real-time by summing all its transactions, without storing the balance directly in the database. The system handles deposits and withdrawals, and the balance must always be derived from the sum of completed transactions (e.g., ignoring pending or failed ones). Heres the setup:

Transactions are stored in a relational database (e.g., PostgreSQL) with columns like account_id, amount (positive for deposits, negative for withdrawals), and status (Pending, Completed, Failed).

Balance is calculated as

SELECT SUM(amount) FROM transactions WHERE account_id = ? AND status = 'Completed

The system must scale to potentially millions of transactions per account and support frequent balance queries efficiently. Currently, I'm using a straightforward SQL query with indexes on account_id and status, but I'm concerned about performance as transaction volume grows—summing millions of rows could become slow based on standard database benchmarks. I've considered in-memory data structures like segment trees (one per account, O(1) query time), but I'm unsure if it's correct.

What algorithms or data structures have been proven to meet this scenario?

Upvotes: 0

Views: 66

Answers (0)

Related Questions