I find myself discussing and explaining sychronous_commit with many PostgreSQL users, especially with novice users. So, I thought of noting down all the key points as a blog post which will be useful for more users. Recently I got an opportunity to talk about some related topics in our PostgreSQL Percona Tech Days.

What Is sychronous_commit All About?

This is the parameter by which we can decide when a transaction-commit can be acknowledged back to the client as successful.

So this parameter is not just about synchronous standbys, but it has a wider meaning and implication which is useful for standalone PostgreSQL instances as well. To better understand, we should look at the overall WAL record propagation and various stages from which a commit confirmation is acceptable. This allows us to opt for varying levels of durability for each transaction. The lesser the durability selection, the faster the acknowledgment, which improves the overall throughput and performance of the system.

WAL Propagation

PostgreSQL WAL (Write Ahead Log) is the record of changes/activities on the Primary side and can be considered as a journal/ledger of the changes happening in the database. The following diagram shows the flow of WAL propagation in a local primary PostgreSQL instance and a remote hot standby instance.

#database #tutorial #postgresql #synchronous commit #synchronous standby replication #wal propagation