vectorized query
**Vectorized Database Execution** is the **query processing strategy that operates on batches (vectors) of column values simultaneously using SIMD instructions and cache-friendly memory access patterns** — replacing the traditional row-at-a-time "Volcano" iterator model with column-at-a-time batch processing that achieves 10-100x better throughput for analytical queries by maximizing CPU hardware utilization.
**Traditional vs. Vectorized Execution**
| Aspect | Row-at-a-Time (Volcano) | Vectorized (Column Batches) |
|--------|------------------------|---------------------------|
| Processing unit | 1 row per call | 1000+ values per call |
| Function call overhead | 1 per row per operator | 1 per batch per operator |
| CPU branch prediction | Poor (type dispatch per row) | Excellent (tight loop) |
| Cache utilization | Poor (wide rows evict cache) | Excellent (column values contiguous) |
| SIMD utilization | None | Full (AVX2/AVX-512) |
| Throughput | ~100 MB/s | ~10 GB/s |
**How Vectorized Execution Works**
1. **Columnar storage**: Each column stored contiguously in memory.
2. **Batch processing**: Read 1024 values of a column into a vector.
3. **Primitive operations**: Apply operation to entire vector in a tight loop.
- Example: `filter(age > 30)` → compare 1024 age values at once using SIMD.
4. **Selection vectors**: Instead of copying filtered rows, use a selection vector (indices of qualifying rows).
5. **Pipeline**: Multiple operators process the same batch before moving to next batch → stays in L1/L2 cache.
**SIMD in Practice**
- **AVX-512**: Process 16 × 32-bit integers per instruction (512 bits).
- **Hash computation**: Hash 16 keys simultaneously for hash join probing.
- **Comparison**: Compare 16 values against filter predicate in 1 cycle.
- **Gather/Scatter**: Load 16 non-contiguous values for join lookups.
**Systems Using Vectorized Execution**
| System | Approach | Language |
|--------|---------|----------|
| DuckDB | Full vectorized engine | C++ |
| ClickHouse | Vectorized column processing | C++ |
| Velox (Meta) | Vectorized execution library | C++ |
| Apache Arrow DataFusion | Vectorized over Arrow columnar | Rust |
| Snowflake | Vectorized cloud engine | C++ |
| MonetDB | Pioneered column-at-a-time | C |
**Vectorized vs. Compiled (Codegen)**
- **Vectorized**: Interprets operators but processes data in batches.
- Advantage: Simpler implementation, good cache behavior.
- **Code Generation (HyPer/Spark)**: JIT-compiles query into native code.
- Advantage: Eliminates all interpretation overhead — maximum IPC.
- **Modern trend**: Hybrid — vectorized execution with selective JIT compilation for hot loops.
**Performance Impact**
- Analytical query on 1 billion rows:
- Row-at-a-time: 100 seconds.
- Vectorized: 2-5 seconds.
- Vectorized + SIMD: 1-2 seconds.
- Improvement comes from: Less function call overhead, better cache utilization, SIMD parallelism, branch prediction improvement.
Vectorized execution is **the dominant paradigm for modern analytical database engines** — by aligning query processing with how modern CPUs actually work (wide SIMD units, deep caches, branch prediction), it extracts an order of magnitude more performance from the same hardware compared to traditional row-oriented processing.