DuckDB: one SQL engine for every database, file, lakehouse, model and map

DuckDB: one SQL engine for every database, file, lakehouse, model and map

Modern data work is an exercise in logistics. The measurements live in PostgreSQL. The exports are Parquet files on S3. The legacy system is SQLite. The data-science team works in pandas and Arrow. The AI pipeline needs embeddings, chunked text and vector search. The GIS team ships GeoPackages. Every one of these islands has its own query engine, its own dialect, its own deployment story — and the glue between them is usually a fragile script.

DuckDB’s proposition is almost rude in its simplicity: one in-process, columnar, transactional SQL engine that can attach to nearly all of it directly — and run analytical queries across the boundaries without moving anything. This article is a deep tour of how that works: the architecture, the connectors, the lakehouse formats, the AI stack, and the geospatial extension that quietly became a credible PostGIS alternative for analytics. Everything below is grounded in the primary sources (papers, docs, engineering blogs), and every command in the walkthrough section was executed and verified against DuckDB v1.5.5 before publication.

"The immense popularity of SQLite shows that there is a need for unobtrusive in-process data management solutions. However, there is no such system yet geared towards analytical workloads." — Raasveldt & Mühleisen, DuckDB: an Embeddable Analytical Database, SIGMOD 2019

1. What DuckDB is — and what it deliberately is not

DuckDB is an open-source (MIT), in-process relational DBMS specialized for OLAP — long-running analytical queries that scan, aggregate and join large fractions of the data. It does not run as a server you install and maintain; it runs inside your process, like SQLite, exposing itself through APIs for Python, R, Java, Node.js, Rust, Go, C/C++ — and, via DuckDB-Wasm, inside the browser tab.

A common misconception, which the project explicitly debunks, is that DuckDB is an "in-memory database". It can run in-memory, but it is not one: it uses memory as a cache, fully supports disk-based persistence in its native single-file format, and spills larger-than-memory operations to disk. The single file contains everything — tables, views, indexes, macros — in a design consciously inspired by SQLite, with a write-ahead log alongside and block checksums guarding against bit rot.

The engine itself is columnar-vectorized: instead of processing row by row (PostgreSQL, MySQL, SQLite) or compiling queries to machine code (a path that drags in heavyweight JIT dependencies), DuckDB interprets queries over vectors — batches of roughly 2,048 values per column in one operation. This "vector volcano" model, inherited intellectually from MonetDB/X100, amortizes interpretation overhead and keeps the whole engine portable: no external dependencies, compiled to a single amalgamation, running from edge devices to many-core servers. Parallelism is morsel-driven; secondary indexes are adaptive radix trees; the SQL dialect is PostgreSQL-based with documented deviations.

Transactions. DuckDB provides ACID guarantees through a bulk-optimized multi-version concurrency control (MVCC) modeled on HyPer’s serializable variant. One process can write; many processes can read concurrently in read-only mode; a single transaction may write only one attached database. DuckDB passes TPC-H’s transactional validation tests, and the write-ahead log provides crash recovery. What it is not built for is OLTP: thousands of concurrent small read-write transactions against one file is the job of Postgres or SQLite, not DuckDB.

The peer-reviewed lineage

Unlike most tools in the modern data stack, DuckDB’s foundations are published academic work, which is part of why it deserves a "scientific" reading:

The project grew out of the CWI database research group in Amsterdam — the same lineage as MonetDB and Vectorwise — created by Mark Raasveldt and Hannes Mühleisen. It is stewarded by the non-profit DuckDB Foundation (which holds the trademark and pins the MIT license in perpetuity), with the company DuckLabs employing the core team.

2. The architecture in one picture

DuckDB as a hub: databases attach as live catalogs, files and lakehouses query in place, Arrow feeds data science zero-copy.
DuckDB as a hub: databases attach as live catalogs, files and lakehouses query in place, Arrow feeds data science zero-copy.

The picture above is the whole thesis. Three connection mechanisms cover everything: table functions (read_parquet, read_csv, read_json, delta_scan, iceberg_scan) for files; replacement scans that let a bare filename or an in-memory DataFrame appear as a table; and the ATTACH statement, which mounts an external database or lakehouse as a first-class catalog you can join against like any local table. The rest of this article walks through each ring of the hub.

Version landscape at the time of writing

3. Connecting databases: ATTACH as a universal adapter

DuckDB’s most under-appreciated trick is that it treats other databases not as import sources but as live catalogs. The postgres, mysql and sqlite extensions let you ATTACH a running server — or a plain file — and then run ordinary SQL against it, including joins that span systems. Data is read at query time; nothing is copied unless you ask.

INSTALL postgres;  LOAD postgres;
ATTACH 'dbname=airdata user=postgres password=*** host=127.0.0.1 port=55432'
       AS pg (TYPE POSTGRES);

SELECT city, round(avg(pm25), 2) AS mean_pm25
FROM pg.public.sensors
GROUP BY city ORDER BY mean_pm25;

MySQL uses key=value connection strings, SQLite just takes a file path — and because DuckDB’s SQL is PostgreSQL-flavored, the round trip feels native. The capability matrix across the three attachable engines is genuinely strong:

capability            PostgreSQL   MySQL   SQLite
query (live scan)         X         X        X
CREATE TABLE / INSERT     X         X        X
UPDATE / DELETE           X         X        X
ALTER TABLE / DROP        X         X        X
COPY table <-> Parquet    X         X        X
COPY FROM DATABASE        X         X        -
transactions              X         X*       X
pass-through SQL          postgres_query / mysql_query

* DDL is not transactional in MySQL itself.
Filters are pushed down where possible (PostgreSQL via an
experimental flag, on by default; parallel scans via ctid ranges).
Legacy *_attach() table functions are deprecated in favor of ATTACH.

This makes DuckDB a credible ETL hub: pull from Postgres, land as partitioned Parquet, join against MySQL, write results back — all in one SQL script, all without a Airflow cluster in sight. Full-database copies are one statement: COPY FROM DATABASE pg TO local_duck;

What we verified, hands-on

For this article we spun up a real PostgreSQL 16 container with 5,000 sensor rows, attached it from Python, and exercised the boundary-crossing queries end to end:

-- write through DuckDB into Postgres (full DML)
INSERT INTO pg.public.sensors (city, pm25) VALUES ('Graz', 8);
UPDATE pg.public.sensors SET pm25 = 99 WHERE city = 'Graz';

-- the money query: local Parquet JOIN live Postgres table
COPY (SELECT i AS id, i*0.1 AS noise FROM range(1,101) s(i))
     TO 'noise.parquet';
SELECT count(*)
FROM read_parquet('noise.parquet') p
JOIN pg.public.sensors s ON s.id = p.id
WHERE s.city = 'Vienna';
-- -> 100 rows, executed across two systems in one statement

-- force execution on the Postgres side itself
SELECT * FROM postgres_query('pg',
  'SELECT city, count(*) FROM sensors GROUP BY city');

Two honest footnotes from that experiment: PostgreSQL’s RETURNING clause is not yet supported for updates through the scanner, and postgres_query takes the attached database name (like 'pg'), not a connection string. The kind of detail that only surfaces when you actually run the thing — which is exactly why we ran it.

4. Connecting files: CSV, JSON and the Parquet engine

DuckDB’s file readers are where the "zero ceremony" promise lands. A bare filename is a table:

SELECT * FROM 'flights.csv';              -- yes, that is SQL
SELECT * FROM 'test/*.parquet';           -- globs work
SELECT * FROM read_parquet('s3://bucket/t/*.parquet');

CSV with a sniffer. The reader auto-detects the delimiter, quote and escape characters, whether a header exists, and per-column types (sampling 20,480 rows by default), promoting through NULL, BOOLEAN, DATE, TIMESTAMP, BIGINT, DOUBLE to VARCHAR. Wrong guesses are overridable per column; tolerant loads can route bad rows into a rejects table instead of failing.

JSON as rows. read_json auto-detects structure and types (array, NDJSON or unstructured), and the JSON extension ships with most distributions, including the -> / ->> extraction operators. Our verification: a two-line NDJSON file, nested extraction with SELECT b.c FROM read_json('j.json') — worked exactly as documented.

Parquet is the native tongue. This is where the columnar engine earns its keep, and it is worth being precise about the mechanics:

-- verified: partitioned write, hive-partitioned read with pruning
COPY (SELECT i AS id, i%10 AS bucket, 'row'||i AS label
      FROM range(1000) s(i))
TO 'pq' (FORMAT PARQUET, PARTITION_BY (bucket));

SELECT count(*) FROM read_parquet('pq/**/*.parquet',
                                  hive_partitioning = true)
WHERE bucket = 3;      -- reads only bucket=3/, returns 100

Even DuckDB’s own database files are readable this way: read_duckdb('my-*.duckdb', table_name = 'numbers') queries tables directly out of .duckdb files — including over HTTPS — without attaching them.

5. Remote data: S3, HTTPS, Azure, Hugging Face

The httpfs extension turns the whole cloud object-storage layer into a filesystem. HTTP(S) is read-only; the S3 API supports reads, writes, globs and multipart uploads. The load-bearing optimization is the HTTP range request: a Parquet footer tells DuckDB which byte ranges contain the columns and row groups a query needs, and only those ranges cross the network. Columnar formats and remote storage are made for each other; a CSV over HTTPS, by contrast, is downloaded essentially in full. We verified a remote CSV query in one line (no credentials, autoloading on first use):

SELECT count(*) FROM read_csv_auto(
  'https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv');
-- -> 150

Authentication is centralized in the secrets manager: one mental model for S3, R2, GCS, Azure, HTTP bearer tokens, Hugging Face and the database scanners — with credential_chain support delegating to the AWS SDK (profiles, SSO, assumed roles, IRSA). Hugging Face gets a dedicated URL scheme, which quietly turns 150k+ public datasets into queryable tables:

SELECT count(*) FROM 'hf://datasets/cais/mmlu/astronomy/*.parquet';

The same machinery powers partitioned writes back to S3 and time-safeguards like version pinning for buckets where objects are overwritten.

6. The lakehouse: Iceberg, Delta, Lance and DuckLake

Table formats are where "query files" matures into "manage tables on files". DuckDB’s official position is that Iceberg, Delta, Lance and DuckLake are first-class citizens — and, unusually, the Iceberg and Delta support is implemented natively (no Java, no Spark): the iceberg extension reads and writes Iceberg directly, and the delta extension builds on delta-kernel-rs. The support matrix, from the official docs:

capability            DuckLake  Iceberg   Delta     Lance
read / write             X         X        X*        X
UPDATE / DELETE          X         X        -         X
time travel              X         X        X         -
CREATE TABLE             X         X        -         X
maintenance/compaction   X         -        -         X
query table changes      X         -        -         -

* Delta writes are blind appends only (no UPDATE/DELETE/CREATE);
  Iceberg writes need a catalog (REST) attach, landed Nov 2025.
-- Iceberg with a REST catalog: full DML, committed as snapshots
ATTACH 'warehouse' AS wh (
  TYPE ICEBERG, SECRET iceberg_secret,
  ENDPOINT 'https://catalog.example.com/catalog');
INSERT INTO wh.sales.events BY NAME SELECT ... ;
MERGE INTO wh.sales.events t USING new_events s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET ...;

-- Delta: reads everywhere, appends, time travel
SELECT * FROM delta_scan('s3://bucket/table');
SELECT * FROM my_table AT (VERSION => 5);

DuckLake — DuckDB Labs’ own "SQL-as-a-lakehouse" format, with a SQL database as the catalog and object storage for data — is the most operationally complete of the four: full CRUD, snapshots, change queries, and encryption, plus a one-call migration path from Iceberg metadata via iceberg_to_ducklake. Apache Hudi, by contrast, has no first-class support (the historical community reader has been removed; nothing ships today). The catalog ecosystem is broad: S3 Tables, AWS Glue, Cloudflare R2, Polaris, Lakekeeper, BigLake all attach through the same TYPE ICEBERG mechanism.

7. DuckDB in the AI stack

The second axis of the thesis: DuckDB as the data layer of machine-learning and LLM work. The foundation here is Apache Arrow. DuckDB and Arrow share a zero-copy streaming integration — the project’s own engineering post calls it unique for exactly that reason — which is what lets the following all be true at once:

Retrieval: full-text, vectors, hybrid

Full-text search (fts extension). A complete inverted index implemented in SQL (originally inspired by SQLite’s FTS5, benchmarked on TREC collections): PRAGMA create_fts_index with the Porter stemmer by default (29 stemmers, 571 built-in English stopwords), and BM25 ranking through a generated match_bm25 macro with tunable k1/b. Verified working:

PRAGMA create_fts_index('docs', 'id', 'title', 'body');
SELECT id FROM (
  SELECT *, fts_main_docs.match_bm25(id, 'vectorized') AS score
  FROM docs) S
WHERE score IS NOT NULL ORDER BY score DESC;   -- -> exact hit

The documented caveat matters for pipelines: the FTS index does not auto-update when the table changes; you rebuild it. For lexical search over a corpus you re-chunk occasionally, that is fine; for a live inventory, it is not the tool.

Vector similarity search. Honest status, as of v1.5: there is no production-hardened ANN index in core. What exists: the fixed-size ARRAY type with array_distance, array_cosine_similarity and inner-product functions — exact brute-force kNN in plain SQL, which for corpora up to a few hundred thousand vectors is often exactly right — plus the experimental DuckDB Labs vss extension providing HNSW indexes (CREATE INDEX ... USING HNSW (vec)), and a community FAISS extension with GPU offload. The docs are refreshingly blunt that HNSW persistence is experimental and not recommended for production. The research frontier is active: the FlockMTL demo (VLDB 2025, arXiv:2504.01157) deeply integrates LLMs and RAG as relational operators (llm_complete, llm_filter, llm_rerank with MODEL and PROMPT objects), and v2.0’s preview mentions approximate-nearest-neighbor joins in the SQL dialect itself.

The practitioner recipe for RAG that has crystallized across the MotherDuck engineering series and DuckDB’s own text-analytics walkthrough: chunk documents, embed them (Hugging Face sentence-transformers locally, or a hosted API), store chunks and vectors in a table, retrieve with SQL cosine similarity, and — the genuinely clever part — do hybrid search by fusing BM25 scores with embedding scores via Reciprocal Rank Fusion in one query. No vector-database deployment; the retrieval layer is a SQL query against a Parquet file. For the record, that is also how this site’s own /knowledge search works — different engine, same philosophy.

At research scale: the Science Data Lake project (arXiv:2603.03126) builds scholarly infrastructure on DuckDB and Parquet — 293 million papers, roughly 960 GB, with BGE-large embeddings for ontology alignment. When people say DuckDB is "just for laptops", that is the counter-example to reach for.

8. DuckDB for geospatial: the quiet PostGIS alternative

Geospatial is where the connector story becomes genuinely surprising, because spatial data was long DuckDB’s weakest front — and then, release by release, it stopped being one.

The spatial pipeline: OSM PBF, Overture GeoParquet and GDAL formats in; R-tree and H3-accelerated analytics; GeoParquet and GeoPackage out.
The spatial pipeline: OSM PBF, Overture GeoParquet and GDAL formats in; R-tree and H3-accelerated analytics; GeoParquet and GeoPackage out.

The type system grew coordinates

The spatial extension bundles GDAL, GEOS and PROJ statically — no system dependencies — and implements the OGC Simple Features model: POINT through GEOMETRYCOLLECTION, WKT literals in SQL, Z/M coordinates, and roughly 150 ST_* functions covering predicates (ST_Intersects, ST_Contains, ST_Within...), measurements, overlays, simplification, Mapbox Vector Tiles (ST_AsMVT) and SVG output. The decisive move came in v1.5: GEOMETRY became a core engine type (stored as standard WKB, with optional column "shredding" that shrinks uniform geometry columns dramatically), so Parquet, Iceberg, DuckLake, the Arrow/GeoArrow conversion and even the Postgres scanner can carry geometries without loading the spatial extension. Coordinate reference systems entered the type system too — GEOMETRY('OGC:CRS84') is now a type annotation, with 7,000+ EPSG codes registered and consistency enforced across function arguments.

Indexes and joins: the limitation that died

For its first years, DuckDB Spatial’s Achilles heel was honest to state: no spatial index. That is over. Since the v1.1 era (late 2024) there is a real R-tree:

CREATE INDEX idx_zones USING RTREE (geom);
-- plan shows RTREE_INDEX_SCAN for constant-argument
-- ST_Intersects / ST_Within / ST_Contains / ... predicates

And v1.3 (May 2025) added a dedicated SPATIAL_JOIN operator that builds an ephemeral R-tree on the smaller input at runtime — no manual index needed. The project’s published benchmark (58 million NYC taxi rides × 310 zones, MacBook M3 Pro):

join strategy (58M rows)              runtime
blockwise nested loop                 1,799.6 s
bbox merge join (v1.2)                  107.6 s
SPATIAL_JOIN via ST_Intersects           28.7 s
SPATIAL_JOIN via native ST_DWithin        4.3 s

Ingest and export: GDAL as a universal spatial adapter

The bundled GDAL exposes some 50+ vector drivers. Shapefiles, GeoPackages, GeoJSON, FlatGeobuf, KML, OpenFileGDB and more are queryable via st_read — or by replacement scan, so FROM 'cities.gpkg' just works. OpenStreetMap gets special treatment: ST_ReadOSM parses compressed .osm.pbf with multithreaded protobuf decoding (much faster than GDAL’s OSM driver), handing you raw nodes/ways/relations with tag maps to build geometry from in SQL. Export is symmetric: COPY (...) TO 'out.gpkg' (FORMAT GDAL, DRIVER GPKG). Both directions verified in this article’s test runs — including a GeoPackage write-read roundtrip and 54 drivers actually present:

-- verified against DuckDB v1.5.5
SELECT ST_Distance_Sphere(ST_Point(16.3738, 48.2082),   -- Vienna
                          ST_Point(13.4050, 52.5200))   -- Berlin
 / 1000.0;                       -- -> 569 km (great-circle)

COPY (SELECT ST_Point(16.37, 48.21) AS geom, 'wien' AS name)
TO '/tmp/pt.gpkg' (FORMAT GDAL, DRIVER GPKG);
SELECT name FROM st_read('/tmp/pt.gpkg');      -- -> [('wien',)]

GeoParquet and the Overture workflow

DuckDB was among the first engines with end-to-end GeoParquet support (v1.1, 2024), and the integration keeps deepening: GeoArrow-encoded columns, and since v1.4 the native Parquet GEOMETRY logical types — files written by DuckDB are simultaneously valid GeoParquet and native-geometry Parquet, with per-row-group geospatial statistics. This aligns exactly with GeoParquet 2.0’s direction (the spec’s 2026 release candidate re-centers on native Parquet geospatial types). The flagship use case is Overture Maps: DuckDB is Overture’s officially documented query tool, and the recommended pattern is a two-step dance — bbox-prefiltered Parquet reads straight off Overture’s S3 buckets (hive-partitioned by theme/type, pruned by the bbox covering), then a regional extract landed as local GeoParquet:

SELECT geometry, names.primary AS name
FROM read_parquet('s3://overturemaps-us-west-2/release/2026-*/theme=buildings/*/*.parquet',
                  hive_partitioning = 1)
WHERE bbox.xmin BETWEEN 16.18 AND 16.58     -- Vienna bounding box
  AND bbox.ymin BETWEEN 48.10 AND 48.34
  AND ST_Intersects(
        geometry,
        ST_GeomFromText('POLYGON((16.18 48.10, 16.58 48.10,
                       16.58 48.34, 16.18 48.34, 16.18 48.10))'));

COPY (...) TO 'vienna_buildings.parquet';   -- local GeoParquet extract

H3: hexagonal grids from the community

Uber’s H3 hexagonal grid system arrives as a community extension (maintained by the H3 binding author, implementing the full H3 v4 API). A detail we hit ourselves: it is no longer on the official registry — the install is INSTALL h3 FROM community. Its practical superpower is turning expensive spatial predicates into cheap hash equi-joins; one documented KNN case reduced candidate pairs from 135 billion to 441 million — a three-hundred-fold reduction — on a query where DuckDB otherwise exhausted memory. Verified here:

INSTALL h3 FROM community;  LOAD h3;
SELECT h3_latlng_to_cell(48.2082, 16.3738, 7);       -- Vienna @ res 7
-- -> 608515207542603775
SELECT count(*) FROM (SELECT unnest(h3_grid_disk(
       h3_latlng_to_cell(48.2082, 16.3738, 7), 1)));
-- -> 7 (the cell + 6 neighbors)
SELECT count(*) FROM (SELECT unnest(h3_polygon_wkb_to_cells(
       ST_AsWKB(ST_GeomFromText('POLYGON((16.30 48.19, 16.45 48.19,
                    16.45 48.25, 16.30 48.19))')), 8)));
-- -> 52 hexagons cover that Vienna district

So: DuckDB Spatial or PostGIS?

The community benchmarks (which are exactly that — community-run, with a documented methodology dispute about loading formats, so take the numbers as indicative) converge on a consistent story: on Parquet-backed analytical workloads — point-in-polygon counts, distance joins, area-weighted interpolation — DuckDB matches or beats PostGIS, often by large factors. PostGIS retains the deepest function coverage (topology, raster, native KNN with the <-> operator) and remains the right answer for multi-user transactional spatial databases. QGIS workflows interoperate cleanly by writing GeoPackage or GeoParquet. There is even a correctness data point: the Spatter fuzzing study (arXiv:2410.12496) tested DuckDB Spatial against PostGIS, MySQL and SQL Server and found it in the same tier — including 34 previously-unknown logic bugs across all four engines, most of them since fixed. The honest summary: PostGIS is the production spatial database; DuckDB is the production spatial analytics engine — and for file-based, single-node analytical work, the second role is the one that has been missing.

9. A workday with DuckDB: one verified script

To close the loop, here is the entire connectivity thesis compressed into one realistic pipeline — every statement below was executed against v1.5.5 during the writing of this article:

-- 1. live operational database
ATTACH 'dbname=airdata user=postgres host=127.0.0.1 port=55432'
       AS pg (TYPE POSTGRES);

-- 2. land a copy as partitioned Parquet (the new cold storage)
COPY (SELECT id, city, pm25, recorded_at FROM pg.public.sensors)
TO 'sensors' (FORMAT PARQUET, PARTITION_BY (city));

-- 3. join it with an in-memory DataFrame (Arrow, zero-copy)
SELECT p.city, avg(p.pm25 + d.noise) AS adjusted
FROM read_parquet('sensors/*/*.parquet', hive_partitioning = 1) p
JOIN df d ON d.id = p.id
GROUP BY p.city;

-- 4. search the result corpus lexically
PRAGMA create_fts_index('docs', 'id', 'title', 'body');

-- 5. ship geometries to GIS colleagues
COPY (SELECT city, ST_Point(lon, lat) AS geom FROM stations)
TO 'stations.gpkg' (FORMAT GDAL, DRIVER GPKG);

-- 6. detach, done. No server was harmed.
DETACH pg;
The modern data stack in a box: extract, land, transform, search, map — five statements, zero servers, one file-format-agnostic engine.

10. Where DuckDB is not the answer

An exhaustive article owes you the failure modes. Four stand out:

Adoption, briefly

The trajectory is unusual for infrastructure software: 40,000 GitHub stars (August 2026), north of 50 million PyPI downloads per month, a DB-Engines ranking climb into the top 50, and the project’s first user survey (2024) reading like a portrait of the analytics long tail — three quarters of users’ largest datasets under 100 GB, 87% running on laptops, Parquet and CSV as the dominant formats. MotherDuck productizes the hybrid cloud angle; Crunchy Data fused the engine into Postgres for their analytics product; Google, Meta and Airbnb usage has been reported (via The Register’s reporting — secondary-source, treat accordingly); Spotify has spoken publicly about SQL-over-listening-history. And the governance model — a Dutch non-profit holding the MIT-licensed IP in perpetuity, no enterprise edition — is itself a thesis about what infrastructure should be.

Coda: the connector is the database

Step back from the features and one design idea remains: DuckDB decoupled the query engine from the data’s location and format — then rebuilt the coupling, cheaply, at the SQL level. Postgres stays the system of record; S3 stays the data lake; the GIS folder stays QGIS-shaped; the notebook stays a notebook. None of them have to become a database first for analytical questions to get answered. That is not a tool winning a category. That is a category — the ETL pipeline as mandatory middleman — quietly shrinking.

Every claim above traces to a primary source, and every code block was executed before publication. If you take one thing from it: run pip install duckdb, open a Python REPL, and point it at whatever heterogeneous mess you have lying around. The engine will do the rest.

Sources