Why DuckDB replaces SQLite for local analytics workloads

The OLAP vs OLTP divide

I see teams ditching SQLite for DuckDB whenever they move from simple app storage to heavy data crunching. SQLite remains the king of OLTP with point lookups hitting sub-1ms speeds using B-tree indexes, but it fails when you try to run heavy aggregations. DuckDB’s columnar architecture beats SQLite by 10 to 50 times on a million-row table. DuckDB is a 50MB binary. SQLite is a 1MB binary. You already know the basics of columnar storage, so I will skip explaining why scanning columns beats scanning rows. DuckDB outperforms pandas by 5 to 20 times on aggregation queries over large CSV or Parquet files because it uses vectorized columnar execution. It also streams data from files, which lets a 16GB laptop process 50GB files without loading everything into RAM. While SQLite requires a manual import step to move CSV or Parquet files into a table, DuckDB reads them natively with zero import step. DuckDB handles Iceberg and Delta Lake formats easily. I recommend using DuckDB for SQL aggregations and filtering, then passing the results to pandas for complex Python transformations or ML preprocessing that requires NumPy integration. Some users even use DuckDB to query Hugging Face datasets directly via the hf:// prefix.

Speed and Scale

I find the performance gap between DuckDB and ClickHouse-local significant when datasets reach massive scales. When I tested querying two billion rows of cloud infrastructure spending data on a 16GB Macbook Pro, ClickHouse-local ran 3X faster than DuckDB. ClickHouse-local processes nearly 200 million rows per second. DuckDB provides better developer experience, yet ClickHouse-local handles raw query speed with more aggression. I find DuckDB easier for local tasks because it saves tables and SQL history to a single file on disk. To preserve results in clickhouse-local, you must manually write them to disk, a process that took 734 seconds in my testing. For a query measuring EC2 compute usage, DuckDB took 30 seconds to compute data points while ClickHouse-local returned results in 11 seconds. When writing an intermediate table, DuckDB needed just under 3 minutes while ClickHouse finished in under a minute. DuckDB has massive popularity, with 6 million monthly Python client downloads and 17 million monthly extension downloads. ClickHouse uses the MergeTree engine and specialized codecs like Gorilla or Delta to maintain its speed. Users can also build DuckDB pipelines with ArgoCD to replace Spark, seeing a 2.3x performance improvement. DuckDB also integrates with Ibis, allowing users to write Python code that translates to efficient DuckDB queries.

The Concurrency problem and 2.0

The concurrency model in DuckDB creates real friction for production workflows. A process with a write lock prevents other processes from even reading the database. I experienced this productivity killer when I had to close the DuckDB CLI just to run a query in another script. The upcoming version 2.0 release this fall aims to fix this with a stable client/server mode via the quack extension. This extension also makes it easier to remotely query other databases such as PostgreSQL and MySQL. The data engine beneath DuckDB now works asynchronously, which allows the I/O layer and the query processing layer to scale separately and helps the system make better use of parallelism during heavy workloads. This update also includes a completely rewritten SQL parser to improve error messages and extensibility. The new storage format for the database helps when opening wide tables with many columns. New optimizer improvements like partition or sorting awareness and cardinality estimation also arrive with this release. This version also provides faster queries for certain workloads, with speedups reaching up to 40 times. Can the new storage format solve the latency issues for wide tables with massive indexes?