Re: [PERFORM] DELETE vs TRUNCATE explanation

On 07/16/2012 02:29 AM, Tom Lane wrote:
> Yeah, you have a point there. It's not real clear that switching fsync
> from off to on is an operation that we can make any guarantees about,
> short of executing something like the code recently added to initdb
> to force-sync the entire PGDATA tree.

There's one way that doesn't have any housekeeping cost to Pg. It's
pretty bad manners if there's anybody other than Pg on the system though:

sync()

Let the OS do the housekeeping.

It's possible to do something similar on Windows, in that there are
utilities for the purpose:

http://technet.microsoft.com/en-us/sysinternals/bb897438.aspx

This probably uses:

http://msdn.microsoft.com/en-us/library/s9xk9ehd%28VS.71%29.aspx

from COMMODE.OBJ (unfortunate name), which has existed since win98.

> Perhaps we should change fsync
> to be PGC_POSTMASTER (ie frozen at postmaster start), and then we could
> skip forwarding fsync requests when it's off?

Personally, I didn't even know it was runtime switchable.

fsync=off is much less necessary with async commits, group commit via
commit delay, WAL improvements, etc. To me it's mostly of utility when
testing, particularly on SSDs. I don't see a DB restart requirement as a
big issue. It'd be interesting to see what -general has to say, if there
are people depending on this.

If it's necessary to retain the ability to runtime switch it, making it
a somewhat rude sync() in exchange for boosted performance the rest of
the time may well be worthwhile anyway. It'd be interesting to see.

All this talk of synchronisation is making me really frustrated that
there seems to be very poor support in OSes for syncing a set of files
in a single pass, potentially saving a lot of time and thrashing. A way
to relax the ordering guarantee from "Files are synced in the order
fsync() is called on each" to "files are all synced when this call
completes" would be great. I've been running into this issue in some
non-Pg-related work and it's been bugging me.

--
Craig Ringer