duckdb

What it is

duckdb is an embedded OLAP database — SQL engine, columnar storage, vectorised executor — packaged as a Python library you pip install. There is no server process; queries run in-process against in-memory tables, local CSV/Parquet/JSON files, pandas DataFrames, polars DataFrames, and Arrow tables, with zero-copy where possible.

On PyPI it occupies the niche between SQLite (row-store transactional) and a standalone analytics warehouse — fast, embedded, zero-config, designed for analytical queries on local data.

Install

pip install duckdb

Output: (none — exits 0 on success)

uv add duckdb

Output: dependency resolved, added to pyproject.toml

poetry add duckdb

Output: lockfile updated, installed into the project venv

pip install --upgrade duckdb

Output: in-place upgrade — re-open any persistent .duckdb files written by an older binary

Versioning & Python support

duckdb uses a fast-moving CalVer-ish minor + storage-format version scheme. Releases are roughly monthly; major (0.x → 1.x) jumps signal stable on-disk format guarantees. As of late 2025 the project is on the 1.x line with cross-version storage compatibility.

The Python binding bundles the C++ engine — there is no separate libduckdb to install. Wheels exist for Linux (manylinux/musllinux), macOS (universal2), and Windows.

Package metadata

• Maintainer: DuckDB Foundation; primary authors Mark Raasveldt and Hannes Mühleisen (CWI Amsterdam)
• Project home: github.com/duckdb/duckdb
• Docs: duckdb.org/docs
• License: MIT
• PyPI: pypi.org/project/duckdb
• Governance: non-profit DuckDB Foundation; commercial sponsor MotherDuck
• First released: 2018 (research project); 1.0 in 2024
• Downloads: > 20 M / month on PyPI as of late 2025

Optional dependencies & extras

duckdb itself ships with no PyPI extras — additional functionality is loaded as DuckDB extensions from inside SQL:

INSTALL httpfs;
LOAD httpfs;
SELECT * FROM read_parquet('s3://bucket/data.parquet');

Output: httpfs extension downloaded into ~/.duckdb/extensions/<version>/ and registered

Common extensions:

PyPI companion packages most pipelines pull alongside duckdb:

pip install duckdb pandas polars pyarrow jupyter

Output: installs duckdb + the DataFrame libraries it interops with zero-copy

Alternatives

Common gotchas

• In-process only. duckdb is not a server. Concurrent processes opening the same .duckdb file fight for the write lock; use a single writer or switch to ClickHouse/Postgres for true concurrency.
• Storage-format compatibility. Files written by 0.x can require export/import to load in 1.x. Within 1.x, format is forward-compatible. Pin the duckdb version in lockfiles for any persistent database.
• Default connection is process-shared. duckdb.sql(...) uses one global in-memory connection. For threads or independent state use duckdb.connect() and pass the connection explicitly.
• pandas/polars interop is zero-copy on Arrow — but not always. df = duckdb.sql("...").df() materialises into pandas (copies). Use .arrow() or .pl() to stay in Arrow and avoid the round-trip.
• Extensions download on first INSTALL. Air-gapped environments need duckdb_extension_directory pre-seeded or the extension built in.
• httpfs S3 credentials. duckdb does not read ~/.aws/credentials automatically — use SET s3_access_key_id and s3_secret_access_key, or CREATE SECRET (1.0+).
• pip install duckdb on Apple Silicon Rosetta. Wheels are universal2; under Rosetta you accidentally pull the x86_64 slice and run 3× slower than necessary. Verify with import duckdb; duckdb.__file__.

Real-world recipes

duckdb is the "one-process SQL warehouse" — its sweet spot is querying mixed local files (CSV, Parquet, JSON), pandas / polars frames, and remote object storage from a single Python script. The companion sections/python/duckdb covers SQL/API depth; the recipes below focus on the packaging-level setup each pattern requires.

Multi-format federation in one query — duckdb's killer feature: join a Parquet directory, a CSV, a JSON dump, and a pandas frame in a single SQL statement.

import duckdb
import pandas as pd

users = pd.DataFrame({"user_id": [1, 2, 3], "tier": ["free", "pro", "free"]})

con = duckdb.connect(":memory:")
con.register("users_df", users)

result = con.sql("""
    SELECT u.tier, COUNT(*) AS event_count, SUM(e.revenue) AS total
    FROM read_parquet('events/*.parquet') e
    JOIN users_df u USING (user_id)
    WHERE e.event_date >= DATE '2026-01-01'
    GROUP BY u.tier
    ORDER BY total DESC
""").df()
print(result)

Output: the aggregation; no copy of users is made — DuckDB scans it via Arrow as a virtual table

S3 / GCS query with the httpfs extension:

import duckdb

con = duckdb.connect()
con.execute("INSTALL httpfs; LOAD httpfs;")
con.execute("CREATE SECRET aws (TYPE s3, KEY_ID 'AKIA...', SECRET '...', REGION 'us-east-1');")

q = """
SELECT date_trunc('day', ts) AS day, count(*) AS n
FROM read_parquet('s3://bucket/events/2026/*/*.parquet')
GROUP BY day
ORDER BY day
"""
df = con.sql(q).df()
print(df.head())

Output: the per-day count; httpfs streams just the necessary row groups from S3, never downloading whole files

Parquet pushdown for column projection + filter:

import duckdb

q = """
SELECT user_id, SUM(revenue) AS total
FROM read_parquet('warehouse/events/*.parquet')
WHERE event_date >= '2026-01-01' AND status = 'active'
GROUP BY user_id
ORDER BY total DESC
LIMIT 10
"""
duckdb.sql(q).show()

Output: the top-10 users by revenue; DuckDB's optimiser pushes the filter and projection into the Parquet reader so only the necessary columns and row groups are decoded

Persistent on-disk database:

import duckdb

con = duckdb.connect("warehouse.duckdb")
con.execute("CREATE TABLE IF NOT EXISTS orders AS SELECT * FROM read_csv_auto('orders.csv')")
con.execute("CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id)")
con.close()

Output: a single-file warehouse.duckdb on disk, queryable by any DuckDB client at the same major version

Postgres scan (no ETL needed):

import duckdb

con = duckdb.connect()
con.execute("INSTALL postgres; LOAD postgres;")
con.execute("ATTACH 'host=db.example.com dbname=prod user=ro' AS pg (TYPE postgres)")
df = con.sql("SELECT id, name FROM pg.public.users WHERE created_at > '2026-01-01'").df()
print(df.head())

Output: a DataFrame containing live Postgres data; no dump-and-reload step required

Performance tuning

duckdb's vectorised executor is fast by default. The remaining tuning levers are about what to skip (column projection, predicate pushdown) and how to spend RAM (thread count, temp-disk spill).

import duckdb

con = duckdb.connect()
con.execute("SET threads TO 8")           # cap CPU usage
con.execute("SET memory_limit = '12GB'")  # spill to disk past this
con.execute("SET temp_directory = '/fast-ssd/duckdb-tmp'")

Output: no return value; subsequent queries respect the settings

Tuning levers in order of impact:

Inspecting the query plan:

import duckdb

q = """
SELECT region, sum(revenue)
FROM read_parquet('events/*.parquet')
WHERE event_date >= '2026-01-01'
GROUP BY region
"""
duckdb.sql("EXPLAIN " + q).show()
duckdb.sql("EXPLAIN ANALYZE " + q).show()

Output: the physical plan (EXPLAIN) and the same plan annotated with per-operator timings (EXPLAIN ANALYZE) — the difference shows where time is going

Memory & dataset-size scaling

duckdb is out-of-core by design — it spills to disk past the configured memory limit and works on datasets much larger than RAM. The main scaling lever is configuring spill location and memory budget; you rarely need to chunk data manually.

import duckdb

con = duckdb.connect()
con.execute("SET memory_limit = '4GB'")
con.execute("SET temp_directory = '/var/tmp/duckdb'")

# Aggregate 500 GB of Parquet on a 4 GB-RAM box
con.sql("""
COPY (
    SELECT user_id, SUM(revenue) AS total
    FROM read_parquet('warehouse/**/*.parquet')
    GROUP BY user_id
) TO 'user_totals.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');
""")

Output: writes user_totals.parquet; spills intermediate hash tables to /var/tmp/duckdb as needed

Streaming arrow output without materialising:

import duckdb
import pyarrow as pa

con = duckdb.connect()
reader = con.execute("SELECT * FROM read_parquet('huge.parquet')").fetch_record_batch(rows_per_batch=100_000)
for batch in reader:
    # process one batch at a time — never holds the full table in RAM
    print(batch.num_rows)

Output: prints the rows per batch as DuckDB streams Arrow record batches; useful for piping into downstream processors

For genuinely massive workloads (TB+), the right pairing is DuckDB + MotherDuck (the hosted DuckDB service from the same authors) or DuckDB + an Iceberg/Delta table layout so multiple processes can read the same dataset.

Version migration guide

DuckDB ships roughly monthly. The on-disk storage format compatibility story is the most important migration concern.

Format compatibility timeline:

• 0.8.x → 0.9.x → 0.10.x: required EXPORT DATABASE/IMPORT DATABASE round-trips between minors. Painful for any persistent file.
• 1.0 (2024): forward-compatible storage format declared. Files written by 1.0 can be opened by every later 1.x release.
• 1.x → 1.x: SQL surface and extension API can change, but storage stays compatible. Read the changelog for breaking SQL keywords or function renames.

import duckdb

print(duckdb.__version__)
con = duckdb.connect("warehouse.duckdb")
print(con.execute("PRAGMA database_size").fetchone())

Output: the installed version and the on-disk size — sanity-check both before upgrading

Common SQL-level migrations within 1.x:

• read_csv_auto is still alive but read_csv with auto_detect=true is the preferred form.
• The STRUCT_PACK keyword for inline structs became more strict on field naming.
• LIST and ARRAY are aliases; older code mixed them — pick one.
• Several extensions moved from "bundled" to "auto-loadable" — first-run pulls them from the extension repository unless autoinstall_known_extensions is false (offline-safe).

Pin the duckdb version in production lockfiles. Persistent .duckdb files written by a future version cannot be opened by an older binary even within 1.x.

Interop with adjacent ecosystems

DuckDB speaks Arrow natively, which is why it pairs so well with pandas and polars. The interop matrix below tells you when a query result is zero-copy versus when it triggers a serialisation.

import duckdb
import pandas as pd
import polars as pl

pdf = pd.DataFrame({"a": [1, 2, 3]})
plf = pl.DataFrame({"a": [4, 5, 6]})

# Both frames are accessible inside SQL with zero copy
result = duckdb.sql("SELECT a FROM pdf UNION ALL SELECT a FROM plf").pl()
print(result)

Output: a polars frame with [1, 2, 3, 4, 5, 6]; DuckDB sees both source frames as Arrow scans

Crossing into the Jupyter ecosystem: duckdb.sql(query) returns a DuckDBPyRelation that renders nicely in notebooks. Use %load_ext sql + %sql duckdb:/// for cell-magic SQL.

Troubleshooting common errors

The errors below are the recurring frictions; most resolve with a one-line setting change.

• IO Error: Cannot open file '...': No such file — relative path mismatch. DuckDB resolves paths relative to the process CWD, not the script directory. Use absolute paths.
• Catalog Error: Table with name X does not exist — you used duckdb.sql(...) (which uses a fresh in-memory connection) and registered the frame on a different connection. Use one explicit con = duckdb.connect(...) for the whole script.
• Permission denied writing to a .duckdb file — another process holds the write lock. DuckDB allows multiple readers but only one writer.
• HTTP 403 from httpfs — wrong region or stale credentials. Use CREATE SECRET ... TYPE s3 (1.0+) rather than legacy SET s3_* for clean error messages.
• Cannot load extension '...': not on the autoload list — an extension is community-maintained. Set SET allow_community_extensions = true first.
• Stale ~/.duckdb/extensions/<version>/ after upgrading — extensions are version-keyed; delete the old version directory or run INSTALL <ext> -- update to refresh.
• Storage version error opening a .duckdb file with an older binary — upgrade the binary or use EXPORT DATABASE from the newer one and IMPORT DATABASE on the older.
• OOM despite memory_limit — some operators (large window functions, recursive CTEs) ignore the limit. Add SET temp_directory = '/fast-disk/...' and ensure that disk has room.

When NOT to use this

duckdb is the right answer surprisingly often; the cases below are where another tool wins.

• Concurrent writers / transactional workloads: DuckDB is one-writer. Use Postgres, SQLite, or a true OLTP system.
• Streaming ingest: duckdb is batch-oriented; use Flink, Beam, or a streaming framework.
• Cluster-scale (multi-TB): duckdb is single-process. Use Snowflake, BigQuery, Clickhouse, or a distributed query engine for petabyte-scale.
• Pure DataFrame ergonomics: if everyone on the team prefers fluent APIs over SQL, polars is a better daily-driver.
• Vector / time-series specialist workloads: duckdb has good support but specialised stores (DuckDB + VSS extension vs Pinecone; TimescaleDB; ClickHouse) sometimes win on shape-specific operations.
• You need cross-region latency-bounded reads: DuckDB does not know about region affinity; a cloud warehouse with regional storage will do better.

See also

• sections/python/duckdb — full API tutorial (SQL, pandas/polars interop, Arrow)
• sections/python/pandas — DataFrames that duckdb queries zero-copy
• sections/python/polars — Arrow-native DataFrame, perfect pair with duckdb
• sections/packages-pip/pip-pandas — package-level comparison
• sections/packages-pip/pip-polars — sibling Arrow-native analytics