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.