DuckDB Internals: Vectorized Execution, Columnar Storage, and Query Processing

# DuckDB processes data in vectors (1024 rows typical)
# Instead of: for each row → process
# DuckDB does: for each 1024 rows → process in batch

// Simplified vector structure
struct Vector {
    VectorType type;        // FLAT, CONSTANT, DICTIONARY
    data_type;             // Column data type
    data;                  // Actual data (flat array)
    nulls;                 // Null bitmap
    selection;             // Selection vector for filtering
    length;                // Number of elements
};

-- DuckDB optimizes these operations
SELECT name, salary * 1.1 FROM employees;  -- Batch multiplication
SELECT * FROM users WHERE age > 25;         -- Batch filtering
SELECT COUNT(*) FROM events;                 -- Aggregation over vector

-- Data is stored column-by-column
-- employees table:
--   - names column: [John, Jane, Bob, ...]
--   - salaries column: [50000, 60000, 70000, ...]
--   - departments column: [Eng, Sales, Eng, ...]

-- DuckDB automatically compresses data
-- Compression schemes:
-- - Dictionary encoding (repeating strings)
-- - Run-length encoding (consecutive duplicates)
-- - Bit packing (booleans, small integers)
-- - Gorilla compression (timestamps)

-- Check compression
PRAGMA table_info('employees');

-- Parser converts SQL to parse tree
-- Input: SELECT name, salary * 1.1 FROM employees WHERE department = 'Eng'

-- Output: Parse tree
-- SELECT
--   ├── name
--   └── WHERE
--       └── department = 'Eng'

-- Resolver performs:
-- 1. Type checking
-- 2. Name resolution
-- 3. Function binding

-- Example error from resolver:
-- SELECT name + 1 FROM employees
-- ERROR: Cannot add VARCHAR to INTEGER

-- Optimizer applies transformations:
-- 1. Predicate pushdown
-- 2. Column pruning
-- 3. Join reordering
-- 4. Constant folding

-- Example: Push down filter
-- Before: SELECT * FROM (SELECT * FROM t WHERE x > 5) WHERE y > 10
-- After: SELECT * FROM t WHERE x > 5 AND y > 10

-- Physical plan determines execution strategy
-- Example: Hash Join vs Nested Loop

EXPLAIN SELECT * FROM a JOIN b ON a.id = b.id;

-- Possible plans:
-- - HASH_JOIN (good for large tables)
-- - MERGE_JOIN (good for sorted inputs)
-- - CROSS_JOIN (cartesian product)

-- DuckDB uses MVCC (Multi-Version Concurrency Control)
-- - Write-Ahead Log (WAL)
-- - Undo buffer
-- - Checkpoint mechanism

-- Transaction isolation
BEGIN TRANSACTION;
UPDATE employees SET salary = salary * 1.1;
COMMIT;

-- DuckDB supports:
-- - READ COMMITTED (default)
-- - REPEATABLE READ (via transaction)
-- - SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Create index
CREATE INDEX idx_employees_dept ON employees(department);

-- Index types:
-- - ART (Adaptive Radix Tree) for equality
-- - Skip list for ordered data

-- Index usage
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

-- DuckDB automatically gathers statistics
-- Statistics used for query planning

-- Force index usage
SELECT * FROM employees USING INDEX idx_employees_dept;

-- Each vector allocates contiguous memory
-- Example: 1024 integers = 4KB
-- Example: 1024 doubles = 8KB

-- Memory allocation
-- - Stack: small vectors
-- - Heap: larger vectors

-- DuckDB allocates memory in segments
-- - Small allocations (< 128KB): direct malloc
-- - Large allocations: memory-mapped

-- Check memory usage
SELECT * FROM duckdb_memory();

-- DuckDB supports:
-- - Integer types (TINYINT to HUGEINT)
-- - Floating-point (FLOAT, DOUBLE)
-- - Decimal (DECIMAL)
-- - String types (VARCHAR, BLOB)
-- - Date/Time (DATE, TIME, TIMESTAMP)
-- - Arrays (v0.10+)
-- - JSON (via extension)

-- Type representation
-- Each type has:
-- - Physical representation (bytes)
-- - Internal type ID
-- - Comparison functions

-- DuckDB is highly extensible
-- Core features as extensions:
-- - json: JSON support
-- - spatial: GIS support
-- - httpfs: HTTP file system
-- - mysql: MySQL scanner
-- - postgres: PostgreSQL scanner

-- Install extension
INSTALL json;
LOAD json;

-- List installed extensions
SELECT * FROM duckdb_extensions();

-- Extensions can add:
-- - New functions
-- - New table functions
-- - New storage handlers
-- - New data types

-- Example: Custom function
-- Defined in C++ or Python

-- Typical performance:
-- - 10M rows aggregation: < 1 second
-- - 100M rows join: < 10 seconds
-- - 1B rows scan: < 1 minute

-- Comparison with SQLite:
-- DuckDB: ~10-100x faster for analytics

-- 1. Use appropriate data types
CREATE TABLE test (
    id INTEGER,     -- Not VARCHAR
    amount DECIMAL(10,2)  -- Not DOUBLE
);

-- 2. Filter early
SELECT * FROM (
    SELECT * FROM large_table WHERE date > '2025-01-01'
) t GROUP BY category;

-- 3. Use indexes for selective queries
CREATE INDEX idx_date ON events(date);

-- 1. Use COPY for bulk loading
COPY large_table FROM 'data.csv' (AUTO_DETECT TRUE);

-- 2. Batch inserts
INSERT INTO table VALUES (1,'a'),(2,'b'),(3,'c');

-- 3. Parallel file reading
SELECT * FROM read_csv_auto('*.csv', parallel=true);

-- Bad: Using cursors for large data
DECLARE cur CURSOR FOR SELECT * FROM large_table;
FETCH cur;

-- Good: Vectorized processing
SELECT * FROM large_table;  -- Automatically vectorized

-- Bad: Using TEXT for IDs
CREATE TABLE bad (id TEXT);

-- Good: Using appropriate types
CREATE TABLE good (id INTEGER);

-- Default should use all cores
-- But verify:
SELECT current_setting('threads');

-- For very large datasets, increase
SET threads = 16;