Fsync on/off For Various Filesystems/Platforms

The fsync Question
------------------

During a conversation with a fellow Postgresql colleague (Jeramey Crawford <jac(at)oogyboogy(dot)org>)
about the penalty of fsync=true, I made a comment to the effect that this is considered
to be much less in current versions.

Jeramey was curious about what the penalty actually is, so we did measurements using
some of the tables from my data warehouse benchmark setup (The 3000000 row ~ 350Mb
fact0 table in particular).

Jeramey has Postgresql running on Mac OS X and he supplied the data for this platform.
I supplied the Intel based results.

Platforms Tested
----------------
Postgres version:7.2
Platforms : G4 733Mhz Mac OS X,P2 333 Linux 2.4.14,P2 333 Freebsd 4.4
IO Subsystems : ATA-66 (G4) , ATA-33 (P2)
Filesystems : ufs (Freebsd + OS X), ext2 ext3 and Reiserfs (Linux) and hf+ (OS X)

Results
-------

Operating Sys...Filesystem..Fsync off/on...Diff
-----------------------------------------------
Freebsd 4.4.....ufs+soft....5:38..5:25 ..-:13
Linux 2.4.14....ext2........5:23..5:35 ...:12
Linux 2.4.14....ext3........5:35..6:03 ...:28
Linux 2.4.14....Reiserfs....5:50..5:53 ...:03
Mac OS X........ufs.........3:01..2:56 ...:05
Mac OS X........hfs+........9:54..9:47 ..-:07

Conslusion
----------

The results turned out to be highly filesystem dependent.

It is interesting that Freebsd ufs and Mac hfs+ record times _faster_ for fsync=true.
However I suspect that the natural variation inherient in our results could be of
a similar magnitude to the differences measured, so I didnt get too excited about it.

Linux + ext3 appears to be the real anomoly here, there is a 10% penalty for fsync=true.

We thought these results were interesting enough to share with the list.

regards

Mark