Question: Configuration
Should I turn off fsync in Postgres?
Answered in the first paragraph. Last updated .
No, unless the entire contents can be recreated from somewhere else. Turning it off tells the server it may report work durable before the operating system has actually put it on the medium. After a crash, files can contain a mixture of old and new data, and the recovery log cannot repair that, because the log was granted the same permission to be lost. The documentation’s word for the outcome is unrecoverable corruption.
The cases where it is genuinely defensible
The documentation names them and they have one thing in common: the recovery plan is to start again. An initial bulk load you would repeat from the source. A test cluster rebuilt by a script. A derived read-only copy regenerated on a schedule. In each case a crash costs the time of a rebuild and nothing else, and the speed is real.
What disqualifies a cluster is any data that exists only there. That includes the obvious cases and one less obvious one: a reporting replica people have started writing to.
The companion setting that tempts people at the same moment protects against a different failure, a page written half-way when the power goes. Those full page images are the reason recovery can rebuild a page at all, and turning them off has the same category of consequence for the same reason.
The setting you probably wanted
If the goal is faster commits, the one that gives most of the benefit with a bounded and well-defined loss is the commit synchronisation level. It can lose a fraction of a second of recently committed transactions in a crash and cannot damage anything, and it is set per transaction so the choice can be made where it matters. Can synchronous_commit off lose data has the arithmetic.
If the goal is faster bulk writing, tables declared as unlogged skip the write-ahead log entirely for their own contents and are emptied after a crash, which is a precise and local version of the same bargain rather than a cluster-wide one.
Two closing notes. This setting requires a restart to change, so it is not something to try during an incident; the general rule is in which settings need a restart. And whatever you decide, decide it identically on the standby, because a replica configured to be fast and unsafe is a replica you cannot promote, which is the sharp end of configuration drift between a primary and its standby.