Accelerating Postgres Analytics by 300x: The pgrust Approach

With the release of pgrust version 0.2, we have seen significant leaps in performance. While it provides a modest boost for standard transactional workloads, its true power shines in analytical processing.

Performance Comparison

Benchmark	pgrust vs. Postgres	Note
OLTP	$\approx 30\%$ Faster	General transactional speedup
Clickbench	$\approx 300\times$ Faster	Analytical database benchmark

The primary driver behind these gains is a complete overhaul of the query engine. To understand how we achieved this, we must first examine why the original Postgres engine struggles with modern analytical workloads.

---

The Evolution of Hardware Bottlenecks

Postgres was architected in an era where the primary constraint was disk I/O. However, the landscape has shifted due to three major trends:

1. RAM Capacity: Many modern datasets now reside entirely in memory, making disk I/O the main concern eliminating it as the primary bottleneck.
2. Workload Shifts: For massive datasets, the limitation is no longer disk throughput, but rather CPU throughput or memory bandwidth.
3. Disk Speed: Storage hardware has evolved to be orders of magnitude faster.

Consequently, the efficiency of CPU and memory utilization is now the critical path for performance. pgrust is designed to minimize both CPU cycles and memory bandwidth consumption.

The "Summation" Benchmark

To illustrate the inefficiency, consider a query that sums 500 million floating-point numbers:

SQL Implementation:

CREATE TABLE my_table AS select col::float8 from generate_series(1.0, 500000000.0) g(col); 
SELECT SUM(col) FROM my_table;

• Postgres Execution Time: $\approx 20\text{ seconds}$

Raw Rust Implementation:

let table: Vec<f64> = (1..=500_000_000usize).map(|i| i as f64).collect(); 
let mut sum = 0.0; 
for value in table { 
    sum += value; 
}

• Rust Execution Time: $358\text{ms}$ (roughly 55x faster)

Note: Even this 358ms result can be further optimized. The gap exists because Postgres carries immense overhead, specifically in parsing the storage format and extracting relevant tuples.

---

Deconstructing the Postgres Query Engine

When a SQL query is executed, Postgres follows a specific pipeline:

1. Query Plan: The SQL is converted into an internal representation (the "Plan") that dictates the execution strategy.
2. Execution: The engine processes this plan to retrieve and aggregate data.

The Execution Flow

Postgres utilizes the Volcano Model. In this architecture, every node in the plan implements a next() method. The root node calls next() on its child, which calls next() on its child, and so on, until a row is returned.

Miniature Volcano Implementation in Rust

Here is a simplified version of how this logic operates:

use std::hint::black_box;

trait Node {
    fn next(&mut self) -> Option<f64>;
}

struct SeqScan<'a> {
    table: &'a [f64],
    pos: usize,
}

impl Node for SeqScan<'_> {
    fn next(&mut self) -> Option<f64> {
        if self.pos == self.table.len() { return None; }
        let value = self.table[self.pos];
        self.pos += 1;
        Some(value)
    }
}

struct SumAggregate<'a> {
    child: Box<dyn Node + 'a>,
    total: f64,
    done: bool,
}

impl Node for SumAggregate<'_> {
    fn next(&mut self) -> Option<f64> {
        if self.done { return None; }
        while let Some(value) = self.child.next() {
            self.total += value;
        }
        self.done = true;
        Some(self.total)
    }
}

fn main() {
    let table: Vec<f64> = (1..=500_000_000usize).map(|i| i as f64).collect();
    let mut plan = SumAggregate { 
        child: black_box(Box::new(SeqScan { table: &table, pos: 0 })), 
        total: 0.0, 
        done: false, 
    };
    let sum = plan.next().unwrap();
}

Why the Volcano Model is Slow

While the Volcano model is elegant and simple to implement, it introduces severe performance penalties:

• Single-Row Processing: The next() method only handles one row at a time.
• Virtual Dispatch: Because the child node is often a dyn Node (determined at runtime), the CPU cannot effectively use pipelining.
• Function Call Overhead: Millions of function calls are made for a single query, creating massive overhead compared to a tight for loop.