[McMini]

Could you provide an example of how you would implement this approach to store Credit/Debit events for an account? Additionally, how would you handle a scenario where there are 30,000 events on the account, and you need to calculate the balance to prevent overdraft?

[hot_gril]

Heh, you found the hard case. You want to add a denormalized table (point #6) specifically for locking on the balance, just cause Postgres/MySQL `serializable` mode is way too slow to rely on. You still keep baseline insert-only credits/debits table(s) that you insert into in the same xact, and all the usual rules apply there.

You can also do this without making such an exception. I used to keep a separate "pending" table that I'd insert into, commit, then check the balance with the pending row included before moving it to non-pending. So two transactions. That worked, problem is it was annoying. Though it was a good solution for debits/credits that involved an async external step that could fail or time out; simply ignore the pending rows that are too old and never got resolved. 30K rows is still small enough to query quickly.

[McMini]

What are the key trade-offs in your approach vs doing event sourcing?

[hot_gril]

Tbh I had to just look up event sourcing, but thanks, I'll add that to my vocabulary. Seems like my approach includes a form of event sourcing, though I'm not always storing the ordering of events, only in cases where it's needed. And that concept isn't specific to relational DBs.