Re: What is the cost of a tx?

On Wed, 2026-09-09 at 17:55 -0400, Ron Johnson wrote:
> On Wed, Sep 9, 2026 at 4:35 PM Mantas Gridinas <mgridinas(at)gmail(dot)com> 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