DuckDB

Part I: The mental model

SQL text  "SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city"
   |
Parser        (src/parser, Postgres-derived grammar)  -> statement tree
   |
Binder        (src/planner/binder)  names -> catalog entries, types resolved,
   |           'listings.parquet' -> replacement scan -> parquet table function
Logical plan  LogicalGet -> LogicalAggregate -> LogicalProjection
   |
Optimizer     (src/optimizer)  pushdown, statistics propagation, join order
   |
Physical plan (src/execution)  TABLE_SCAN -> HASH_GROUP_BY -> PROJECTION
   |
Pipelines     (src/parallel)  plan broken at pipeline breakers,
   |           worker threads pull morsels of input through each pipeline
Vectors       DataChunks of up to 2048 values per column flow through operators
   |
Result        columnar chunks handed to the host process (pandas, Arrow, CLI)

The one-sentence identity: DuckDB is a complete analytical SQL engine, parser to storage, compiled into a library that lives inside your process and executes queries as parallel pipelines of column vectors. There is no port to connect to and no daemon to manage; "the database" is a stack of C++ objects behind a connection handle, and the data it queries is as often a Parquet file or a pandas DataFrame sitting next to it in memory as it is DuckDB's own storage file.

The diagram is the textbook database pipeline on purpose, and that is what makes the repository such a good read: each named stage is a directory. What distinguishes DuckDB from the textbook is concentrated in the bottom half. Operators do not exchange one row at a time; they exchange chunks of column vectors, up to 2048 values per call, so interpreter overhead is amortized to near nothing while inner loops stay cache-resident. And the executor does not assign static partitions to threads; it breaks the plan into pipelines and lets a pool of workers pull small fragments of input, morsels, through each pipeline until the input is gone, which keeps every core busy regardless of skew.

Hold both halves together: the top half is why DuckDB speaks thoroughly standard SQL with a real optimizer, and the bottom half is why it routinely embarrasses row-at-a-time engines and single-threaded dataframe code on analytical scans, joins, and aggregations.

Part II: Using it

Install

The CLI installs with one line on Linux and macOS, or via Homebrew; the Python client is a wheel with zero dependencies:

curl https://install.duckdb.org | sh     # Linux and macOS
brew install duckdb                       # macOS alternative
pip install duckdb                        # Python client, same engine

I verified this chapter against the 1.5 line (1.5.0 "Variegata" and its patch releases); everything here is stable behavior, and where the repository moves I say so.

First session

The shell starts in a transient in-memory database, and because DuckDB treats file paths as table names, the first query can run against a raw Parquet file with no import step:

$ duckdb
DuckDB v1.5.x (Variegata)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
D SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city LIMIT 3;
┌─────────┬────────────────────┐
│  city   │     avg(price)     │
│ varchar │       double       │
├─────────┼────────────────────┤
│ ...     │                ... │
└─────────┴────────────────────┘

(Banner and box formatting vary slightly by version.) The Python client is the same engine as a module, and it is where most people meet DuckDB:

import duckdb

duckdb.sql("SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city").show()

con = duckdb.connect("analytics.db")     # persistent single-file database
con.sql("CREATE TABLE listings AS SELECT * FROM 'listings.parquet'")
con.sql("SELECT count(*) FROM listings").show()

Results convert to and from pandas and Arrow directly (.df(), .arrow()), and you can query a DataFrame sitting in your process by naming the variable in SQL, which is the feature that tends to convert people on first contact:

import pandas as pd
df = pd.DataFrame({"city": ["ams", "ams", "nyc"], "price": [90, 110, 250]})
duckdb.sql("SELECT city, avg(price) FROM df GROUP BY city").show()

Mistakes beginners make

Losing tables to the in-memory default. duckdb.sql(...) and bare duckdb.connect() use an in-memory database; every table vanishes with the process. If you meant to keep it, connect to a path:

# wrong: CREATE TABLE into the default in-memory database, gone at exit
duckdb.sql("CREATE TABLE t AS SELECT * FROM 'listings.parquet'")

# right: a file-backed database that persists
con = duckdb.connect("analytics.db")
con.sql("CREATE TABLE t AS SELECT * FROM 'listings.parquet'")

Dragging results through Python row by row. fetchall() plus a Python loop rebuilds every value as a Python object, which can cost more than the query. Keep data columnar until the last moment:

# wrong: millions of Python tuples, then summed in interpreted code
rows = con.sql("SELECT price FROM t").fetchall()
total = sum(r[0] for r in rows)

# right: aggregate in the engine, or hand columns to pandas/Arrow wholesale
total = con.sql("SELECT sum(price) FROM t").fetchone()[0]
frame = con.sql("SELECT city, price FROM t").df()

Re-decoding the same Parquet file in a loop. Querying 'listings.parquet' is a scan-and-decode every time; that is the point for one-shot queries, and a waste for twenty. If you will query it repeatedly, load it once with CREATE TABLE ... AS into a persistent database, or at least create a view so the path and schema live in one place.

SELECT * over wide files. DuckDB only reads the Parquet columns the query references; asking for all 300 columns turns projection pushdown off by definition. Select what you need, especially over HTTP.

Treating the database file like Postgres. One process opens a DuckDB file read-write at a time; a second writer gets a lock error rather than queueing. Multiple processes can open the same file together only if all of them pass read_only=True. Cross-process concurrency is an architecture question, which the next section is about.

Part III: When it is the right tool

DuckDB is the right tool when the work is analytical, the data is reachable from one process, and the consumer is code: exploring and joining Parquet and CSV files, powering notebook and script analytics that would otherwise strain pandas, running the transform step of a local or serverless ELT job, serving as the query engine inside an application (dashboards over bundled data, log crunching in a CLI tool, SQL in the browser via WebAssembly), and generally replacing the "spin up a warehouse to look at one file" reflex. Single-digit gigabytes are trivial for it, hundreds of gigabytes are workable on one good machine, and the larger-than-memory story below stretches that further.

The boundaries are equally clear. For transactional workloads, many small writes with concurrent clients, you want an OLTP engine: SQLite embedded, or PostgreSQL as a server; DuckDB's row-group columnar storage makes point updates comparatively expensive by design. For a shared warehouse serving many users and BI tools concurrently over tens of terabytes, server systems like ClickHouse, BigQuery, or Snowflake are the category, with MotherDuck as the hosted DuckDB take. For teams fully invested in dataframe-native pipelines, Polars covers similar ground with a dataframe API rather than SQL. My databases note places these options side by side.

The architecture-shaped warning: DuckDB is an in-process engine with a single-writer file model, not a multi-client database server, and pointing a fleet of writers at one file is the failure mode. The safe shapes either give each process its own database, share data through immutable files, or keep one writer and many read-only openers.

SAFE                                      DANGEROUS

 ETL job (sole writer)                    web-1    web-2    web-3
     |                                        \      |      /
     v                                         v     v     v
 analytics.db  or  parquet files          one analytics.db opened
     |                                    read-write by every server
     v                                    (second opener errors; retries
 readers: notebooks, dashboards,           and NFS make it worse)
 lambdas, each read_only or own copy

Part IV: The full life of one query

This is the core of the chapter. The canonical operation is one analytical query from Python over a Parquet file:

import duckdb
duckdb.sql("SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city").df()

We follow it from the binding call to the last vector, naming the directories and files under src/ that do the work at each stage.

Stage 1: The client call

The Python module is a thin binding over the C++ API in src/main/: a Connection (connection.cpp) wraps a ClientContext (client_context.cpp), which owns the transaction state, the settings, and the query lifecycle. sql() produces a lazily executed relation; calling .df() forces it, at which point the context runs the statement through the stages below and streams result chunks back. Nothing crosses a socket at any point; "client" and "server" are the same address space, which is why handing the result to pandas at the end is a columnar memory handoff rather than a wire protocol.

Stage 2: Parser

The parser in src/parser/ is derived from Postgres's grammar (a heavily adapted fork of it lives in third_party/libpg_query), which is why DuckDB speaks a thoroughly debugged SQL dialect on day one rather than a homegrown subset. The Postgres parse tree is immediately transformed into DuckDB's own statement classes: our query becomes a SelectStatement whose FROM clause holds a base table reference named listings.parquet, with parsed expressions for city and avg(price). No name has been resolved yet; the parser knows syntax, not schemas.

Stage 3: Binder

The binder (src/planner/binder.cpp and the binder/ subdirectory) resolves every name against the catalog (src/catalog/) and assigns types. Here the query's most DuckDB-specific moment happens: there is no table called listings.parquet in the catalog, so the binder consults the registered replacement scans, hooks that turn an unresolved table name into a table function call; the filename pattern matches, and the reference is rewritten into read_parquet('listings.parquet'). The Parquet reader (in extension/parquet/) opens the file's footer, reports the schema, and city and price bind to typed columns. The same hook is how a pandas DataFrame variable becomes queryable. The binder's output is a tree of logical operators: a LogicalGet over the table function, a LogicalAggregate grouping by city, and a projection on top.

Stage 4: Optimizer

The optimizer in src/optimizer/ rewrites the logical plan: expression rewriting rules (rule/), filter and limit pushdown (pushdown/, filter_pushdown.cpp), join-order search (join_order/, idle for this query), and statistics propagation (statistics_propagator.cpp), which walks the plan asking each operator what it can promise about its output. For our query the decisive work is projection pushdown: of everything in the Parquet file, only city and price are needed, and that column list is pushed into the scan so other columns are never read from disk. Had there been a WHERE clause, its predicate would be pushed into the scan too, to be checked against per-row-group min/max statistics in the Parquet metadata so non-matching row groups are skipped wholesale.

Stage 5: Physical plan

src/execution/physical_plan_generator.cpp maps each logical operator to a physical one from src/execution/operator/: the get becomes a TABLE_SCAN over the Parquet function, the aggregate becomes a HASH_GROUP_BY backed by the radix-partitioned aggregate hash table (radix_partitioned_hashtable.cpp, aggregate_hashtable.cpp), and the projection becomes a PROJECTION evaluating expressions through the vectorized ExpressionExecutor. This is the tree EXPLAIN prints.

Stage 6: Pipelines and scheduling

The executor in src/parallel/ (executor.cpp, meta_pipeline.cpp, pipeline.cpp) breaks the physical tree at its pipeline breakers, the operators that must consume all input before producing any output. A hash aggregate is one, so our plan becomes two pipelines: pipeline 1 runs the Parquet scan as source and the aggregate's build side as sink; pipeline 2, dependent on pipeline 1 finishing, reads the finished hash table as a source and streams groups through the projection into the result collector. Dependencies between pipelines are tracked as events (event.cpp), and the TaskScheduler (task_scheduler.cpp) hands tasks to a pool of worker threads, by default one per core. Each task processes a morsel of input, in DuckDB's case row-group-sized slices of the scan, so threads that finish their slice simply take the next one, and skew balances itself without any up-front partitioning decision.

Stage 7: Vectors through the pipeline

Within a task, pipeline_executor.cpp drives the actual data movement: the Parquet reader decodes a slice of a row group into a DataChunk, one Vector per column (src/common/types/data_chunk.cpp, vector.cpp), holding up to 2048 values each, with a validity bitmask for NULLs. The chunk is pushed through the pipeline's operators in one virtual call per operator per chunk, 2048 values amortizing every dispatch. Vectors keep compressed shapes where possible: a dictionary-encoded Parquet column can flow as a dictionary vector without materializing repeated strings, and a constant column costs one value. The aggregate sink hashes the city vector, partitions groups by hash radix into thread-local tables, and updates running sums and counts for avg(price).

Stage 8: Finalize and hand back

When the scan is exhausted, pipeline 1's finish event fires: the thread-local partitioned tables are merged, finalizing one hash table containing every city with its sum and count. Pipeline 2 then scans it, computes the averages, and pushes result chunks into the collector; .df() receives those columnar chunks and builds the DataFrame directly from them. One detail worth noticing at the end of the journey: at no stage did a row exist as an object anywhere. The row is a fiction maintained by aligned positions in column vectors, from the file footer to the DataFrame.

Part V: Internals deep dives

Deep dive: columnar-vectorized execution

The classic interpreted engine (Postgres's executor, SQLite's VM) is tuple-at-a-time: each operator's "give me the next row" call cascades down the tree, paying function dispatch, branchy interpretation, and cache misses per row. At the other extreme, whole-column engines and naive dataframe code materialize each intermediate as a full column in memory, so (price * 0.9) > 100 allocates gigabyte-sized temporaries that blow past every cache on their way to RAM and back. Vectorized execution, worked out in the MonetDB/X100 research that DuckDB descends from, is the deliberate middle: interpret the plan, but per chunk of 2048 values, so dispatch cost is amortized three orders of magnitude while every intermediate stays small enough to live in the CPU caches.

DataChunk (up to 2048 rows)
  city:  Vector VARCHAR  [ams|ams|nyc|ams|...]   validity: 1111...
  price: Vector DOUBLE   [ 90|110|250| 85|...]   validity: 1111...

vector shapes: FLAT (plain array)
               CONSTANT (one value stands for all rows)
               DICTIONARY (indices into a small distinct-value array)
               + selection vectors filter rows without copying them

The vector size is a compile-time constant (STANDARD_VECTOR_SIZE, 2048 by default), chosen so a handful of double vectors plus working state fit comfortably in L1/L2; the operators in src/execution/ and the function implementations are all written against chunks of this shape, with tight loops over arrays plus a validity mask check. Two corrections worth making explicitly. First, "vectorized" here primarily means amortized interpretation and cache locality, not SIMD; the tight loops are written so compilers can auto-vectorize, and that helps, but the big win is architectural. Second, vectorization is also why row-at-a-time escape hatches hurt so much: a per-row Python UDF forces the engine to deconstruct vectors into objects and back, and tends to erase the engine's entire advantage; if you must apply Python, Arrow-level batch UDFs keep the columnar contract.

Deep dive: morsel-driven parallelism

DuckDB's parallelism follows the morsel-driven design from Leis, Boncz, Kemper, and Neumann's HyPer paper, adapted to DuckDB's push-based pipelines. The plan is factored into pipelines at its breakers, each pipeline is a source, a chain of streaming operators, and a sink, and the sink is the only place threads meet:

SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city

pipeline 1:  parquet scan  -> hash-aggregate SINK (build groups)
                 morsel queue: [rg0][rg1][rg2][rg3][rg4]...
                 thread A takes rg0, then rg3, ...
                 thread B takes rg1, then rg2, ...   (self-balancing)

pipeline 2 (after 1 finishes):
             aggregate scan -> projection -> result SINK

Sinks are built for this: the aggregate keeps thread-local, radix-partitioned hash tables so workers rarely contend, and a finalize step merges partitions when the pipeline completes. The scheduling consequence is the one to internalize: no thread is ever assigned a fixed share of the data, so a slow disk region, a skewed group, or a busy core just shifts morsels to whoever is free, the work-stealing effect without explicit stealing. The unit of parallel work over storage is the row group, 122,880 rows, and that quantum has a visible consequence: a table smaller than a couple of row groups cannot use many threads, no matter how many cores you have, which is the answer to "why is my 50,000-row query not parallel". The knobs are few and honest: SET threads = N caps the pool, and EXPLAIN ANALYZE shows what each operator cost. One more trap: because a pipeline breaker must finish before anything downstream starts, a GROUP BY produces no first row early; time-to-first-row and time-to-last-row are nearly the same, unlike streaming operators.

Deep dive: the storage story

DuckDB has one foot in its own format and one foot in everyone else's, and the design treats both as first class. Its own format (src/storage/) is a single file holding all tables, organized column by column within row groups of 122,880 rows; within a row group each column is stored in compressed blocks, with the compression scheme chosen per block by inspecting the data (dictionary and run-length encoding, bit-packing, FSST for strings, ALP for floats, described here at concept level since schemes keep being added). Changes go through a write-ahead log and are folded into the file at checkpoints, and MVCC gives readers snapshots while a writer works. The format has been backward-compatible since 1.0, which ended the era when upgrading DuckDB meant exporting and reimporting your database.

The other foot: external data is queried in place, not imported. Replacement scans map file paths and host-language objects into table functions; the Parquet reader scans with projection and filter pushdown so only referenced columns and row groups whose min/max statistics might match are read; the CSV reader's sniffer infers delimiters and types; Arrow data in the host process is scanned zero-copy; and with the httpfs extension (now developed in its own repository, duckdb/duckdb-httpfs, and autoloaded on first use) a path can be an HTTP URL or S3 object read via range requests, fetching footer and needed column chunks rather than the whole file. Two traps: the DuckDB file format is not Parquet, and neither replaces the other (Parquet is the interchange format, DuckDB's format is the queryable working store with ACID updates); and external scans re-decode on every query, so repeated analysis of one file justifies CREATE TABLE ... AS once.

Deep dive: larger than memory

DuckDB defaults to a memory budget of 80% of physical RAM (SET memory_limit = '...' to change it), and the blocking operators, hash aggregation, hash join, sorting, are written to degrade gracefully rather than die when the budget is hit: working state spills to temporary files (in the database's .tmp directory, or SET temp_directory for in-memory connections), processed partition by partition, and the query completes slower instead of failing. This is concept-level by design here; which operators spill and how well improves release by release. The practical guidance is stable though: larger-than-memory aggregation and sorting work; do watch the difference between the query and the result, because a query can run in bounded memory and then explode at the end when a hundred-million-row result is materialized into a DataFrame. If you hit memory errors from Python, check whether it is the .df() at the end, not the engine, and stream or aggregate further instead.

Part VI: Reading the repository

The C++ source under src/ is the pipeline in directory form: parser, planner, optimizer, execution, parallel, with storage, catalog, transaction, function, and common supporting them and main holding connections and the client context. Headers live apart under src/include/duckdb/, mirroring the source tree. Read it as a syllabus:

Stage 0, run it. Build the shell (make in the repo root, or just install the binary) and run our canonical query under EXPLAIN and EXPLAIN ANALYZE. You should be able to answer: which operators appear, and which of them is the pipeline breaker?

Stage 1, the frontend. Skim src/parser/ (the transformer that turns the Postgres-derived parse tree into DuckDB statements) and read src/planner/binder.cpp plus a couple of binders in src/planner/binder/. Questions: why does reusing Postgres's grammar buy more than saved effort? Where exactly does a Parquet path become a table function, and where would a DataFrame?

Stage 2, plans. Read src/planner/operator/ for the logical operators, then in src/optimizer/: filter_pushdown.cpp, statistics_propagator.cpp, and a look at join_order/. Questions: what information does statistics propagation move, and which optimizations does it unlock? What can be pushed into a scan, and what cannot?

Stage 3, execution. Read src/execution/physical_plan_generator.cpp, one simple operator in src/execution/operator/ (projection or filter), then src/common/types/vector.cpp and data_chunk.cpp, then radix_partitioned_hashtable.cpp. Questions: what are the vector shapes and why do dictionary vectors matter? Why is the aggregate hash table radix-partitioned?

Stage 4, parallelism. Read src/parallel/: meta_pipeline.cpp, pipeline.cpp, pipeline_executor.cpp, event.cpp, task_scheduler.cpp. Questions: how does a plan become pipelines and events? Where is the morsel-driven behavior actually implemented, and what is the unit of a task?

Stage 5, storage and extensions. Read src/storage/ at skim depth (row groups, WAL, checkpointing) and extension/parquet/ for a real pushdown-capable reader; note that parquet and json living in extension/ even though they ship with every build is a deliberate statement that core features go through the public extension interface. Questions: what happens on checkpoint? How does the Parquet reader use row-group statistics?

Where not to start: not src/include/ (a mirror maze without behavior), not src/function/ (an enormous library of implementations that teaches breadth, not architecture), and not the join-order optimizer, which is the hardest math in the tree. Start at the shell with EXPLAIN output in one window and src/execution/operator/ in the other.

Part VII: Hands-on labs

Lab 1: DuckDB versus pandas on one group-by. Generate a Parquet file with DuckDB itself, then time the same aggregation both ways:

import duckdb, pandas as pd, time

duckdb.sql("""
  COPY (SELECT (random()*200)::INT AS city, random()*500 AS price
        FROM range(20_000_000))
  TO 'listings.parquet' (FORMAT parquet)
""")

t = time.time()
pd.read_parquet('listings.parquet').groupby('city').price.mean()
print("pandas:", time.time() - t)

t = time.time()
duckdb.sql("SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city").df()
print("duckdb:", time.time() - t)

Expect DuckDB to win by a healthy multiple on a multi-core machine; the exact factor varies with cores, disk, and versions, so measure rather than quote. Then explain the gap with this chapter: pandas materializes the whole file into memory first and aggregates single-threaded; DuckDB streams two columns through a parallel pipeline. Note the file has enough rows for many row groups; rerun with 100,000 rows and watch the gap shrink, which is the parallelism quantum from the deep dive.

Lab 2: read EXPLAIN, then EXPLAIN ANALYZE.

EXPLAIN SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city;
EXPLAIN ANALYZE SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city;

EXPLAIN prints the physical operator tree (TABLE_SCAN, HASH_GROUP_BY, PROJECTION, formatting varies by version); check that the scan lists only city and price as projections, which is pushdown made visible. EXPLAIN ANALYZE runs the query and adds per operator timings and row counts; find where the time goes (scan versus aggregate) and match each printed operator to a class under src/execution/operator/.

Lab 3: query a remote Parquet file over HTTP. The httpfs extension autoloads on first use in current builds (run INSTALL httpfs; LOAD httpfs; manually if your environment blocks autoinstall):

SELECT count(*) FROM 'https://blobs.duckdb.org/data/taxi_2019_04.parquet';
SELECT count(*) FROM 'https://blobs.duckdb.org/data/taxi_2019_04.parquet'
WHERE pickup_at BETWEEN '2019-04-15' AND '2019-04-20';

The first query returns in far less time than downloading the file would take, because count(*) is answered mostly from the Parquet footer metadata via range requests; the second reads only the pickup_at column chunks, plus skips row groups whose min/max cannot match. This is pushdown crossing the network.

Lab 4: see projection pushdown in the wall clock. Write a wide Parquet file (say 50 columns via a generated SELECT), then time SELECT avg(col1) FROM ... against SELECT avg(col1) FROM (SELECT * FROM ...) style full-width reads, and compare EXPLAIN's projection lists. The narrow query should be dramatically cheaper, and the plan shows why before the clock does.

Lab 5: threads and the row-group quantum.

SET threads = 1;
EXPLAIN ANALYZE SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city;
SET threads = 8;
EXPLAIN ANALYZE SELECT city, avg(price) FROM 'listings.parquet' GROUP BY city;

Compare total times on the 20M-row file (near-linear scaling up to your core count is typical), then repeat on a 100,000-row table and observe scaling flatten, because fewer than two row groups cannot feed eight workers.

Lab 6: watch a query spill. In a fresh shell, set SET memory_limit = '500MB'; and SET temp_directory = '/tmp/duck_spill';, then run a big ORDER BY over the 20M-row file while watching ls /tmp/duck_spill from another terminal: temporary files appear as sort runs spill, and the query completes anyway. Remove the limit and rerun to compare times; that difference is the price of out-of-core execution, and its existence is the feature.

Part VIII: Check your understanding

What is DuckDB in one sentence, and how is it unlike both SQLite and Postgres? A complete analytical SQL engine packaged as an in-process library: like SQLite in deployment (linked into your process, single-file storage) but columnar, vectorized, and parallel where SQLite is row-oriented and OLTP-shaped; like Postgres in SQL maturity but with no server, no clients, and no cross-process write concurrency.

Walk the life of a query in one breath. Parser turns SQL text into statements; binder resolves names against the catalog (or replacement scans for files and DataFrames) and types the logical plan; the optimizer pushes projections and filters and propagates statistics; the physical planner picks operators; the executor breaks the plan into pipelines at breakers and worker threads pull morsels through them as 2048-value column chunks into sinks, whose finalized state feeds the next pipeline and finally the result.

Why vectors of about 2048 values rather than one row or one column? One row pays interpretation overhead per value; one whole column creates intermediates that overflow the caches and hammer RAM. A 2048-value chunk amortizes dispatch by three orders of magnitude while keeping every intermediate cache-resident, which is the vectorized middle path DuckDB inherits from the MonetDB/X100 line of research.

What is a pipeline breaker and what does it imply for latency? An operator that must consume its entire input before emitting anything, like a hash aggregate build or sort. The plan is split into pipelines at breakers, and nothing downstream starts until the breaker finalizes, so a GROUP BY has no early first row; its time-to-first-row is essentially its total time.

Explain morsel-driven parallelism without the paper. Do not pre-assign data to threads; queue small fragments of input and let workers repeatedly grab the next one and push it through the current pipeline into a thread-friendly sink. Skew, slow cores, and slow storage self-balance because a slow worker simply takes fewer morsels, and the only merge point is the sink's finalize.

Why can a small table fail to use all your cores? The unit of parallel scan work is the row group of 122,880 rows, so a table spanning one or two row groups offers one or two units of work; parallelism is capped by available morsels, not by cores.

What is a replacement scan? A binder hook that fires when a table name resolves to nothing in the catalog, giving registered handlers a chance to turn the name into a table function: file paths become Parquet or CSV scans, and in Python a variable name can become a scan of the DataFrame it refers to. It is the mechanism behind "just FROM the file".

What do projection and filter pushdown save on a Parquet scan? Projection pushdown reads only referenced columns, which in a wide file is most of the I/O; filter pushdown checks predicates against per-row-group min/max statistics in the metadata and skips whole row groups, and over httpfs both translate into fewer HTTP range requests rather than fewer disk reads.

How does DuckDB behave when data exceeds memory? It works under a budget, by default 80% of RAM: blocking operators spill partitions of their working state to temporary files and proceed, trading speed for completion instead of failing. The common surprise is that the crash people do see is often the final materialization into a DataFrame, not the query itself.

DuckDB file format versus Parquet: when each? Parquet is the compressed interchange and lake format everything reads; DuckDB's own file is the working store, with ACID updates, a WAL, checkpoints, all tables in one file, and per-block adaptive compression, backward-compatible since 1.0. Query Parquet in place for one-shot work; load into DuckDB's format when you will query or mutate repeatedly.

What are the concurrency rules? Inside one process: many connections and threads, with MVCC isolating transactions. Across processes: one read-write opener per database file, or many read-only openers; there is no lock queue like a server database, so multi-writer designs must go through a single owning process or a server-shaped system instead.

Why did DuckDB reuse the Postgres grammar? A SQL dialect is decades of parsing edge cases and user expectations; forking a battle-tested grammar bought compatibility and correctness on day one and let the team spend novelty where it pays, in execution and storage, rather than in inventing another dialect.

A query is slow. What is the first diagnostic move? EXPLAIN ANALYZE, then read it against the pipeline model: check the scan's projected columns (pushdown working?), see which operator dominates time, check row counts for surprises (a join exploding), and check whether the table is large enough in row groups for the thread count to matter.

When is DuckDB the wrong choice even though it would run? Many concurrent writers (an app backend's OLTP store), a shared always-on warehouse serving many users and tools, and point-lookup-update workloads where row stores shine; the honest statement is that DuckDB replaces the analytical uses of a server database, not the server database.

What does it mean that Parquet support lives in extension/? Even always-shipped features are built against the public extension interface, which keeps that interface honest and complete, keeps the core small, and means third-party extensions are not second-class citizens; httpfs even graduated to its own repository while remaining a one-line install.

Part IX: Design lessons

Choose your quantum per resource, and choose two if you need two. DuckDB has a cache quantum (2048-value vectors) and a parallelism quantum (122,880-row groups), each sized for its bottleneck, deliberately not the same number. The general lesson, batch size is a resource-specific design variable, recurs in GPU warps versus grid blocks, TCP segments versus windows, and page sizes versus extents.

Amortize interpretation instead of eliminating it. Query compilation to machine code beats interpretation per value, but vectorization gets most of the win for a fraction of the complexity, keeping the engine debuggable and portable. The same economics justify batching in RPC layers, ML inference servers, and syscall batching interfaces like io_uring.

Move the engine to the data's address space. In-process means no wire serialization, zero-copy interchange with Arrow and pandas, and deployment reduced to an import; the lesson SQLite taught for OLTP, DuckDB re-taught for analytics. The recurring form: when the consumer is code rather than many humans, a library beats a server.

Adopt boring frontends, spend novelty on the core. The Postgres-derived parser is a deliberate refusal to innovate where innovation is waste, funding the innovative executor. The same allocation shows up in LLVM frontends versus its optimizer, and in systems that adopt SQL, S3's API, or POSIX as compatibility surfaces.

Dogfood your plugin interface. Shipping Parquet, JSON, and httpfs through the extension mechanism forces that mechanism to be powerful enough for real features, the same discipline that made VS Code's extension API good (its built-in features use it) and keeps Postgres's extension ecosystem viable.

Degrade, don't die. Spilling operators turn memory exhaustion from a failure mode into a performance mode, which is what makes the tool trustworthy at the edge of its envelope. The pattern generalizes: backpressure instead of OOM, load shedding instead of collapse, graceful degradation as a first-class requirement.

Part X: The memorization framework

One sentence: DuckDB parses SQL with a Postgres-derived grammar, binds it against a catalog extended by replacement scans, optimizes with pushdown and statistics, and executes physical plans as morsel-parallel pipelines pushing 2048-value column vectors into sinks, over its own row-grouped columnar file or straight over Parquet, CSV, and Arrow.

SQL -> Parser -> Binder -> Logical plan -> Optimizer -> Physical plan
    -> Pipelines (breakers) -> morsels x threads -> Vectors(2048) -> Sink -> Result

Parser         src/parser (grammar from third_party/libpg_query)
Binder         src/planner/binder.cpp, binder/, replacement scans
Optimizer      src/optimizer (filter_pushdown.cpp, statistics_propagator.cpp)
Physical plan  src/execution/physical_plan_generator.cpp, operator/
Pipelines      src/parallel (meta_pipeline.cpp, pipeline_executor.cpp,
               task_scheduler.cpp, event.cpp)
Vectors        src/common/types (vector.cpp, data_chunk.cpp)
Storage        src/storage; readers in extension/parquet, extension/json;
               httpfs in duckdb/duckdb-httpfs

Memorize these blocks:

• Numbers: vectors hold up to 2048 values (compile-time STANDARD_VECTOR_SIZE); row groups hold 122,880 rows (60 vectors); default memory budget is 80% of RAM; default thread count is your core count.
• Invariants: in-process, no server; one read-write process per file, or many read-only; storage format backward-compatible since 1.0; pipeline breakers gate downstream work.
• Mechanisms: replacement scans turn paths and DataFrames into table functions; projection and filter pushdown reach into Parquet row groups and across HTTP; sinks hold the thread-shared state and finalize once.
• Positioning: SQLite is the embedded row store, Postgres the OLTP server, ClickHouse and the warehouses the shared serving tier; DuckDB is the analytical engine that lives where the code lives.