Despite its obvious advantages, the biggest drawback of DuckDB is its concurrency model [1]. If a process opens a database in read-write mode, it acquires an exclusive lock on the file. This prevents even simple read operations from other processes as long as the writer remains open. Maybe there's a simple workaround I haven't come across, but I found it to be quite a productivity killer.
So yes, all these benchmarks are great, but it wasn't so fun working with DuckDB when I had to close duckdb cli, just so a query in another script could run.
Duckdb has a server mode[1] which might alleviate some of those pain points. SQLite is a bit more precise in that only a single connection can write to the DB which provides more concurrency but still has pain points. For a single file DB either choice seems justifiable to manage complexity.
Quack is now kind of a workaround for that limitation, as you can have a process with the lock offer read access to other processes. It's not perfect, but for that specific use case it's pretty good.
As many others have said here, you should just think about transactional vs analytical as well as single user in-process vs multi-user client-server when making choices.
That said, I do think duckdb has a wider range of use cases than people here might think. It can whip through fairly large datasets (I use it for ~1B row tables all the time)
Yes - it's a specific workload for sure. SQLite is still the GOAT when it comes to OLTP, but DuckDB is really becoming the GOAT in the OLAP world - I think DuckDB is simply amazing and truly an amazing piece of technology for anyone working with large amounts of data.
How are these two DB engines even comparable other than at the edges? They handle two completely separate workload types: one is more a general-purpose DB engine and the other is specifically for columnar datasets, analytics and the like -- of course a hand-tuned DB engine is going to destroy SQLite on any reasonable benchmark: SQLite wouldn't be optimized for that hand-tuned use-case whereas something like DuckDB is.
SQLite is _the_ tool of choice for local SQL databases with minimal overhead. If you needed a single file DB for an OLAP workload, SQLite was still the best option even if the technology wasn't an ideal fit. Duckdb is exciting specifically because SQLite/duckdb aren't comparable; we can stop shoehorning OLAP into an OLTP database.
I ran into this myself; I tried using SQLite to store the results of whole-internet rDNS scan and a count() over the entire DB could take 8 minutes. I used the wrong DB for the job and the narrative around SQLite/duckdb is around reckoning with perfectly reasonable limitations and tradeoffs that SQLite made.
With indexes SQLite is very very fast even for aggregation at medium scales.
And DuckDB is reasonably fast for even single record writes. ~1000x slower than SQLite, but that's still pretty fast if you're only doing a few hundred writes per second or batching.
DuckDB is modern but written in C++ and crashes in production more than SQLite, which is old and it doesn’t really crash. you have to build in resilience to use DuckDb.
TL;DR: SQLite was doing OLAP work it was never built for (and it was okay at it, I must add), and the perf. ceiling moves two orders of magnitude when you use something that's more fit for the purpose (DuckDB in this case).
TLDRTL;DR: If everything you do is column-store territory, use a column-store.
Great job on comparing apples to oranges. DuckDB is a columnar OLAP engine, SQLite is row-oriented OLTP. DuckDB should stomp SQLite in that particular use case.
AI slop. The content might be valuable but the framing makes it too painful to read.
Please, folks, write with your own voice -- especially if it's for your business blog. It's good for you as an author (practice makes perfect) and it's good for your readers (whom you want to influence).
Too bad Android doesn't have JDBC APIs. Getting the native binaries compiled on Android is the easy part, but there is no straightforward way to access it from Java. The Room APIs are tied to SQLite as well.
I ran this through an AI checker and it flagged half of it immediately. @dang, I know Substack just enabled Pangram integration, is there anyway you could get Y Combinator to spring for a Pangram subscription for the front page or something ?
Yes, I am asking if that particular policy could be changed. A little AI here and there is fine, to each their own, but I would prefer not to read posts that are overwhelmingly so.
So yes, all these benchmarks are great, but it wasn't so fun working with DuckDB when I had to close duckdb cli, just so a query in another script could run.
[1]: https://duckdb.org/docs/current/connect/concurrency
[1]: https://duckdb.org/2026/05/12/quack-remote-protocol
That said, I do think duckdb has a wider range of use cases than people here might think. It can whip through fairly large datasets (I use it for ~1B row tables all the time)
That is workload specific. Title should be "Choose DuckDB rather than SQLite for Analytics" IMHO
Can you explain this more, especially why SQLite is best at OLTP and what happens at scale?
I ran into this myself; I tried using SQLite to store the results of whole-internet rDNS scan and a count() over the entire DB could take 8 minutes. I used the wrong DB for the job and the narrative around SQLite/duckdb is around reckoning with perfectly reasonable limitations and tradeoffs that SQLite made.
And DuckDB is reasonably fast for even single record writes. ~1000x slower than SQLite, but that's still pretty fast if you're only doing a few hundred writes per second or batching.
And I am fatigued by the AI style in all code comments, reviews, PRs etc :(
That server is now $51.09 for those wondering See https://news.ycombinator.com/item?id=48540844
TLDRTL;DR: If everything you do is column-store territory, use a column-store.
Please, folks, write with your own voice -- especially if it's for your business blog. It's good for you as an author (practice makes perfect) and it's good for your readers (whom you want to influence).