I'm Sorry,
that I wrote that the option would risk the LOG
persistency with PostgreSQL.
I should have asked instead, that how you have taken this into account.
Tom Lane's email below convinces me, that you have taken the metadata
only journalling into account and still fulfill the persistency
of committed transactions.
This means, that Ext3 with data=writeback is safe with PostgreSQL even with
a hardware reset button. Metadata only journalling is faster, when it
can be used.
I didn't know, that any database can keep the database guarantees
with the metadata only journalling option.
I looked at your problem.
One of the problems is that you need to keep the certain data
cached in memory all the time.
That could be solved by doing
SELECT COUNT(*) from to_be_cached;
as a cron job. It loads the whole table into the Linux Kernel memory cache.
Marko Ristola
Tom Lane wrote:
>Right. I think the optimal setting for a Postgres data directory is
>journaled metadata, non-journaled file content. Postgres can take care
>of the data integrity for itself, but it does assume that the filesystem
>stays structurally sane (eg, data blocks don't get reassigned to the
>wrong file), so you need a filesystem guarantee about the metadata.
>
>WAL files are handled in a much more conservative way (created, filled
>with zeroes, and fsync'd before we ever put any valuable data in 'em).
>If you have WAL on its own drive then I think Mike's recommendation of
>no filesystem journalling at all for that drive is probably OK. Or
>you can do same as above (journal metadata only) if you want a little
>extra protection.
>
>And of course all this reasoning depends on the assumption that the
>drive tells the truth about write-completion. If the drive does write
>caching it had better be able to complete all its accepted writes before
>dying in a power failure. (Hence, battery-backed write cache is OK, any
>other kind is evil.)
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>