There are a lot of amazing features coming in PostgreSQL 9.6, but I’m personally very happy about a really small, simple one that helps close a long-standing user foot-gun.
commit a31212b429cd3397fb3147b1a584ae33224454a6
Author: Robert Haas
Date: Wed Apr 27 13:46:26 2016 -0400
Change postgresql.conf.sample to say that fsync=off will corrupt data.
Discussion: 24748.1461764666@sss.pgh.pa.us
Per a suggestion from Craig Ringer. This wording from Tom Lane,
following discussion.
There’s a bit of terrible advice floating around that turning fsync=off will make PostgreSQL run faster. Which is true, as far as it goes, but neglects that little risk massive data corruption on crash part. So users get bitten. I’ve tried to scrub as much of that advice from the Internet as I can or get it qualified with warnings, but I still see people advised to do it on random forums sometimes.
The docs do a good job of explaining that setting fsync=off is a bad idea, but the sample config file made it seem pretty innocuous:
#fsync = on # turns forced synchronization on or off
so users keep turning it off. Then teams like 2ndQuadrant land up doing expensive forensic data recovery on their database clusters once they crash weeks, months or years later and experience severe corruption… or notice the corruption they experienced a while ago. Or users just write off their data and start again because it’s all too hard and expensive.
To make turning fsync=off a little less attractive the sample now reads:
#fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable disk corruption)
It won’t stop someone using ALTER SYSTEM SET without realising, but there’s only so much you can do.
I’m really happy about this. It’s nice to knock off such minor improvements that have a disproportionate impact on usability and UX.
If you are ever tempted to set fsync=off, pretend it’s called eat_my_data_if_you_feel_like_it=on and see if you still want to set it. synchronous_commit=off is probably a better choice. Read the manual.