transaction_read_only

Definition PG 18 manual

This parameter reflects the current transaction's read-only status. At the beginning of each transaction, it is set to the current value of default_transaction_read_only. Any subsequent attempt to change it is equivalent to a SET TRANSACTION command.

How it works

transaction_read_only sets the current transaction's read-only status. It reflects the current transaction, begins from default_transaction_read_only, and must be set before operations that conflict with read-only mode.

transaction_read_only is session-settable but represents the current transaction; PostgreSQL restricts changes after the transaction has acquired a snapshot or performed conflicting work.

The default_ variables seed the corresponding transaction_ state. Isolation, read-only status, deferrability, retries, and snapshot lifetime must be designed together.

Operational considerations

Changing transaction_read_only in one session and assuming role defaults, database defaults, or other pooled sessions changed with it.

Changing transaction semantics as a performance experiment and silently weakening an application's consistency contract.

Letting a pooled session retain transaction-related state because checkout or rollback/reset handling is incomplete.

Changing transaction_read_only globally without a rollback plan and a client or operational compatibility test.

Workload guidance

OLAP: For reporting, consider a read-only transaction and an isolation choice that matches snapshot requirements; use deferrability only with read-only serializable work.

OLTP: Choose transaction_read_only for correctness semantics first. Keep the common OLTP path explicit, then override only transactions whose consistency contract justifies different blocking or retry behavior.

SMALL: Do not change transaction_read_only as a generic speed tweak. Higher isolation or long snapshots can amplify contention and vacuum pressure on a small node.