Tuning the Lookahead Distance for PostgreSQL Asynchronous IO

PostgreSQL (PG) recently introduced support for asynchronous IOs (PG-AIO). Under the hood, the PG-AIO subsystem adopts a classic producer/consumer model. The producer submits AIO requests to a global queue resident in shared memory, and the consumer, which by default includes a number of “IO workers,” grabs the AIO
requests from the queue and performs the actual IO work. To hide the implementation details of this producer/consumer model, PG offers a “read stream” abstraction with a set of API functions for table access methods, such as sequential scan and bitmap heap scan, that rely on the producer to send in AIO requests. Although this streaming API significantly simplifies the implementation of the access methods, it poses a new challenge that is performance critical: every read stream needs to decide the “lookahead distance,” i.e., the number of AIO requests to submit at the same time. In this paper, we address this challenge by proposing an adaptive approach to automatically adjust the lookahead distance by only leveraging feedback information based on the IO completion time. Our approach is inspired by and draws a connection to the theory of optimal flow control in computer networking, which has a profound impact on the design of modern TCP congestion control mechanisms such as BBR. Experimental evaluation with a variety of real-world setups shows that our approach can often achieve the optimal lookahead distance that minimizes query latency.