DuckDB vs SQLite
Two Embedded Databases, Opposite Architectures β Interactive Deep Dive
Architecture at a Glance
Both are embedded, zero-dependency, single-file databases. But they were designed for opposite workloads and make opposite engineering trade-offs at every layer.
SQLite
- Workload: OLTP (transactional, row-at-a-time)
- Storage: B-tree row store (rows stored together)
- Execution: Bytecode VM (VDBE), one row at a time
- Parallelism: Single-threaded reads and writes
- Best for: Web apps, mobile, config, small datasets
DuckDB
- Workload: OLAP (analytical, scan-millions-of-rows)
- Storage: Columnar store (columns stored separately)
- Execution: Vectorized engine, 2048 rows per batch
- Parallelism: Morsel-driven, all CPU cores
- Best for: Analytics, data science, ETL, Parquet queries
Storage Layout: Row Store vs Column Store
The core difference. SQLite stores each row as a contiguous unit (all columns together). DuckDB stores each column as a contiguous unit (all values of one column together). Watch how this affects an analytical query.
Click "Run" to see how each database reads data for an aggregation query.
Execution: Bytecode VM vs Vectorized Engine
SQLite compiles SQL into bytecode that its VDBE (Virtual Database Engine) interprets one row at a time. DuckDB compiles SQL into a pipeline of operators that process vectors of 2048 rows at a time, keeping data in CPU cache and enabling SIMD instructions.
SQLite: Row-at-a-Time
Each row processed individually. Function call overhead per row. Poor CPU cache utilization.
DuckDB: Vectorized Batches
2048 rows per operator call. Tight loops over typed arrays. SIMD-friendly. Cache-resident.
Parallelism: Single-Threaded vs All Cores
SQLite is fundamentally single-threaded for both reads and writes. DuckDB distributes work across all available CPU cores using morsel-driven parallelism. On an 8-core machine, DuckDB can be 8x faster for parallelizable queries.
Head-to-Head Comparison
| Dimension | SQLite | DuckDB |
|---|---|---|
| Storage engine | B-tree row store | Columnar row-group segments |
| Execution model | Bytecode VM (VDBE), row-at-a-time | Vectorized, 2048-row batches |
| Parallelism | Single-threaded | Morsel-driven, all cores |
| Concurrency | WAL mode: multi-reader, single-writer | Single-writer, MVCC for reads |
| Index types | B-tree (primary and secondary) | ART (Adaptive Radix Tree) + zone maps |
| Scan 100M rows + aggregate | ~60 seconds | ~2 seconds (8 cores) |
| Single-row lookup by PK | ~0.01ms (B-tree seek) | ~0.1ms (ART seek, less optimized) |
| Write throughput (row inserts) | ~400K rows/sec (WAL mode) | ~200K rows/sec (single-writer) |
| File format | .sqlite / .db (B-tree pages) | .duckdb (columnar row groups) |
| External data | Limited (CSV via extension) | Parquet, CSV, JSON, Arrow, S3 |
| Maturity | 22+ years, billions of deployments | ~5 years, rapidly growing adoption |
When to Use Which
Use SQLite When...
- Building a web application backend (OLTP)
- Storing user data in a mobile or desktop app
- Configuration and state persistence
- Small datasets (under 10GB)
- You need maximum write concurrency (WAL mode)
- Reliability and stability are paramount
- Embedding in resource-constrained environments
Use DuckDB When...
- Running analytical queries (OLAP)
- Data science and exploratory analysis in Python/R
- ETL pipelines and data transformations
- Querying Parquet, CSV, or JSON files directly
- Embedded analytics within your application
- Crunching millions to billions of rows locally
- Replacing pandas for complex SQL analytics
Use Both Together
DuckDB can attach and query SQLite databases directly. A common pattern: SQLite handles your application's transactional data, DuckDB runs analytical reports over the same data without migration.
ATTACH 'app.sqlite' AS app (TYPE sqlite);
SELECT category, AVG(price) FROM app.products GROUP BY category; Concrete Benchmark: 100M Row Aggregation
Scanning 100 million rows and computing AVG, SUM, and COUNT grouped by a category column. Single machine, 8 cores, 32GB RAM, NVMe SSD.
Frequently Asked Questions
Can DuckDB replace SQLite?
Not for transactional workloads. SQLite excels at OLTP: fast point lookups, single-row inserts, high write concurrency in WAL mode, and rock-solid reliability for embedded applications (mobile apps, desktop software, configuration stores). DuckDB excels at OLAP: scanning millions of rows, aggregations, joins on large tables, and columnar analytics. They solve fundamentally different problems despite both being embedded single-file databases.
How much faster is DuckDB than SQLite for analytics?
Typically 10-100x faster for analytical queries. On a benchmark scanning and aggregating 100 million rows, DuckDB finishes in roughly 2 seconds while SQLite takes around 60 seconds. The gap widens with more cores (DuckDB parallelizes, SQLite cannot) and more columns (DuckDB reads only needed columns, SQLite reads full rows). For single-row lookups, SQLite is faster because its B-tree row store is optimized for that access pattern.
Can DuckDB and SQLite work together?
Yes. DuckDB has a built-in SQLite scanner extension that can attach a SQLite database and query its tables directly. This means you can run analytical queries over your existing SQLite data using DuckDB's columnar engine without migrating anything. The typical pattern is: use SQLite for your application's transactional data, then point DuckDB at the same file for analytical reporting.
Why does storage layout matter so much?
Because analytical queries typically touch a few columns across millions of rows. In SQLite's row store, reading the price column forces you to also read every other column in each row (name, description, metadata, etc.). In DuckDB's column store, the price column is stored contiguously on disk, so you read only what you need. For a table with 50 columns where your query uses 3, DuckDB reads 94% less data.
Should I use DuckDB for a web application backend?
Probably not as the primary database. Web applications need fast single-row reads, concurrent writes from multiple requests, and transactional guarantees for user operations β that is OLTP territory where SQLite (or PostgreSQL) excels. However, DuckDB is excellent as a secondary analytical engine: use it for reporting dashboards, data exports, or background analytics jobs that process large result sets from your primary database.