default_transaction_deferrable — PostgreSQL configuration parameter

When running at the serializable isolation level, a deferrable read-only SQL transaction may be delayed before it is allowed to proceed.

At a glance

What it does

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.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW default_transaction_deferrable; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'default_transaction_deferrable';.

Tuning guidance

This sets a session default (locale, formatting, search path or transaction behaviour) rather than a performance knob. Set it per role or database with ALTER ROLE/DATABASE ... SET so the right default follows the right workload, and prefer setting it explicitly in the application for behaviour the query results depend on.

Reference

PostgreSQL documentation — default_transaction_deferrable.