DuckDB
- đź”— https://duckdb.org/
- Why DuckDB
- Awesome DuckDB for examples on projects build with this db.
- Similar scope to Polars
DuckDB has a similar philosophy to SQLite: simple to install and embedded in-process operation.
DuckDB has no external dependencies, neither for compilation nor during run-time. For releases, the entire source tree of DuckDB is compiled into two files, a header and an implementation file, a so-called “amalgamation”.
DuckDB is completely embedded into a host process, this has advantages like:
- high-speed data transfer to and from the database
- process foreign data without copying
- e.g. the DuckDB Python package can run queries directly on Pandas data without ever importing or coping any data
Key Charactestics of DuckDB:
- Portable
- Feature-Rich
- Fast
- Extensible
- Free
- Thoroughly Tested
DuckDB supports analytical query workloads and online analyical processing (OLAP). DuckDB uses a columnar-vectorized query execution engine, where queries are still interpreted, but a large batch of values (a “vector”) are processed in one operation. This greatly reduces overhead present in traditional systems such as PostgreSQL, MySQL or SQLite which process each row sequentially.
A similar project is Polars
Use Cases
Some use cases are:1
- DuckDB outperform Pandas in benchmarks2, it allows you to run SQL queries directly against in-memory Pandas DataFrames.
- Analyzing local data (or large data sets copied from cloud storage).
- e.g one user on reddit reported that they have loaded a 50GB of csv file using the glob syntax on a computer with 32GB RAM and the RAM went up only 1GB. It performed
group byin 5 minutes. - e.g. test BigQuery from Google Cloud locally
- e.g one user on reddit reported that they have loaded a 50GB of csv file using the glob syntax on a computer with 32GB RAM and the RAM went up only 1GB. It performed
- Do data diffs locally. E.g.
Select \* from (select c1, c2 from new_table except select c1, c2 from old_table UNION ALL select c1, c2 from old_table except select c1, c2 from new_table). - Write SQL queries locally that you can copy past in other sysmtes like dbt, notebooks etc.
- DuckDB allows for the copying of a compressed data file into memory without having to copy the entire file.
- It can be deployed in embedded systems where there are fewer resources
Use example taken from digital ocean3:
pip install duckdb
import duckdb
import pandas as pd
# A small DataFrame in pandas
df = pd.DataFrame(
{
"city": ["Paris", "Berlin", "Lagos", "Delhi"],
"temp_c": [21, 19, 30, 35],
}
)
# DuckDB sees 'df' as a table via replacement scans
result = duckdb.sql("""
SELECT
city,
temp_c,
temp_c * 9.0/5.0 + 32 AS temp_f
FROM df
WHERE temp_c >= 25
""").df()
print(result)- df should be a plain pandas DataFrame.
duckdb.sql(…)implicitly reads df as a table called df.- There is no copy operation; DuckDB accesses the existing DataFrame in memory.
- The result is materialized as another pandas DataFrame at the end.
DuckDB Limitations
- Concurrency: DuckDB has a single‑writer model
- No Built-in plotting: instead use pandas, matplotlib or other libraries.