Combined (Vectorized) I/O in PostgreSQL 17
PostgreSQL, like most databases, operates using fixed-size pages of data, each 8 kilobytes in size. When reading data, the database fetches these pages from disk.
Background
In a database system, a page is the fundamental unit of data storage and retrieval. Most databases, including PostgreSQL, organize data into fixed-size pages, commonly 8KB in size, though this can vary between systems. Each database page can contain multiple rows of a table, index entries, or metadata. When the database needs to access or modify data, it typically retrieves an entire page from disk into memory.
These pages are stored in the database buffer cache, a special area in memory where recently accessed pages are kept. If data from a particular page is requested again, the database can serve it directly from memory, avoiding slow disk reads.
The operating system also employs page caching to improve performance by storing recently accessed blocks (system pages) from disk in memory. For instance, when PostgreSQL requests a database page, the OS may use its page cache to retrieve the corresponding blocks from memory rather than going directly to the disk.
A system page, also called a memory page, is the smallest unit of memory that the operating system manages, usually 4KB in size, though larger sizes (such as 8KB, 16KB, or 1MB) are also common depending on the architecture. When an application (like a database) needs to store or access data in RAM, the operating system allocates memory in pages.
If the data isn’t available in the memory cache (shared buffers), PostgreSQL issues a system call to the operating system to fetch it from storage. This operation, while efficient for small amounts of data, can become cumbersome when reading large amounts of data during sequential scans.
Currently, PostgreSQL reads one page at a time. Each page read requires a separate system call to the kernel, which retrieves the specified data block from disk. This is inefficient in cases where multiple sequential pages are needed. For example, if a table scan requires reading five pages, PostgreSQL will issue five separate read commands, each retrieving 8KB of data. This creates overhead due to the multiple system calls and disk accesses.
With Combined I/O, PostgreSQL can group multiple page reads into a single system call, drastically reducing the number of system calls and improving performance. Instead of issuing five separate calls for five pages, PostgreSQL can request them all at once in a single call, reducing overhead.
This approach is particularly beneficial in sequential scans or range queries where multiple pages are needed in order. By predicting the upcoming pages, PostgreSQL can combine them into one system call, making the data retrieval process faster and more efficient.
Combined I/O operates by leveraging a system call called preadv, which allows for vectorized I/O. This system call lets PostgreSQL specify a starting point on disk and then retrieve multiple blocks of data into different buffers in one go. Instead of making a separate system call for each 8KB page, PostgreSQL can now read several pages at once, reducing latency and the load on the CPU.
For example, in a sequential scan, PostgreSQL may predict that it will need three consecutive pages. With Combined I/O, it sends one system call that reads from the starting position and fetches all three pages, cutting down on the time and resources required. The idea behind Combined I/O is not entirely new. It draws inspiration from a long-standing feature in operating systems known as read-ahead. Read-ahead blocks is also configurable. Read-ahead allows the kernel to predict future reads and load additional blocks of data into memory, anticipating that subsequent requests will need this data.
If the kernel fails the prediction this can pollute the cache as the queries can be partially usable. Not all databases can use the default read-ahead efficiently however. The kernel will kick out unused block using LRU algorithm.
Combined I/O in PostgreSQL builds on this concept, but instead of relying solely on the kernel, it actively manages page reads within the database itself, giving PostgreSQL more control over data access patterns.
Issues
A potential with Combined I/O is read amplification. This occurs when PostgreSQL predicts that multiple pages will be needed and issues a combined read, but the actual query doesn’t require all the pages retrieved. As a result, excess data is read into memory, which can lead to wasted resources.
A second potential issue is when managing caching in both the kernel’s page cache and PostgreSQL’s shared buffer cache which can introduce complexities.
Advantages
Improved Performance in Sequential Scans - By combining multiple page reads into a single system call, the overall disk I/O is reduced, which can lead to significant performance gains, especially for large tables.
Reduced System Call Overhead: Each system call has a cost associated with switching the CPU into kernel mode. By reducing the number of system calls, Combined I/O minimizes this cost, resulting in better resource utilization and faster query execution.
Flexibility Beyond Sequential Reads: While Combined I/O is most beneficial in sequential scans, it can also be applied in non-sequential reads. For example, it can group non-contiguous pages into a single system call, improving efficiency even in more complex queries.