DuckDB is an in-process analytical database: think “SQLite for analytics”. There’s no server to install or run. It’s a single binary or library that queries CSV, JSON and Parquet files, or its own database file, with fast, columnar, vectorised SQL. On a server, it’s a superb tool for ad-hoc analysis of logs, exports and data dumps, without loading them into MySQL or PostgreSQL first.

When DuckDB shines

  • Analysing large CSV or JSON exports, or Parquet data lakes, with SQL.
  • Crunching web server logs, billing exports and analytics dumps.
  • Data pipelines and reports in Python or R.
  • Replacing slow spreadsheet or pandas workflows on millions of rows.

It’s not a replacement for your application database. DuckDB is optimised for analytical queries (scans, aggregations, joins over many rows), not for many concurrent small writes from a web app. See SQLite vs PostgreSQL.

Install

The CLI is a single binary:

curl https://install.duckdb.org | sh          # or download from duckdb.org
~/.duckdb/cli/latest/duckdb --version

Python:

pip install duckdb        # inside a virtual environment

See Python virtual environments.

Query files directly

duckdb
-- CSV with automatic schema detection
SELECT country, count(*) AS orders, sum(total) AS revenue
FROM 'orders-2026.csv'
GROUP BY country
ORDER BY revenue DESC
LIMIT 10;

-- many files at once with a glob
SELECT count(*) FROM 'exports/orders-*.csv';

-- JSON (newline-delimited)
SELECT event, count(*) FROM read_json_auto('events.jsonl') GROUP BY event;

-- Parquet, local or on S3-compatible storage
SELECT * FROM 's3://bucket/data/*.parquet' LIMIT 5;

DuckDB reads only the columns it needs. Parquet queries over gigabytes often return in seconds.

Analyse Nginx access logs

Convert the combined log format with a regular expression:

CREATE TABLE logs AS
SELECT
  regexp_extract(line, '^(\S+)', 1)                         AS ip,
  strptime(regexp_extract(line, '\[([^\]]+)\]', 1), '%d/%b/%Y:%H:%M:%S %z') AS ts,
  regexp_extract(line, '"(\S+) (\S+)', 2)                   AS path,
  CAST(regexp_extract(line, '" (\d{3}) ', 1) AS INTEGER)    AS status,
  regexp_extract(line, '"([^"]*)"$', 1)                     AS user_agent
FROM read_csv('/var/log/nginx/access.log', columns={'line': 'VARCHAR'}, delim='\x01', header=false, quote='');

-- top 404 paths
SELECT path, count(*) FROM logs WHERE status = 404 GROUP BY path ORDER BY 2 DESC LIMIT 20;

-- requests per hour
SELECT date_trunc('hour', ts) AS hour, count(*) FROM logs GROUP BY 1 ORDER BY 1;

-- top user agents
SELECT user_agent, count(*) FROM logs GROUP BY 1 ORDER BY 2 DESC LIMIT 10;

This is often more convenient than chains of awk | sort | uniq once questions get complex. See sort and uniq and Nginx logs.

Query your existing databases

DuckDB can attach PostgreSQL, MySQL and SQLite databases, and run analytical queries on them, or copy data out:

INSTALL postgres; LOAD postgres;
ATTACH 'host=127.0.0.1 dbname=shop user=readonly' AS pg (TYPE postgres, READ_ONLY);
SELECT date_trunc('month', created_at) m, sum(total) FROM pg.public.orders GROUP BY m ORDER BY m;

Use a read-only database user, and run heavy analyses against a replica rather than the production primary. See database replication.

Export results

COPY (SELECT * FROM logs WHERE status >= 500) TO 'errors.csv' (HEADER);
COPY logs TO 'logs.parquet' (FORMAT parquet, COMPRESSION zstd);

Parquet files are compact and fast to query later, by DuckDB and many other tools.

Python example

import duckdb
con = duckdb.connect("analytics.duckdb")       # or duckdb.connect() for in-memory
df = con.sql("""
  SELECT country, sum(total) AS revenue
  FROM 'orders-*.csv' GROUP BY country ORDER BY revenue DESC
""").df()   # pandas DataFrame
print(df.head())

Resources

  • DuckDB uses all CPU cores and as much memory as allowed. Set limits on shared servers: SET memory_limit='2GB'; SET threads=2;.
  • Larger-than-memory queries spill to disk automatically. Point temp_directory at fast storage.
  • Run big analyses on a separate box, or at quiet times, so they don’t compete with production services. See process priority.

DuckDB vs other analytics tools

Tool Choose when
DuckDB Single-machine analytics on files or exports, no server
ClickHouse Always-on analytics service, many users, continuous ingestion. See ClickHouse
PostgreSQL Mixed transactional and moderate analytics in one database
pandas Small data, heavy Python transformations

Quick reference

Task SQL
Query CSV SELECT * FROM 'file.csv'
Many files FROM 'dir/*.csv'
JSON read_json_auto('file.jsonl')
Parquet FROM 'file.parquet'
Export CSV COPY (…) TO 'out.csv' (HEADER)
Export Parquet COPY t TO 'out.parquet' (FORMAT parquet)
Limit memory SET memory_limit='2GB'

Related: time-series databases and install SQLite.

  • Databases
  • Analytics
  • Data