Optimising kernels and file systems for PostgreSQL, a cross-project talk

<p>In this talk I will: * introduce the unusual I/O needs of databases and PostgreSQL's new I/O architecture and direction * show how PostgreSQL works on FreeBSD, NetBSD, OpenBSD and illumos today * compare those systems' available support for native asynchronous I/O with Windows and Linux * speculate on the pathways that need to be drilled through their kernels to achieve the state of the art * speculate on the API design constraints and options I see * discuss OpenZFS's exciting new direct I/O and block cloning features and their relevance to PostgreSQL * show-and-tell some experimental patches for full-featured direct I/O on FreeBSD's UFS * show-and-tell some experimental patches for PostgreSQL with FreeBSD's native AIO and <code>kqueue</code></p> <p>My goal is to provide a database hacker's take on the I/O concerns that "go together" and explain how and why they are linked. It is written for a cross-project kernel and file system hacker audience, a rare opportunity provided by this FOSDEM devroom. The presentation begins with a high-level problem space overview, before diving down to user space, VFS and device levels to discuss the options as I see them. It includes some exploratory patches developed over the past few years of working full time on PostgreSQL I/O, porting and testing on ~10 operating systems, and hacking on FreeBSD for fun and education.</p> <p>It is a 25 minute talk, broken up into 5 subtopics consisting of 5 one-minute slides, and the pace will be fast:</p> <ul> <li>What databases want and why, a 30,000 foot overview </li> <li>User space programming interfaces for asynchronous I/O</li> <li>Kernel interfaces for asynchronous I/O</li> <li>PostgreSQL on FreeBSD/ZFS</li> <li>Using FreeBSD/UFS as a starting point for database/kernel interface exploration</li> </ul>