DuckDB is absurdly good at crunching gigabytes with no database server

DuckDB is an embedded SQL engine that queries CSV, Parquet, JSON, and Arrow files directly. No import step, no server, no config files. Point it at a 10GB Parquet file and write SELECT * FROM 'data.parquet' WHERE value > 100. It returns results faster than Pandas because it uses a vectorized columnar engine that scans data in 2048-row batches with SIMD. The current stable release is v1.5.1 (March 2026), MIT-licensed, with a stable on-disk format since v1.0 in mid-2024.

It installs with pip install duckdb or brew install duckdb, and runs in-process inside Python, Node, R, Rust, Go, Java, and the CLI. If you’ve waited minutes for a Pandas groupby to finish on a multi-gigabyte dataset, or spun up a Postgres instance just to run a few aggregate queries on log files, DuckDB removes that overhead.

What DuckDB Is and Why It Differs From SQLite

Developers hearing “embedded database” usually think of SQLite . That comparison is roughly right: both run in-process, both need zero config. However, the execution models are opposites, and that gap explains why DuckDB handles analytics so much better.

SQLite is an OLTP database . It stores data row by row. It is great at transactional patterns: insert a row, look up a row by primary key, update a field. DuckDB is an OLAP database. It stores data in columns and is built for scans, aggregations, and joins across millions or billions of rows.

Run SELECT region, SUM(revenue) FROM sales GROUP BY region. SQLite reads every column of every row even though it only needs two. DuckDB reads only the region and revenue columns, skips row groups that don’t match, and processes the data in vectorized batches with CPU SIMD where available.

The sweet spot is datasets from roughly 100MB to hundreds of GB. Too big for Pandas to be pleasant, too small to justify Postgres, ClickHouse, or Spark. On a laptop with 16GB of RAM, DuckDB can handle datasets much larger than memory because the engine spills intermediate results to disk on its own.

Performance in Context

How does DuckDB stack up to other tools developers reach for? Here are rough numbers from recent benchmarks on 100-million-row datasets:

Polars is usually the fastest for pure DataFrame work. However, DuckDB has a clear edge: it speaks SQL and can query files on disk, on S3, or over HTTP without loading them into a DataFrame first. If you already know SQL, the learning curve is about zero.

Compared to ClickHouse local mode and Apache DataFusion , DuckDB trades raw speed on some queries for much simpler setup and broader language support. DataFusion wins on some Parquet-heavy benchmarks (it topped the ClickBench single-node rankings), but it needs Rust and ships with fewer batteries included. ClickHouse local gives you the full ClickHouse SQL dialect without a server, but it’s a 200MB+ binary next to DuckDB’s small footprint.

Installing DuckDB and Running Your First Query

Install takes seconds in any environment:

# CLI (Linux/macOS) - the recommended universal method
curl https://install.duckdb.org | sh

# Python
pip install duckdb

# Node.js
npm install @duckdb/node-api

# Rust
cargo add duckdb --features bundled

# Go
go get github.com/duckdb/duckdb-go/v2

# macOS via Homebrew
brew install duckdb

After install, run duckdb for an in-memory session, or duckdb my.db to save data to a file.

A quick sanity check:

SELECT 42 AS answer;

Creating a table from inline data:

CREATE TABLE orders AS
SELECT * FROM (VALUES
    (1, 'Alice', 250.00),
    (2, 'Bob', 175.50),
    (3, 'Carol', 320.00)
) t(id, customer, amount);

On machines with little RAM, set a memory limit so DuckDB does not eat too much:

SET memory_limit = '4GB';
SET threads = 4;

A useful tip: set the memory limit to about 50% of total RAM. That leaves room for the OS and other apps. The buffer manager stays inside this limit, but some operations (like hash tables for big joins) can spike past it for a moment.

Querying CSV, Parquet, and JSON Files Directly

This is the feature that sold me on DuckDB. No import step, no schema, no CREATE TABLE followed by COPY. You write SQL that points at a file path, and DuckDB works out the rest.

CSV files get auto delimiter detection and type inference:

SELECT * FROM 'sales.csv';

-- For messy CSVs, take control:
SELECT * FROM read_csv('sales.csv',
    header = true,
    delim = ';',
    dateformat = '%d/%m/%Y'
);

Parquet is where DuckDB shines. Parquet stores data in columns with min/max stats per row group, so DuckDB can skip whole chunks that don’t match your WHERE clause:

SELECT customer_id, SUM(amount)
FROM 'transactions.parquet'
WHERE transaction_date >= '2026-01-01'
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 20;

JSON and JSONL (newline-delimited JSON) files work with auto schema detection:

SELECT * FROM read_json_auto('server-logs.jsonl');

Glob patterns let you query whole directories at once. DuckDB scans matching files in parallel:

SELECT * FROM 'data/2026/*.parquet';
SELECT * FROM 'logs/**/*.csv';

Writing results back to files works the same way:

COPY (
    SELECT region, SUM(revenue) AS total
    FROM 'raw_sales/*.csv'
    GROUP BY region
) TO 'summary.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

More formats are available via extensions. INSTALL spatial adds support for shapefiles and GeoJSON. INSTALL excel handles .xlsx files. The built-in GEOMETRY type (new in v1.5) folds spatial data into the core engine.

Python, Node, and CLI Workflows

In practice, most people use DuckDB from a host language, not the standalone CLI. The Python client is the most mature.

Python

import duckdb

# Query a file and get a Pandas DataFrame
df = duckdb.sql("SELECT * FROM 'events.parquet' WHERE status = 'error'").df()

# Or get a Polars DataFrame
pl_df = duckdb.sql("SELECT * FROM 'events.parquet'").pl()

# Or a PyArrow table for zero-copy interop
arrow_table = duckdb.sql("SELECT * FROM 'events.parquet'").arrow()

The zero-copy Arrow interop is a bigger deal than it sounds. When you call .df() or .pl(), DuckDB shares Arrow memory buffers with Pandas and Polars directly. No serialization round-trip. A 500MB query result lands in a Pandas DataFrame in milliseconds.

In Jupyter notebooks, DuckDB works as a drop-in for Pandas groupby on datasets too big for memory. Write SQL in a cell, get back a DataFrame, then plot and analyze as usual. When you wrap DuckDB queries in your own Python transform functions, property-based testing finds edge cases automatically by checking those transforms against round-trip and invariant rules. If you prefer natural language over SQL, see how Claude Code handles large CSV datasets through prompts and produces complete analytical reports.

Node.js

The @duckdb/node-api package runs async queries with full prepared statement support:

import { DuckDBInstance } from '@duckdb/node-api';

const instance = await DuckDBInstance.create();
const conn = await instance.connect();
const result = await conn.run(
    "SELECT * FROM 'access-logs.csv' WHERE status >= 500"
);

CLI Pipelines

DuckDB drops into Unix shell pipelines. Feed CSV data through stdin:

cat server.log | duckdb -c "SELECT status, COUNT(*)
    FROM read_csv('/dev/stdin', columns={'ip': 'VARCHAR', 'status': 'INT', 'path': 'VARCHAR'})
    GROUP BY status"

Load SQL scripts with the .read dot command. Pass values in through environment variables for repeatable analysis scripts. Because the CLI takes a one-line -c query, it slots neatly into the shell loops that command-line AI assistants run, letting an agent draft the SQL and read back the result in one step.

Joining Remote Files From S3, HTTP, and Hugging Face

DuckDB’s file-querying reach extends to remote storage. Install the httpfs extension (bundled with most builds since v1.0):

INSTALL httpfs;
LOAD httpfs;

Now you can query Parquet files on S3 as if they were local:

SELECT *
FROM 's3://my-analytics-bucket/events/year=2026/month=03/*.parquet'
WHERE event_type = 'purchase';

DuckDB reads credentials from ~/.aws/credentials or environment variables. For partitioned datasets, it prunes partitions: if your query filters on year=2026, DuckDB skips the directories for other years.

A handy pattern is joining local data with remote data in one query:

SELECT l.customer_id, l.name, r.total_purchases
FROM 'local_customers.csv' l
JOIN 's3://bucket/purchases.parquet' r
    ON l.customer_id = r.customer_id;

Hugging Face datasets are reachable via the hf:// URI scheme. You can explore ML datasets without downloading them first:

SELECT * FROM 'hf://datasets/openai/gsm8k/main/test.parquet' LIMIT 10;

One thing to watch out for: DuckDB pushes down filters to cut bytes read from remote storage. Yet every query still racks up egress costs on cloud storage. For repeated analysis on the same remote dataset, download it locally first.

Extensions Worth Knowing

DuckDB’s extension system goes well beyond file reading. Some of the most useful:

The postgres extension is the one I reach for most. It lets DuckDB act as a query accelerator for an existing Postgres database. DuckDB reads directly from Postgres tables, runs the analytical query in its own columnar engine, and returns results without an export or a replica. If you have an OLTP Postgres and need to run heavy aggregate reports, this keeps the load off your production database.

INSTALL postgres;
LOAD postgres;
ATTACH 'postgresql://user:pass@host:5432/mydb' AS pg (TYPE POSTGRES);
SELECT category, AVG(price), COUNT(*)
FROM pg.products
GROUP BY category;

Performance Tuning and Troubleshooting

A few settings worth knowing for big datasets:

DuckDB auto-detects CPU cores. On shared machines, cut threads so you don’t starve other processes:

SET threads = 4;

For long queries, turn on the built-in progress bar:

PRAGMA enable_progress_bar;

Use EXPLAIN ANALYZE to see where time is spent in a query:

EXPLAIN ANALYZE SELECT region, SUM(amount) FROM 'big_sales.parquet' GROUP BY region;

Common Pitfalls

Hash joins on two large tables can blow past available memory. The fix sounds odd: set memory_limit to roughly 50% of your RAM (e.g., SET memory_limit = '8GB' on a 16GB machine). That leaves headroom for ops that skip the buffer manager. You can also try SET preserve_insertion_order = false so the engine picks the order that uses the least memory.

Another common snag is CSV schema inference. DuckDB samples the first rows of a CSV to guess types. If your CSV has nulls in the first thousand rows of a numeric column, DuckDB may pick VARCHAR. Fix this by setting types directly:

SELECT * FROM read_csv('messy.csv', types = {'price': 'DOUBLE', 'quantity': 'INTEGER'});

If INSTALL httpfs (or any extension) fails, check that your DuckDB version matches the extension repo. Running FORCE INSTALL httpfs pulls the extension again for your current version.

When to Consider MotherDuck

MotherDuck is the hosted cloud version of DuckDB, built by several DuckDB core contributors. It adds collaboration, shared databases, and managed storage on top of the same engine.

Its main selling point is hybrid execution. Your local DuckDB connects to MotherDuck and can query local and cloud tables in the same SQL statement. You prototype on your laptop, then push queries and datasets to shared cloud storage when teammates need access.

Pricing changed in early 2026. The old $25/month Lite plan was dropped, replaced by a free tier with limited compute and a Business plan at $250/month. For solo developers, the free tier often covers occasional cloud queries. The upgrade pays off when a team needs shared access to datasets, or when your data lives in cloud storage and repeated egress costs add up.

For most solo analytics work, local DuckDB is plenty. MotherDuck steps in when shared work and persistent cloud storage matter.