Re: performance of insert/delete/update

Josh Berkus <josh@agliodbs.com> writes:
> Thanks for the info.  As long as I have your ear, what's your opinion on the
> risk level of running with fsynch off on a production system?

Depends on how much you trust your hardware, kernel, and power source.

Fsync off does not introduce any danger from Postgres crashes --- we
always write data out of userspace to the kernel before committing.
The question is whether writes can be relied on to get to disk once
the kernel has 'em.

There is a definite risk of data corruption (not just lost transactions,
but actively inconsistent database contents) if you suffer a
system-level crash while running with fsync off.  The theory of WAL
(which remember means write *ahead* log) is that it protects you from
data corruption as long as WAL records always hit disk before the
associated changes in database data files do.  Then after a crash you
can replay the WAL to make sure you have actually done all the changes
described by each readable WAL record, and presto you're consistent up
to the end of the readable WAL.  But if data file writes can get to disk
in advance of their WAL record, you could have a situation where some
but not all changes described by a WAL record are in the database after
a system crash and recovery.  This could mean incompletely applied
transactions, broken indexes, or who knows what.

When you get right down to it, what we use fsync for is to force write
ordering --- Unix kernels do not guarantee write ordering any other way.
We use it to ensure WAL records hit disk before data file changes do.

Bottom line: I wouldn't run with fsync off in a mission-critical
database.  If you're prepared to accept a risk of having to restore from
your last backup after a system crash, maybe it's okay.

            regards, tom lane