Does PostgreSQL run some performance optimizations for read-only transactions

21
votes

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?

2
databasepostgresqltransactionsreadonlydatabase-concurrency
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). - Nick Barnes
@NickBarnes That matches my understanding too. READ ONLY is really more of a safety thing. - Craig Ringer
Thanks. So the deferrable transactions are the only substantial optimization then. - Vlad Mihalcea
@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
For info if you use JDBC this is what the postgres driver does: github.com/pgjdbc/pgjdbc/blob/REL42.1.4/pgjdbc/src/main/java/… - Christophe Roussy

2 Answers

11
votes

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
7
votes

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.
 */