On Wed, 2026-09-09 at 17:55 -0400, Ron Johnson wrote: > On Wed, Sep 9, 2026 at 4:35 PM Mantas Gridinas <[email protected]> wrote: > > 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.)
That's not entirely accurate; still the idea to use a DO statement or a function is often a good one. But let me answer the question first. You mentioned that you want to run these transactions on a read replica. You cannot modify any data there, so PostgreSQL won't assign a transaction ID, and it won't have to go through a commit, flush WAL etc. So that would be cheap, and it doesn't matter much if you run three statements in one or in three (read-only) transactions. Taking a snapshot per query is an overhead, granted. To reduce that overhead, you would have to run the three queries in a single REPEATABLE READ transaction. But the overhead of taking a snapshot is not huge, unless you have gazillions of database connections. Running the three queries in a single DO statement would not change anything, because with the default READ COMMITTED isolation level every query would still take its own snapshot. You'd have to start an explicit REPEATABLE READ transaction first. The big advantage of using a DO statement or a function is that you safe yourself a number of client-server round trips. Calling a function (or executing a DO statement) is a single client-server round trip. That can be a big advantage, particularly on connections with a high latency. The big disadvantage of a DO statement would be that it cannot return any results. You'd have to create a function for that. So, in short: - running several queries in a single REPEATABLE READ trasaction gives you a small performance benefit (perhaps less than you hope) - running several queries in a function saves you client-server round trips, which can be a big improvement Yours, Laurenz Albe
