DuckDB vs SQLite: 5 Decisions Before You Choose
Cover Image
Your application can save an order instantly and still struggle to summarize a year of orders. That does not automatically mean the database needs replacing. It may mean the reporting query has a different job from the checkout query.
The practical DuckDB vs SQLite choice starts there. SQLite is a strong starting point for embedded application state, and DuckDB is built for embedded analytical workloads. For broader reading on the analytics landscape, see the best data analytics blogs. Both can execute SQL inside your process. Both support transactions. Neither wins every query that happens to use a local file.
Before migrating, answer five questions. What does a typical query touch? How does storage serve it? Who owns writes? How fresh do reports need to be? What does a representative test actually measure? Those answers often lead to a smaller change than a full database replacement.
Start with the work your application needs done, not the size of its database file.
Choose the workload, not the file size
An application query might retrieve one customer, update a preference, or save an order. An analytical query might group every order by month, join it to customer attributes, and calculate regional totals. Both are SQL, but their access patterns differ.
SQLite's appropriate-use guidance emphasizes local application storage and embedded use. DuckDB's official design overview describes an analytical engine with column-oriented, vectorized execution. Treat these as design priorities, not a list of operations the other engine cannot perform.
Your dominant requirement | A sensible starting point | What to check
Local app state with indexed lookups and short writes | SQLite | Index design, transaction length, writer contention
Broad scans, joins and grouped reports | DuckDB | Memory, intermediate results, ingestion cost
An existing SQLite app with occasional reporting | Keep SQLite initially | Whether a better query or index is enough
An existing SQLite app with expensive recurring analytics | Evaluate DuckDB beside it | Snapshot freshness and operational overhead
Many independent services writing concurrently | Evaluate a client-server database | Whether embedded ownership still fits
There is no universal row-count threshold where SQLite becomes wrong. A selective query against an indexed table can behave very differently from a full-table aggregation over the same file. A small dataset can also make either engine fast enough that adding another dependency buys little.
That distinction becomes easier to reason about once you look at how each engine organizes the work.
Row storage and column storage solve different problems
Think of an order ledger. To inspect one order, you want its customer, amount, status and timestamp together. To total sales by month, you may need the timestamp and amount from nearly every order, but not its delivery notes or contact details.
SQLite's row-oriented tables suit the first access pattern. DuckDB's column-oriented storage and vectorized execution suit the second. Processing relevant columns in batches is a good match for scans and aggregates. This is the architectural reason to evaluate DuckDB for reporting — not evidence that every analytical query will be faster.
SQLite can aggregate and join. DuckDB can insert, update and delete. Calling DuckDB read-only or immutable confuses optimization priorities with supported behavior. Its design overview explicitly includes ACID transactions and persistent storage.
Another distinction matters: the query engine and the data's storage format are separate choices. Running DuckDB over a SQLite database does not automatically convert those SQLite pages into DuckDB's native columnar storage. An extension-based scan and a materialized DuckDB table can therefore have different performance characteristics.
If an existing SQLite report is slow, start with the query, schema and indexes. If the workload repeatedly scans and aggregates substantial portions of the data, test an analytical engine. Neither step requires declaring your current database obsolete.
Storage explains the read path. Process ownership determines whether the write path will work at all.
Decide who owns writes before comparing speed
"Embedded" says where the database engine runs. It does not mean every engine supports the same arrangement of processes, connections and writers.
SQLite: one writer, with useful reader concurrency
In SQLite's write-ahead logging documentation, readers and a writer can operate concurrently, but there is still only one writer at a time. WAL is not a route to unlimited write concurrency. Its shared-memory requirements also mean participating processes must run on the same host.
For a local application, short transactions and deliberate connection handling may fit that model well. If many independent writers keep competing for the same database, address that bottleneck instead of assuming an engine swap will remove it.
DuckDB: scope the claim to its native embedded file
DuckDB's official concurrency documentation describes concurrent writer threads within a single writer process. It uses multi-version concurrency control and optimistic concurrency control. Conflicting changes can require application-level handling.
For the native embedded database-file model, do not design several unrelated processes to write the same file as though it were a client-server database. One process can own the database and coordinate work, or a different deployment architecture may be appropriate. Extensions and other services need their own concurrency assessment. This native-file rule is not a blanket description of every system built around DuckDB.
Write this question on the architecture diagram: which process is responsible for mutations? If nobody can answer it clearly, settle that before timing queries.
Once ownership is explicit, you can separate operational writes from reporting without replacing the application database.
Keep app state in SQLite and analyze a snapshot in DuckDB
Consider an order-management example: SQLite handles current orders, while a reporting job analyzes a consistent snapshot. The app keeps its existing write path. DuckDB gets a stable input for analytical work. The trade-off is freshness: a report describes the snapshot, not necessarily the latest committed order.
Use SQLite's online backup API or another documented consistent export mechanism to create that snapshot. Do not copy only a live database's main file and assume you captured committed data held in its WAL.
A practical sequence is:
Produce a consistent SQLite snapshot and record its completion time.
Give the reporting job exclusive ownership of its analytical workspace.
Query the snapshot through DuckDB's SQLite extension, or materialize the data into DuckDB before repeated analysis. If reports need richer presentation, review the best data visualization tools for the output layer.
Display the snapshot time beside the report so readers understand freshness.
The official SQLite extension documentation describes attaching and querying SQLite databases. Direct attachment avoids requiring an immediate full conversion, but it does not make the original storage columnar. Materialization adds load time and another copy to manage. Measure that cost rather than treating it as free.
For a small illustration of the analytical query shape, the SQL below uses synthetic values — not production data or a benchmark. It runs in either engine and requires no tables or external files.
-- Illustrative report: aggregate synthetic orders by region.
WITH orders(region, amount_cents) AS (
VALUES ('North', 1200), ('South', 800), ('North', 300)
)
SELECT region, SUM(amount_cents) AS revenue_cents
FROM orders
GROUP BY region
ORDER BY revenue_cents DESC;The expected totals are North: 1500 and South: 800. Those results demonstrate the query's meaning, not relative engine speed. If the reports need every newly committed order immediately, a periodic snapshot may be the wrong design. Budget explicitly for synchronization, or choose an architecture that meets that requirement.
With ownership and freshness rules written down, a benchmark can answer a useful question.
Test the queries your users actually run
A benchmark is useful only if it measures the decision you are making. If DuckDB needs an import step but SQLite already holds the data, timing only the final aggregation omits part of the first report's cost. For repeated reporting, that one-time cost may amortize — but it still belongs in the explanation.
Build a small test set around the actual workload.
A representative indexed lookup or short transaction, if the app depends on it.
A broad aggregation resembling a real report.
A join with realistic key distributions and intermediate-result sizes.
The full snapshot, load and query path, when proposing a two-engine workflow.
Record engine versions, hardware, configuration, dataset shape and whether runs are cold or warm. Check output equivalence before comparing timings. Repeat runs and retain memory usage, temporary-disk usage and failures alongside elapsed time. A failed query is not a missing data point to quietly discard.
DuckDB's workload-tuning guidance is a better starting point for interpreting its resource use than an old screenshot of a performance table. The same caution applies to historical reports of memory limitations. Engine behavior changes over time, and joins or aggregations may stress different resources across versions.
This article does not present a hands-on performance experiment or claim a speedup multiplier. The decision framework is documentation-based. The measurements that justify a migration need to come from your own workload.
Use those measurements to choose the smallest change that solves the problem.
Make the smallest database decision that fits
For DuckDB vs SQLite, begin with a default you can explain. SQLite for embedded application state and short transactional work. DuckDB for substantial analytical scans and transformations. Then test the exceptions instead of defending the default at all costs.
If SQLite already meets the application's latency and reliability needs, keep it. If reporting becomes expensive, try DuckDB against a consistent snapshot before rewriting the operational store. If multiple independent writers or strict freshness requirements dominate the design, address those constraints before choosing an embedded engine on query speed alone.
The best outcome may be one database, or two with a clearly defined boundary. What matters is knowing who writes, what each query reads, and how current the answer needs to be. Let the checkout stay simple and the reporting job do its own work.
Key Takeaways- Pick SQLite for application state with indexed lookups; DuckDB for broad scans and grouped reports.- Decide which process owns writes before timing anything.- For mixed workloads, keep SQLite as the operational store and analyze a consistent snapshot in DuckDB.- A benchmark is only useful if it reflects the actual workload.- Start with the smallest change that solves the problem, not the default you defend at all costs.
