DuckDB Internals Deep Dive — 임베디드 OLAP, 벡터화 실행, Morsel-Driven 병렬, Storage Format 완전 정복 (2025)

import duckdb

con = duckdb.connect('my.duckdb')  # 또는 ':memory:'
con.execute("CREATE TABLE users (id INT, name VARCHAR)")
con.execute("INSERT INTO users VALUES (1, 'Alice')")
result = con.execute("SELECT * FROM users").fetchall()

my_data.duckdb  ← 하나의 파일에 모든 테이블, 인덱스

Table "events":
  Row Group 1 (122,880 rows):
    Column "timestamp": [bytes...]
    Column "user_id": [bytes...]
    Column "event_type": [bytes...]
    Column "value": [bytes...]
  Row Group 2:
    ...

for each row:
    apply_filter(row)
    transform(row)
    aggregate(row)

for each batch of 1024 rows:
    filter_batch()
    transform_batch()
    aggregate_batch()

struct Vector {
    Type type;           // INT, VARCHAR, DOUBLE, etc.
    VectorType vtype;    // FLAT, CONSTANT, DICTIONARY, SEQUENCE
    ValidityMask nulls;  // 각 행의 null 여부
    void* data;          // 실제 값
    idx_t count;         // 현재 행 수
};

struct DataChunk {
    vector<Vector> data;
    idx_t count;  // 현재 행 수 (최대 1024)
};

SELECT user_id, SUM(value)
FROM events
WHERE event_type = 'click'
GROUP BY user_id

HashAggregate (GROUP BY user_id, SUM(value))
    ↓
Filter (event_type = 'click')
    ↓
TableScan (events)

class Operator {
    virtual unique_ptr<DataChunk> GetChunk() = 0;
};

  Filter → Exchange → Aggregate
            (무거운)

Pipeline 1: TableScan → Filter → PartialHashAggregate
Pipeline 2: FinalHashAggregate → Output

Input morsels:  [M1] [M2] [M3] [M4] [M5] ...

Worker 1: pull M1 → process → output
Worker 2: pull M2 → process → output
Worker 3: pull M3 → process → output
Worker 4: pull M4 → process → output
...

Phase 1: 각 worker가 partial hash table 빌드 (local morsel 기반)
Phase 2: Partial hash tables를 merge
Phase 3: Final hash table에서 output

A (1M) JOIN B (100) JOIN C (10)

SELECT * FROM events WHERE timestamp > '2024-01-01'

SELECT name, email FROM users WHERE age > 30

import duckdb
import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob'],
    'age': [30, 25]
})

# DataFrame을 SQL로 직접 쿼리
result = duckdb.sql("SELECT * FROM df WHERE age > 28").df()
#                                    ^^ Python 변수를 테이블로
print(result)

import polars as pl
df = pl.DataFrame({'a': [1,2,3]})
duckdb.sql("SELECT * FROM df").pl()  # Polars 결과

import pyarrow as pa
tbl = pa.table({'a': [1,2,3]})
duckdb.sql("SELECT * FROM tbl").arrow()

rel = duckdb.sql("SELECT * FROM df")
    .filter("age > 28")
    .project("name, age * 2 as double_age")
    .aggregate("SUM(double_age)")

# cell 1
import duckdb
con = duckdb.connect(':memory:')

# cell 2
%load_ext sql  # ipython-sql 확장
%sql duckdb:///my.duckdb

# cell 3
%%sql
SELECT * FROM my_table LIMIT 10

df = pd.read_parquet('data.parquet', dtype_backend='pyarrow')

SELECT * FROM 'data.parquet' WHERE year = 2024

SELECT * FROM 'logs/*.parquet'
SELECT * FROM read_parquet(['f1.parquet', 'f2.parquet'])

data/year=2024/month=01/day=01/part.parquet

SELECT * FROM 's3://bucket/data.parquet'
SELECT * FROM 'https://example.com/data.csv'

SELECT * FROM 'data.csv'  -- 자동 스키마 감지
SELECT * FROM read_json('data.json', auto_detect=true)

SELECT item->>'name' AS name, (item->>'price')::FLOAT AS price
FROM 'items.json'

result = con.execute("SELECT * FROM t").fetch_arrow_table()

<script type="module">
import * as duckdb from '@duckdb/duckdb-wasm';

const JSDELIVR_BUNDLES = duckdb.getJsDelivrBundles();
const bundle = await duckdb.selectBundle(JSDELIVR_BUNDLES);

const worker = new Worker(bundle.mainWorker);
const logger = new duckdb.ConsoleLogger();
const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);

const conn = await db.connect();
const result = await conn.query(`
    SELECT * FROM 'https://example.com/data.parquet' LIMIT 10
`);
</script>

import pandas as pd

df = pd.read_parquet('large.parquet')
grouped = df[df['event_type'] == 'click'].groupby('user_id')['value'].sum()
top10 = grouped.nlargest(10)

import duckdb

result = duckdb.sql("""
    SELECT user_id, SUM(value) AS total
    FROM 'large.parquet'
    WHERE event_type = 'click'
    GROUP BY user_id
    ORDER BY total DESC
    LIMIT 10
""").df()

con = duckdb.connect('warehouse.duckdb')

# Extract: API/file에서
con.execute("""
    CREATE OR REPLACE TABLE raw_events AS
    SELECT * FROM read_parquet('s3://bucket/events/*.parquet')
""")

# Transform
con.execute("""
    CREATE OR REPLACE TABLE clean_events AS
    SELECT
        user_id,
        event_type,
        CAST(timestamp AS TIMESTAMP) AS ts,
        value
    FROM raw_events
    WHERE value > 0
""")

# Load
con.execute("COPY clean_events TO 'output.parquet' (FORMAT PARQUET)")

%load_ext sql
%sql duckdb:///analysis.duckdb

%%sql
SELECT
    date_trunc('month', purchase_date) AS month,
    category,
    SUM(amount) AS revenue
FROM sales
GROUP BY 1, 2
ORDER BY 1, 2

# profiles.yml
my_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /tmp/my.duckdb

import polars as pl
import duckdb

# Polars DataFrame
df = pl.read_parquet('data.parquet')

# DuckDB로 SQL 쿼리
result = duckdb.sql("SELECT name, SUM(value) FROM df GROUP BY name").pl()
#                                                                    ^^
#                                                         Polars 결과로 반환

INSTALL httpfs;
LOAD httpfs;

-- 이제 S3 접근 가능
SELECT * FROM 's3://bucket/file.parquet';

INSTALL 'my_extension' FROM 'https://example.com/';
LOAD 'my_extension';

SELECT * FROM iceberg_scan('s3://bucket/iceberg-table');

로컬 (노트북):
  - 사용자 상호작용
  - 작은 데이터 캐시
  - 최근 쿼리

클라우드 (MotherDuck):
  - 큰 데이터셋 저장
  - 공유 데이터
  - 무거운 계산

DuckDB:      1x (baseline)
PostgreSQL:  20-50x slower
SQLite:      100x+ slower
ClickHouse:  0.5-0.8x (약간 빠름)

Pandas:  45초 (메모리 부족 위험)
Polars:  12초
DuckDB:  8초

입력: 10 GB CSV

Spark (local mode): 120초 (JVM startup + plan + execute)
Spark (cluster):    40초 (cluster에 비용 있음)
DuckDB:             8초

SET memory_limit='8GB';

SET threads=8;

SET temp_directory='/fast/ssd/tmp';

EXPLAIN ANALYZE SELECT * FROM t WHERE x > 10;

PRAGMA enable_profiling;

┌─────────────────────────────────────────────────────┐
│                DuckDB Cheat Sheet                    │
├─────────────────────────────────────────────────────┤
│ 철학:                                                  │
│   Embedded OLAP database                             │
│   "SQLite for analytics"                             │
│   단일 파일, 단일 프로세스                             │
│   No server, no lock                                 │
│                                                       │
│ 저장:                                                  │
│   컬럼나                                               │
│   Row groups (122,880 rows)                          │
│   Compression: RLE, bitpack, dict, FSST             │
│   Checkpoint + WAL                                   │
│                                                       │
│ 실행:                                                  │
│   Vector-based (1024 rows per chunk)                 │
│   SIMD friendly                                      │
│   Late materialization                               │
│   Pipeline operators                                 │
│                                                       │
│ 병렬:                                                  │
│   Morsel-Driven Parallelism                          │
│   ~100K rows per morsel                              │
│   Work stealing                                      │
│   거의 선형 확장                                      │
│                                                       │
│ Optimizer:                                            │
│   Rule-based (pushdown, pruning, CSE)                │
│   Cost-based join ordering                           │
│   HyperLogLog distinct count                         │
│   Row group skipping (min/max)                       │
│                                                       │
│ Python:                                               │
│   pip install duckdb                                 │
│   Zero-copy DataFrame 접근 (Pandas, Polars, Arrow)   │
│   Relational API (lazy)                              │
│   Jupyter ipython-sql 통합                           │
│                                                       │
│ 파일 통합:                                             │
│   Parquet (predicate pushdown)                       │
│   Arrow (zero-copy)                                  │
│   CSV (smart parser)                                 │
│   JSON (nested 지원)                                 │
│   S3 / HTTP (httpfs extension)                       │
│                                                       │
│ 브라우저:                                              │
│   DuckDB-WASM                                        │
│   Observable, Malloy, Evidence                       │
│                                                       │
│ Extensions:                                           │
│   httpfs, postgres_scanner, iceberg, delta          │
│   fts, spatial, vss (vector search)                  │
│                                                       │
│ MotherDuck:                                           │
│   관리형 서비스                                        │
│   Hybrid execution (local + cloud)                   │
│   무료 티어                                           │
│                                                       │
│ vs 경쟁자:                                             │
│   Pandas: 10배 빠름, 큰 데이터 OK                     │
│   Polars: 유사 철학, DataFrame API                    │
│   SQLite: OLAP에 훨씬 빠름                            │
│   ClickHouse: DuckDB가 임베디드                      │
│   Spark: DuckDB가 단일 머신에서 빠름                  │
│                                                       │
│ 튜닝:                                                  │
│   memory_limit, threads                              │
│   EXPLAIN ANALYZE                                    │
│   Projection + predicate pushdown                    │
│   COPY for bulk load                                 │
└─────────────────────────────────────────────────────┘