DuckDB vs SQLite: pick by workload, not speed

DuckDB and SQLite look like the same thing: one file, no server, a library you import. They are, and that is where the resemblance ends. SQLite is built for the writes your application makes. DuckDB is built for the questions you ask about them.

So this comparison is about which shape of work you have, not about whose benchmark to believe.

The short answer

Use SQLite when a program stores and changes its own state — rows in and out one at a time, lookups by key, many small transactions, several processes sharing one file. That is what phones, browsers, desktop apps and most small web apps do all day.

Use DuckDB when you read a lot of rows to answer one question — group by, join, window functions, scans over millions of rows, or SQL straight over Parquet and CSV. That is analytics, and DuckDB is built for it from the storage layout up.

They are not competing for the same job. And here is the fact most comparisons underplay: DuckDB can query a SQLite file directly, in two lines, with no export. For a lot of teams the answer is "both", with no migration in between.

What each one is

SQLite is an in-process, single-file, row-oriented SQL database. It has been the default embedded database for over two decades, it is in the public domain, and its own project describes it as the most widely deployed database engine in the world. It ships inside Android and iOS, inside web browsers, and inside a very large amount of desktop and embedded software. It stores rows in a B-tree, so reading one row by primary key touches very few pages. It is ACID, it is small enough to ship on a phone, and the project's stated commitment is to keep the current file format readable through 2050.

DuckDB is an in-process, single-file, columnar SQL database for analytics. The usual shorthand is "SQLite for analytics", and it is fair as far as the packaging goes. It stores each column separately and compressed, and it runs queries in vectorised batches across all your cores. It reads Parquet, CSV and JSON directly, including files in object storage, with no load step. It is MIT-licensed and past 1.0 with a storage format stability commitment. What it is genuinely good and bad at is its own post.

At a glance

	SQLite	DuckDB
Built for	OLTP — an application's own state	OLAP — questions about a lot of rows
Storage	Row-oriented B-tree	Columnar and compressed
Execution	One row at a time, one thread per query	Vectorised batches, all cores
Typing	Dynamic affinity by default, STRICT tables opt in	Static and enforced
Writers	One at a time, but several processes may share the file	One process holds the file read-write; others cannot open it
Readers	Many, concurrent with a writer in WAL mode	Many, when no process is writing
Files	Its own format; CSV import via the shell	Reads Parquet, CSV, JSON and object storage natively
Memory	Frugal; a small page cache is enough	Wants RAM, and spills to disk when a query exceeds it
SQL dialect	Its own, minimal	Close to Postgres, with analytical conveniences
Licence	Public domain	MIT

Row storage and column storage decide most of it

Everything in that table follows from one choice, made before either engine runs a query: how the bytes sit on disk.

SQLite keeps a whole row together. Fetching order 91234 is a B-tree descent and a small number of page reads, and nothing else is touched. That is why it is the right engine for an application: the unit of work is a row, and the storage layout matches.

DuckDB keeps a whole column together. Averaging amount over forty million orders reads the amount column and ignores the other thirty-nine, compressed, in batches, on every core you have. The unit of work is a column, and analytics is a column-shaped job.

Add the execution model on top. SQLite runs a query on a single thread, one row at a time, with no vectorised operators and no parallelism within a query. DuckDB was built the other way round.

The corollary people miss: DuckDB does have indexes, but it is not tuned for thousands of small point reads and writes a second. If that is your workload, the row store is not a legacy choice you are settling for. It is the correct one.

Concurrency: the difference that surprises people

This is where the two diverge most, and it is the difference most likely to break a design.

SQLite lets several processes share one file. They take locks, and the locking serialises writes — one writer at a time, but the writers can live in different processes. In WAL mode, readers do not block the writer and the writer does not block readers. That is exactly why a web app with four worker processes on one SQLite file works.

DuckDB does not. One process may hold a database file read-write, and while it does, no other process can open that file at all. Many processes may read the same file at once as long as none of them is writing. Inside a single process, DuckDB is happy with many threads and connections reading and writing.

So the honest summary is: SQLite is the more permissive engine across processes, DuckDB the more parallel one inside a single process. The design that sidesteps DuckDB's rule entirely is one writer producing immutable Parquet and any number of read-only readers over it. That model, and what it costs, is the subject of is DuckDB safe for production.

Typing: flexible versus enforced

SQLite uses dynamic typing with column affinity. A column declared INTEGER has integer affinity, so SQLite converts a value that looks like an integer — but it will also store a string there if the string cannot be converted. STRICT tables opt in to real enforcement, and they are worth using in new schemas. Most existing SQLite databases do not use them.

SQLite also has no dedicated date, time or boolean type. Dates are stored as text, numbers or Unix timestamps, by convention, and the convention is per application.

DuckDB is statically typed and refuses a value that does not fit the declared type. It has real DATE, TIMESTAMP and BOOLEAN types.

This matters the moment you point DuckDB at a SQLite file, because DuckDB has to settle on one type per column. If the application has been relaxed about what it writes, that is where you find out — unwelcome on the day, useful in general.

Measure it on your own data

The speed claims above are directional. Your data, your schema and your queries decide the actual numbers, so here is a script that builds the same table in both engines and runs the same two queries: one analytical, one a point lookup. Both drivers are easy to get — sqlite3 is in the Python standard library, and pip install duckdb gets the other.

# duckdb_vs_sqlite.py — same rows, same two queries, two engines.
import csv, os, sqlite3, time
import duckdb

N = 5_000_000
CSV_PATH = "events.csv"

def timed(label, fn):
    t0 = time.perf_counter()
    fn()
    print(f"{label:<22} {time.perf_counter() - t0:7.3f}s")

# 1. One CSV that both engines load, so the data is identical.
if not os.path.exists(CSV_PATH):
    duckdb.sql(f"""
        COPY (
          SELECT
            i                          AS id,
            (i * 2654435761) % 50000   AS user_id,
            CASE i % 3 WHEN 0 THEN 'click'
                       WHEN 1 THEN 'view'
                       ELSE 'buy' END  AS kind,
            (i % 997) / 10.0           AS amount
          FROM range({N}) AS t(i)
        ) TO '{CSV_PATH}' (HEADER)
    """)

# 2. Load SQLite, with the index an OLTP schema would have.
if not os.path.exists("events.sqlite"):
    sq = sqlite3.connect("events.sqlite")
    sq.execute("CREATE TABLE events "
               "(id INTEGER PRIMARY KEY, user_id INTEGER, kind TEXT, amount REAL)")
    with open(CSV_PATH, newline="") as fh:
        reader = csv.reader(fh)
        next(reader)  # header
        rows = ((int(a), int(b), c, float(d)) for a, b, c, d in reader)
        timed("sqlite load", lambda: sq.executemany(
            "INSERT INTO events VALUES (?, ?, ?, ?)", rows))
    sq.execute("CREATE INDEX events_user ON events (user_id)")
    sq.commit()
    sq.close()

# 3. Load DuckDB.
if not os.path.exists("events.duckdb"):
    dk = duckdb.connect("events.duckdb")
    timed("duckdb load", lambda: dk.execute(
        f"CREATE TABLE events AS SELECT * FROM read_csv('{CSV_PATH}')"))
    dk.close()

AGGREGATE = ("SELECT kind, count(*) AS n, avg(amount) AS avg_amount "
             "FROM events GROUP BY kind ORDER BY kind")
LOOKUP = "SELECT id, amount FROM events WHERE user_id = 12345"

sq = sqlite3.connect("events.sqlite")
dk = duckdb.connect("events.duckdb")

timed("sqlite aggregate", lambda: sq.execute(AGGREGATE).fetchall())
timed("duckdb aggregate", lambda: dk.execute(AGGREGATE).fetchall())
timed("sqlite lookup", lambda: sq.execute(LOOKUP).fetchall())
timed("duckdb lookup", lambda: dk.execute(LOOKUP).fetchall())

Run it twice and read the second run, so both files are warm in the page cache. Then change N, add columns, and swap in a query you actually run. The shape of the result is the point: the gap on the aggregate grows with the row count and the table width, and the point lookup goes the other way. If your own queries do not reproduce that shape, trust your queries over this post.

The pattern most comparisons miss: DuckDB over your SQLite file

You do not have to choose. DuckDB ships an extension that attaches a SQLite database and exposes its tables as ordinary DuckDB tables:

INSTALL sqlite;
LOAD sqlite;

-- READ_ONLY matters when the app is live: DuckDB will not write to the file.
ATTACH 'app.db' AS app (TYPE sqlite, READ_ONLY);

SELECT
  date_trunc('month', CAST(created_at AS TIMESTAMP)) AS month,
  count(*)                                           AS orders,
  sum(amount)                                        AS revenue
FROM app.orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

The cast on created_at is not decoration. SQLite has no timestamp type, so the column arrives as text and DuckDB will not guess.

The same thing from Python:

import duckdb

con = duckdb.connect()                       # in-memory DuckDB
con.execute("INSTALL sqlite; LOAD sqlite;")
con.execute("ATTACH 'app.db' AS app (TYPE sqlite, READ_ONLY)")

con.sql("SELECT kind, count(*) FROM app.events GROUP BY kind").show()

# Snapshot it for the analytics side, then query the snapshot from here on.
con.execute("COPY (SELECT * FROM app.events) TO 'events.parquet' (FORMAT parquet)")

That last line is the step I would push most teams towards. Attaching the live SQLite file is perfect for an ad-hoc question. For anything on a schedule, export to Parquet and run the analytics against the Parquet, so a heavy dashboard query never competes with the application for the same file. From there the rest of the local-first toolkit applies unchanged — partitioning, memory limits, the read-only fan-out — and that is running DuckDB on your own infrastructure.

The useful way to hold it: SQLite is the system of record, DuckDB is the engine that reads it. Neither is being replaced.

When SQLite is the right call

• The database holds an application's own state — orders, sessions, settings, documents.
• Work arrives as small transactions: insert a row, update a row, read a row by key.
• Several processes need the same file. This one is decisive on its own.
• It runs on a phone, in a browser, on a router, in a desktop app, or on a device with little RAM.
• You want a format you can still open in twenty years, with no vendor and no licence.
• Your analytics are modest: a few million narrow rows, queried occasionally.

When DuckDB is the right call

• The query reads a lot of rows to produce a few: group by, join, window function, rollup.
• The data already sits in Parquet or CSV, locally or in object storage, and you would rather not load it anywhere.
• One writer, many readers, and staleness measured in minutes is fine.
• The work happens inside one program — a batch job, a notebook, a CI run, a service that owns its own queries.
• Reports take tens of seconds in SQLite and someone has started asking for a warehouse.

That last one is worth pausing on. A slow SQLite report is not evidence that you need a warehouse. It is usually evidence that you are running a column-shaped query on a row store. Try the right engine on the same machine before you price a cluster. If the answer really is a server, the next comparisons are DuckDB vs ClickHouse for concurrency and freshness, and DuckDB vs Postgres when what you have is an application database already buckling under analytical load.

If you are choosing for a new project

Start with SQLite for the application. Add DuckDB the first time a question takes too long, and point it at the SQLite file rather than moving anything. If the analytics keep growing, export to Parquet on a schedule and let DuckDB read that instead. Each step is small, reversible, and leaves the data in a format something else can read — which is the whole argument for building this way, and the subject of my book, Local-First Analytics.

If you are weighing DuckDB against a dataframe library rather than against a database, DuckDB vs Polars is the closer comparison.