Re: read only transactions

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>