David kindly ran some tests of this thing on real hardware. The results were mostly in line with expectations, but we learned some new things.
TL;DR We probably should consider this as a safer default, but it'd be good for someone more hands-on with this OS and knowledgeable about storage to investigate and propose that. My original goal here was primarily Unix/Windows harmonisation and cleanup since I'm doing a bunch of hacking on I/O, but I can't unsee an unsafe-at-least-on-consumer-gear default now that I've seen it. The main thing I'm aware of that we don't know yet is what happens if you try it on a non-NTFS file system (ReFS? SMB?) -- hopefully it falls back to fsync behaviour. Observations from an old Windows 8.1 system with a SATA drive: 1. So far you can apparently still actually compile and run on 8.1, despite recent commits to de-support it. 2. You can use the new wal_sync_method=fdatasync, without error, and timings are consistent with falling back to full fsync behaviour. That makes sense, I guess, because the function existed. It's just a new flag bit, and the default behaviour for flags == 0 was already their fsync. That seems like a good outcome even though 8.1 isn't a target anymore. Observations from a current Windows 11 system with an NVMe drive: 1. fdatasync is faster than fsync, as expected. Twice as fast with write cache disabled, a bit faster with write cache enabled. 2. Timings seem to suggest that open_datasync (the current default) is not really writing through the drive cache. I'd previously thought that was a SATA-only problem based on [1], which said that EIDE/SATA drivers did not pass through the FUA flag that NTFS sends for FILE_FLAG_WRITE_THROUGH (= O_DSYNC) on the basis that many drives ignored it anyway, but these numbers seem to suggest that David's recent-ish NVMe system has the same problem as the old SATA system. Generally, Windows' approach seems to be that NTFS FILE_FLAG_WRITE_THROUGH fires an FUA flag into the storage stack, and either the driver or the drive is free to fling it out the window, and it's the user's problem to worry about that, whereas Linux at least asks nicely if the drive understands FUA and falls back to flushing the whole cache if not[2]. I also know that Linux has been flaky around this in the past too, especially on consumer storage, and macOS and at least some of the older BSD/UFS systems just don't do this stuff at all for user data (yet) so it's not like there is anything universal about this topic. Note that drive caches are enabled by default in Windows, and our manual does already tell you about this problem[3]. One thing to note about the numbers below: pg_test_fsync.c's open_datasync test is also using FILE_FLAG_NO_BUFFERING (= O_DIRECT), unlike PostgreSQL, which muddies the waters slightly. (There was a patch upthread to fix that and report both numbers, I may come back to that.) Windows 11, NVMe, write cache enabled: open_datasync 27306.286 ops/sec 37 usecs/op fdatasync 3065.428 ops/sec 326 usecs/op fsync 2577.498 ops/sec 388 usecs/op Windows 11, NVMe, write cache disabled: open_datasync 3477.258 ops/sec 288 usecs/op fdatasync 3263.418 ops/sec 306 usecs/op fsync 1641.502 ops/sec 609 usecs/op Windows 8.1, SATA: open_datasync 19934.532 ops/sec 50 usecs/op fdatasync 231.429 ops/sec 4321 usecs/op fsync 240.050 ops/sec 4166 usecs/op (We couldn't figure out how to disable the write cache on the 8.1 machine -- the usual checkbox had no effect -- but we didn't waste time investigating that old system beyond the curiosity of checking if it'd work at all.) [1] https://devblogs.microsoft.com/oldnewthing/20170510-00/?p=95505 [2] https://techcommunity.microsoft.com/t5/sql-server-blog/sql-server-on-linux-forced-unit-access-fua-internals/ba-p/3199102 [3] https://www.postgresql.org/docs/devel/wal-reliability.html