SQLite and DuckDB: When Each Is the Right Choice

Tested with SQLite 3.53.4 · DuckDB 1.5.5 · Python 3.14.7 · Docker Engine 29.5.2 · verified 2026-09-16

Updated: 2026-09-16

SQLite^[1] and DuckDB^[2] share something striking: both are embedded databases, a library that lives in your process with no separate server. And yet they solve different problems. SQLite is the king of small transactions and local persistence; DuckDB is the king of fast columnar analytics without infrastructure. Choosing wrong has real cost, and combining them where it makes sense multiplies the value of both.

Reviewed on 16 September 2026 against the stable releases of that day: SQLite 3.53.4, released on 24 July 2026, and DuckDB 1.5.5, from 22 July. It includes a reproducible benchmark run on our own machine. In it, DuckDB ran two aggregations over 5 million rows at least 40 times faster than SQLite, and more than 140 times faster on the simpler one. SQLite, in turn, answered each primary-key lookup almost 60 times faster.

The common ground: embedded

Both discard the client-server model. Your application imports the library and runs SQL on a file. No systemd, no ports, no users, no replication as a central concern. This drastically reduces operational complexity:

• SQLite: a .db file, plus the -wal and -shm files while connections have it open in WAL mode. With the database closed, a copy is a cp. While it is live, use VACUUM INTO, the backup API or sqlite3_rsync: copying the file in the middle of a transaction can produce a corrupt copy.

• DuckDB: a .duckdb file (or in memory, or directly on Parquet).

This embedded pattern is perfect for mobile apps, desktop, single-instance services, analytic notebooks, and local ETL pipelines. It is the anti-pattern for systems with dozens of concurrent services against the same database.

Structural difference: rows vs columns

The separation is not cosmetic. It is architectural:

• SQLite stores data by rows. Each row is a contiguous block. Perfect for SELECT * FROM users WHERE id = 42: reading a whole row is fast.

• DuckDB stores data by columns. Each column is a contiguous block. Perfect for SELECT AVG(amount) FROM transactions WHERE year=2025: it scans only the relevant column across the whole table.

OLTP vs OLAP, in embedded form. The difference shows on disk too: the same 5-million-row table took 124.5 MB in SQLite and 66.1 MB in DuckDB. DuckDB applies lightweight compression to the data it stores.

When to choose SQLite

Scenarios where SQLite is clearly right:

• Mobile app with local DB: iOS, Android, and desktop. SQLite ships on every Android and iOS device.

• App configuration and persistence: Firefox, Chrome, and Safari all embed it.

• Single-node web service with mixed moderate read/write.

• Prototype or MVP before migrating to PostgreSQL.

• Durable transactional local backup with full ACID.

• WASM in the browser via the official WebAssembly build^[3] or sql.js^[4].

SQLite does what it should do well: small transactions, high integrity, low latency per operation. The deployment patterns that have held up are in SQLite in production: patterns that have aged well.

When to choose DuckDB

Scenarios where DuckDB shines:

• Log analysis: millions of rows, aggregate queries.

• Query Parquet/CSV files directly without importing: SELECT * FROM 'data.parquet' works out of the box.

• Replace pandas in mid-sized pipelines: DuckDB queries pandas and Polars DataFrames without importing them, with native SQL.

• Ad-hoc notebook analysis: Jupyter + DuckDB is a powerful combination.

• Consolidate OLTP bases for reports without touching production (CDC + DuckDB).

• Vectorized processing over tabular data without distributed infrastructure.

DuckDB is, in practice, what pandas-with-native-SQL should have been. More use cases are in DuckDB: Fast Analytics Without Moving Data.

Python: both in the same session

Both work the same way from Python. This snippet runs as is with Python 3.14.7, DuckDB 1.5.5 and pandas 3.0.5, given an events.parquet file with a type column:

# SQLite: OLTP, transactional persistence
import sqlite3
con = sqlite3.connect('app.db')
con.execute("CREATE TABLE IF NOT EXISTS users "
            "(id INTEGER PRIMARY KEY, name TEXT)")
con.execute("INSERT INTO users (name) VALUES (?)", ("Ana",))
con.commit()

# DuckDB: OLAP, fast analysis
import duckdb
con = duckdb.connect('analytics.duckdb')
# Query Parquet directly, no import needed
df = con.execute("SELECT * FROM 'events.parquet' WHERE type='signup'").df()

The final .df() needs pandas: without it, DuckDB 1.5.5 fails with ModuleNotFoundError: No module named 'numpy'. DuckDB has especially polished Python integration: it queries pandas and Polars DataFrames by their variable name and returns results with .df() or .pl(). This fits data pipelines that also use Kafka for event streaming.

Using them together

Combining them is a productive pattern:

• OLTP in SQLite, OLAP in DuckDB: the app writes to SQLite; a periodic job dumps changes to DuckDB/Parquet for analysis.

• DuckDB reads SQLite directly via the sqlite extension (formerly sqlite_scanner), which DuckDB downloads and loads by itself on first use: ATTACH 'app.db' AS app (TYPE sqlite), then SELECT * FROM app.users. The sqlite_scan('app.db', 'users') function still works in 1.5.5. No manual ETL.

• Archive old SQLite data to Parquet with COPY app.events TO 'events.parquet' (FORMAT parquet): DuckDB queries historicals without bloating the production DB.

Attaching the file already speeds up analysis. In our test, the 20-group aggregation took 41 ms in DuckDB over the attached SQLite file, against 0.95 s in SQLite itself. The Parquet copy, which DuckDB wrote in 0.70 s and which took 66.4 MB, brought that query down to 8 ms. The "SQLite for hot + DuckDB for cold" pattern reduces complexity while keeping each engine in its optimal terrain.

Real limitations

SQLite is not for:

• High concurrent writes: it allows unlimited readers but only one writer at a time, WAL included. Other writers queue.

• Two or more processes writing with no wait budget. If another process holds the write lock, your INSERT waits for the connection timeout (5 s by default in Python) and then fails with database is locked. We reproduced it: with timeout=1.5 the error came after 1.55 s, while a WAL reader kept reading. On network filesystems, locking can fail and WAL does not work.

• WAL with two or more connections on old versions. The "WAL-reset" bug, present from 3.7.0 to 3.51.2, could corrupt the database in rare cases when two connections wrote or checkpointed at the same instant. It is fixed from 3.51.3 on (and in 3.44.6 and 3.50.7). Python uses the system SQLite: in the python:3.14.7-slim-trixie image that is Debian 13’s 3.46.1, whose changelog does not list that fix.

• Databases heading for a terabyte: the hard limit is 281 TB, but the SQLite docs themselves suggest considering a client/server engine once the content approaches that range.

• Built-in replication; tools like Litestream^[5] fill this gap, as covered in Litestream: Near-Real-Time Replication for SQLite.

DuckDB is not for:

• Real OLTP across processes: in read-write mode a single process opens the file, and the others can only open it read-only. Inside that process, threads write under optimistic concurrency control, and the second one to modify the same row gets a conflict error.

• Workloads dominated by point queries: each primary-key lookup cost 253 µs, against 4.2–4.3 µs in SQLite. That is still under a millisecond, but a loop of 20,000 lookups takes 5 s in DuckDB and 0.09 s in SQLite.

• Workloads with sustained concurrent writes from more than one process. For those, its documentation points to Quack, a remote protocol released in beta on 12 May 2026, or to DuckLake with its catalog in PostgreSQL.

Trying to make each one do the other’s job only hurts.

Measured benchmark: aggregation, key lookups and inserts

The figures this article used to quote had no source: 15 s against 1 s for a GROUP BY over a million rows, and 1,000 transactions per second with WAL. We replaced them with our own measurement.

The test runs in a python:3.14.7-trixie container on Linux arm64 with 18 cores and 121 GB of RAM, with no CPU limits. The machine is shared: the one-minute load average reported by uptime ranged from 3.8 to 6.4, at times slightly above the threshold of 6 we had set ourselves. We discarded an earlier run made with the load between 9 and 14.

Python uses the system SQLite library (3.46.1 on Debian 13), so the first block compiles 3.53.4 from the official amalgamation and loads it with LD_LIBRARY_PATH. The zip’s SHA3-256 hash matched the one on the download page:

mkdir -p bench/data && cd bench    # save bench.py here
docker run --rm -it -v "$PWD":/w -w /w python:3.14.7-trixie bash
# inside the container:
V=3530400
curl -sSO https://www.sqlite.org/2026/sqlite-amalgamation-$V.zip
unzip -q sqlite-amalgamation-$V.zip
gcc -O2 -fPIC -shared -o libsqlite3.so.0 \
    sqlite-amalgamation-$V/sqlite3.c -lpthread -lm
pip install duckdb==1.5.5
LD_LIBRARY_PATH=/w python bench.py

The bench.py script builds the same 5-million-row table in both engines with integer arithmetic, no random data. Every measurement runs five times and keeps the median:

import os, random, sqlite3, statistics, time
import duckdb

N, K, M, RUNS = 5_000_000, 20_000, 1_000, 5
DDL = ("CREATE TABLE events (id INTEGER PRIMARY KEY, user_id INTEGER,"
       " category TEXT, amount DOUBLE)")
ROW = ("SELECT i, (i * 2654435761) % 100000, 'c' || CAST(i % 20 AS TEXT),"
       " CAST((i * 7919) % 100000 AS DOUBLE) / 100")
AGG = ("SELECT category, count(*), round(sum(amount), 2) FROM events"
       " GROUP BY category ORDER BY category")
TOP = ("SELECT user_id, round(sum(amount), 2) AS total FROM events"
       " GROUP BY user_id ORDER BY total DESC, user_id LIMIT 10")

def med(fn):
    times = []
    for _ in range(RUNS):
        t = time.perf_counter(); fn(); times.append(time.perf_counter() - t)
    return statistics.median(times)

def fresh(path):
    for ext in ("", "-wal", "-shm", "-journal", ".wal"):
        if os.path.exists(path + ext): os.remove(path + ext)
    return path

The first test runs two aggregations, one with 20 groups and one with 100,000 groups plus a top 10, and checks that both engines return the same rows. The second runs 20,000 primary-key lookups on randomly chosen keys:

lite = sqlite3.connect(fresh("data/bench.sqlite"))
lite.execute(DDL)
lite.execute(f"INSERT INTO events WITH RECURSIVE r(i) AS (SELECT 1"
             f" UNION ALL SELECT i + 1 FROM r WHERE i < {N}) {ROW} FROM r")
lite.commit()
duck = duckdb.connect(fresh("data/bench.duckdb"))
duck.execute(DDL)
duck.execute(f"INSERT INTO events {ROW} FROM range(1, {N} + 1) t(i)")
duck.execute("CHECKPOINT")
print("sqlite", sqlite3.sqlite_version, "| duckdb", duckdb.__version__,
      "| threads", duck.sql("SELECT current_setting('threads')").fetchone())

for name, sql in (("agg", AGG), ("top", TOP)):
    assert lite.execute(sql).fetchall() == duck.execute(sql).fetchall()
    print(f"{name}  sqlite {med(lambda: lite.execute(sql).fetchall()):.3f} s"
          f"  duckdb {med(lambda: duck.execute(sql).fetchall()):.3f} s")

keys = random.Random(42).choices(range(1, N + 1), k=K)
def lookups(con):
    for k in keys:
        con.execute("SELECT * FROM events WHERE id = ?", [k]).fetchone()
for name, con in (("sqlite", lite), ("duckdb", duck)):
    print(f"pk   {name} {med(lambda: lookups(con)) / K * 1e6:.1f} µs/lookup")

The third measures 1,000 single-row INSERT statements, each in its own transaction, on a fresh file for every repetition:

def inserts(connect):
    con = connect()
    con.execute("CREATE TABLE log (id INTEGER PRIMARY KEY, msg TEXT)")
    t = time.perf_counter()
    for i in range(M):
        con.execute("INSERT INTO log VALUES (?, ?)", [i, "evento"])
    rate = M / (time.perf_counter() - t)
    con.close()
    return rate

def sqlite_mode(journal, sync):
    def connect():
        con = sqlite3.connect(fresh("data/ins.sqlite"), isolation_level=None)
        con.execute(f"PRAGMA journal_mode = {journal}")
        con.execute(f"PRAGMA synchronous = {sync}")
        return con
    return connect

SQLite runs with three combinations of journal_mode and synchronous: the default, WAL with the same durability, and WAL with synchronous = NORMAL, which its documentation calls the best balance between performance and safety in WAL mode:

modes = {
    "sqlite delete+full": sqlite_mode("DELETE", "FULL"),
    "sqlite wal+full": sqlite_mode("WAL", "FULL"),
    "sqlite wal+normal": sqlite_mode("WAL", "NORMAL"),
    "duckdb": lambda: duckdb.connect(fresh("data/ins.duckdb")),
}
for name, connect in modes.items():
    rates = [inserts(connect) for _ in range(RUNS)]
    print(f"ins  {name}: {statistics.median(rates):,.0f} tx/s")

These are the results of two full runs with 3.53.4. Each figure is the median of five repetitions, and the ranges show the difference between runs; the single-thread row and the file sizes come from a third pass over the same tables:

Aggregations are DuckDB’s ground: 5–6 and 21–22 ms against 0.95 and 0.97–0.98 s for SQLite. The 18 cores are not the whole story, because with SET threads = 1 DuckDB took 15 and 32 ms, at least 30 times less than SQLite. With Debian’s 3.46.1, SQLite took 1.07 and 1.09 s, so the version barely moves these figures.

On key lookups, SQLite answered almost 60 times sooner: 4.2–4.3 µs per query, Python overhead included, against 253 µs for DuckDB. In DuckDB 1.5.5, EXPLAIN for that query shows a SEQ_SCAN with the id=42 filter applied inside the scan, not a read of the primary-key index.

For single-row writes, syncing to disk decides the result. With default settings DuckDB committed more transactions per second than SQLite, and with WAL at the same durability SQLite pulled ahead. With synchronous = NORMAL, which syncs to disk at fewer points than FULL, SQLite reached 76,000–125,000 transactions per second, at the price of possibly losing the last ones on a power cut. A 4 KB fsync took a median 1.05 ms on this disk, so these are the rows that change most on other hardware.

Batching helps both: the same 1,000 inserts in a single transaction took 5.2 ms in SQLite and 295 ms in DuckDB. DuckDB’s documentation advises against row-by-row INSERT statements for bulk loads; a single INSERT ... SELECT loaded the 5 million rows in 1.31 s (1.12 s in SQLite).

Quick verdict

How to decide in ten seconds:

• Short transactions, high integrity, one active process? SQLite.

• Analytic queries over large volumes, few writes? DuckDB.

• Mobile, desktop, or simple service app? SQLite.

• Notebooks, local ETL, replace pandas? DuckDB.

• Both? Use them together.

Any honest doubt resolves by looking at the main query: if it is almost always a WHERE id=?, SQLite. If it is a GROUP BY over the whole table, DuckDB. When semantic search enters the picture, see also vector databases.

Conclusion

SQLite and DuckDB are complementary tools, not competitors. Both represent the "embedded matters" philosophy applied to different problems. For local OLTP, SQLite is unbeatable for simplicity and robustness. For infra-less OLAP, DuckDB aggregated 5 million rows in 5–6 ms from a Python script, with no server to run.

Knowing both and applying the right one is a real technical advantage. Combining them where it makes sense multiplies the value of both.

Sources:

1. SQLite: When to Use SQLite^[6]
2. DuckDB: Why DuckDB^[7]
3. Litestream: Streaming Replication for SQLite^[5]
4. sql.js: SQLite compiled to WebAssembly^[4]
5. SQLite: Release History^[8]
6. DuckDB: releases on GitHub^[9]
7. SQLite: Download Page^[10]
8. SQLite: Most Widely Deployed SQL Database Engine^[11]
9. SQLite: Write-Ahead Logging^[12]
10. SQLite: PRAGMA synchronous^[13]
11. SQLite: How To Corrupt An SQLite Database File^[14]
12. Debian: sqlite3 3.46.1 changelog in trixie^[15]
13. Python: sqlite3 module^[16]
14. DuckDB: Concurrency^[17]
15. DuckDB: SQLite Extension^[18]
16. DuckDB: Python API^[19]
17. DuckDB: Storage^[20]
18. DuckDB: INSERT Statements^[21]
19. DuckDB: Quack Remote Protocol^[22]

Frequently asked questions

Can I query my SQLite database from DuckDB without building an ETL?

Yes: DuckDB reads SQLite directly through its sqlite extension, which it downloads and loads by itself: ATTACH 'app.db' AS app (TYPE sqlite), then SELECT * FROM app.users, with no manual ETL (the older sqlite_scan('app.db', 'users') still works in 1.5.5). That is the basis of the "SQLite for hot + DuckDB for cold" pattern: the app writes transactions to SQLite while a periodic job dumps changes to DuckDB or Parquet for analysis. Attaching the file already speeds up analysis. In our test, an aggregation that SQLite answered in 0.95 s took 41 ms from DuckDB over the same file and 8 ms over a Parquet copy.

How much faster is DuckDB than SQLite on analytic queries?

By a wide margin on its own ground, going by our test with 5 million rows on Linux arm64. DuckDB 1.5.5 ran a 20-group GROUP BY in 5–6 ms, while SQLite 3.53.4 took 0.95 s. With 100,000 groups and a top 10 it was 21–22 ms against 0.97–0.98 s, and on a single thread DuckDB took 15 and 32 ms. The tables turn for OLTP: SQLite answered each primary-key lookup in 4.2–4.3 µs, almost 60 times sooner than DuckDB.

Is SQLite suitable when several processes write to the same database at once?

Only if writes are short and every connection has a wait budget. SQLite allows unlimited readers but one writer at a time, WAL included; the others wait for their timeout (5 s by default in Python) and, if the lock never frees, fail with database is locked. With WAL and two or more connections, run SQLite 3.51.3 or later, which fixes the "WAL-reset" bug, and avoid network filesystems, where locking can fail. For a single-node web service with mixed, moderate read/write it remains the right choice.

Sources

1. SQLite
2. DuckDB
3. official WebAssembly build
4. sql.js
5. Litestream
6. SQLite: When to Use SQLite
7. DuckDB: Why DuckDB
8. SQLite: Release History
9. DuckDB: releases on GitHub
10. SQLite: Download Page
11. SQLite: Most Widely Deployed SQL Database Engine
12. SQLite: Write-Ahead Logging
13. SQLite: PRAGMA synchronous
14. SQLite: How To Corrupt An SQLite Database File
15. Debian: sqlite3 3.46.1 changelog in trixie
16. Python: sqlite3 module
17. DuckDB: Concurrency
18. DuckDB: SQLite Extension
19. DuckDB: Python API
20. DuckDB: Storage
21. DuckDB: INSERT Statements
22. DuckDB: Quack Remote Protocol