databasepostgresqltransactionsreadonlydatabase-concurrency

Does PostgreSQL run some performance optimizations for read-only transactions


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?


Solution

  • 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