> Manfred Spraul > [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
