DuckDB, an analytical database that runs in your process

DuckDB, an analytical database that runs in your process

DuckDB is a SQL engine for analytics that runs inside your own process, with no server and no background daemon. It reads CSV, Parquet and JSON files straight from disk or from S3, attaches to Postgres and computes aggregates over columns. The current version is 1.5.5, released on 22 July 2026, and the licence is MIT.

How DuckDB differs from a database with a server

The comparison to SQLite is accurate about the execution model and misleading about the use case, so let us take it apart.

The execution model is the same. You install a library, open a connection, and the database lives in your process memory. There is no port, no configuration file, no user account to grant privileges to. The whole database is a single file on disk or, if you give no filename, a structure existing only in memory and vanishing with the process. That missing network layer is the main reason a query returns instantly: the data does not cross a socket, is not serialised into a protocol and does not queue behind a planner on the far side.

The use case is different. SQLite stores data in rows and is tuned for single record reads and writes, meaning transactional load. DuckDB stores data in columns and executes queries vectorised, processing chunks of a few thousand values from one column at a time. For a query like "sum sales by month and region across a hundred million rows" the gap is not a few percent but an order of magnitude, because DuckDB reads only the three columns it needs and never touches the other forty.

A simple selection rule follows. If your application writes individual records from many threads at once, you want PostgreSQL or SQLite. If you read large sets and compute aggregates over them, DuckDB does it faster and without maintaining anything.

There is a third property, often overlooked. DuckDB treats files as tables. A path to a Parquet file can sit in the FROM clause with no prior loading, import or schema declaration. That changes how you work with data more than raw speed does, because the step where something has to be uploaded somewhere first simply disappears.

Version, licence and who stands behind it

The current release is 1.5.5, dated 22 July 2026. The PyPI package requires Python 3.10 or newer. A long term support line runs in parallel: branch 1.4 under the name Andium, whose latest release 1.4.5 arrived on 17 June 2026, with the first one, 1.4.0, in September 2025. If you are building something meant to stand unchanged for a year, the LTS line is a saner pick than the current branch, which shipped five minor versions in four months during the first half of 2026.

The licence needs checking in three places, because each of them says something slightly different.

The LICENSE file in the repository holds MIT text with the line "Copyright 2018-2026 Stichting DuckDB Foundation". That is the baseline answer and it is true for the engine itself.

The PyPI metadata is thinner than it looks. The license field and the newer license_expression field are both empty, and the only licence information is the classifier License :: OSI Approved :: MIT License. An audit tool reading only the SPDX field will see nothing there and flag the dependency as unlicensed. A tool reading classifiers will see MIT. Same package, two different answers, depending on the scanner.

The contents of the published package are the interesting part. The binary wheel for Python 3.12 on arm64 carries two licence files: duckdb-1.5.5.dist-info/licenses/LICENSE at 1072 bytes with MIT text, and duckdb/experimental/spark/LICENSE with the full text of Apache License 2.0. The second belongs to the experimental Spark interface compatibility layer, which inherits its licence from PySpark. The source archive holds thirty nine of them, because the licences of libraries vendored into the source tree come along. Among them sits external/duckdb/extension/tpch/dbgen/LICENSE, an End User License Agreement version 2.2 belonging to the TPC consortium. That is not an open licence and it is not MIT. It covers the test data generator used by the tpch extension, so it is irrelevant in ordinary use, but if you redistribute your own DuckDB build together with that extension, it is a condition the "MIT" label on the package does not describe.

In the npm registry the license field reads MIT both for the new @duckdb/node-api client at version 1.5.5-r.4 and for the older duckdb package at 1.4.4. Keep an eye on which one you install, since the version numbers of those two packages have drifted apart by a whole minor release.

Rights to the project are held by Stichting DuckDB Foundation, a non-profit registered in the Netherlands. The board consists of Hannes Mühleisen, Mark Raasveldt and Peter Boncz. Funding comes from donations, with the Silver level starting at 10,000 euro and Gold at 100,000 euro. The arrangement is transparent and sturdier than a project controlled by a single company, but it still means no support contract you can enforce against a deadline.

Your first query on a file

Installation is one command, and the first query needs no database to be created.

pip install duckdb

# a query straight from the terminal, with no database file
duckdb -c "SELECT count(*) FROM 'sales.csv'"

# the built-in graphical interface in a browser
duckdb -ui

On the SQL side a file is a table and nothing else needs declaring.

-- a whole directory of files as one table
SELECT region, date_trunc('month', order_date) AS month, sum(net_amount) AS revenue
FROM 'data/sales-*.parquet'
GROUP BY ALL
ORDER BY month;

-- when type detection fails, you state the types explicitly
SELECT *
FROM read_csv('flights.csv',
    delim = '|',
    header = true,
    sample_size = 20_000,
    columns = {
        'FlightDate': 'DATE',
        'UniqueCarrier': 'VARCHAR',
        'OriginCityName': 'VARCHAR',
        'DestCityName': 'VARCHAR'
    });

-- materialising the result into a file
COPY (SELECT * FROM 'sales-*.csv' WHERE net_amount > 0)
TO 'sales.parquet' (FORMAT parquet, COMPRESSION zstd);

Two things in that example deserve comment. GROUP BY ALL groups by every non-aggregate column and saves you rewriting the list on every change to the query. The sample_size parameter driving CSV type detection looks at a limited sample by default, so a column that looks like an integer for the first twenty thousand rows and then holds text will break the query. At that point you either raise the sample, or state columns explicitly, or set auto_type_candidates to narrow the list of considered types.

The duckdb -ui interface starts a local server and opens a query editor in the browser. It carries one property worth knowing about inside a company with a restrictive network: the graphical layer is fetched by default from the remote address given by the ui_remote_url setting, while the local port changes through ui_local_port. The data itself does not leave, but the network connection exists and does get blocked.

Attaching Postgres and files in the cloud

Extensions install from SQL, once per machine, and load per session.

INSTALL postgres;
LOAD postgres;

-- the production database exposed as a schema, read only
ATTACH 'dbname=analytics user=readonly host=10.0.0.4' AS pg (TYPE postgres, READ_ONLY);

-- joining a Postgres table with a Parquet file sitting locally
SELECT c.country, sum(o.total) AS revenue
FROM pg.public.customers AS c
JOIN 'orders-2026-*.parquet' AS o ON o.customer_id = c.id
GROUP BY ALL;

-- moving a table into the local DuckDB database
CREATE TABLE customers_local AS FROM pg.public.customers;

The READ_ONLY flag on ATTACH is not decoration. Without it the connection can write to production Postgres, and an analyst working in a notebook will sooner or later type DELETE in the wrong window. If you omit the connection string and pass an empty one, the extension falls back to the standard environment variables PGHOST, PGUSER, PGPASSWORD and PGDATABASE.

Access to cloud objects goes through a secrets mechanism rather than global variables.

INSTALL httpfs;
LOAD httpfs;

-- keys stated directly
CREATE OR REPLACE SECRET s3_raw (
    TYPE s3,
    PROVIDER config,
    KEY_ID 'AKIAIOSFODNN7EXAMPLE',
    SECRET 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
    REGION 'eu-central-1'
);

-- or credentials from the provider chain, narrowed to one path
CREATE OR REPLACE SECRET s3_scoped (
    TYPE s3,
    PROVIDER credential_chain,
    REGION 'eu-central-1',
    SCOPE 's3://reports/2026/'
);

SELECT * FROM 's3://reports/2026/events-*.parquet' LIMIT 10;

Narrowing through SCOPE lets you hold several secrets at once and bind them to different prefixes, which is more convenient than switching a profile before every query. When reading from S3, DuckDB fetches only those fragments of the Parquet files that the query needs, so the transfer bill often comes out far lower than downloading the whole set.

Instead of Pandas, or where the real gain sits

The biggest difference in daily work shows up where you used to load the entire set into a data frame and wait. DuckDB in Python sees variables from the local scope as tables, so nothing has to be moved.

import duckdb
import pandas as pd

orders = pd.read_parquet("orders.parquet")

# a frame from local scope is visible as a table
report = duckdb.sql("""
    SELECT country, count(*) AS count, median(total) AS median_total
    FROM orders
    WHERE status = 'paid'
    GROUP BY ALL
    ORDER BY count DESC
""").df()

# the result can also come back as Polars or Arrow
arrow_table = duckdb.sql("SELECT * FROM orders LIMIT 1000").arrow()

# a persistent connection to a database file plus resource settings
con = duckdb.connect("analytics.duckdb", config={"threads": 4})
con.sql("SET memory_limit = '8GB'")
con.sql("SET temp_directory = '/var/tmp/duckdb'")
con.sql("SET max_temp_directory_size = '100GB'")
con.sql("SET preserve_insertion_order = false")

This arrangement works both ways and moving data between the two sides is cheap, because both understand the Arrow format. A query returns a Pandas frame through df(), a Polars frame through pl(), an Arrow table through arrow() and a list of tuples through fetchall(). Writing goes through write_parquet() or write_csv().

The gain comes from three things at once. First, DuckDB runs aggregates and joins across many cores while Pandas does it on one. Second, when reading a Parquet file it reads only the columns appearing in the query rather than the whole file. Third, it can work on sets larger than memory: when an intermediate result does not fit inside the limit set by memory_limit, it spills into the directory given by temp_directory instead of giving up.

That last point is the one that usually decides. A data frame that does not fit in memory ends in a killed process and a lost session. The same query in DuckDB reaches the end, only slower. Setting preserve_insertion_order to false releases the engine from having to keep row order and noticeably lowers memory use on large reads, provided your query ends with an ORDER BY clause anyway.

What DuckDB does not replace: the rest of Pandas. Transformations needing loops over rows, integration with machine learning libraries, drawing charts, working on a time index all stay on that side. A sensible split puts filtering, joins and aggregation in SQL, and only the result, usually small, into a frame. It looks similar inside pipelines managed by Apache Airflow, where DuckDB tends to be a step processing files between tasks rather than the destination store. When preparing text for embeddings it pairs well with Unstructured, which extracts content from documents, and with pgvector, which stores the finished vectors on the Postgres side.

MotherDuck, a separate company and a separate price list

MotherDuck is a commercial cloud service built around DuckDB and run by a separate company. It is not a paid tier of the project nor a product of the foundation. The distinction matters in practice: DuckDB itself has no paid variant and announces none, and everything below concerns the MotherDuck service alone.

The connection looks like an ordinary DuckDB connection with a different prefix.

import duckdb

# browser based authentication
con = duckdb.connect("md:my_db")

# or a token, for a batch job say
con = duckdb.connect("md:?motherduck_token=<token>")

con.sql("SHOW DATABASES").show()

# a local and a cloud database at once, inside one query
local_con = duckdb.connect("analytics.duckdb")
local_con.sql("ATTACH 'md:my_db'")

The service documentation states that DuckDB client versions from 1.4.1 through 1.5.5 are supported. That is a real constraint on how fast you can upgrade, because a new DuckDB release does not work with MotherDuck from day one.

On top of the platform fee sits usage billed by the second. Storage costs 0.04 USD per gigabyte per month on both plans. A Pulse instance runs at 0.60 USD per hour, Standard at 2.40 USD, Jumbo at 4.80 USD, Mega at 12.00 USD and Giga at 24.00 USD. Artificial intelligence functions, including prompt() and embedding(), are billed at 1.00 USD per AI unit. The Lite plan includes 10 gigabytes of storage and 10 hours of Pulse time per month at no charge, two service accounts and community support only. The Business plan adds unlimited service accounts, up to sixteen read replicas, query history and a stated availability of 99.9 percent, with a seven day trial. The Enterprise plan throws in AWS PrivateLink connectivity, IP allowlisting, self-defined roles and a HIPAA BAA.

Vendor lock-in risk here is moderate but not zero. Queries are ordinary DuckDB SQL and will move elsewhere, whereas features such as execution split between the local machine and the cloud, managed DuckLake or the pipelines named Flights exist only inside the service. Lean your product logic on those and leaving stops being a change of connection string.

DuckDB against the alternatives

The choice comes down to two questions. The first: must the data be written by several processes at once. If so, DuckDB is out as the primary store, because the database file admits one writing process at a time. Postgres remains, possibly in a managed form from a vendor like Neon, while DuckDB can still serve as a reporting layer reading a copy of the data. The second question: does the set fit on one machine. A single server with a few hundred gigabytes of memory and an NVMe disk today handles sets that would have needed a cluster ten years ago, so the point at which a distributed warehouse pays off has moved distinctly upward.

The comparison with Turso stands apart, since it solves a different problem: it is a distributed flavour of SQLite aimed at transactional reads close to the user rather than at aggregates. The two tools sit next to each other in write-ups because both grow out of the in-process database model, but they do not compete for the same job.

Common mistakes

The first is treating the database file as shared storage. A .duckdb file serves one writer at a time and is not meant to sit on a network drive shared by several machines. Concurrency comes from splitting roles: one process writes, the others read a copy or Parquet files.

The second is relying on automatic CSV type detection with production data. The sample is limited, so an identifier column made of digits alone will be read as a number and lose its leading zeros. State columns explicitly everywhere the schema is known.

The third is skipping a memory limit on a shared machine. By default DuckDB takes a sizeable share of available memory, and with two jobs running at once one of them gets killed by the system. Set memory_limit and threads explicitly in every batch job.

The fourth is forgetting temp_directory with sets larger than memory. Without a working directory a query that would have to spill intermediate data to disk fails instead of slowing down. While you are there, check max_temp_directory_size, since the default is ninety percent of free space.

The fifth is attaching Postgres without READ_ONLY. The extension can write into the attached database, and nothing warns the analyst that an UPDATE is landing on production from a notebook.

The sixth is mixing up the Node packages. duckdb and @duckdb/node-api are two different clients with diverging version numbers, and examples found online quietly assume one of them.

The seventh is assuming the MIT label describes the whole archive. The engine is MIT, but the Spark compatibility layer ships under Apache 2.0 and the tpch extension data generator under the TPC consortium licence, which is not an open licence. When redistributing your own build, check what exactly sits inside it.

FAQ

Will DuckDB replace Postgres for me?

Not as an application database. DuckDB does not support many concurrent writers, has no network layer and no user level permission system. It does replace the part of the work where you pulled data out of Postgres to compute a report over it, since it can attach the database and compute the aggregate on its own side.

Can DuckDB handle a set larger than memory?

Yes, provided you set temp_directory. The engine then spills intermediate results to disk and finishes the query more slowly instead of giving up. The size of the working directory is capped by the max_temp_directory_size setting.

How does MotherDuck differ from DuckDB?

MotherDuck is a commercial cloud service run by a separate company, not a paid variant of the project. DuckDB itself is fully open and free. MotherDuck adds cloud storage, compute instances billed by the second and features unavailable locally, and starts with the Lite plan at 0 USD capped at three active users.

Is DuckDB free for commercial use?

Yes. The engine is under the MIT licence, with no fees and no restrictions on commercial use. When redistributing your own build, do check the licences of components vendored into the source tree, because not all of them are MIT.

Which version should I pick for production?

If you upgrade regularly, take the current branch, at present 1.5.5. If the deployment is meant to stand unchanged for a long stretch, take the LTS line, meaning branch 1.4 under the name Andium. When working with MotherDuck, check the list of supported client versions as well, since the service does not accept the newest release straight away.

Can I query files without downloading them?

Yes, through the httpfs extension and a secret of type s3. DuckDB then fetches only those fragments of the Parquet files that the query needs, so the transfer is often a fraction of the size of the set.

Documentation lives on the DuckDB site, information about the foundation on the DuckDB Foundation page, and the cloud service price list on the MotherDuck site.