27

According to the reference documentation the READ ONLY transaction flag is useful other than allowing DEFERRABLE transactions?

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups.

Does the database engine runs other optimizations for read-only transactions?

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • 4
    My understanding is that read-write transactions carry some overhead, but that you don't incur this overhead until you actually write something. In other words, in terms of performance, a `READ ONLY` transaction should be the same as a `READ WRITE` transaction which only contains reads. This stems from the way Postgres handles XID assignment (some info on this [here](https://wiki.postgresql.org/wiki/Hint_Bits)). – Nick Barnes Oct 26 '15 at 02:42
  • 1
    @NickBarnes That matches my understanding too. `READ ONLY` is really more of a safety thing. – Craig Ringer Oct 26 '15 at 06:08
  • 1
    Thanks. So the deferrable transactions are the only substantial optimization then. – Vlad Mihalcea Oct 26 '15 at 06:19
  • @VladMihalcea I am wondering what substantial optimization is provided by using `deferrable`. The docs say "deferrable ... may be delayed before it is allowed to proceed ... once it begins ... it does not incur any of the overhead required to ensure serializability; so serialization code will have no reason to force it to abort ... making this option suitable for long-running read-only transactions". There is definitely benefit to not being canceled, but is that potential delay trade-off worth it for `reduced serialization overheads`? Not for a short-running query. – Davos Nov 14 '18 at 04:57
  • For info if you use JDBC this is what the postgres driver does: https://github.com/pgjdbc/pgjdbc/blob/REL42.1.4/pgjdbc/src/main/java/org/postgresql/jdbc/PgConnection.java#L688-L704 – Christophe Roussy Nov 05 '19 at 15:06
  • @NickBarnes I don't really understand how what you said (w.r.t. `READ ONLY` and `READ WRITE` transaction performance) follows from the way Postgres handles XID or from the link you provided. Would you mind explaining a bit or linking some supplementary material if you have any? – AmagicalFishy Jan 07 '23 at 02:36

2 Answers2

15

To sum up the comments from Nick Barnes and Craig Ringer in the question comments:

  1. The READ_ONLY flag does not necessarily provide any optimization
  2. The main benefit of setting the READ_ONLY flag is to ensure that no tuple is going to be modified
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • 1
    Additionally, postgres has an optimization related to transaction id of read only transactions. Postgres gives virtual transaction id instead of plain transaction id. It could improve performance because less transaction ids will be created that will speed up freezing process to avoid wraparounds – Almas Abdrazak Jun 29 '20 at 16:54
  • 1
    @AlmasAbdrazak, yes but that optimization happens whether or not the transaction is explicitly made read-only. A transaction that happens to not make any modifications doesn't generate a real transaction ID either. https://www.postgresql.org/message-id/7011.1286918180@sss.pgh.pa.us – Paul Draper May 17 '21 at 04:12
  • 1
    @PaulDraper I didn't know , thanks for the reference – Almas Abdrazak May 17 '21 at 17:58
9

Actually, it does. Let me just cite source code comment here:

/*
 * Check if we have just become "RO-safe". If we have, immediately release
 * all locks as they're not needed anymore. This also resets
 * MySerializableXact, so that subsequent calls to this function can exit
 * quickly.
 *
 * A transaction is flagged as RO_SAFE if all concurrent R/W transactions
 * commit without having conflicts out to an earlier snapshot, thus
 * ensuring that no conflicts are possible for this transaction.
 */
Yaroslav Schekin
  • 119
  • 1
  • 5
  • 2
    The comment you're pulling out belongs to a part of the codebase dedicated to snapshot isolation. The default transaction isolation level is `READ COMMITTED`, which does not benefit from this optimization. – num1 Jun 25 '20 at 20:40