What is the difference between fsync and synchronous_commit? I read the documentation, the only thing I understood is both of them are trying to make updates physically written to disk.
1 Answer 1
When fsync is OFF, the PostgreSQL server will never issue any fsync system call, leaving entirely to the operating system the decision as to which blocks to transfer from write cache to disk, and when. The database engine never knows what has been sync'ed or not. In case of a server crash, it has to be assumed that the database is in an inconsistent state.
When synchronous_commit is OFF, the server issues fsync call, but not necessarily immediately at commit time for each transaction. It may delay them after the commit, for a maximum of wal_writer_delay multiplied by 3. With a default configuration that would be 600ms. This allows to reduce the number of fsync calls, especially for workloads with lots of small transactions.
In case of a server crash, the transactions not yet sync'ed in the last 600ms before the crash may be rolled back, but the state of the database is always consistent.