DuckDB vs SQLite: Choose the Right Embedded Database

DuckDB vs SQLite is not a contest for one embedded database crown. SQLite is built for application data and transactional work. DuckDB is built for analytical queries over many rows. Both run inside your process and can store a database in one file, but their storage and execution engines optimise for opposite workloads.

Choose SQLite for a mobile app, desktop app, device, browser, local service, or any workload dominated by small inserts, updates, deletes, and indexed lookups. Choose DuckDB for data analysis, ETL, notebooks, local reporting, and scans over CSV, JSON, Parquet, or large tables.

If your application stores operational data in SQLite and later needs serious reporting, use both. DuckDB can query the SQLite file directly without forcing the application to migrate.

DuckDB vs SQLite is OLAP vs OLTP

SQLite is an online transaction processing database. A typical query finds or changes a small number of rows:

SELECT title, completed
FROM tasks
WHERE id = 8472;

UPDATE tasks
SET completed = 1
WHERE id = 8472;

An index lets SQLite reach the row without scanning the table. Its B-tree storage, mature transaction engine, compact file format, and small library make it a good place to keep the state of an application.

DuckDB is an online analytical processing database. A typical query reads a few columns from many rows and reduces them:

SELECT
  date_trunc('month', ordered_at) AS month,
  region,
  sum(total) AS revenue,
  count(*) AS orders
FROM orders
WHERE ordered_at >= DATE '2026-01-01'
GROUP BY month, region
ORDER BY month, revenue DESC;

DuckDB processes data in vectors and keeps related column values together. An aggregate that only needs ordered_at, region, and total does not need to pull every other column through the CPU. This is the workload its engine was designed to run.

SQLite can execute that analytical query. DuckDB can execute the point lookup. The difference appears as data grows and the less natural workload becomes the common one.

Row storage and columnar execution

SQLite stores table records in B-trees. Values from one row are encoded together, which suits fetching a complete record by primary key and updating individual records. Secondary B-tree indexes provide ordered scans and selective lookups.

DuckDB uses a columnar-vectorised execution engine. It processes batches of values from a column at once, reducing per-value overhead during filters, joins, and aggregates. Its native storage also maintains automatic min-max indexes, usually called zonemaps, that can skip blocks whose value ranges cannot match a filter.

This creates a useful rule:

• If a query asks for one row with many columns, SQLite is in its preferred shape.
• If a query asks for a few columns from many rows, DuckDB is in its preferred shape.

Do not turn that into a universal benchmark result. Data order, types, compression, cache state, query shape, indexes, and storage all affect performance. Benchmark the query that matters, not SELECT 1 or a synthetic table that resembles neither system.

Reading CSV and Parquet

External data is where DuckDB separates itself most clearly.

DuckDB can query a Parquet file as if it were a table:

SELECT
  country,
  count(*) AS signups
FROM 'events/2026/*.parquet'
WHERE event_name = 'account_created'
GROUP BY country
ORDER BY signups DESC;

Its Parquet reader pushes column selection and filters into the scan. It can skip unused columns and, when file statistics allow it, skip row groups that cannot match the predicate. DuckDB can also read multiple files through globs and write query results back to Parquet.

SQLite expects data to be inside its database file. CSV data can be imported, and extensions can add other formats, but external analytical files are not its central abstraction.

Choose DuckDB when the data already lives in Parquet on local disk, HTTPS, or object storage. Copying the data into SQLite before every analysis adds work without improving the query.

Indexes and point queries

SQLite's indexing model is one reason it works well for applications. B-tree indexes support equality, range, ordering, uniqueness, and multi-column access patterns. EXPLAIN QUERY PLAN makes it possible to see whether a query scans a table or uses an index.

DuckDB has two built-in index types. Zonemaps are created automatically for general-purpose columns and help scans skip ranges. Adaptive Radix Tree indexes support primary and unique constraints and can accelerate point or highly selective queries. DuckDB's documentation describes ART indexes as most useful below roughly 0.1% selectivity.

An ART index does not turn DuckDB into an OLTP server. Index maintenance slows loads and updates, and the engine remains designed around analytical batches. If an application performs thousands of tiny indexed writes and reads throughout the day, SQLite has the more appropriate design.

Transactions and write patterns

Both databases provide ACID transactions. They differ in the work those transactions expect.

SQLite is comfortable with frequent small transactions. A desktop app can insert a note, update a setting, or delete a cached response as the user works. Batching writes in a transaction improves throughput, but individual transactions remain a normal pattern.

DuckDB expects changes to arrive in larger batches. Appending a data frame, importing a Parquet dataset, or replacing a partition suits its analytical engine. Row-by-row insert loops spend time crossing the client boundary and maintaining storage structures when a bulk insert or COPY would do less work.

For either database, durability settings matter. SQLite's rollback journal and WAL modes make different trade-offs, while DuckDB checkpoints its native database state. A benchmark that excludes commits or uses unsafe settings is not a durability comparison.

Concurrency is limited in different ways

SQLite permits many simultaneous readers but only one writer at an instant. In WAL mode, readers and a writer can proceed at the same time because new pages are appended to the write-ahead log. Two writers still take turns.

That model works for many applications because write transactions finish in milliseconds. It becomes a poor fit when several processes constantly write to the same database or when the file sits on a network filesystem. SQLite's own when-to-use guidance recommends a client-server database for many concurrent writers or direct access across a network.

DuckDB's native read-write mode centres on one process. Inside that process, multiple threads can read and write concurrently using MVCC and optimistic concurrency control. Appends do not conflict, while two threads updating the same row can produce a transaction conflict. Separate processes can open the native database read-only.

DuckDB is developing remote and lakehouse options for multi-process access, but they do not change the default embedded design. If unrelated application servers need to share a write-heavy database, PostgreSQL or another client-server system is a better starting point than either SQLite or DuckDB.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

SQL dialect and types

SQLite has dynamic typing. Columns have type affinity, but a value carries its own storage class. A non-strict table can store text in a column declared INTEGER. Strict tables are available when an application wants stronger enforcement.

DuckDB uses a strong type system with types suited to analytics, including lists, structs, maps, unions, decimals, intervals, timestamps, and large numeric types. Its SQL dialect includes analytical conveniences such as QUALIFY, PIVOT, UNPIVOT, ASOF JOIN, and rich list operations.

SQLite has the smaller SQL surface, but its stability and availability are hard to match. It ships in operating systems, browsers, phones, language runtimes, and countless applications. DuckDB has broader analytical SQL and direct integration with Python, R, Arrow, and data files.

If a schema receives inconsistent values, the difference becomes visible when the systems meet. DuckDB must map every SQLite column to a concrete DuckDB type. A SQLite column declared as INTEGER but containing the text unknown can fail when DuckDB scans it unless the values are cleaned or imported as strings.

Extensions and portability

SQLite's database file format has a long-term compatibility commitment. A database file is easy to copy, archive, attach to a bug report, or ship with an application. The ecosystem includes extensions for full-text search, JSON, geospatial work, vectors, and encryption, though availability depends on how SQLite was built and embedded.

DuckDB also supports a single-file native database and an extension system. Many important integrations, including Parquet, JSON, HTTP/S3 access, and additional database scanners, are delivered through extensions. Analytical projects often treat open file formats such as Parquet as the durable interchange layer rather than making the DuckDB file the only copy.

For application state that must remain readable for years across many runtimes, SQLite has the stronger portability story. For analysis that moves between Python, R, Arrow, notebooks, object storage, and local SQL, DuckDB has the stronger interoperability story.

Use DuckDB and SQLite together

The most useful answer to DuckDB vs SQLite is often "both."

DuckDB's official SQLite extension can attach a SQLite file and query its tables directly:

ATTACH 'shop.db' AS shop (TYPE sqlite);

SELECT
  p.category,
  date_trunc('month', o.ordered_at) AS month,
  sum(oi.quantity * oi.unit_price) AS revenue
FROM shop.orders AS o
JOIN shop.order_items AS oi ON oi.order_id = o.id
JOIN shop.products AS p ON p.id = oi.product_id
GROUP BY p.category, month
ORDER BY month, revenue DESC;

The application can continue using SQLite for transactions. An analyst, export job, or reporting screen can use DuckDB for the wide scan and aggregation. There is no duplicate source of truth, and no need to force SQLite to become an analytical engine.

DuckDB can also copy results to Parquet:

COPY (
  SELECT *
  FROM shop.orders
  WHERE ordered_at >= DATE '2026-01-01'
) TO 'orders-2026.parquet' (FORMAT parquet, COMPRESSION zstd);

This pattern works well for desktop software, local-first applications, and services that keep one SQLite database per customer or tenant. Use a short-lived read connection or a consistent snapshot so the analytical job does not interfere with application writes.

Choose SQLite when

• The database stores application state.
• Workloads contain frequent small inserts, updates, deletes, and indexed reads.
• A mobile, desktop, browser, device, or local service needs a dependable embedded database.
• Long-term file-format compatibility matters.
• The database has many readers but low write concurrency.
• You need mature B-tree indexes, triggers, and broad runtime availability.

Our SQLite guide covers how far that architecture can go before a server database is necessary. The SQLite desktop client is useful when you need to inspect the file rather than query it through application code.

Choose DuckDB when

• Queries scan, join, sort, or aggregate a large share of the data.
• Data lives in Parquet, CSV, JSON, Arrow, Pandas, or object storage.
• The workload is analysis, ETL, a notebook, local reporting, or an embedded analytics feature.
• Bulk loads matter more than small concurrent transactions.
• Rich analytical SQL and nested data types reduce application code.
• You want to query a SQLite or PostgreSQL source without moving every row into a warehouse first.

DuckDB also pairs naturally with a server OLAP database when local work grows into a shared service. Our DuckDB vs ClickHouse comparison covers that next decision.

The verdict

SQLite should be the default embedded database for application state. Its transaction model, indexes, file stability, and enormous deployment footprint fit software that creates and changes individual records.

DuckDB should be the default embedded database for analytics. Its columnar execution, bulk operations, analytical SQL, and direct Parquet access fit software that asks questions across a dataset.

Choose from the query shape, not the fact that both databases fit in one file. When an application needs both shapes, keep the operational data in SQLite and let DuckDB do the scan.