4 ms·
PostgreSQL used fsync incorrectly for 20 years (2019) [video]
- guenthert 5y ago"with possibly disastrous consequences for data durability/consistency (which is something the PostgreSQL community really values)." Do they though? Well, sure, they might value it, but do they prioritize it? E.g. checksums for data pages were only introduced in PostgreSQL 9.3 and they are still disabled by default today in PostgreSQL 14. That might be the right choice if your underlying filesystem provides checksums for content (e.g. ZFS) and memory is protected from corruption via other means (likely given on a DB server holding data someone cares about), but should that assumption be the default? See https://bsdmag.org/page-checksum-protection-in-postgresql/ https://bsdmag.org/page-checksum-protection-in-postgresql/
- dang 5y agoDiscussed at the time: PostgreSQL used fsync incorrectly for 20 years - https://news.ycombinator.com/item?id=19119991 https://news.ycombinator.com/item?id=19119991 - Feb 2019 (307 comments)
- cphoover 5y agoI didn't even know PostgreSQL was 20 years old
- treeman79 5y ago20 years ago I was trying to decide between the two. But sql sounded hard to learn. so I wrote my own database. The joys of being a jr programmer with no oversight.
- NoahKAndrews 5y agoThat's utterly hilarious
- cglace 5y agoWell, How did it go?
- Beltalowda 5y agoIt goes back all the way to the 80s; a brief overview can be found here: https://en.wikipedia.org/wiki/PostgreSQL#History https://en.wikipedia.org/wiki/PostgreSQL#History
- mulmen 5y agoI'm also a fan of https://www.postgresql.org/docs/current/history.html https://www.postgresql.org/docs/current/history.html.
- asah 5y agowell... it's over 25 years... and thanks for making me feel old AF ;-)
- a-dub 5y agoif i recall, it rose to prominence against a backdrop of frustration with myisam not supporting transactions and stored procedures among several other things. early postgres had its own complications though. myisam was limited, but save for dumps it basically ran itself. postgres required tuning and manually run maintenance operations. it was not a drop in replacement for myisam like innodb was...
- oblio 5y ago> Initial release 8 July 1996; > 25 years ago
- worik 5y agoForty five minute video? What is the issue? What are the implications? Is there a written summary some place? These people do great work, bless them. I love watching these sorts of videos (bless them) when I have some time to kill. But I would like to know the implications of this, and a forty five minute video might be of some help - or might not. After forty five minutes erased from my life....
- tux3 5y agoThe short of it is that fsync on Linux used to be really bad at reporting errors (POSIX APIs are still bad, but we've now sorted out some ways to work around this particular source of pain) IIRC, after an IO error you could get into a situation where on the next fsync postgres thought things were back on track, but Linux had actually just thrown away some data.
- dataflow 5y agoThe tl;dr (and blunt truth) is that Linux doesn't implement POSIX's semantics of fsync() correctly. The standard says "the fsync() function is intended to assure that after a system crash or other failure that all data up to the time of the fsync() call is recorded on the disk". Instead, Linux merely provides this guarantee for "all data since the last* call to fsync(), whether successful or not", which means data that failed to flush once will continue to fail to get flushed on subsequent calls to fsync(). But nobody I've seen has been willing to admit this is a broken implementation yet. People instead go through extreme contortions to justify the status quo, blaming Postgres for not using a broken fsync() "correctly". Given how it took some 2 decades for Linux folks to change their minds on other issues, like realizing the notion of a pidfd is a useful one, I'm guessing they might eventually get around to this too, but it might take another couple of decades for views to change.
- a-dub 5y agoto be fair, nothing really perfectly confirmed to POSIX now that i think of it. who remembers the myriad choices in synchronization primitives that could be used for the main loop in preforking apache? (to work around all the different flavors of broken in unix derivatives) i remember one desperate choice falling all the way through to flock() on a sentinel file on disk...
- rwaksmunski 5y agoThis was a Linux fsync implementation issue. FreeBSD did the right thing.
- nu11ptr 5y agoIf I recall correctly, this absolutely killed FreeBSD in benchmarks while Linux looked amazing. I recall the editors having to put a note in there saying that Linux wasn't committing I/O but FreeBSD was to explain the huge difference. At least I think this is the situation I'm remembering. Hmm..
- fulafel 5y agoThe fsync vs I/O errors bug didn't have any performance impact in absence of failing disks, it was about what happened when you tried to fsync again after your storage stack reported a write error. But for a OS that has a bad fsync, have a look at macOS - they even document that it doesn't commit data to stable storage and you should use some other system call to do it: https://developer.apple.com/library/archive/documentation/System/Conceptual/ManPages_iPhoneOS/man2/fsync.2.html https://developer.apple.com/library/archive/documentation/Sy... (A web search now only finds iOS man pages, but the fsync caveat was the same in OS X/Darwin manpages before they were taken offline)
- masklinn 5y ago> This was a Linux fsync implementation issue. No, since most OS had (and still have) the "incorrect" behaviour. This was (and still is!) a generalised issue of fsync itself, since the state of buffers is undefined in case of failure. The assumption that you could retry fsync on error was always effectively incorrect, per the posgres wiki on the subject openbsd, netbsd, and macos had essentially the same behaviour (invalidate buffers on failure). FreeBSD diverged from the rest because they rewrote brelse() long after separating from the others. After this issue surfaced, openbsd updated their implementation to ensure the call would always fail, but only if the file is kept open which had not been guaranteed for postgres (don't know if it now is). Linux had (has?) the additional issue of wonky error reporting, but that would not save your ass.
- sitkack 5y agoAll bugs are shallow (don't believe) https://en.wikipedia.org/wiki/Linus%27s_law https://en.wikipedia.org/wiki/Linus%27s_law