The data scientists I work with use this. Why do they use it? I don't really know much about it, but I've noticed they use it quite often. I mainly use MySQL or PostgreSQL. What are the advantages of DuckDB? It seems like they usually use it as an alternative to Pandas.
DuckDB has been probably my most used tool in 2026 - if you're comfortable with SQL it's incredible at quickly prototyping and slicing / dicing data.
I do a lot of experiments with regexes, and if you get used to the RE2 syntax that DuckDB uses, you can see up to 10-100x uplift in terms of speed compared to Postgres on things like regexp_matches(), regexp_extract(), etc (depending on query/table/machine specifics). It has quite powerful scripting with custom Macros, fixes a lot of annoyances of SQL for me compared to Postgres.
I think if you have access to a machine with a lot of RAM / cores and a beefy data set, then it's basically like a RAMdisk version of Snowflake running locally on your machine.
(and of course the fact that it makes it convenient to read CSV/parquet, read/write from S3, etc) - it's a very ergonomic tool.
"10-100x uplift in terms of speed compared to Postgres on things like regexp_matches()"
was about, so I checked, and DuckDB's regexp_matches() is not the same as PostgreSQL's regexp_matches(). DuckDB's version "Returns true if string contains the regexp pattern, false otherwise." [1] while PostgreSQL's "returns a set of text arrays of matching substring(s)" [2].
I think the closest think in PostgreSQL to DuckDB's regexp_matches() is `string ~ pattern` or `regexp_like(string, pattern)`.
Here is the thing, it’s a write only single file format. If you need to run analytical queries it’s optimized for reading, you just open a file and query for the parts you want. If you have multiple clients that read and write data to the database, you should use postgresql.
It’s not really a database in the traditional sense, there is no ACID complexity, it’s a library that lets use write SQL to query a tabular data file.
DuckDB is a column-oriented database. This is why it's so fast compared to SQLite for a lot of analytical queries. CSV is a row-based format. DuckDB has to load it completley to memory before it can run any queries. Depending on the amount of RAM and Swap you have avialable, it's better to use parquet or similar column-based formats for data storage. Even SQLite as format is faster than CSV, since SQLite is already optimized, integers and floats take less space in SQLite format than as CSV.
The idea is that you treat data storage and data processing as two distinct tasks. You have your data in S3 or HDFS or a local directory and you run DuckDB on whatever single-node compute you have: a local machine or a container in a cluster.
There are companies that write cluster computing engines with duckdb as the byte-cruncher at their heart, but usually it's more like NumPy, Pandas or Polars on steroids. Or SQLite, but for running OLAP queries.
In my previous job (working with electric vehicles) we had a AWS batch job that pulled all data from S3[1] into containers (one container per vehicle) and then push that data into duckdb then run some basic queries and data analysis.
The key thing is that this scaled horizontally pretty much forever, since each vehicle had a fixed amount of data per year we could tightly control the performance characteristics of the analysis. Adding more vehicles didn't make things slower, just linearly more expensive.
I vaguely remember the data from those containers also being used to process some aggregate analysis (like the each vehicle-container would output some data that would be consumed by another job that did aggregates). But I don't remember the specifics.
[1]: I believe we used JSONL or parquet format, but I didn't work in that part of the stack directly
