Intermediate LevelPostgreSQL
What are synchronous_commit options (off, local, remote_write, on, remote_apply)?
❓ Interview Prompt: Explain `synchronous_commit` levels (`off`, `local`, `remote_write`, `on`, `remote_apply`), balancing transaction latency vs replica durability.
'synchronous_commit' controls when a transaction commit responds to the client relative to WAL flushing:
•
'off': Responds before WAL is flushed to disk (fastest latency, slight risk of recent write loss on crash).
•
'local': Responds after local WAL disk flush.
•
'on' (Default): Responds after WAL is written to local disk AND standby replica disk.
•
'remote_apply': Responds ONLY after standby replica applies the WAL write (guarantees immediate read consistency on replica!).
Code Implementation Example
sql
-- Set synchronous_commit to remote_apply for critical financial transaction
SET LOCAL synchronous_commit = 'remote_apply';💡Setting synchronous_commit to remote_apply for maximum read-replica consistency.
🎯 Interview Key Takeaway
Use synchronous_commit = off for low-priority logging to boost transaction throughput, and remote_apply for critical reads.
#synchronous_commit#Replication#Durability#Intermediate
Back to all PostgreSQL Questions→