If you run an analytical query on a standard Postgres database containing hundreds of millions of rows, you will quickly hit a wall. A simple query like SELECT SUM(amount) FROM transactions WHERE status = 'completed' can take tens of seconds, if not minutes.
To understand why this happens, we have to look at the core architecture of the Postgres query engine. Postgres was designed in the late 1980s and early 1990s. Even with its legacy architecture, it remains the industry standard because developers choose tools they trust. It uses an execution model known as the Volcano style iterator. In this model, every operator in a query plan-like a sequential scan, a filter, or an aggregation-pulls data from its child operator one row at a time.
When you process millions of rows, this row-by-row approach introduces massive overhead. The CPU has to perform a virtual function call for every single tuple. It has to check null bitmaps, extract attributes, and pass the data up the execution tree. The CPU instruction cache gets saturated, the branch predictor fails to guess the path of the execution loop, and the CPU spends most of its time waiting for memory access rather than doing actual math.
Database engineers have bypassed these limits. By introducing vectorized batch execution, SIMD hardware acceleration, and operator fusion, they have turned Postgres into an analytical speed demon, achieving query speedups of up to 300x.
The Problem with Row-at-a-Time Execution
In a traditional Postgres execution loop, the code looks conceptually like this:
TupleTableSlot* ExecScan(ScanState *node) {
for (;;) {
TupleTableSlot *slot = node->ScanTupleEngine(node);
if (TupIsEmpty(slot))
return NULL;
if (FilterMatches(node, slot))
return slot;
}
}Every time ScanTupleEngine runs, it fetches a single pointer to a tuple, decodes the tuple header, extracts the fields, and evaluates the filter.
On modern CPU architectures, this code is incredibly inefficient. Modern CPUs rely on deep execution pipelines, branch prediction, and instruction-level parallelism. They want to execute the same instruction on consecutive blocks of memory.
With the Volcano model, the CPU cannot predict which function pointer it will call next. It constantly suffers from instruction cache (I-cache) misses. The data is also scattered across different memory locations, leading to data cache (D-cache) misses. The CPU spends its cycles stalled, waiting for data to travel from L3 cache or main memory into the CPU registers.
Vectorized Batch Execution
Vectorization changes the unit of work. Instead of passing a single row up the query plan, the engine passes a vector-a cache-friendly array of values, typically between 1024 and 4096 items.
The execution loop changes from a row-based loop to a vector-based loop:
Vector* ExecVectorScan(VectorScanState *node) {
Vector *batch = GetNextBatch(node);
if (batch->size == 0)
return NULL;
// Evaluate filter on the entire batch at once
FilterBatch(node, batch);
return batch;
}This simple shift has massive implications for performance.
First, the number of virtual function calls drops by a factor of 1000. Instead of calling next() 100 million times, the engine calls it 100,000 times. The function call overhead disappears from the profiling charts.
Second, the data for a column is packed tightly in memory. When the CPU loads the first value of a vector, the hardware prefetcher automatically pulls the subsequent values into the L1/L2 data cache. The CPU almost never stalls waiting for the next item in the array.
Unleashing SIMD Acceleration
Once your data is laid out in contiguous arrays, you can use the vector processing capabilities built into modern CPUs. SIMD (Single Instruction, Multiple Data) allows a CPU to perform the same mathematical or logical operation on multiple data points simultaneously using wide registers.
For instance, an AVX-512 register on a modern Intel or AMD CPU is 512 bits wide. It can hold sixteen 32-bit integers or eight 64-bit floats. With a single CPU instruction, you can add sixteen pairs of integers at the same time.
Consider a simple scan that filters rows where price > 100. In a scalar execution loop, the CPU compares each price one by one:
for (int i = 0; i < batch_size; i++) {
selection_vector[i] = (price_array[i] > 100);
}Compiler auto-vectorization sometimes helps, but it often fails due to complex memory pointer aliasing or control flow. By writing explicit SIMD intrinsics, database engineers can force the compiler to generate optimal vector instructions:
#include <immintrin.h>
void filter_vector_simd(const float* price_array, int batch_size, int* selection_vector, int* output_size) {
__m512 limit = _mm512_set1_ps(100.0f);
int count = 0;
for (int i = 0; i < batch_size; i += 16) {
// Load 16 floats into an AVX-512 register
__m512 prices = _mm512_loadu_ps(&price_array[i]);
// Compare all 16 floats against the limit
__mmask16 mask = _mm512_cmp_ps_mask(prices, limit, _CMP_GT_OQ);
// Expand the bitmask to output indices
while (mask > 0) {
int tz = __builtin_ctz(mask);
selection_vector[count++] = i + tz;
mask &= (mask - 1); // Clear the lowest set bit
}
}
*output_size = count;
}This code loads 16 values, compares them in a single clock cycle, and uses bitwise operations to extract the matching indices. The branch predictor is bypassed entirely because there are no conditional branches inside the main loop. The speedup over the scalar version is often 10x or more for this specific operation.
Operator Fusion
While vectorization speeds up individual operations, it introduces a new bottleneck: intermediate memory storage.
If you have a query like SELECT (price * tax) + shipping FROM orders, a naive vectorized engine will:
- Multiply the
pricevector by thetaxvector, writing the result to a temporary vectortemp1. - Add the
shippingvector totemp1, writing the result to a temporary vectortemp2.
Writing these intermediate arrays back to memory and reading them back in the next step wastes memory bandwidth. Memory bandwidth is often the ultimate limiting factor in analytical query engines.
Operator fusion solves this by combining multiple operations into a single compiled loop. Instead of executing multiple vectorized loops sequentially, the engine generates code that performs all operations on a single data element while it resides in the CPU registers.
In Postgres, this is achieved using LLVM-based Just-In-Time (JIT) compilation. When Postgres receives a query, it can compile the expression tree into machine code at runtime.
Instead of executing generic, interpreted code that looks up data types and calls operator functions, the compiled code looks like this:
// JIT compiled expression evaluation
double evaluate_expression(TupleTableSlot *slot) {
double price = slot->values[0];
double tax = slot->values[1];
double shipping = slot->values[2];
return (price * tax) + shipping;
}This compiled function keeps the values in the CPU's general-purpose registers throughout the evaluation. It eliminates function calls, type checks, and intermediate memory writes.
How to Get This in Postgres Today
Vanilla Postgres has JIT compilation using LLVM, but it still executes queries using the row-by-row Volcano model. It does not natively support vectorized execution or columnar memory layouts. To get the 300x speedups, you have to use extensions or alternative storage engines designed for OLAP workloads.
1. Citus / Hydra Columnar
Hydra (which builds on Citus's columnar work) introduces a columnar storage format directly inside Postgres. It groups data by columns rather than rows and uses vectorized execution paths to process queries. This allows Postgres to avoid scanning unnecessary columns and run vectorized filter operations.
2. PG-Strom
PG-Strom targets GPUs rather than CPUs. It translates Postgres query plans into CUDA code and executes them directly on NVIDIA graphics cards. GPUs are massive SIMD machines, running thousands of parallel execution threads. PG-Strom bypasses the CPU entirely, using PCIe peer-to-peer DMA to stream data from NVMe drives directly to the GPU memory.
3. DuckDB Integration via Foreign Data Wrappers
Many engineers use DuckDB alongside Postgres. DuckDB is a dedicated analytical database designed from the ground up for vectorized execution and operator fusion. Using tools like pg_analytics or DuckDB foreign data wrappers, you can keep your primary transaction engine in Postgres (taking advantage of reliable Postgres transactions) while delegating heavy analytical queries to a vectorized DuckDB instance running on the same data files.
The Trade-offs of Warp Speed
If vectorization and SIMD are so fast, why doesn't vanilla Postgres adopt them for everything?
The answer lies in the workload. Postgres is primarily an OLTP (Online Transaction Processing) database. It is optimized for inserting, updating, and deleting individual rows, which requires managing complex database locks to ensure consistency under heavy write loads.
Vectorization and columnar storage add significant overhead to write operations. When you insert a single row into a columnar table, the database cannot simply append the row to a page. It has to split the row into individual columns, find the correct blocks for each column, and write them separately. This often requires updating multiple compression blocks.
JIT compilation also has a compilation cost. Compiling a query using LLVM can take hundreds of milliseconds. If your query only takes 2 milliseconds to run anyway, JIT compilation makes the query 100x slower. Postgres uses heuristics to only enable JIT when the estimated query cost is high, but these estimates are not always perfect.
For mixed workloads, the challenge is balancing these two architectures. The future of Postgres performance lies in hybrid engines that can dynamically choose between row-based execution for transactions and vectorized execution for analytical scans.
