Re: NOT LOGGED options (was Point in Time Recovery )

> Manfred Spraul
> Simon(at)2ndquadrant(dot)com wrote:
>
> >>Tom Lane wrote
> >>
> >>
> >>>NOT LOGGED options on CREATE INDEX and COPY, to allow users to take
> >>>advantage of the no logging optimization without turning off
> PITR system
> >>>wide. (Just as this is possible in Oracle and Teradata).
> >>>
> >>>
> >>Isn't this in direct conflict with your opinion above? And I cannot say
> >>that I think this one is a good idea. We do not have support for
> >>selective catalog xlogging;
> >>
> Is it possible to skip the xlog fsync for NOT LOGGED transactions?
>

Hmm...good thinking...however,

For very large operations, its the volume of the xlog writes thats the
problem, not the fsync of the logs. The type of things I'm thinking about
are large CREATE INDEX and large COPY operations, for very large tasks i.e.
> 1Gb. These are most useful in data warehousing operations - which is about
20% of the user base according to the survey stats from www.postgresql.org.

The wal buffer only gets synced at end of transaction, or when the buffer is
full. On long operations there is still only one commit, so not fsyncing
there won't gain much. The buffer will fill up repeatedly and require
flushing - which you can't really skip because when you get to the commit
you need to be certain that everything is down to disk - there's not much
point fsyncing the commit if the previous wal records haven't been.

If there was a way to tell whether a block in the wal buffer had been
written by a NOT LOGGED transaction, then it might be possible to vary the
fsync behaviour accordingly. That's a good idea if thats what you meant,
though it would mean changing some critical, well tested code that every wal
record goes through. I'd rather simply not write wal at all for the certain
specific situations when the user requests it - there are already decision
points in the code for both the situations I've mentioned, since these have
been optimised in 8.0 for when archive_command has not been set. It would be
a simply matter to add in a check at that point.

Anyway...this is probably 8.1 stuff now.

Best Regards, Simon Riggs