Join the discussion
Write your take first — we'll ask for email only when you're ready to publish.
- Hacker News
- 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.
by egeozcan - 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.by lanstin
- Wait seriously? That's a dealbreaker if true.by frollogaston
- > $16.49/month server [...] hetzner CCX13
That server is now $51.09 for those wondering See https://news.ycombinator.com/item?id=48540844
by raro11 - 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.
- This is a bait and switch article. Compare apples and oranges, "oh btw look at our product".by oathvz
- I couldn't read the article as it read like AI.
And I am fatigued by the AI style in all code comments, reviews, PRs etc :(
by k3liutZu - Title, which is already synthetic, should be "Choose DuckDB rather than SQLite for Analytics Workloads".by tptacek
- 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.by ethin
- 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.
- 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.
by coldbrewed - 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.
by shubhamjain - You can have multiple read only processes. But yes, concurrent read mode and write mode is blockedby biophysboy
- 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.
- This is actually the biggest reason I don't automatically choose sqlite for small project databases. I've built a lot of toy utilities that crossed a bare threshold of usefulness, and then suddenly it's not a toy anymore when people start using it.
Now it's debt. Oops.
by threatofrain - 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.by coldbrewed
- > DuckDB's columnar engine
That is workload specific. Title should be "Choose DuckDB rather than SQLite for Analytics" IMHO
by brightball - Yeah, someone who has more tokens than simple sense.
- DuckDB also implements MVCC. In DuckLake, the DB is used for absorbing small writes and compacting them before sending to object storage.
It has some characteristics typical of OLTP engines. But they are targeted and limited to areas DuckDB feels are important.
by adsharma - 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.by ray_v
- This. The title might as well be “Choose a hammer rather than a wrench (when driving nails)”by dangoodmanUT
- This is AI slop, but I really want to know more about their write patterns and how they were doing batches.
- 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).
by otterley - Agreed. It felt like I was reading a chat with Claude Code.by headz