Thread: read only transactions

Are there any performance implications (benefits) to executing queries
in a transaction where
SET TRANSACTION READ ONLY;
has been executed?


--
Jon

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

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

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>

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