Re: Simplifying wal_sync_method

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Marko Kreen wrote:
> >> On same topic:
> >> http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
> >> Why does win32 PostgreSQL allow data corruption by default?
>
> > It behaves the same on Unix as Win32, and if you have battery-backed
> > cache, you don't need writethrough, so we don't have it as default. I
> > am going to write a section in the manual for 8.1 about these
> > reliability issues.
>
> I thought we had changed the default for Windows to be fsync_writethrough
> in 8.1? We didn't have that code in 8.0, but now that we do, it surely
> seems like the sanest default.

Well, 8.0 shipped with commit() for fsync(), which in fact is
writethrough, but we decided that that wasn't a good default because:

o it didn't match Unix
o Oracle doesn't use that method for fsync
o we would be slower than Oracle on Win32
o it is a loss for battery backed RAID

so we moved commit() to fsync_writethrough, and found a way to do real
fdatasync as the default on Win32 in 8.0.2. This is clearly mentioned
in the release notes:

* Enable the wal_sync_method setting of "open_datasync" on Windows, and
make it the default for that platform (Magnus, Bruce) Because the
default is no longer "fsync_writethrough", data loss is possible during
a power failure if the disk drive has write caching enabled. To turn off
the write cache on Windows, from the Device Manager, choose the drive
properties, then Policies.

This was discussed on the lists extensively.

One problem with writethrough is that drives that don't do writethrough
by default are often the ones with the worst performance for this,
namely IDE drives.

Also, in FreeBSD, if you add "hw.ata.wc=0" to /boot/loader.conf, you get
write-through, but for all ATA drives. Should we recommend that?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073