default_transaction_deferrable

Definition PG 18 manual

When running at the serializable isolation level, a deferrable read-only SQL transaction may be delayed before it is allowed to proceed. However, once it begins executing it does not incur any of the overhead required to ensure serializability; so serialization code will have no reason to force it to abort because of concurrent updates, making this option suitable for long-running read-only transactions.

This parameter controls the default deferrable status of each new transaction. It currently has no effect on read-write transactions or those operating at isolation levels lower than serializable. The default is off.

Consult SET TRANSACTION for more information.

How it works

default_transaction_deferrable sets the default deferrable status of new transactions. The default matters only for read-only serializable transactions, which may wait for a safe snapshot and then avoid serialization failures.

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