synchronous_commit
Specifies whether a transaction needs to wait for the WAL record to be written to disk before the command returns a "success" indicating to the client.
The legal value is {local,remote_write,remote_apply,on,off}
The default and safe setting is on.
Unlike fsync, setting this parameter to off does not create a risk of database inconsistency: an operating system or database crash may cause some recently reported transactions to be lost, but the database state is consistent, just as those transactions have been cleanly aborted. Therefore, when performance is more important than ensuring transaction persistence completely, closing synchronous_commit can be used as an effective alternative.
This parameter can be modified at any time; the behavior of any transaction is determined by the settings that take effect at the time of submission. Therefore, some transactions can be submitted synchronously while others can be submitted asynchronously. For example, when the default is the opposite, an asynchronous commit of a single multi-statement transaction is implemented, in which SET LOCAL synchronous_commit TO OFF is issued.
Single instance environment
on：
When the database submits a transaction, wal first writes to wal buffer and then writes to wal log file. Set to on means that when submitting the transaction, you need to wait for the local wal to write to wal log before returning to the client successfully.
on is the default setting, the database is very safe, but has some performance loss.
off：
When the database commits transactions, there is no need to wait for the local wal buffer to write to the wal log, and then returns to the client successfully. Setting it to off will bring some risks to the database: a small number of newly submitted transactions may be lost when the database is down. After the database restarts, it will be considered that these transactions terminate abnormally and will rollback.
It is suitable for scenarios where database accuracy is not high and database performance is pursued.
local：
The meaning of local is similar to on, which means that when submitting a transaction, you need to wait for the local wal to be written before returning to the client successfully.
Stream replication environment
on：
It means that when the stream copying the main library submits a transaction, it needs to wait for the backup library to receive the wal log stream sent by the main library and write it to the wal file before returning it to the client successfully. Simply put, on means that the local wal has been dropped, and the wal of the backup library has also been dropped, and there are two persistent wals, but the backup library has not completed the redone yet.
This option brings higher transaction response time.
remote_write：
It means that when the stream replication master library submits transactions, it is necessary to wait for the backup library to receive the wal log stream sent by the master library and write it to the backup node operating system cache, and then return to the client successfully. In this case, there will be no risk of transmittted wal log loss when the backup library is closed abnormally. However, the backup library operating system has an abnormal downtime and there is a risk of transmittted wal loss. At this time, wal may not have been fully written to the backup node wal file. Simply put, remote_write means that the local wal has fallen out of disk, and the backup library's wal is still in the backup library operating system cache, which means that there is only one persistent wal.
This option brings lower transaction response time.
remote_apply：
It means that when the stream copying the main library submits a transaction, it needs to wait for the backup library to receive the wal stream sent by the main library and write to the wal file. At the same time, the backup library has completed redone, and then it returns to the client successfully. Simply put, remote_apply means that the local wal has been dropped, the backup library wal has been dropped and redone has been completed. This setting ensures that there are two persistent wals, and the backup library has also completed redone.
This option brings the highest transaction response time.
Supplement: Some parameters of postgresql wal log
fsync
fsync: Controls whether wal log refresh is turned on and refresh to disk. This parameter controls the refresh method of wal_sync_method parameter. If fsync is off, then wal_sync_method method is meaningless.
If this parameter is not enabled, the instance may have block break after the failure due to the wal log block not being refreshed to disk permanent storage (oracle calls it block currency)
wal_sync_method
wal_sync_method: wal log refresh method, optional value is open_datasync/fdatasync/fsync/fsync_writethrough/open_sync
Linux system defaults to fdatasync, and those starting with open are not supported on some systems
wal_buffers
wal_buffers: wal buffer, default to -1, shared_buffer with a size of 1/32, the minimum is no less than 64k, and the maximum is no more than one wal_segment (default 16M size). Generally, it is only possible to keep the default, because after wal_writer_delay (default 200ms), this cache will always be refreshed and cleared, and if the settings are too large, it will not be used.
wal_writer_delay
wal_writer_delay: As mentioned earlier, this is a bit similar to the 1s timed log writing strategy of oracle and mysql. The data in the wal log buffer will be flushed every so long, and then sleep, and then swipe it after reaching the point, which will repeat in this cycle.
commit_delay
commit_delay: The delay time of commit. If this parameter is set, it will delay after commit for a period of time before committing. This mechanism can merge other transactions and then commit together for group commit. However, the number of merged transactions is limited. At least the commit_siblings parameter is required to wait for commit. All transactions will be delayed when there are a large number of transactions, and if there are few transactions, it will not be delayed again.
commit_siblings
commit_siblings: The minimum number of group submissions, this parameter has been explained above
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.