In-Process Columnar OLAP with DuckDB: Architecture, Vectorized Execution, and Analytics Engineering

1. The Rise of In-Process OLAP

For decades, data architectures were divided into two rigid tiers:

1. Embedded Transactional Storage (OLTP): Dominated by SQLite, where applications embed a lightweight C library directly in-process to execute point lookups and transactions.
2. Distributed Analytical Storage (OLAP): Dominated by ClickHouse, Snowflake, BigQuery, or Amazon Redshift, requiring dedicated multi-node clusters, complex networking, and client-server socket communication.

When engineering local data applications, data pipelines, or interactive dashboards (e.g., in Streamlit or Next.js backends), traditional client-server databases can add avoidable overhead:

• Socket Serialization Overhead: Transferring 5,000,000 rows over TCP sockets involves converting internal binary representations to wire formats and deserializing them back into Python or Node memory.
• Infrastructure Footprint: Managing connection pools, database users, and server instances adds operational surface that a local, single-user workload may not need.

DuckDB solves this by delivering an embedded, serverless, columnar execution engine designed specifically for analytical query processing.

2. Row-Oriented vs Column-Oriented Storage

Transactional engines like PostgreSQL or SQLite store data in row-major format (tuples stored contiguously on disk pages).

Row-oriented (SQLite / Postgres):
[Row 1: ID, Timestamp, User, Amount] [Row 2: ID, Timestamp, User, Amount] ...

If an analytical query calculates the total revenue across 10,000,000 transactions:

SELECT SUM(amount) FROM transactions WHERE status = 'completed';

A row-oriented engine typically has to read whole rows into CPU cache lines (including user strings, timestamps, and IDs), spending memory bandwidth on columns the query never uses.

Column-oriented engines store each column in a contiguous array:

Column-oriented (DuckDB / Parquet):
IDs:      [1, 2, 3, 4, ...]
Status:   ['completed', 'pending', 'completed', ...]
Amounts:  [120.50, 45.00, 310.00, ...]

A columnar engine such as DuckDB only needs to read the status and amount vectors from memory or disk, which improves cache use and lets it scan those columns efficiently.

3. Vectorized Execution & SIMD Processing

Traditional database query engines use the Volcano Iterator Model (Tuple-at-a-time). The physical query plan calls next() on an operator, which returns a single tuple, incurring a virtual function call for every row in the dataset. Across 10,000,000 rows, 10,000,000 virtual function calls stall CPU instruction pipelines.

DuckDB implements Vectorized Engine Execution (Vector-at-a-time, inspired by the VectorWise paradigm):

• Data flows through query operators in fixed-size arrays called DataChunks (typically 2,048 values per vector).
• Instead of looping row by row with function call overhead, arithmetic operations (e.g., additions, aggregations, filters) execute in tight loops over contiguous memory blocks.
• Tight loops over contiguous memory suit the compiler and CPU: they can be compiled into SIMD (Single Instruction, Multiple Data) instructions (such as AVX2 or ARM NEON), which process several values per instruction.

4. Morsel-Driven Parallelism

DuckDB scales across multi-core processors using Morsel-Driven Parallelism (Leis et al., SIGMOD 2014):

• Rather than assigning static partitions to worker threads, the work is divided into small units ("morsels"; the original paper uses morsels of roughly 100,000 tuples).
• Worker threads take available morsels dynamically as they finish their previous one.
• If one thread is slowed by an expensive expression or a cache miss, the other threads keep taking the remaining morsels, which reduces imbalance between cores.

5. Zero-Copy Interoperability with Arrow & Pandas

In Python and Node.js data workflows, moving data between Pandas, Polars, and database engines often consumes more time than the actual computation.

DuckDB integrates with Pandas DataFrames and Apache Arrow tables, and can query them in place instead of first loading them into a separate table. Numeric, NumPy-backed columns can be read without copying; other types, such as Python string objects, may need conversion. The example below is illustrative, not benchmarked:

import duckdb
import pandas as pd

# Generate sample analytical dataset
df = pd.DataFrame({
    'category': ['Electronics', 'Home', 'Apparel'] * 1_000_000,
    'price': [199.99, 45.50, 29.99] * 1_000_000,
    'quantity': [1, 3, 2] * 1_000_000
})

con = duckdb.connect()

# DuckDB resolves the name "df" to the Pandas DataFrame in the local scope and queries it directly
result = con.execute("""
    SELECT
        category,
        SUM(price * quantity) AS total_revenue,
        AVG(price) AS average_price
    FROM df
    GROUP BY category
    ORDER BY total_revenue DESC
""").df()

print(result)

Querying a DataFrame this way avoids a separate load step. How much time that saves depends on the data types and sizes involved.

6. Practical Query Optimization Strategies

1. Filter and Column Pushdown on Parquet Files

DuckDB can read only the columns a query needs and can use Parquet metadata (min/max statistics) to skip row groups that cannot match a filter. Reading from S3 as below also requires the httpfs extension and credentials:

-- Reads only the needed columns and the row groups that can match the filter
SELECT customer_id, SUM(amount)
FROM 's3://bucket/transactions_2025_*.parquet'
WHERE transaction_date >= '2025-06-01'
GROUP BY customer_id;

2. Window Functions vs Self-Joins

A window function is usually simpler to write than a self-join for rolling calculations, and is often faster; measure on your own data:

-- Rolling 7-row average (a 7-day average if there is one row per day)
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_sales;

7. Architectural Boundaries & Limits

While DuckDB is exceptional for analytics, understanding its architectural boundaries is essential:

• Concurrency Model: According to the DuckDB documentation, a database file can be opened read-write by one process, or read-only by several processes, but not both at once. Within one process, several threads can write, and conflicting changes to the same rows fail rather than queue. It is not designed for high-concurrency transactional web apps with many simultaneous writers (use PostgreSQL for OLTP).
• RAM Constraints: While DuckDB supports out-of-core execution (spilling to disk when intermediate aggregations exceed RAM), queries execute fastest when active working sets fit in available system memory.