Vectorized Query Execution: Processing Batches, Not Rows

WHY IT EXISTS: Traditional databases processed data one row at a time. This "iterator model" was inefficient for analytics, with studies showing 50-80% of CPU cycles were wasted on interpreter overhead, like function call costs, rather than doing actual work. Vectorized execution was invented to eliminate this bottleneck.

THE MENTAL MODEL: Instead of a cashier scanning each grocery item individually (row-by-row), imagine a factory machine processing an entire tray of items at once. The tray is a "vector" or batch of data. This approach amortizes the setup cost of an operation across thousands of items, making the overall process vastly more efficient.

HOW IT WORKS: Each query operator, like a filter, becomes a function that acts on a batch of column values, typically 1024 to 4096 at a time. These batches are sized to fit in the CPU's fast L1 or L2 cache. The core logic is a tight loop over the batch. Because this loop is simple and predictable, compilers can automatically optimize it into SIMD (Single Instruction, Multiple Data) instructions like AVX2. This allows a single CPU instruction to process 8, 16, or more values simultaneously. Instead of copying data, a filter operator writes the indices of matching rows to a "selection vector," which tells downstream operators which rows to process, avoiding costly data movement.

WHEN TO USE IT: This is the dominant model for modern analytical (OLAP) databases. It's ideal for data warehousing, real-time analytics, and observability systems where queries scan and aggregate billions of rows. Engines like ClickHouse, DuckDB, Snowflake, and Databricks Photon are built on this principle to deliver sub-second query performance.

WHEN NOT TO USE IT: The model is less suited for transactional (OLTP) workloads characterized by many small, single-row reads and writes, like fetching a user's profile. Row-oriented databases like PostgreSQL and MySQL are optimized for these low-latency, single-row operations and use the traditional iterator model.

ONE CANONICAL EXAMPLE: A query filtering a billion-row table for price > 100. A vectorized engine loads the price column in batches of 4096. A tight loop compares each price to 100, which the compiler turns into SIMD instructions, comparing many prices at once. The indices of rows that match are written to a selection vector. A subsequent SUM(quantity) operator then uses this vector to process only the relevant rows from the quantity column, again in efficient batches.