In the current system that i am working on the feature in question requires between 1 and 4 requests (individual tx) to a read replica for extra data (assume orm usage where entity b must be fetched if entity a doesn’t exist ant so on). At scale this ends up being between 250k (best case) to 1m (worst case) requests per minute. The individual tx setup uses "read committed” tx mode. I am considering to move those 4 requests into single transaction to reduce the amount of transactions in general, but without behavior change there would still be up to 4 individual queries during that transaction, so database doesn’t really have full information about what I want from it. Is the logic sound to perform the change or (at my scale) the overhead is negligible where it doesn’t really matter that it’s 1 larger transaction that takes 4 time units compared to 4 smaller transactions that take 1 time unit?
I suspect that using repeatable read would push the change into single tx direction since I wouldn’t be snapshotting the database per query.
The ultimate "single transaction" would be putting all the logic in a single (anonymous or named) DO procedure. I don't remember if that alone would make it effectively Repeatable Read (since the DB engine would see it as one single, very large statement) without explicitly setting the transaction mode, but it sure would cut down on BIND overhead, etc (though at the cost of extra load on the DB server, since the application logic would happen there instead of in the app server.).
