Re:Rediscovering Transaction Processing from History and Fir...
by RaftPeople on Wednesday July 24, 2024 @08:55PM
(#41063779)
Can you expand on why sproc isn't a good solution (e.g. send set of requests, process those that are still in valid state, error those that aren't, return responses)?
Maybe knowing the volumes you are dealing would help also.
Re:Rediscovering Transaction Processing from History and Fir...
by jorangreef on Thursday July 25, 2024 @05:21AM
(#41066568)
Instead, all kinds of complicated debit/credit contracts (up to 8k financial transactions at a time, linked together atomically) can be expressed in a single request to the database, composed in terms of a rich set of debit/credit primitives (e.g. two-phase debit/credit with rollback after a timeout), to enforce financial consistency directly in the database.
On the other hand, moving the data to the code, to make decisions outside the OLTP database was exactly the anti-pattern we were wanting to fix in the central bank switch, as it tried to implement debit/credit primitives but over general-purpose DBMS. It's really hard to get these things right on top of Postgres.
And even if you get the primitives right, the performance is fundamentally limited by row locks interacting with RTTs and contention. Again, these row locks are not only external, but also internal (i.e. how I/O interacts with CPU inside the DBMS), and why stored procedures or extensions aren't enough to fix the performance.