2. The embedded model

“In-process” sounds like a deployment detail. It is the design decision the whole system hangs off, and it is worth being precise about what it means.

The boundary that is not there

Here is what happens when your Python program queries PostgreSQL:

   YOUR PROCESS              POSTGRES PROCESS

   psycopg2
     ↓ serialise the SQL
     ↓ write to a socket
        ─────── TCP ───────→  parse
                              plan
                              execute
                              serialise the rows
        ←────── TCP ────────  write to the socket
     ↓ read from socket
     ↓ deserialise every row
     ↓ build Python objects
   your DataFrame

Every row crosses a process boundary twice — once as bytes on a wire, once as objects being rebuilt. For a hundred rows this is free. For fifty million rows it is frequently the dominant cost of the whole operation, larger than the query itself.

Here is DuckDB:

   YOUR PROCESS

   duckdb.sql("SELECT ...")
     ↓ parse        ]
     ↓ plan         ]  all of this is a
     ↓ execute      ]  function call on your
     ↓ result       ]  own thread stack
   your DataFrame  ← the result vectors ARE
                     the DataFrame's memory

No socket. No serialisation. No copy. The result columns DuckDB produced in memory can become an Arrow table, and a pandas or Polars DataFrame over that Arrow table, without the data moving at all. Chapter 12 covers exactly how far that zero-copy claim goes, because it is stronger than most people expect and it is not unconditional.

What “embedded” removes

Count what a server requires that DuckDB simply does not have:

   ✗ a process to start and supervise
   ✗ a port, and a firewall rule for it
   ✗ a connection string
   ✗ a connection pool, and its exhaustion
     failure mode
   ✗ users, passwords, roles, GRANTs
   ✗ TLS between client and server
   ✗ a wire protocol, and its version skew
   ✗ network latency and network partitions
   ✗ a container, a pod, a StatefulSet,
     a PVC
   ✗ an operator, a Helm chart, a backup job
   ✗ someone on call for it

This list is the product. Everything DuckDB is good at can be restated as “the above list is empty.” A test suite that needs an analytical database does not need Docker. A CLI tool that ships analytics does not need its users to run a server. A Lambda function can carry a query engine in its deployment package.

And it is why the comparison to SQLite is exact rather than rhetorical. SQLite is the most-deployed database in the world precisely because it removed that same list for transactional workloads. DuckDB is the same trade made for analytical ones.

The three ways it runs

   1. IN MEMORY ONLY
      con = duckdb.connect()

      No file. Nothing on disk. The database
      dies with the process. This is the
      default in Python and it is what most
      people use without realising it.

   2. A SINGLE FILE
      con = duckdb.connect('analytics.duckdb')

      One file. Copy it, email it, put it in
      object storage, check it into a
      release artefact. Chapter 3 is what is
      inside it.

   3. NO DATABASE AT ALL
      duckdb.sql("SELECT * FROM 'x.parquet'")

      In-memory catalog, files on disk as the
      data. The "database" holds only the
      query. Chapter 5.

Mode 3 is the one that is genuinely novel and the one people take longest to internalise. You are not loading data into a database. You are pointing a query engine at files and getting an answer. The engine is stateless; the files are the state.

The client is not a client

In a server database, the “client library” is a protocol implementation — it speaks the wire format and knows nothing about query execution. psycopg2 cannot plan a query; it can only ask Postgres to.

In DuckDB, the Python package contains the entire database. pip install duckdb downloads a compiled C++ engine — parser, optimiser, execution engine, storage layer, the lot — wrapped in Python bindings. The same is true of the R, Java, Node, Rust and Go packages. There is one C++ codebase and many thin bindings over it.

   duckdb (PyPI wheel)
   ├── the Python bindings          ~small
   └── libduckdb (compiled C++)     ~40 MB
       ├── parser
       ├── binder
       ├── optimizer
       ├── execution engine
       ├── storage
       └── the built-in extensions

Two consequences worth knowing:

The wheel is large, tens of megabytes, which matters for Lambda deployment packages and cold starts. Chapter 13 covers what to do about that.

Every client is the same database. A .duckdb file written by the Python package opens identically in the CLI, in R, in Java. There is no server-side/client-side split to get out of sync, and no “the JDBC driver does not support that yet.”

Replacement scans — the trick that makes it feel native

This is a small feature with an outsized effect on how DuckDB feels in Python.

import duckdb, pandas as pd

orders = pd.read_csv('orders.csv')

duckdb.sql("SELECT country, sum(amount) FROM orders GROUP BY 1")

orders is a Python variable. It is not a table. There is no CREATE TABLE, no register(), no import. DuckDB’s binder, when it fails to find a table called orders in its catalog, runs a replacement scan: it looks in the calling Python frame’s local and global scope for a variable of that name, finds a DataFrame, and wraps it as a table.

   BINDER: "orders" — is it a table?
             no.
           is it a view?
             no.
           → REPLACEMENT SCAN
             ask each registered handler
             "do you know what `orders` is?"

             pandas handler:  yes, it is a
                              DataFrame in the
                              caller's frame.
             → bind it as a scan over that
               DataFrame's numpy buffers

The same mechanism handles Polars DataFrames, Arrow tables, Arrow datasets, and — the case from chapter 1 — bare file paths. FROM 'events.parquet' works because a replacement scan recognises the string as a path and substitutes read_parquet('events.parquet').

It reads the DataFrame’s memory in place. For a pandas DataFrame with primitive numeric columns, DuckDB scans the underlying numpy arrays directly — no copy, no conversion. Object-dtype columns (strings, in older pandas) do cost a conversion. Chapter 12 has the details, because knowing which columns are free and which are not is the difference between a query taking two seconds and twenty.

What you give up

Four things, and they are all real.

1. One writer

A DuckDB database file is held by one process for writing. That process can run many concurrent read and write queries inside itself; a second process opening the same file for writing gets an error.

   process A: open('db.duckdb')          ✓ holds
                                           the write
                                           lock
   process B: open('db.duckdb')          ✗ IO Error:
                                           file is
                                           locked

   process B: open('db.duckdb',
                   read_only=True)       ✗ still
                                           blocked
                                           while A
                                           holds it

   — but with NO writer, many readers can
     open read_only simultaneously.

Chapter 11 is this in full, including what to do about it. It is the single most common reason DuckDB is the wrong answer, and it is not a bug to be fixed later — it follows from having no server to arbitrate.

2. Nothing runs when your program does not

There is no background compaction, no autovacuum, no scheduled job, no continuous ingestion. The database exists only while a process has it open. If you want something to happen every hour, something outside DuckDB must run it — Airflow, systemd, cron.

3. Your memory is its memory

DuckDB allocates inside your process. A query that wants 30 GB takes it from the same address space as your application. A DuckDB OOM is your program being killed, not a database restarting while your program handles the error.

This makes memory_limit (chapter 13) far more important than the equivalent setting in a server database, because there is no isolation between the query and the thing that issued it.

4. No security model worth the name

There are no users, no roles, no row-level security, no audit log. Whoever can run your process can run any query and read the whole file. Access control is filesystem permissions and nothing else. If you need Vault-shaped secrecy or Trino-shaped governance, DuckDB has no layer to hang it on.

There is one partial answer — the enable_external_access setting and the securable-extension flags let you lock a DuckDB instance down so that embedded SQL cannot read arbitrary files or reach the network. That is for sandboxing untrusted SQL inside your own application, which is a genuinely useful thing and a completely different problem from multi-user access control.

The shape this pushes you towards

   TYPICAL SERVER DATABASE ARCHITECTURE

     many app processes
            ↓ network
     one database process
            ↓
     one set of files


   TYPICAL DUCKDB ARCHITECTURE

     many processes, each with its OWN
     engine
            ↓
     SHARED FILES in object storage
     (Parquet, or an Iceberg table)

The state moves out of the database and into the files. The engine becomes disposable — start one, query, exit. That is the same shape Trino has, and the same shape a Spark job has; DuckDB just does it in one process instead of a cluster.

This is the architecture to aim for, and chapter 15 argues for it directly: use DuckDB’s own file format for local work, caches and intermediate results, and put anything shared or long-lived in Parquet or an Iceberg table where every other tool can also read it.

What to take from this chapter

• In-process means no process boundary — no socket, no serialisation, no row-by-row rebuild. For large results that boundary is frequently the dominant cost, and DuckDB does not pay it.
• What embedding removes is a long list — port, connection string, pool, users, TLS, wire protocol, container, operator, on-call — and that list being empty is the product.
• Three modes: in memory, a single file, or no database at all with files as the data. The third is the genuinely novel one.
• The client library is the whole database. One C++ engine, thin bindings, ~40 MB, identical behaviour and file format everywhere.
• Replacement scans are why FROM orders finds a Python variable and FROM 'x.parquet' finds a file — the binder asks handlers what an unknown name is before giving up.
• Four real costs: one writer per file; nothing runs in the background; its memory is your memory, so an OOM kills your program; and no security model beyond filesystem permissions.
• enable_external_access sandboxes untrusted SQL inside your app. That is not multi-user access control and should not be mistaken for it.
• Aim for state in files, engines disposable. DuckDB’s own format for local and intermediate work; Parquet or Iceberg for anything shared.

Next: the storage layer — what is inside a .duckdb file, and why it is a single file at all.