Message-ID: <3af6e9e8bb4018eade44c4ebf7290fa8fdad40d7.camel@cybertec.at>
Subject: Re: What is the cost of a tx?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ron Johnson <ronljohnsonjr@gmail.com>,
 "pgsql-generallists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Thu, 10 Sep 2026 13:54:36 +0200
In-Reply-To: 
 <CANzqJaAxk8Ub8TsON9spt4Lezh0XR-SrGQSc6mS_j1EAjL3dDA@mail.gmail.com>
References: 
	<CAE9hW8SZdCnpMtXJYE8JnUqqFK_P3kPXn7T+0LrMyPTT9RbDMQ@mail.gmail.com>
	 <CANzqJaAxk8Ub8TsON9spt4Lezh0XR-SrGQSc6mS_j1EAjL3dDA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.60.2 (3.60.2-1.fc44) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/3af6e9e8bb4018eade44c4ebf7290fa8fdad40d7.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-09-09 at 17:55 -0400, Ron Johnson wrote:
> On Wed, Sep 9, 2026 at 4:35=E2=80=AFPM Mantas Gridinas <mgridinas@gmail.c=
om> wrote:
> > In the current system that i am working on the feature in question requ=
ires between
> > 1 and 4 requests (individual tx) to a read replica for extra data (assu=
me orm usage
> > where entity b must be fetched if entity a doesn=E2=80=99t 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=E2=80=9D tx mode. I am conside=
ring to move those 4
> > requests into single transaction to reduce the amount of transactions i=
n general,
> > but without behavior change there would still be up to 4 individual que=
ries during
> > that transaction, so database doesn=E2=80=99t really have full informat=
ion 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=E2=80=99t really matter that it=E2=80=99s =
1 larger transaction that takes
> > 4 time units compared to 4 smaller transactions that take 1 time unit?
> >=20
> > I suspect that using repeatable read would push the change into single =
tx direction
> > since I wouldn=E2=80=99t be snapshotting the database per query.
>=20
> The ultimate "single transaction" would be putting all the logic in a sin=
gle (anonymous
> or named) DO procedure.=C2=A0 I don't remember if that alone would make i=
t effectively
> Repeatable Read (since the DB engine would see it as one single, very lar=
ge statement)
> without explicitly setting the transaction mode, but it sure would cut do=
wn on BIND
> overhead, etc (though at the cost of extra load on the DB server, since t=
he 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 fun=
ction
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.  Y=
ou 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 does=
n't matter
much if you run three statements in one or in three (read-only) transaction=
s.

Taking a snapshot per query is an overhead, granted.
To reduce that overhead, you would have to run the three queries in a singl=
e
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 anythin=
g,
because with the default READ COMMITTED isolation level every query would s=
till
take its own snapshot.  You'd have to start an explicit REPEATABLE READ tra=
nsaction first.

The big advantage of using a DO statement or a function is that you safe yo=
urself
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, particu=
larly on
connections with a high latency.

The big disadvantage of a DO statement would be that it cannot return any r=
esults.
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