Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

Last week, the developers released version 0.2 of pgrust, a significant performance improvement over its previous iteration. The new version achieved remarkable gains in speed and performance. According to the release, pgrust is now 300 times faster than Postgres. This enhancement was primarily driven by the development of a more efficient query engine.

The query engine is a crucial part of the database system, responsible for processing the SQL query and executing the necessary operations. In Postgres, the query engine utilizes the Volcano model, which processes one row at a time and adds considerable overhead. The developers aimed to optimize this process to improve overall performance.

To achieve this, they first built a miniature version of the Postgres query engine. The query engine processes SQL queries by converting them into an internal representation called a "Query Plan." The query plan describes the steps Postgres will take to execute the query, which may involve various operations such as reading data, sorting, and aggregating.

The developers optimized the query engine using several techniques, including batching, operator fusion, and SIMD (Single Instruction, Multiple Data) processing. Batching, for instance, reduces the overhead by processing multiple rows at once, significantly improving performance. Operator fusion, on the other hand, combines common operations into a single node, eliminating the need for memory allocations and further enhancing performance.

In the optimized query engine, the Volcano model's overhead is eliminated, and the process becomes more akin to a straightforward for loop, resulting in a substantial performance boost. This demonstrates the potential for significant improvements in database performance when addressing CPU and memory bottlenecks. Overall, the enhancements in pgrust's query engine have led to an impressive 300x speedup compared to Postgres, highlighting the importance of optimizing the query engine for improved performance.