DuckDB vs SQLite for Analytics: When to Switch

SQLite is everywhere. It's in your phone, your browser, your Electron apps, probably your thermostat. It's the most widely deployed database engine in history, and for good reason — it's a single file, zero config, battle-tested across billions of devices.

But try running a window function over 50 million rows in SQLite and you'll have time to make coffee. SQLite was designed to store and retrieve individual records fast. Analytical workloads — aggregations, scans, joins across large tables — are a fundamentally different problem, and SQLite's architecture fights you every step of the way.

DuckDB exists because the CWI Amsterdam team (same lab that built MonetDB) asked a pointed question: what if we built something with SQLite's deployment model but a columnar OLAP engine underneath? The result is an embedded database that feels like SQLite to deploy and feels like a warehouse to query.

I've been running both side by side for two years. Here's where the line actually falls.

The architecture difference that explains everything

SQLite stores data row by row. Each record sits contiguously on disk — all columns packed together. When you SELECT * FROM users WHERE id = 42, SQLite finds that one row and reads all its fields in a single disk seek. Blazing fast for transactional lookups.

Now imagine SELECT region, SUM(revenue) FROM orders GROUP BY region against 80 million rows. SQLite has to read every column of every row — even the ones you don't care about — because they're stored together. That's the tax of row-oriented storage on analytical queries: you read ten columns to use two.

DuckDB stores data column by column. When you run that same GROUP BY, it reads only the region and revenue columns, skips everything else, and processes them in compressed vectorized batches. On analytical queries touching a few columns out of many, this isn't 2x faster. It's 10-50x faster.

Where DuckDB pulls away

Aggregations and scans. The bread and butter of analytical SQL. Anything involving GROUP BY, COUNT, SUM, AVG over large tables is DuckDB territory.

-- DuckDB: ~0.6 seconds on 30M rows
SELECT
    date_trunc('month', order_date) AS month,
    product_category,
    COUNT(*)              AS orders,
    SUM(amount)           AS revenue,
    AVG(amount)           AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY 1, 2
ORDER BY 1 DESC, revenue DESC;
 
-- SQLite: same query, same data — 23 seconds.
-- And SQLite doesn't have date_trunc, so you'd write:
-- strftime('%Y-%m', order_date) instead.

Window functions. DuckDB's window function support is rich and fast. Running totals, rankings, lag/lead, percent of total — these are first-class operations.

-- Running 7-day average revenue per product line
-- DuckDB handles this naturally
SELECT
    order_date,
    product_line,
    daily_revenue,
    AVG(daily_revenue) OVER (
        PARTITION BY product_line
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS revenue_7d_avg
FROM daily_sales;

SQLite supports window functions (since 3.25), but the performance on large datasets is painful. A windowed query that runs in 2 seconds in DuckDB can take minutes in SQLite because the engine processes it row by row instead of in vectorized batches.

Querying files directly. This is the feature that changes workflows. DuckDB reads Parquet, CSV, and JSON files as if they were tables — no import step, no schema definition.

-- Query a CSV without importing it
SELECT department, AVG(salary)
FROM 'employee_export.csv'
GROUP BY department;
 
-- Query a folder of Parquet files
SELECT *
FROM 'events/2026/**/*.parquet'
WHERE event_type = 'purchase'
  AND event_date >= '2026-01-01';
 
-- Join a Parquet file against a CSV
SELECT c.name, SUM(o.amount)
FROM 'orders.parquet' o
JOIN 'customers.csv' c ON o.customer_id = c.id
GROUP BY c.name;

SQLite can't do any of this. You'd need to import the CSV first (.import in the CLI, or a CREATE TABLE + INSERT pipeline), define the schema manually, and hope the data types are right. For ad-hoc analysis on files you just received, the friction gap is enormous.

SQL dialect. DuckDB speaks a modern SQL dialect — EXCLUDE columns, QUALIFY for filtering window results, list comprehensions, STRUCT types, UNION BY NAME. If you've used BigQuery or Snowflake, DuckDB's SQL feels familiar. SQLite's dialect is more limited, and some of the gaps bite hard in analytical contexts (no full outer join until 3.39, no INTERSECT ALL, no LATERAL).

Where SQLite still wins

This isn't a "switch everything to DuckDB" post. SQLite is the right choice for a lot of real work.

Single-row lookups and point queries. SQLite with a B-tree index on an integer primary key can find a row in microseconds. It was built for this. DuckDB will be slower on SELECT * FROM users WHERE id = 42 because columnar storage has to reconstruct the row from separate column segments — an overhead that doesn't exist in row-oriented storage.

Write-heavy transactional workloads. Application databases, session stores, message queues, mobile app data — anything with lots of individual INSERTs and UPDATEs. SQLite handles concurrent reads and serialized writes efficiently. DuckDB is optimized for bulk loads and analytical reads, not high-frequency transactional writes.

Embedded applications and mobile. Your iOS app's local database should absolutely be SQLite. It's 700KB, runs on every platform, and has decades of production hardening. DuckDB is larger, less battle-tested on mobile, and optimized for a workload profile that doesn't match most app-level data access.

When you need a single-file portable database. SQLite databases are single files you can email, copy, or embed in an app bundle. DuckDB supports persistent databases too, but SQLite's single-file story is simpler and more widely supported by tooling.

Very small datasets. Under ~100K rows, the performance difference is negligible. SQLite's simpler architecture actually has less overhead per query for small scans. If your dataset fits on a spreadsheet, use whichever tool you already know.

Quick comparison

The "wrong tool" smell test

You're probably using the wrong engine if:

• You have SQLite and your queries are full of GROUP BY, window functions, or multi-table analytical joins. The queries work but they're slow and getting slower as data grows.
• You have DuckDB and you're doing lots of individual INSERT/UPDATE/DELETE operations — user sessions, application state, form submissions. You're fighting the engine.
• You're importing CSVs into SQLite just to run one aggregation query. DuckDB queries the file directly.
• You're trying to make SQLite scan a 10-million-row table with 40 columns when you only need 3 of them. That's literally the problem columnar storage was invented to solve.

Using both

They're not mutually exclusive. A pattern I've seen work well: SQLite as the application database (user records, config, session data), DuckDB as the analytical engine that reads exports or replicated data for reporting. Different tools for different access patterns on the same underlying data.

This is roughly what we do inside Fastero. When you connect Stripe, HubSpot, Postgres, or upload a file, the data lands in DuckDB as the cross-source analytical store. Joins across connectors, aggregations for dashboards, AI-driven analysis — all of that runs against DuckDB because the query profile is analytical, not transactional. If you're building something similar and want to understand the "query files directly" workflow in depth, we wrote a guide on joining CSV files with database tables using DuckDB.

Picking the right engine

The decision is usually obvious once you name the workload:

• "Show me user #4821's profile." SQLite.
• "What's the revenue trend by region for the last 18 months?" DuckDB.
• "Store form submissions from a mobile app." SQLite.
• "Scan 500 Parquet files and find anomalies." DuckDB.
• "I need a portable single-file database for an Electron app." SQLite.
• "Join three CSV exports and build a dashboard." DuckDB.

The embedded deployment model is the same. The engine underneath is completely different. Treating them as interchangeable is like choosing between a screwdriver and a drill — they're both handheld, but one spins a lot faster when you need it to.

For more on how DuckDB fits into an analytics stack, see DuckDB in the cloud as your team's analytics engine, DuckDB vs Pandas for local data analysis, or our comparison of how to connect multiple databases to one dashboard without a warehouse. And if you're evaluating purpose-built OLAP databases beyond the embedded tier, Tinybird vs ClickHouse covers the serverless vs self-hosted trade-off.

Try Fastero free — connect your data sources, query across them with DuckDB under the hood, and build dashboards your team can actually use. No credit card required.