> You may know exactly what information you need for a single given query. But you don't know what the next query will be. You don't know if the next query will

> You may know exactly what information you need for a single given query. But you don't know what the next query will be. You don't know if the next query will need the same indices or something else entirely. I think this misrepresents how many modern database engines work. Queries are generatively decomposed into many thousands (or millions) of independent page operations, depending on the amount of data they necessarily touch, before they get to the execution queue. Ops from concurrent queries are all blended together. The I/O and execution scheduler can, in fact, see upwards of a million page operations into the future across all concurrent operations in some systems and adaptively, dynamically reorder the entire schedule to optimize for locality across concurrent operations while preserving invariants. This enables powerful optimizations that circumvent theoretical limits on cache effectiveness and improves locality under high concurrency in ways simple caching cannot. This is not a new idea, it is how silicon is designed to deal with data access patterns that intrinsically have poor cache locality. A modern server can retire tens of millions of these ops per second, so you can get incredible throughput with tight tail latencies because, again, you control what happens and when. This works beautifully even when the working storage is orders of magnitude larger than available memory. It does require exquisitely fine control of I/O and execution scheduling as a matter of architecture. It is worth acknowledging that the throughput limits are not an mmap() problem. Most non-mmap() database kernels struggle to saturate modern storage hardware too. Classic database architectures are not effective at consistently and inexpensively generating enough concurrency to use the available hardware. I used to use mmap for database kernel-y stuff. The recurring pain point was that it is impossible to saturate fast storage arrays using it for many real-world workloads when the data won’t mostly fit in memory, and the computational cost of trying to do so was high. And then I did a stint in supercomputing where it turned out everyone knew you could circumvent these limits in highly parallel systems with precise I/O and execution scheduling, and relying much less on caching, but those people don’t work on databases.