pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is the cost of a tx?
3+ messages / 3 participants
[nested] [flat]
* What is the cost of a tx?
@ 2026-09-09 20:34 Mantas Gridinas <mgridinas@gmail.com>
2026-09-09 21:55 ` Re: What is the cost of a tx? Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mantas Gridinas @ 2026-09-09 20:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
I am also aware that I could just measure the change but sadly I do not
want to test in production, and this question is more about learning about
other’s experiences.
--
// Mantas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What is the cost of a tx?
2026-09-09 20:34 What is the cost of a tx? Mantas Gridinas <mgridinas@gmail.com>
@ 2026-09-09 21:55 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-10 11:54 ` Re: What is the cost of a tx? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 3+ messages in thread
From: Ron Johnson @ 2026-09-09 21:55 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, Sep 9, 2026 at 4:35 PM Mantas Gridinas <mgridinas@gmail.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.).
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What is the cost of a tx?
2026-09-09 20:34 What is the cost of a tx? Mantas Gridinas <mgridinas@gmail.com>
2026-09-09 21:55 ` Re: What is the cost of a tx? Ron Johnson <ronljohnsonjr@gmail.com>
@ 2026-09-10 11:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2026-09-10 11:54 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, 2026-09-09 at 17:55 -0400, Ron Johnson wrote:
> On Wed, Sep 9, 2026 at 4:35 PM Mantas Gridinas <mgridinas@gmail.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-10 11:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-09 20:34 What is the cost of a tx? Mantas Gridinas <mgridinas@gmail.com>
2026-09-09 21:55 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-10 11:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox