DuckDB: embedded analytical DBMS and architecture

History and context

Core architecture elements

Execution and storage model

Here is an interactive walk through how DuckDB runs a query: in-process deployment, the vectorized engine, storage layout, transactional semantics, and the write-concurrency limit that trips people up most often.

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

DuckDB architecture by layers

The diagram pulls the whole contour into one picture: client embedding, the SQL/optimizer layer, vectorized execution pipelines, the storage subsystem, and ecosystem integration points.

Write and read paths through components

This unified flow combines write and read paths: from client SQL command through optimizer and vectorized execution to storage persistence or analytical result delivery.

When to choose DuckDB

Practice: DDL and DML

Below are practical DuckDB SQL operations: DDL for schema/index setup and DML for ingest, transformation, and analytics queries.

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY,
  plan VARCHAR,
  created_at TIMESTAMP
);

CREATE TABLE events (
  event_id BIGINT,
  user_id BIGINT,
  event_type VARCHAR,
  event_ts TIMESTAMP,
  payload JSON,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE INDEX idx_events_user_ts
ON events (user_id, event_ts);

ATTACH 'warehouse.duckdb' AS wh;
USE wh;

CREATE SCHEMA IF NOT EXISTS mart;

CREATE TABLE mart.daily_events AS
SELECT DATE(event_ts) AS dt, event_type, count(*) AS cnt
FROM events
GROUP BY 1, 2;