Does PostgreSQL run some performance optimizations for read-only transactions
database, database-concurrency, postgresql, readonly, transactions
Solution
To sum up the comments from Nick Barnes and Craig Ringer in the question comments:
- The READ_ONLY flag does not necessarily provide any optimization
- The main benefit of setting the READ_ONLY flag is to ensure that no tuple is going to be modified
Problem
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?