Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 38 ยท Getting Started

What Is DuckDB?

Learn what DuckDB is, why an in-process analytical SQL database is useful, how it differs from SQLite and PostgreSQL, and when it is the right tool for your data.

DuckDB is a database that lives inside your program. There is no server to install, no port to open and no user account to create. You load a library (or run one small executable), point it at a file, and start asking questions in SQL. It is built for analytics: queries that read a lot of rows, group them, join them and summarize them, such as "what was revenue per country per month?".

If you have ever loaded a 2 GB CSV file into a spreadsheet and watched it freeze, or written a long pandas script just to compute a few grouped averages, DuckDB is aimed at you. It reads CSV, Parquet and JSON files directly, runs your SQL on all your CPU cores, and gives the answer in seconds.

About the examples in this course. The real DuckDB engine cannot run inside a web page here. Every runnable editor in this tutorial uses SQLite (in your browser) as a stand-in, with portable SQL that DuckDB also understands. Code that only works in DuckDB is shown in plain read-only blocks and labelled as such. Whenever a runnable example is a stand-in, its title says so.

The one-sentence description

DuckDB is an in-process, columnar, vectorized SQL database for analytics (OLAP). Each word matters:

  • In-process means the database engine runs inside your application's own process, like a library. There is no network hop between your code and the data.
  • Columnar means values are stored column by column instead of row by row, so a query that needs only 3 of 40 columns reads only those 3.
  • Vectorized means the engine processes thousands of values at a time in tight loops that CPUs love, instead of one row at a time.
  • OLAP means online analytical processing: big read-heavy queries, as opposed to OLTP (many tiny reads and writes, like a shop checkout).

You will meet all four ideas again in Columnar storage and vectorized execution.

A short history

DuckDB started in 2018 at CWI, the national research institute for mathematics and computer science in Amsterdam, created by Mark Raasveldt and Hannes Muhleisen. The goal was a "SQLite for analytics": something as easy to embed as SQLite, but fast on aggregations over large tables. It is open source under the permissive MIT license and is developed by the DuckDB Foundation and DuckDB Labs. Version 1.0 arrived in 2024, which came with a promise to keep the storage format and APIs stable.

What a first session looks like

Here is a read-only taste of the real thing. You start the command-line program and type SQL:

SQL
-- DuckDB (read-only example)
SELECT 42 AS answer;

-- query a CSV file without creating a table first
SELECT country, count(*) AS customers
FROM 'customers.csv'
GROUP BY country
ORDER BY customers DESC;

The second query is the magic trick: the file name appears where a table name normally goes. DuckDB looks at the file, detects the delimiter and column types, and runs the query. Nothing was imported first. You will learn how this works in Reading CSV files.

You can run the same shape of query right now on a table in the browser stand-in:

SQL Stand-in: customers per country ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The SQL is ordinary. The difference in DuckDB is where the data comes from and how fast it is processed.

DuckDB compared with other databases

Three kinds of database

SQLite Embedded, row-based, great for apps
PostgreSQL Server, row-based, great for many users
DuckDB Embedded, columnar, great for analytics

DuckDB takes the "no server" idea of SQLite and applies it to analysis.

1 / 3

In more detail:

SQLite PostgreSQL DuckDB
Runs as Library Server process Library
Storage layout Rows Rows Columns
Best at Application data, lookups Multi-user transactional apps Analytical scans and aggregates
Many concurrent writers One at a time Yes One process writes at a time
Reads files directly (CSV/Parquet) No Via extensions Yes, built in

DuckDB is not "a faster SQLite". They solve different problems and work well together: a program can keep its small application state in SQLite and use DuckDB to analyze a big export. DuckDB can even attach a SQLite file and query it, as you will see in Transactions, files and attached databases.

Where people use DuckDB

  • Data analysis on a laptop. Explore a 5 GB Parquet file without a cluster.
  • Python and R notebooks. Run SQL on pandas DataFrames, or hand results back as DataFrames (Python chapter).
  • Data pipelines. A single script converts messy CSV files into clean Parquet.
  • Embedded analytics. A desktop or web application ships DuckDB (even in the browser through WebAssembly) to power dashboards.
  • Data lakes. Query files in cloud storage such as S3 without loading them into a warehouse first.

What DuckDB is not

Be honest about limits so you pick the right tool. DuckDB is not designed to be a shared network database for hundreds of simultaneous clients. Only one process can open a database file for writing at a time, and it is not tuned for workloads of many tiny single-row updates. For those, use PostgreSQL or MySQL. DuckDB is also not a distributed system: one machine, many cores, and as much memory and disk as that machine has.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does "in-process" mean for DuckDB?

    In-process means no separate server. Your program loads DuckDB and calls it directly.
  2. 2. Which workload is DuckDB designed for?

    DuckDB is an OLAP engine. It is at its best on scans and aggregations.
  3. 3. What is a "columnar" layout good for?

    Columnar storage lets the engine skip columns the query does not mention, which saves a lot of reading.
  4. 4. Why do the runnable examples in this course say "Stand-in"?

    The real DuckDB engine is not available in the page. Portable SQL is run with a stand-in and DuckDB-only code is read-only.

Summary

  • DuckDB is an in-process, columnar, vectorized SQL database built for analytics.
  • It needs no server: load the library or run the single CLI executable.
  • It can query CSV, Parquet and JSON files directly, in place.
  • It complements SQLite and PostgreSQL rather than replacing them.
  • It is single-machine and not meant for many concurrent writers.

Next: Installing DuckDB and running your first query