A recent deep-dive by malisper hit the front page of Hacker News with 285 points. It details how to make PostgreSQL hundreds of times faster for analytics workloads using three techniques: batching, operator fusion, and SIMD instructions. The results are remarkable, and the engineering insights apply far beyond Postgres.
Here's a breakdown of what they did and why it works.
The Problem: Postgres Wasn't Built for Analytics
PostgreSQL is an incredible OLTP database — it handles transactional workloads with ACID guarantees, row-level locking, and a mature query planner. But analytics workloads are fundamentally different. Instead of inserting/updating single rows, you're scanning millions of rows to compute aggregates. Postgres's row-oriented storage format, per-tuple overhead, and execution model are optimized for the wrong access pattern.
The standard solution is to use a columnar store like ClickHouse, Redshift, or DuckDB for analytics. But what if you want to keep your data in Postgres and still get analytic query performance? That's the problem this work solves.
Technique 1: Vectorized Batch Execution
The single biggest performance win comes from batching — processing multiple rows at a time instead of one at a time.
Traditional Postgres execution processes one tuple at a time. Each tuple goes through the executor, gets filtered, projected, and aggregated individually. This means:
- Per-tuple function call overhead
- Poor CPU cache utilization (each tuple brings in a whole cache line but uses only a few bytes)
- No opportunity for SIMD vectorization
The solution is to process tuples in batches of 1,000-10,000 rows. Instead of calling the filter function on each row, you call it on a batch of rows. Instead of summing one value at a time, you sum a vector of values.
The performance impact is dramatic:
- 5-10x from reducing per-tuple overhead
- 3-5x from better cache utilization
- 4-8x from SIMD vectorization of batch operations
- Combined: 50-300x on analytic workloads
The key insight is that modern CPUs are incredibly fast if you keep the pipeline full. The bottleneck isn't computation — it's data movement and function call overhead. Batching minimizes both.
Technique 2: Operator Fusion
The second technique is operator fusion — combining multiple operations into a single pass over the data.
In a traditional Postgres execution plan, a query like SELECT SUM(price) FROM orders WHERE status = 'shipped' generates a plan like:
Aggregate (SUM)
→ Seq Scan (filter: status = 'shipped')
→ Table
Each operator calls the next one for each tuple. The filter calls the scan, gets a tuple, checks the condition, and passes it to the aggregate. This means each tuple goes through multiple function calls, multiple branches, and multiple context switches.
Operator fusion combines the filter and aggregate into a single loop:
for each batch:
if status[i] == 'shipped':
sum += price[i]
This eliminates the function call overhead between operators and allows the compiler to optimize the combined loop. The CPU branch predictor can learn the filter selectivity pattern, and the data stays in registers across operations.
The gain is 2-5x on queries with multiple operators, which compounds with the batching speedup.
Technique 3: SIMD Vectorization
The final technique is SIMD (Single Instruction, Multiple Data) — using CPU vector instructions to process multiple values in a single instruction.
Modern CPUs have 256-bit or 512-bit SIMD registers. A 256-bit register can hold 8 32-bit integers or 4 64-bit floats. A single SIMD instruction can compare 8 values, add 8 values, or compute 8 conditionals simultaneously.
For analytics workloads, SIMD is transformative:
- Filtering: Compare 8 values against a constant in one instruction instead of 8
- Aggregation: Sum 8 values in one instruction using horizontal add
- Hashing: Compute 8 hash values simultaneously for hash joins
The challenge is that Postgres's type system and executor don't natively support SIMD. The solution requires generating specialized code paths for common type combinations and using compiler intrinsics or auto-vectorization.
Postgres 18 introduced experimental support for vectorized aggregates, but the full potential requires extensions or custom execution engines.
The Combined Result
| Technique | Speedup | What It Fixes |
|---|---|---|
| Batching | 50-100x | Per-tuple overhead, cache misses |
| Operator Fusion | 2-5x | Inter-operator call overhead |
| SIMD | 4-8x | Instruction-level parallelism |
| Combined | 300x+ | All of the above |
On a scan of 100M rows with a filter + aggregate, the baseline Postgres takes about 60 seconds. With all three techniques applied, the same query completes in under 0.2 seconds — a 300x speedup that brings Postgres into the same league as purpose-built analytic databases.
Practical Implications
For teams running Postgres and considering a migration to a columnar database for analytics, this work suggests an alternative path: optimize Postgres itself. The three techniques described here can be implemented via:
- Postgres extensions with execution hooks
- Custom executor nodes using the Custom Scan API
- JIT compilation of query plans with vectorized code generation
- Modified executors in a Postgres fork
The third option — improving JIT compilation — is the most promising long-term path. Postgres already compiles query plans to LLVM IR. Extending this to generate vectorized, fused code is a natural evolution.
Why This Matters
The database world has been bifurcating for years: OLTP databases for transactions, OLAP databases for analytics. This work shows that the boundary is permeable. With the right execution engine optimizations, a single database can handle both workloads well.
For small teams, this is huge. Instead of maintaining two databases, two data pipelines, and two query patterns, you can keep everything in Postgres and still get analytic query performance. The operational simplicity is worth more than the raw speedup.
For the broader engineering community, the techniques here — batching, operator fusion, SIMD — are universally applicable. Whether you're building a database, a data processing pipeline, or a game engine, the same principles apply: minimize per-element overhead, keep data in cache, and use the CPU's vector units. The 300x speedup isn't magic. It's just good engineering applied systematically.
Top comments (0)