default_transaction_read_only

Definition PG 18 manual

A read-only SQL transaction cannot alter non-temporary tables. This parameter controls the default read-only status of each new transaction. The default is off (read/write).

Consult SET TRANSACTION for more information.

How it works

default_transaction_read_only sets the default read-only status of new transactions. Each transaction inherits this default, but read-only mode still permits temporary-table changes and does not substitute for standby or privilege enforcement.

default_transaction_read_only is a USER-context setting and can be assigned per role, database, or session; its value is copied when a new transaction starts, so it does not rewrite a transaction already in progress.

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

Operational considerations

Changing default_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 default_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 default_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 default_transaction_read_only as a generic speed tweak. Higher isolation or long snapshots can amplify contention and vacuum pressure on a small node.