DuckDB vs SQLite: choosing an embedded database
DuckDB and SQLite are both embedded databases: a library inside your process, no server to run. SQLite is built for transactional, row-by-row work; DuckDB is built for analytics over many rows. They complement each other more than they compete.
TL;DR
- Choose SQLite if an application needs a small, reliable, transactional store: settings, local caches, mobile and desktop apps, small websites, tests.
- Choose DuckDB if you need to analyze data: aggregate large tables, query Parquet or CSV files directly, or replace a pandas script with SQL.
DuckDB vs SQLite at a glance
| Dimension | SQLite | DuckDB |
|---|---|---|
| Workload | Transactions (OLTP) | Analytics (OLAP) |
| Storage | Row-oriented | Columnar |
| Execution | Row at a time | Vectorized, multi-threaded |
| Reads files directly | No (import first) | Parquet, CSV, JSON, also from S3 |
| Concurrency | Many readers, one writer | Many readers, one writer process |
| Typical home | Apps, devices, tests | Notebooks, pipelines, local analytics |
Row storage vs column storage
SQLite stores each row together, which is ideal for "fetch this user" or "update this order". DuckDB stores each column together and processes batches of values at once, which is ideal for "sum revenue by month over 50 million rows": it reads only the columns the query uses and compresses them well.
Working with files
DuckDB can query files where they sit:
SELECT customer_id, sum(total)
FROM 'orders/*.parquet'
GROUP BY customer_id;
SQLite needs data imported into its own file first. That difference alone makes DuckDB a common tool in data pipelines for transforming and checking Parquet and CSV data.
Using them together
A common pattern is SQLite (or Postgres) for the application and DuckDB for analysis. DuckDB can attach a SQLite file or a Postgres database and query it, so you get analytics speed without moving the source of truth.
Which should you choose?
Store application state in SQLite. Analyze data with DuckDB. If you are choosing for a data engineering task - transforming files, building a report, testing a pipeline locally - DuckDB is almost always the better fit.
Practice it
Query Parquet files with SQL in How to Query Parquet Files With DuckDB and track down a join that multiplies rows in SQL Join Returning Duplicate Rows. See also the DuckDB guide.
Frequently asked questions
What is the main difference between DuckDB and SQLite?
SQLite is a row-oriented database optimized for transactions: many small reads and writes of individual rows. DuckDB is a columnar, vectorized database optimized for analytics: scans, joins and aggregations over many rows. Both run inside your process with no server.
Is DuckDB faster than SQLite?
For analytical queries (GROUP BY, joins and aggregates over large tables) DuckDB is usually much faster. For many small inserts, updates and point lookups, SQLite is often faster and simpler.
Can DuckDB read SQLite files?
Yes. DuckDB's sqlite extension can attach a SQLite database file and query its tables directly, which is a common way to run analytics on data an application stores in SQLite.
Should I use DuckDB in a web application?
Usually not as the application's main database. Use SQLite or Postgres for the app's transactional data, and DuckDB for reporting, data processing and analytics over files or exports.
Do real production work, free. Live cloud workspaces in your browser - every project you complete lands on your portfolio.
Get started - free