TopolearnKnowing where DuckDB fits tells you when to reach for it instead of pandas loops, SQLite or a data warehouse.
DuckDB is an analytical (OLAP) SQL database that runs in-process: it is a library linked into your program, the same way SQLite is, so there is no server to install, no port and no user accounts. You pip install duckdb, import it and run SQL.
Where SQLite is built for many small transactional reads and writes of single rows, DuckDB is built for queries that scan and aggregate millions of rows: it stores data by column, processes vectors of values at a time and uses all your CPU cores. Typical uses:
- ad-hoc analysis of CSV, Parquet and JSON files without loading them anywhere first
- SQL over pandas or Polars DataFrames
- local ETL steps and data-pipeline tests
- embedding an analytics engine in an app or notebook
The dialect is PostgreSQL-flavoured standard SQL plus many conveniences, so the SQL you already know works. A database is either in-memory (gone when the process exits) or a single .duckdb file.
DuckDB is not a replacement for PostgreSQL behind a web app: only one process can open a database file for writing, and it is tuned for big scans, not thousands of tiny concurrent updates.
Example
import duckdb
# duckdb.sql runs on a default in-memory database
rel = duckdb.sql("SELECT 42 AS answer, version() AS v")
print(rel.fetchall()) # [(42, 'v1.5.x')]
# aggregate ten million generated rows in well under a second
print(duckdb.sql("""
SELECT count(*) AS n, avg(i) AS mean
FROM range(10_000_000) t(i)
""").fetchone()) # (10000000, 4999999.5)Remember
- DuckDB is an embedded analytical database: a library, not a server
- Columnar, vectorized and multi-threaded, so large scans and aggregates are fast
- Use it for analysis and pipelines, not as an OLTP backend for many concurrent writers
Watch out for
- Expecting several processes to write the same database file at once; DuckDB allows one writer process.