Forum OpenACS Q&A: PostgreSQL and Journaling FS

I'm trying to get familiar with Postgres, and I ran across this statement in the PostgreSQL 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.

Would using a journaling FS like ReiserFS or ext3 protect against the type of data corruption possible in no-fsync mode?

The idea is that by simply replaying whatever is in the write ahead log in the event of a crash, all changes can be properly recorded, and the database can be back in production mode that much quicker.

I doubt that using a journaling fs would change much.  I believe that PG no longer does an fsync to disk on every transaction as of 7.1.

Oracle and DB2 at least, offer the option to use "raw" devices, where you have a disk or part of a disk that does not have any filesystem on it.  The DBMS has exclusive control over it.

1. The system writes information about changed tuples (rows) to the WAL (write-ahead log, functionally equivalent to Oracle's REDO log) when it processes UPDATE/INSERT/CREATE commands.  The WAL is fsynch'd at the end of every transaction.

Every 30 seconds (a configurable number, BTW) the dirty pages in the shared memory buffer are flushed to disk and data files are fsynch'd.  This is done to prevent the need for an unbounded WAL (WAL segments can be reused once the data being tracked has been safely fsynch'd on the disk).

This increases the speed of inserts and updates by roughly an order of magnitude when the DB's subjected to typical ones which change relatively short rows.  Inserting big chunks into large objects won't perform so well, but no one expects this to run fast anyway.

I've heard mixed results of PG on a ReiserFS partition (it only journals metadata, BTW, so won't prevent data loss on a crash).

Personally my bet's riding on RAID 1, a good UPS and a non-journaling file system ...

Ext3 works in several modes: writeback, journal, and ordered, which log respectively, metadata-only, both data and metadata, or something in between. (Ext3 FAQ). So at least with ext3, it is posible to protect data in addition to metadata, but it seems like this is already being accomplised with WAL.

So using a journaling fs may not give me additional coverage over the write-ahead log, but I'm still inclined to use it so that I can avoid a costly fsck on my data partition. However, now there is the issue Roberto raised about potential double-logging if you try to use both. It seems odd that my decision to use a journaling fs on a partition would depend on whether or not an application writing to that partition has its own journaling method. I'll have to think about that more.

I would expect to see more overhead from ext3 (or any other journalled FS), since there are additional seeks/writes to the journals.

As far as running no-fsync + journal protecting you while gaining performance: the data corruption comes in the window where you're holding the data in RAM, before the FS layer tries to write it down to disk[1]; a journalling FS won't help with that, because it only commits data to the journal periodically.  You could reduce that period, but then you'll be paying the same performance penalty you do for allowing Postgresql to run fsync() constantly.

Also, note that Reiser only journals metadata, not data; it'll leave you with consistent filesystems, but can leave you with corrupt data on them.  It's also optimised for the case of many small files (proxy caches, news servers, etc), not for a small number of large files (RDBMS).

ext3 can journal data, but it'll cost you for performance, since data is being written twice.

Really, the best answer for data you really care about is dual power supplies, one on city power, and one running through a UPS, and disks with small caches, or caches that can be write-disabled.  If IO is your bottleneck, RAID 10 is the answer.

[1] Of course, with modern discs with 4+ MB caches that buffer writes, even calling fsync leaves a window of vulnerability, since data in the cache of the disk may be lost.  This gets worse if you use hardware RAID that doesn't have battery backup for the drives and its cache, since it's unlikely you'll save your 64 MB+ of buffered writes to disc in the event of a power loss.