Why DuckDB Processes Millions of Rows Without Indexes: Columnar Storage Meets Vectorization

The Problem: Analytical Queries That Should Be Fast But Aren't

When you're analyzing a 10GB CSV file with Python pandas, you've probably noticed that simple aggregations like SELECT COUNT(*) or SELECT department, AVG(salary) GROUP BY department can still take seconds or minutes. Traditional row-oriented databases require explicit indexes to speed these queries, but analytical workloads rarely benefit from index maintenance overhead.

The Thesis: Columnar Storage + Vectorization = Automatic Speed

DuckDB solves this by storing data column-wise rather than row-wise, and by processing data in vectors (batches of 128-1024 rows at a time) instead of individual rows. This means analytical queries automatically skip indexes and still fly.

How Columnar Storage Changes the Game

In a row-store, all columns for a single record are stored together. To compute AVG(salary), the database must read every column for every row, even though it only needs the salary column. In a column-store, all salary values are stored contiguously in memory. This gives two key advantages:

1. Compression: Similar values compress better. Salaries of 70000, 72000, 71000 compress to a fraction of their original size.
2. Selective reads: Only the salary column needs to be read from disk, reducing I/O by 90% or more for wide tables.

Vectorization: Processing in Batches, Not Rows

Traditional databases process one row at a time. DuckDB's vectorized execution processes 1024 rows simultaneously using SIMD (Single Instruction, Multiple Data) CPU instructions. When filtering WHERE salary > 50000, DuckDB can compare 1024 salary values in a single CPU instruction rather than 1024 separate comparisons.

A Practical Example: Analyzing Employee Data

import duckdb

# Create sample data - in practice, this would be read_csv('large_file.csv')
con = duckdb.connect()
con.execute("CREATE TABLE employees AS SELECT * FROM (VALUES (1, 'Engineering', 85000), (2, 'Marketing', 65000), (3, 'Engineering', 92000), (4, 'Sales', 58000), (5, 'Engineering', 78000)) AS t(id, department, salary)")

# This query benefits from columnar storage and vectorization
result = con.execute("""
    SELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
""").fetchall()

print(result)  # [('Engineering', 3, 85000.0), ('Marketing', 1, 65000.0), ('Sales', 1, 58000.0)]

The query compiler automatically determines that it only needs the department and salary columns, reads them contiguously, and processes them in vectors. No indexes, no manual tuning.

Trade-offs and Limitations

Write performance

Columnar storage excels at reads but can slow bulk inserts since new data must be sorted into columnar format. DuckDB mitigates this with append-only storage and background optimization.

Memory usage

Vectorized processing requires loading entire vectors into memory. Queries returning millions of rows may need explicit chunking or result set management.

Selective queries

Queries filtering on a single row out of millions don't fully benefit from vectorization since the CPU still processes the entire vector before filtering.

Verification: See It in Action

Run this to observe columnar scan performance:

pip install duckdb
python -c "import duckdb; con = duckdb.connect(); con.execute(\"CREATE TABLE test AS SELECT range(10000000) as id, range(10000000) as value\"); import time; start = time.time(); con.execute('SELECT AVG(value) FROM test WHERE id > 5000000').fetchone(); print(f'Query took {time.time() - start:.3f} seconds')"

The Bottom Line

If you're doing analytical work in Python or R, DuckDB's columnar storage and vectorization mean you get index-like performance automatically. Load your CSV directly with read_csv_auto(), run your aggregations, and let DuckDB's query compiler figure out the optimal execution plan. The trade-off is slightly slower writes, but for analytical workloads that's almost always worth it.