PostgreSQL processes queries one row at a time, which is slow for analytical scans. Batching rows into vectors, fusing operators together, and using SIMD instructions can cut execution time dramatically on the right workloads.
Key takeaways
- PostgreSQL’s default executor uses a row-at-a-time iterator model that spends much of its time on per-row overhead rather than on the arithmetic a query actually requires.
- Vectorised execution processes columns in batches of many values at once, amortising that overhead across a whole batch instead of paying it per row.
- Operator fusion combines adjacent steps in a query plan so that intermediate results stay in registers or cache instead of being written out and read back.
- SIMD instructions let a single CPU instruction apply the same operation to several values simultaneously, which only helps when data is laid out contiguously.
- Very large speed-up figures quoted for analytical engines usually describe narrow benchmark queries and rarely transfer unchanged to a mixed production workload.
What is actually being proposed here
The idea under discussion is not a new database but a different way of executing queries inside an existing one. PostgreSQL’s executor is built on a classic design in which each node in the query plan pulls one tuple at a time from the node beneath it. That design is flexible and easy to reason about, and it handles the transactional workloads Postgres was built for well. It is, however, expensive per row: every tuple passed upward involves function calls, checks on null handling, and repeated interpretation of expressions that are known before the query begins.
Vectorised execution changes the unit of work. Instead of one tuple, a node passes a batch — typically some hundreds or a few thousand values from a single column — to the node above it. The per-batch bookkeeping cost is paid once rather than once per row. Operator fusion goes further by collapsing steps that would otherwise be separate: a filter followed by an arithmetic expression followed by an aggregation can be compiled into a single tight loop over a batch, so intermediate arrays are never materialised. SIMD, short for single instruction, multiple data, is the hardware layer beneath both. Modern CPUs can add, compare or mask several values in one instruction, which is only possible if those values sit next to each other in memory in a uniform format.
Why this is being discussed now
Analytical workloads have drifted towards databases that were originally chosen for transactions. Teams that already run PostgreSQL for their application increasingly want to run dashboards, reporting queries and ad hoc aggregations against the same data, rather than move it into a separate warehouse. That creates pressure to close the performance gap between Postgres and purpose-built columnar engines.
At the same time, PostgreSQL’s extension mechanism makes it possible to add columnar storage, alternative executors and custom scan nodes without forking the core. Several projects have taken this route in various forms. The techniques themselves — vectorisation, fusion, SIMD — are long established in the database research literature and in commercial analytical systems; what is new is the effort to fit them into Postgres without disrupting its existing behaviour. Discussion tends to surface whenever someone publishes concrete numbers from such an attempt.
The background a newcomer needs
Two distinctions do most of the explanatory work. The first is row storage versus column storage. In row storage, all the fields of one record sit together, which suits queries that fetch or update whole records. In column storage, all the values of one field sit together, which suits queries that read one or two columns across millions of records and ignore the rest. Analytical queries are usually the second kind, and column storage also compresses better because neighbouring values are similar.
The second is the executor model. The row-at-a-time approach is sometimes called the Volcano or iterator model. Its alternative is either vectorised execution, as described above, or compilation, where the query is turned into machine code before it runs. These are not mutually exclusive, and real systems mix them.
SIMD sits underneath. It requires contiguous, uniformly typed data, which is why it pairs naturally with columnar layouts and batches. Applying SIMD to scattered row tuples with per-field null flags and variable-length text gains little, because the cost of gathering the data into the right shape can exceed the saving.
Who is affected, and how
Teams running reporting queries against an operational PostgreSQL database stand to gain most. Queries that scan large tables, filter on a few columns and aggregate — counts, sums, averages grouped by a handful of keys — are the shape these techniques target. If such queries currently take minutes and the underlying data fits the columnar model, the difference can be substantial.
Teams whose workload is dominated by short transactional queries, primary-key lookups, or updates gain little and may lose. Columnar storage generally makes single-row inserts and updates more expensive, and vectorised execution adds overhead to queries that touch only a handful of rows. There is also an operational cost: extensions must be installed, maintained across major version upgrades, and trusted with correctness in a system where correctness matters more than speed.
Application developers are affected indirectly. Where these techniques are implemented as an extension with a custom scan node, the SQL surface usually does not change, which means existing queries can benefit without rewriting. Where they require a separate table type or a copy of the data, the application must know which copy to query.
Where informed people disagree
The main disagreement is over how much of the reported gain generalises. Large multipliers are usually measured on benchmark suites designed to stress analytical scans, on data that fits in memory, with warm caches, and on queries chosen to exercise the new code path. Sceptics point out that real workloads include joins with awkward selectivity, text columns, user-defined functions and correlated subqueries, and that the executor is only one contributor to total latency — planning, I/O, network transfer and client-side processing also matter. Where a query is bound by disk reads, a faster executor changes little.
A second disagreement concerns architecture. One camp argues that the right answer is to build these capabilities into PostgreSQL itself, so that all users benefit and the planner can reason about them. Another argues that the transactional core should not absorb this complexity, and that extensions or separate analytical systems are the cleaner boundary. A third position is that the effort is better spent on a dedicated engine that reads from Postgres, rather than on making Postgres into something it was not designed to be.
There is also honest technical disagreement about how much of the benefit comes from each layer. Batching alone removes a large share of interpretive overhead; fusion and SIMD then act on what remains. Attributing a headline number to SIMD specifically is harder than it looks, and careful write-ups usually separate the contributions.
What this means in practice
Before pursuing any of this, it is worth establishing where time is actually going. PostgreSQL’s EXPLAIN (ANALYZE, BUFFERS) shows which plan nodes dominate and whether a query is reading from cache or disk. If most of the time is spent waiting on I/O, storage and indexing changes will help more than a faster executor.
Conventional tuning should be exhausted first. Appropriate indexes, including partial and covering indexes, table partitioning to prune irrelevant data, materialised views for repeated aggregations, and correct planner statistics all address common analytical slowness without new dependencies. Adjusting work memory and parallel worker settings can also change plan shape substantially.
If those are exhausted and the workload genuinely looks like large scans with aggregation, columnar or vectorised extensions become worth evaluating. The evaluation should use a copy of the real data and the real queries, not a benchmark. Correctness checks matter: results should match those from the standard executor exactly, including edge cases involving nulls, numeric precision and time zones. Upgrade paths, licensing and maintenance status of any extension deserve as much attention as its benchmark figures.
What to watch next
Watch whether vectorisation work moves from extensions towards the core PostgreSQL codebase, which would be visible in the project’s public mailing list discussions and release notes. Watch also for standardised comparisons — evaluations run by parties without an interest in the result, on published data and queries, are far more informative than vendor-published multipliers.
On the hardware side, wider SIMD register widths and larger core counts continue to shift the balance between compute and memory bandwidth, which changes which optimisations pay off. Finally, watch how these approaches handle the awkward cases: joins, strings, and the mixed workloads that most production databases actually serve.
Frequently asked questions
What does vectorised query execution mean in a database?
Vectorised execution means the query engine processes data in batches rather than one row at a time. Each step in the plan receives an array of values from a column, applies its operation to the whole array, and passes the result onward. This spreads the fixed overhead of function calls and type checks across many values, which usually reduces total CPU time on queries that scan large amounts of data.
Does PostgreSQL support columnar storage natively?
PostgreSQL’s built-in heap storage is row-oriented. Columnar storage is available through extensions and foreign data wrappers rather than as a core table type. Availability, maturity and licensing vary considerably between projects, and support for a given PostgreSQL major version is not guaranteed. Anyone considering one should check its current status against the specific version they run rather than relying on older write-ups.
Will these techniques speed up every slow query?
No. They target queries that scan many rows and aggregate a small number of columns. Queries dominated by disk reads, network transfer, planning time, row-by-row updates or single-record lookups see little benefit and can be slower under columnar storage. Establishing the actual bottleneck with EXPLAIN (ANALYZE, BUFFERS) before changing anything avoids spending effort on the wrong layer of the system.
What is operator fusion?
Operator fusion merges adjacent operations in a query plan into a single compiled loop. Rather than filtering a batch, writing the result, then computing an expression over that result, a fused implementation performs both inside one pass. This avoids materialising intermediate arrays and keeps working data in CPU registers or cache, which reduces memory traffic — often the limiting factor in analytical processing.
Should I move analytics to a separate warehouse instead?
That depends on data volume, freshness requirements and operational capacity. A separate analytical system generally handles very large datasets better but adds a pipeline to build and monitor, and introduces lag between the operational data and the reports. Keeping analytics in PostgreSQL avoids that complexity and is often adequate at moderate scale. There is no universally correct answer.
How reliable are large speed-up claims for database benchmarks?
Treat them as an upper bound observed under specific conditions, not a prediction. Benchmark results depend heavily on the query set, data distribution, cache state, hardware and configuration, and are frequently produced by parties with an interest in the outcome. The only figure that matters for a given deployment is the one measured on its own data and queries, with correctness verified against the existing executor.
Sources and further reading
- The PostgreSQL project’s official documentation, particularly the sections covering query planning, EXPLAIN output and parallel query execution.
- Academic database systems literature on vectorised execution and query compilation, widely available through university and conference proceedings archives.
- CPU vendor architecture and optimisation manuals, which document the SIMD instruction sets and their data alignment requirements.
- Public technical discussion on developer forums and the PostgreSQL mailing lists, useful for tracking which proposals are under active consideration.
Surfaced from the hackernews signal “database query optimisation techniques”. AI-assisted draft, editorially reviewed.
