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.

Get started - freeLive cloud workspace in your browser · nothing to install
#duckdb#sqlite#sql#analytics#data-engineering

TL;DR

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
Get started - free