<story><title>But how, exactly, do databases use mmap?</title><url>https://brunocalza.me/but-how-exactly-databases-use-mmap/</url></story><parent_chain><item><author>bonzini</author><text>The right answer is that they shouldn&amp;#x27;t. A database has much more information than the operating system about what, how and when to cache information. Therefore the database should handle its own I&amp;#x2F;O caching using O_DIRECT on Linux or the equivalent on Windows or other Unixes.&lt;p&gt;The article at &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.scylladb.com&amp;#x2F;2017&amp;#x2F;10&amp;#x2F;05&amp;#x2F;io-access-methods-scylla&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.scylladb.com&amp;#x2F;2017&amp;#x2F;10&amp;#x2F;05&amp;#x2F;io-access-methods-scylla...&lt;/a&gt; is a bit old (2017) but it explains the trade-offs</text></item></parent_chain><comment><author>masklinn</author><text>&amp;gt; Therefore the database should handle its own I&amp;#x2F;O caching using O_DIRECT on Linux or the equivalent on Windows or other Unixes.&lt;p&gt;That&amp;#x27;s not wrong, but at the same time it adds complexity and requires effort which can&amp;#x27;t be spent elsewhere unless you&amp;#x27;ve got someone who really only wants to DIO and wouldn&amp;#x27;t work on anything else anyway.&lt;p&gt;Postgres has never used DIO, and while there have been rumbling about moving to DIO (especially following the fsync mess) as Andres Freund noted:&lt;p&gt;&amp;gt; efficient DIO usage is a metric ton of work, and you need a
large amount of differing logic for different platforms. It&amp;#x27;s just not realistic to do so for every platform.  Postgres is developed by a small number of people, isn&amp;#x27;t VC backed etc. The amount of resources we can throw at something is fairly limited.  I&amp;#x27;m hoping to work on adding linux DIO support to pg, but I&amp;#x27;m sure as hell not going to do be able to do the same on windows (solaris, hpux, aix, ...) etc.</text></comment>
<story><title>But how, exactly, do databases use mmap?</title><url>https://brunocalza.me/but-how-exactly-databases-use-mmap/</url></story><parent_chain><item><author>bonzini</author><text>The right answer is that they shouldn&amp;#x27;t. A database has much more information than the operating system about what, how and when to cache information. Therefore the database should handle its own I&amp;#x2F;O caching using O_DIRECT on Linux or the equivalent on Windows or other Unixes.&lt;p&gt;The article at &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.scylladb.com&amp;#x2F;2017&amp;#x2F;10&amp;#x2F;05&amp;#x2F;io-access-methods-scylla&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.scylladb.com&amp;#x2F;2017&amp;#x2F;10&amp;#x2F;05&amp;#x2F;io-access-methods-scylla...&lt;/a&gt; is a bit old (2017) but it explains the trade-offs</text></item></parent_chain><comment><author>jorangreef</author><text>Yes, and it&amp;#x27;s not only about performance, but also safety because O_DIRECT is the only safe way to recover from the journal after fsync failure (when the page cache can no longer be trusted by the database to be coherent with the disk): &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.usenix.org&amp;#x2F;system&amp;#x2F;files&amp;#x2F;atc20-rebello.pdf&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.usenix.org&amp;#x2F;system&amp;#x2F;files&amp;#x2F;atc20-rebello.pdf&lt;/a&gt;&lt;p&gt;From a safety perspective, O_DIRECT is now table stakes. There&amp;#x27;s simply no control over the granularity of read&amp;#x2F;write EIO errors when your syscalls only touch memory and where you have no visibility into background flush errors.</text></comment>