Re: no universally correct setting for fsync

All,

Updated docs based on tracking this discussion.  fsync through full page
writes recorded below.

============

               fsync</> configuration parameter          fsync (boolean)                  If this parameter is on, the PostgreSQL</> server       will try to make sure that updates are physically written to       disk, by issuing fsync()</> system calls or various       equivalent methods (see ).       This ensures that the database cluster can recover to a       consistent state after an operating system or hardware crash.      
             While turning off fsync is often a performance       benefit, this can result in unrecoverable data corruption in the
event       of an unexpected system shutdown or crash.  Thus it is only
advisable       to turn off  fsync if you can easily recreate       your entire database from external data.      
      Examples of safe times to turn off fsync would be       when initially loading a new database from a backup file, on a
database which isonly used for processing statistics on an hourly basis and is then
deleted,       or on a reporting read-only clone of your database which gets
recreated very       night and is not used for failover.  High quality hardware alone
is not a       sufficient justification for turning off fsync.      
             In many situations, turning off        for noncritical transactions can provide much of the potential       performance benefit of turning off fsync, without       the attendant risks of data corruption.      
             fsync can only be set in the
postgresql.conf</>       file or on the server command line.       If you turn this parameter off, also consider turning off       .               
         synchronous_commit
(boolean)           synchronous_commit</> configuration
parameter                       Specifies whether transaction commit will wait for WAL records       to be written to disk before the command returns a success</>       indication to the client.  The default, and safe, setting is       on</>.  When off</>, there can be a delay between       when success is reported to the client and when the transaction is       really guaranteed to be safe against a server crash.  (The maximum       delay is three times .)  Unlike       , setting this parameter to
off</>       does not create any risk of database inconsistency: a crash might       result in some recent allegedly-committed transactions being
lost, but       the database state will be just the same as if those
transactions had       been aborted cleanly.  So, turning
synchronous_commit</> off       can be a useful alternative when performance is more important than       exact certainty about the durability of a transaction.  For more       discussion see .                   This parameter can be changed at any time; the behavior for any       one transaction is determined by the setting in effect when it       commits.  It is therefore possible, and useful, to have some       transactions commit synchronously and others asynchronously.       For example, to make a single multi-statement transaction commit       asynchronously when the default is the opposite, issue SET       LOCAL synchronous_commit TO OFF</> within the transaction.               
         wal_sync_method (enum)           wal_sync_method</> configuration
parameter                       Method used for forcing WAL updates out to disk.       If fsync is off then this setting is irrelevant,       since WAL file updates will not be forced out at all.       Possible values are:                                  open_datasync</> (write WAL files with
open()</> option O_DSYNC</>)                                    fdatasync</> (call fdatasync()</> at each
commit)                                    fsync_writethrough</> (call fsync()</> at
each commit, forcing write-through of any disk write cache)                                    fsync</> (call fsync()</> at each commit)                                    open_sync</> (write WAL files with open()</>
option O_SYNC</>)                                 Not all of these choices are available on all platforms.       The default is the first method in the above list that is supported       by the platform.       The open_</>* options also use O_DIRECT</> if
available.       The utility src/tools/fsync</> in the PostgreSQL
source tree       can do performance testing of various fsync methods.       This parameter can only be set in the postgresql.conf</>       file or on the server command line.               
               full_page_writes</> configuration
parameter          full_page_writes
(boolean)                  When this parameter is on, the PostgreSQL</> server       writes the entire content of each disk page to WAL during the       first modification of that page after a checkpoint.       This is needed because       a page write that is in process during an operating system crash
might       be only partially completed, leading to an on-disk page       that contains a mix of old and new data.  The row-level change data       normally stored in WAL will not be enough to completely restore       such a page during post-crash recovery.  Storing the full page image       guarantees that the page can be correctly restored, but at the price       of increasing the amount of data that must be written to WAL.       (Because WAL replay always starts from a checkpoint, it is
sufficient       to do this during the first change of each page after a checkpoint.       Therefore, one way to reduce the cost of full-page writes is to       increase the checkpoint interval parameters.)      
             Turning this parameter off speeds normal operation, but       might lead to either unrecoverable data corruption, or silent       data corruption, after a system failure. The risks are similar
to turning off       fsync, though smaller, and it should be
turned off       only based on the same circumstances recommended for that parameter.      
             Turning off this parameter does not affect use of       WAL archiving for point-in-time recovery (PITR)       (see ).      
             This parameter can only be set in the postgresql.conf</>       file or on the server command line.       The default is on</>.               




--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.                                    http://www.pgexperts.com