Rebuilding Postgres for 300x faster analytics: batching, operator fusion, and SIMD - malisper.me
Primary Menu
malisper.me
malisper.me
General<br>About Me
Table of Contents for Postgres Posts
RSS
Subscribe to Blog via Email
Enter your email address to subscribe to this blog and receive notifications of new posts by email.
Email Address
Subscribe
Skip to content
Home » pgrust » Rebuilding Postgres for 300x faster analytics: batching, operator fusion, and SIMD
Last week we released version 0.2 of pgrust. This release was all about performance. It’s 10x faster than the previous version of pgrust. On OLTP benchmarks, pgrust is 30% faster than Postgres, and on Clickbench, Clickhouse’s benchmark for analytical databases, pgrust is 300x faster than Postgres. It’s even ahead of Clickhouse!
The query engine is one of the biggest changes we made to achieve much better performance. On its own, the query engine drove ~10x of the 300x. We’ll start with a miniature version of the Postgres query engine and we’ll one by one add the same optimizations we made to make the pgrust query engine so fast.
To give some background on why there’s so much room for improvement vs Postgres, Postgres was created in a different era. The original Postgres project dates back to the 80s. It was built at a time when the main bottleneck to database performance was disk I/O. Three trends have made that no longer the case:
Many datasets now fit in RAM, eliminating most disk I/O
For datasets that don’t fit in RAM, the workloads differ. Data analytics scans data in bulk. The bottleneck is often no longer your disk throughput and is often either your CPU throughput or memory throughput
Disks have gotten much faster in recent years. NVMe is hundreds of times faster than a hard drive.
All three trends have made CPU and memory speeds more important than they were historically. Many of the optimizations we’ve made target this. The query engine is the main user of CPU in a database. We optimized the pgrust query engine to use less CPU and less memory bandwidth than Postgres when processing the same queries.
To give you a sense of just how slow the Postgres query engine is, let’s take a simple query that sums the first 500 million numbers:
CREATE TABLE my_table AS select col::float8 from generate_series(1.0, 500000000.0) g(col);<br>SELECT SUM(col) FROM my_table;
When I run this in Postgres, it takes ~20 seconds. This was done on a c8g.4xl with parallel queries disabled.
For comparison, when I time the equivalent in Rust:
let table: Vec = (1..=500_000_000usize).map(|i| i as f64).collect();
let mut sum = 0.0;<br>for &value in &table {<br>sum += value;
The query takes 358ms. That’s around 55x faster, and believe it or not, we can do even faster than 358ms. Now this example isn’t an apples-to-apples comparison. There’s a lot more going on under the hood in Postgres. At the same time, optimizing a database is all about removing as much of this overhead as possible. (If you’re curious two of the biggest causes of overhead from Postgres are 1. locking and 2. parsing the Postgres storage format and extracting the tuples relevant to the query).
To narrow our focus to just the impact of the query engine, let’s build a miniature version of the Postgres query engine. First, a brief explanation of what a query engine is. When processing your SQL query, Postgres first converts your query into an internal representation called a "Query Plan," which describes *how* Postgres will execute the query. In the example above, Postgres will produce a query plan that may look something like the following:
This effectively says "get rows from my_table and sum the values in those rows". This query plan is pretty simple given the nature of the query, but they can get much more complicated when you start working with joins/sorts/subqueries etc. In total Postgres has over 40 different types of plan nodes.
After generating the query plan, Postgres passes it to the query engine. The Postgres query engine is the part of Postgres that takes the query plan and actually retrieves the rows and performs the aggregation. Postgres uses a style of executor known as the "Volcano model." To get a sense of how it works, here’s a miniature implementation of the Postgres query engine:
use std::hint::black_box;
trait Node {<br>fn next(&mut self) -> Option;
struct SeqScan {<br>table: &'a [f64],<br>pos: usize,
impl Node for SeqScan {<br>fn next(&mut self) -> Option {<br>if self.pos >= self.table.len() {<br>return None; // end of table<br>let value = self.table[self.pos];<br>self.pos += 1;<br>Some(value)
struct SumAggregate {<br>child: Box,<br>total: f64,<br>done: bool,
impl Node for SumAggregate {<br>fn next(&mut self) -> Option {<br>if self.done {<br>return None;<br>while let Some(value) = self.child.next() {<br>self.total += value;<br>self.done = true;<br>Some(self.total)
let table: Vec = (1..=500_000_000usize).map(|i| i as f64).collect();
let mut plan = SumAggregate {<br>child: black_box(Box::new(SeqScan { table: &table,...