pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedread only transactions
5+ messages / 4 participants
[nested] [flat]
* read only transactions
@ 2010-10-12 17:24 Jon Nelson <jnelson+pgsql@jamponi.net>
2010-10-12 18:58 ` Re: read only transactions Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-12 19:33 ` Re: read only transactions Tom Lane <tgl@sss.pgh.pa.us>
2010-10-12 20:32 ` Re: read only transactions Chris Browne <cbbrowne@acm.org>
0 siblings, 3 replies; 5+ messages in thread
From: Jon Nelson @ 2010-10-12 17:24 UTC (permalink / raw)
To: pgsql-performance
Are there any performance implications (benefits) to executing queries
in a transaction where
SET TRANSACTION READ ONLY;
has been executed?
--
Jon
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: read only transactions
2010-10-12 17:24 read only transactions Jon Nelson <jnelson+pgsql@jamponi.net>
@ 2010-10-12 18:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2010-10-12 18:58 UTC (permalink / raw)
To: Jon Nelson <jnelson+pgsql@jamponi.net>; pgsql-performance
Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> Are there any performance implications (benefits) to executing
> queries in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
I don't think it allows much optimization in any current release.
It wouldn't be a bad idea to use it where appropriate, though, as
future releases might do something with it. If you include this on
the BEGIN statement, that will save a round trip.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: read only transactions
2010-10-12 17:24 read only transactions Jon Nelson <jnelson+pgsql@jamponi.net>
@ 2010-10-12 19:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-10-12 19:33 UTC (permalink / raw)
To: Jon Nelson <jnelson+pgsql@jamponi.net>; +Cc: pgsql-performance
Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> Are there any performance implications (benefits) to executing queries
> in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
No.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: read only transactions
2010-10-12 17:24 read only transactions Jon Nelson <jnelson+pgsql@jamponi.net>
@ 2010-10-12 20:32 ` Chris Browne <cbbrowne@acm.org>
2010-10-12 21:16 ` Re: read only transactions Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 5+ messages in thread
From: Chris Browne @ 2010-10-12 20:32 UTC (permalink / raw)
To: pgsql-performance
jnelson+pgsql@jamponi.net (Jon Nelson) writes:
> Are there any performance implications (benefits) to executing queries
> in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
Directly? No.
Indirectly, well, a *leetle* bit...
Transactions done READ ONLY do not generate actual XIDs, which reduces
the amount of XID generation (pretty tautological!), which reduces the
need to do VACUUM to protect against XID wraparound.
<http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#VACUUM-BASICS;
If you process 50 million transactions, that chews thru 50 million XIDs.
If 45 million of those were processed via READ ONLY transactions, then
the same processing only chews thru 5 million XIDs, meaning that the
XID-relevant vacuums can be done rather less frequently.
This only terribly much matters if:
a) your database is so large that there are tables on which VACUUM
would run for a very long time, and
b) you are chewing through XIDs mighty quickly.
If either condition isn't true, then the indirect effect isn't important
either.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
"I'm not switching from slrn. I'm quite confident that anything that
*needs* to be posted in HTML is fatuous garbage not worth my time."
-- David M. Cook <davecook@home.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: read only transactions
2010-10-12 17:24 read only transactions Jon Nelson <jnelson+pgsql@jamponi.net>
2010-10-12 20:32 ` Re: read only transactions Chris Browne <cbbrowne@acm.org>
@ 2010-10-12 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-10-12 21:16 UTC (permalink / raw)
To: pgsql-performance
Chris Browne <cbbrowne@acm.org> writes:
> jnelson+pgsql@jamponi.net (Jon Nelson) writes:
>> Are there any performance implications (benefits) to executing queries
>> in a transaction where
>> SET TRANSACTION READ ONLY;
>> has been executed?
> Directly? No.
> Indirectly, well, a *leetle* bit...
> Transactions done READ ONLY do not generate actual XIDs, which reduces
> the amount of XID generation (pretty tautological!), which reduces the
> need to do VACUUM to protect against XID wraparound.
You're right that a read-only transaction doesn't generate an XID.
But that is not a function of whether you do SET TRANSACTION READ ONLY;
it's a function of refraining from attempting any database changes.
The SET might be useful for clarifying and enforcing your intent, but
it's not a performance boost to use it, versus just doing the read-only
transaction without it.
Also, I believe that SET TRANSACTION READ ONLY isn't a "hard" read only
restriction anyway --- it'll still allow writes to temp tables for
example, which will cause assignment of an XID.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-10-12 21:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-12 17:24 read only transactions Jon Nelson <jnelson+pgsql@jamponi.net>
2010-10-12 18:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-12 19:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-12 20:32 ` Chris Browne <cbbrowne@acm.org>
2010-10-12 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
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