Re: Windows now has fdatasync()

On Sun, Dec 12, 2021 at 3:48 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> [...] I
> tried out a quick POC patch and it runs a bit faster than fsync(), as
> expected. I'm not sure if it's worth bothering with or not given the
> other options, but figured it was worth sharing.

One reason to consider developing this further is the problem
discussed in the aptly named thread "Loaded footgun open_datasync on
Windows"[1] (not the problem that was fixed in pg_test_fsync, but the
problem with cache control, or lack thereof). I saw 10x more TPS with
open_datasync than with this experimental fdatasync on my little test
VM, which was more than a little fishy, so I turned off the device
write caching in "Device Manager" and got about the same number from
open_datasync and fdatasync. Clearly you can lose committed
transactions on power loss[2] with the default OS settings and default
PostgreSQL settings, though perhaps only if you're on SATA storage due
to lack of FUA pass-through[3] (?). I didn't try an NVMe stack.

That suggests that fdatasync would actually be a better default ...
except for the problems already mentioned with versions and not
working on non-NTFS (not sure how it fails on non-NTFS, though, maybe
it does a full flush, [4] doesn't say).

(The patch is a little rough; I couldn't figure out the headers to get
that macro. Insert my usual disclaimer that I'm not a Windows guy,
this is stuff I'm just figuring out, all clues welcome...)

[1] https://www.postgresql.org/message-id/flat/1527846213.2475.31.camel%40cybertec.at
[2] https://github.com/MicrosoftDocs/feedback/issues/2747
[3] https://devblogs.microsoft.com/oldnewthing/20170510-00/?p=95505
[4] https://docs.microsoft.com/en-us/windows-hardware/drivers/ddi/ntifs/nf-ntifs-ntflushbuffersfileex