From the FAQ:
---------------------------------------------------------------------
PostgreSQL runs in two modes. Normal fsync mode flushes every completed
transaction to disk, guaranteeing that if the OS crashes or loses power in
the next few seconds, all your data is safely stored on disk. In this
mode, we are slower than most commercial databases, partly because few of
them do such conservative flushing to disk in their default modes. In
no-fsync mode, we are usually faster than commercial databases, though in
this mode, an OS crash could cause data corruption. We are working to
provide an intermediate mode that suffers less performance overhead than
full fsync mode, and will allow data integrity within 30 seconds of an OS
crash.
---------------------------------------------------------------------
Has this changed since the FAQ?
I understand that Oracle & Sybase (at least) have guaranteed data
consistency in case of a crash. From this FAQ, there is a suggestion that
at least some commercial RDBMS doesn't even have guaranteed consistency?
In Sybase/Oracle this guaranteed consistency + performance is implemented
by ensuring that the transaction is guaranteed to be flushed to disk
before the data page is flushed, but that neither is guaranteed to be
flushed at all unless your application does a specific COMMIT.
Therefore, even though you might lose some transactions, you will never
lose internal consistency when using transactions. Also, you will not
receive a result from COMMIT until at least the transaction is flushed.
It would seem the FAQ is saying we always flush every transaction all the
time in fsync mode, and in no-fsync mode we don't really guarantee that
the transaction will be flushed before the data page is flushed.
Am I reading this correctly?
(in summation...) So if data consistency is the most important thing to
me, performance be damned, I still, as of 7.2.1, want to run in fsync
mode?
Thanks,
--
Tim Ellis
DBA, Gamet