PostgreSQL 17: Exploring Combined I/O and Its Implications

In the past two years, PostgreSQL has emerged as the most popular open-source database, becoming significantly faster thanks to ongoing innovations within its community. With substantial attention and funding, PostgreSQL continues to evolve.

Notably, PostgreSQL 17 was officially released on September 26, 2023, introducing exciting features, including the combined I/O, also known as vectorized I/O.

Understanding the Basics: Pages and Reads

PostgreSQL operates using fixed-size pages, typically 8 kilobytes in size, essential for managing indexes, tables, and transaction logs. When a query is executed, PostgreSQL reads these pages from memory or disk, depending on their availability.

In traditional operations, PostgreSQL reads a single page at a time, resulting in multiple system calls for multiple pages. This inefficiency is particularly evident during sequential scans, where the database scans through tables or indexes, often requiring multiple reads for sequential data.

The Inefficiency of Traditional Reads

For example, if a sequential scan needs three pages, PostgreSQL issues three separate read requests. This means three system calls, each transitioning from user mode to kernel mode, which incurs overhead and latency. Each read operation adds up, leading to potential performance bottlenecks.

The Combined I/O

With combined I/O, PostgreSQL aims to streamline these read operations. This new approach allows the database to predict when multiple pages will be needed, consolidating requests into a single system call. For instance, if the planner anticipates the need for three consecutive pages, it can request them all in one go, thereby minimising the number of system calls.

This process leverages the p-vread system call, which stands for “vectorized read." It allows the database to specify an offset and an array of buffers, enabling the reading of multiple pages simultaneously.

Instead of issuing separate reads for each page, PostgreSQL can fetch them all in a single operation, significantly reducing the overhead associated with system calls.

The Advantages of Combined I/O

1. Efficiency: By minimising the number of system calls, combined I/O reduces the CPU overhead associated with transitioning between user and kernel modes. This leads to faster query execution times, especially for operations that require multiple sequential reads.
2. Improved Caching: With fewer reads, the pressure on memory is reduced. PostgreSQL can cache more pages effectively, enhancing performance for future queries that require access to the same data.
3. Better Resource Management: By grouping read requests, PostgreSQL can optimise its interaction with the file system, potentially leading to better disk utilisation and reduced I/O operations

Challenges and Considerations

While combined I/O presents exciting opportunities for PostgreSQL, it also raises important considerations. One primary concern is the risk of over-reading. If PostgreSQL anticipates the need for several pages but the query only requires a few, unnecessary data may be read into memory, leading to read amplification.

Conclusion

The introduction of combined I/O in PostgreSQL 17 is a significant step forward in enhancing the database’s speed, performance, and efficiency.

For more information, check out the official release announcement.