Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a success indication to the client. Valid values are
off. The default, and safe, setting is
off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. (The maximum delay is three times wal_writer_delay.) Unlike fsync, setting this parameter to
off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning
synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see wal-async-commit.
If synchronous_standby_names is non-empty, this parameter also controls whether or not transaction commits will wait for their WAL records to be replicated to the standby server(s). When set to
on, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to disk. This ensures the transaction will not be lost unless both the primary and all synchronous standbys suffer corruption of their database storage. When set to
remote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s). When set to
remote_write, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and written it out to their operating system. This setting is sufficient to ensure data preservation even if a standby instance of PostgreSQL were to crash, but not if the standby suffers an operating-system-level crash, since the data has not necessarily reached stable storage on the standby. Finally, the setting
local causes commits to wait for local flush to disk, but not for replication. This is not usually desirable when synchronous replication is in use, but is provided for completeness.
If synchronous_standby_names is empty, the settings
local all provide the same synchronization level: transaction commits only wait for local flush to disk.
This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction.
- Postgresql - Commit not happening when synchronous_commit is OFF
- PostgreSQL. Slow Prepare Transaction and Commit Prepared
- Is there a way to apply synchronous_commit to off on postgres-sqlalchemy?
- Two postgresql server with same configuration, different performance
- Postgres: Checkpoints Are Occurring Too Frequently