What lead to that design decision and what are the tradeoffs from it? Intuitively it seems like it'd be better to bypass the OS, but on the other hand Postgres does work incredibly well.
What lead to that design decision and what are the tradeoffs from it? Intuitively it seems like it'd be better to bypass the OS, but on the other hand Postgres does work incredibly well.
The tradeoff is that it greatly increases the complexity of maintaining portability and consistency because every operating system and file system behaves differently and offers different guarantees at this level of abstraction. PostgreSQL makes a priority of being highly portable, and has therefore chosen to avoid this particular code swamp and testing nightmare altogether thus far even though it comes at a significant cost to peak performance potential.
PostgreSQL is quite efficient and well designed for the design constraints they currently operate within but their primary value and philosophy is not peak performance per se.
It's interesting how many different approaches there are when stepping back and using some of the OS - for example where does MySQL/InnoDB fit in this?
It seems they are intending you to use their buffer pool rather than the OS's disk cache (as they advise to make the buffer pool take large percentage of RAM), as opposed to Postgres where they recommend having shared_buffers as ~20% of RAM and leaving the rest for the OS to use. Is InnoDB doing something different here or is it just because they have a better cache implementation?
Way back around 2000 I did a performance bake off of Informix with direct disk io vs the Solaris 2.7 (maybe 2.6) file system. For our workloads, performance was within 5% give or take. We opted to switch to file based storage as it saved us a bunch of money.
There's no one answer -- and some technical explanations are mumbo jumbo concealing the real ask. Some DBAs will demand direct IO simply because of office politics. File systems "belong" to the storage team, but LUNs with Oracle "belong" to the DBAs.
OS vendors have to adapt their systems to changing hardware environments: if you rely on the OS, you get the benefit of that effort for free. If you bypass the OS, you've got to do it yourself.