fsync (boolean)

If this parameter is turned on, the PostgreSQL server will try to ensure that updates are physically written to disk by issuing fsync() system calls or using multiple equivalent methods (see wal_sync_method).

This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

Although closing fsync often yields performance gains, irrecoverable data damage can occur when power failures or system crashes occur.

Therefore, closing fsync is recommended only when it is easy to reconstruct the entire database from external data.

Examples of environments that can safely shut down fsync include initial loading of a new database cluster from a backup file, and using a database cluster to process a batch of data after the database has been deleted and rebuilt.

Or a read-only database clone that is often reconstructed but not used for failure backup. High-quality hardware alone is not enough to justify shutting down fsync.

When fsync is changed from closed to open, all modified buffers in the kernel need to be forced into persistent storage in order to recover reliably. This can be done at multiple times:

When the cluster is closed or when fsync is opened because initdb sync-only is running, when sync is running, when file system is uninstalled, or when server is restarted.

In many cases, closing synchronous_commit for unimportant transactions can provide a lot of potential performance benefits of closing fsync, but not at the same time, closing fsync can provide many potential performance advantages.

There is no risk of data corruption.

fsync can only be set in the postgresql.conf file or on the server command line. If you close this parameter, consider closing full_page_writes as well.

synchronous_commit (enum)

Specifies whether a transaction needs to wait for WAL records to be written to disk before the command returns "success" instructions to the client. The legal values are on, remote_write, local, and off.

The default and safe setting is on. When set to off, there is a delay between reporting success to the client and really ensuring that transactions are not threatened by server crash (the maximum delay is three times that of wal_writer_delay).

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.

It's like these transactions have been cleanly suspended. Therefore, closing synchronous_commit can be an effective alternative when performance is more important than fully ensuring transaction durability. See section 30.3 for more discussion.

If synchronous_standby_names is set, this parameter also controls whether the WAL record waiting for the transaction is copied to the backup server by the transaction submission. When this parameter is set to on,

Transactions on the primary server do not commit until a reply from the current synchronized backup server indicates that the backup server has received the transaction's submission record and flushed it to disk. This ensures that transactions will not be lost.

Unless both primary and backup servers suffer from database storage corruption. When this parameter is set to remote_write, the submission will wait until a reply from the current synchronized backup server indicates that the server

The transaction submission record has been received and written to the backup server's operating system, but the data does not necessarily reach the stable storage on the backup server. This setup is sufficient to ensure that the data is available on the backup server.

PostgreSQL instances are saved when they crash, but there is no guarantee that data can be maintained when the backup server suffers an OS-level crash.

When using synchronous replication, it will be sensitive to waiting for local write disk and WAL record replication, or to allowing transaction asynchronous commit. However, setting local can be used for transactions that want to wait for a local write to the disk but not for synchronous replication.

If synchronous_standby_names are not set, on, remote_write, and local all provide the same level of synchronization: transaction commits wait only for local write disks.

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 its submission. Therefore, some transactions can be submitted synchronously, while other transactions can be submitted asynchronously. For example, when the default is the opposite,

Implement an asynchronous commit of a single multi-statement transaction, in which SET LOCAL synchronous_commit TO OFF is issued.

Let's do an experiment.

mondb=# \d tmp_wal_compress
         Table "public.tmp_wal_compress"
   Column    |         Type          | Modifiers 
-------------+-----------------------+-----------
 id          | bigint                | 
 random_char | character varying(50) | 
 random_int  | bigint                | 


#####################################################

mondb=# truncate table tmp_wal_compress;
TRUNCATE TABLE
Time: 3.429 ms


mondb=# select name,setting from pg_settings ps where ps.name in ('synchronous_commit','fsync');
        name        | setting 
--------------------+---------
 fsync              | on
 synchronous_commit | on
(2 rows)

Time: 2.484 ms

mondb=# insert into tmp_wal_compress select generate_series(1, 10000000) as id, md5(random()::text) as info ,trunc(random()*10000000)
mondb-# ;
INSERT 0 10000000
Time: 48370.781 ms


#####################################################

mondb=# truncate table tmp_wal_compress;
TRUNCATE TABLE
Time: 39.273 ms


mondb=# select name,setting from pg_settings ps where ps.name in ('synchronous_commit','fsync');
        name        | setting 
--------------------+---------
 fsync              | off
 synchronous_commit | on

Time: 2.484 ms

mondb=# insert into tmp_wal_compress select generate_series(1, 10000000) as id, md5(random()::text) as info ,trunc(random()*10000000)
mondb-# ;
INSERT 0 10000000
Time: 40566.090 ms



#####################################################

mondb=# truncate table tmp_wal_compress;
TRUNCATE TABLE
Time: 3.429 ms


mondb=# select name,setting from pg_settings ps where ps.name in ('synchronous_commit','fsync');
        name        | setting 
--------------------+---------
 fsync              | on
 synchronous_commit | off
(2 rows)

Time: 2.559 ms


mondb=# insert into tmp_wal_compress select generate_series(1, 10000000) as id, md5(random()::text) as info ,trunc(random()*10000000)
;
INSERT 0 10000000
Time: 43026.087 ms


#####################################################

mondb=# truncate table tmp_wal_compress;
TRUNCATE TABLE
Time: 9.112 ms


mondb=# select name,setting from pg_settings ps where ps.name in ('synchronous_commit','fsync');
        name        | setting 
--------------------+---------
 fsync              | off
 synchronous_commit | off
(2 rows)

Time: 2.559 ms

mondb=# insert into tmp_wal_compress select generate_series(1, 10000000) as id, md5(random()::text) as info ,trunc(random()*10000000)
;
INSERT 0 10000000
Time: 40418.334 ms

It can be seen from simple experiments.
When fsync is set from on to off, performance improves by about 15%. Although the performance is improved, the risk is increased.
When synchronous_commit is set from on to off, the performance improvement is not particularly significant.