DuckDB, and why small data got fast

For most of the last twenty years, analytics meant a server you had to ask nicely: a warehouse in somebody else's cloud, a connection string, a queue. DuckDB turned that around. The database comes to your data, runs where you already are, and answers in the time it takes to press return.

In part one we met Pondside Coffee and the three questions its tools could not answer on their own. Before we build the warehouse that can, it is worth getting to know the engine at the center of it. This part covers what DuckDB is, where it came from, what it is unusually good at, and the few things it is deliberately not for. Then we will spend five minutes with it on Pondside's data.

What DuckDB is

DuckDB is a SQL database, which makes it sound ordinary. Two choices make it not.

It runs in-process. There is no server to install, start or connect to. DuckDB is a library that lives inside whatever program uses it: the duckdb command-line tool, a Python script, a Node.js service, a Jupyter notebook. A database is either a single file on disk or nothing at all, held in memory for as long as the program runs. If you have used SQLite, this will feel familiar, and it is why DuckDB is often described as “SQLite for analytics.”

It is built for analysis. SQLite, Postgres and MySQL are designed to look up, insert and update individual rows very quickly, many times a second. DuckDB is designed to read a great many rows and summarize them: totals by week, averages by product, the top ten of anything. It stores data by column instead of by row, compresses it heavily, and processes it in batches across every core the machine has. Those choices are what part three opens up; for now it is enough to know that they make analytical queries fast in a way a row-at-a-time database cannot match.

The comparison with SQLite is useful, but it breaks down in an instructive place. Both are a single file and need no server. But SQLite is excellent at being the database for an application, the place where a phone app keeps its records. DuckDB is excellent at being the database you ask questions of. They are complements, not rivals. DuckDB can even read a SQLite file directly, through an extension.

Where it came from

DuckDB started in 2018 at CWI, the Dutch national research institute for mathematics and computer science in Amsterdam, as the work of two database researchers, Mark Raasveldt and Hannes Mühleisen. CWI has a long history here: it is where much of the research behind column-oriented, vectorized databases was done. The first public release, 0.1, appeared in 2019. Version 1.0 followed on June 3, 2024, with a promise that its file format would stay readable from then on.

DuckDB is open source under the MIT license, which lets anyone use, change and ship it, commercially or not. Its intellectual property and trademarks are held by the DuckDB Foundation, a non-profit set up in 2021, so the project does not belong to any one company. The core developers work at a company spun out of CWI to support it, and a wide community builds on top: extensions, client libraries for most languages, and services like the one this blog belongs to.

Why one machine is enough now

The usual objection to a database that runs on one machine is that one machine will not be enough. For a long time that was true, and it is why the big cloud warehouses spread every query across many servers. Two things changed.

• Machines grew faster than most data did. An ordinary server or laptop now has many cores, plenty of memory and fast solid-state storage. Pondside's entire sample history, every café payment, web order and subscription invoice for 21 months, is a few hundred thousand rows. A real business ten times its size is still comfortably small for a single modern machine.
• Engines learned to use the machine. A query in DuckDB keeps every core busy and reads only the columns it needs, compressed. When a calculation outgrows memory, DuckDB spills the excess to disk and carries on rather than failing. There is no network between machines to wait on, because there is only one machine.

For most businesses the question was never whether the data fits on one machine. It was whether the software could use the machine.

None of this means a distributed warehouse is never the right tool. If you have petabytes, or thousands of analysts querying at once, you need one. Most businesses have neither, and pay for it anyway.

Five minutes with DuckDB

The best way to understand DuckDB is to use it. These steps are for macOS and Linux (on Windows, the DuckDB site has an installer and the rest is the same), and use the Pondside sample data from part one.

1. Install it

   One command. There is nothing else to set up and no account to create.

   Terminal

   # macOS and Linux
   curl https://install.duckdb.org | sh
   
   # or, with Homebrew
   brew install duckdb

2. Load Pondside and open it

   The sample is a plain SQL script. Running it creates a database file, pondside.duckdb, holding a schema for each source, as a synced warehouse would.

   Terminal

   curl -O https://duckhouse.co/blog/pondside.sql
   duckdb pondside.duckdb < pondside.sql
   duckdb pondside.duckdb

3. Look around

   DuckDB describes itself in SQL. duckdb_tables() is one of a family of functions that list what a database holds.

   What is in here?

   SELECT
     schema_name AS source,
     count(*) AS tables,
     sum(estimated_size) AS approx_rows
   FROM duckdb_tables()
   GROUP BY ALL
   ORDER BY source;

   Result, 4 rows

   source	tables	approx_rows
   sheets	1	5
   shopify	3	15,227
   square	7	190,763
   stripe	5	10,466

4. Get the measure of a table

   SUMMARIZE profiles every column in one go: type, minimum, maximum, an estimate of distinct values, averages, quartiles and how many are missing. It is the fastest way to meet a table you have never seen.

   DuckDB

   SUMMARIZE SELECT amount, tip_amount, source_type
   FROM square.payments;

   Amounts come back in cents, because that is how Square reports money. A minimum of 325 is a single espresso at $3.25. Distinct counts and quartiles are approximations, computed with fast estimating algorithms, which is why they can differ by a hair from one run to the next.

5. Ask a first question

   How much did the café take each month this year? Note the query starts with FROM: DuckDB lets you name the table first and the columns after, which is how most people think about a question anyway.

   Café sales by month, 2026

   FROM square.payments
   SELECT
     date_trunc('month', created_at AT TIME ZONE 'America/Chicago')::DATE AS month,
     count(*) AS payments,
     round(sum(amount) / 100) AS sales,
     round(sum(tip_amount) / 100) AS tips
   WHERE status = 'COMPLETED'
     AND year(created_at AT TIME ZONE 'America/Chicago') = 2026
   GROUP BY ALL
   ORDER BY month;

   Result, 9 rows

   month	payments	sales	tips
   2026-01-01	4,311	43,046	3,164
   2026-02-01	4,010	38,694	2,828
   2026-03-01	4,569	43,990	3,333
   2026-04-01	4,633	44,789	3,289
   2026-05-01	5,300	52,372	3,791
   2026-06-01	5,027	50,353	3,692
   2026-07-01	5,412	52,737	3,847
   2026-08-01	5,569	55,231	4,128
   2026-09-01	5,028	49,227	3,660

   Two details are worth copying into every query you write against synced data. Square records each payment as an instant in time, so AT TIME ZONE 'America/Chicago' turns it into the café's own wall clock before it is grouped into months; skip that and late-evening sales slide into the wrong day depending on where your laptop is. And GROUP BY ALL groups by every column that is not an aggregate, so you never have to repeat yourself.

6. Save a copy as Parquet, and read it back

   Parquet is the standard file format for analytical data: columnar, compressed and readable by nearly every data tool. DuckDB writes it and reads it as easily as a table.

   DuckDB

   COPY (FROM square.payments WHERE status = 'COMPLETED')
     TO 'cafe_payments.parquet' (FORMAT parquet);
   
   SELECT count(*) AS payments, round(sum(amount) / 100) AS sales
   FROM 'cafe_payments.parquet';

That is the whole loop: install, load, explore, ask, save. Everything else is more of the same with bigger questions.

Files are tables

The last step hints at one of DuckDB's most useful habits. Put a file name where a table name would go and DuckDB reads the file, working out its columns and types for you. That works for CSV, Parquet and JSON, for a whole folder of files at once with a wildcard, and for files on the web.

-- A CSV export: the delimiter, header and column types are detected for you.
SELECT * FROM 'exports/orders_2026_09.csv';

-- Every Parquet file in a folder, read as one table.
SELECT count(*) FROM 'archive/*.parquet';

-- A file on the web. DuckDB loads its httpfs extension on first use.
SELECT * FROM read_parquet('https://example.com/data/prices.parquet') LIMIT 10;

For anything large, DuckDB reads only what the query needs. Ask a Parquet file on a web server for two columns and it fetches just those two, guided by the metadata at the end of the file, rather than downloading the lot. That makes a lot of “load it first” work unnecessary. Often you can simply point DuckDB at the export and ask.

SQL that meets you halfway

DuckDB speaks standard SQL, close to Postgres's dialect, so most of what you know carries over. It also adds a set of conveniences its documentation calls “friendly SQL”, aimed squarely at the repetitive parts of analysis. A few earn their keep every day.

EXCLUDE: all the columns but these

Synced tables carry bookkeeping columns you rarely want to see. Rather than listing the twelve you do want, name the four you do not.

SELECT * EXCLUDE (created_at, updated_at, _deleted, _synced_at)
FROM square.team_members
ORDER BY id;

QUALIFY: filter on a window

“The busiest day of each month” means ranking days within each month and keeping the first. Standard SQL needs a subquery to filter on a ranking. DuckDB's QUALIFY does it in place, the way HAVING filters on an aggregate.

SELECT
  strftime(day, '%B') AS month,
  day,
  dayname(day) AS weekday,
  payments
FROM (
  SELECT
    CAST(created_at AT TIME ZONE 'America/Chicago' AS DATE) AS day,
    count(*) AS payments
  FROM square.payments
  WHERE status = 'COMPLETED'
  GROUP BY ALL
)
WHERE year(day) = 2026
QUALIFY row_number() OVER (PARTITION BY month(day) ORDER BY payments DESC, day) = 1
ORDER BY day;

Saturdays, every month but one: in September the busiest day was the Sunday of the Labor Day weekend. That is already a hint at Pondside's second question, whether the Saturday rush is growing or just seasonal, and a reminder that the answer will need history, not a single week.

And the rest

Extensions: reaching other databases

DuckDB keeps its core small and adds abilities through extensions, most of which install themselves the first time a query needs them. Three matter for a warehouse.

• httpfs reads files over HTTPS and from S3-compatible object storage, which is how DuckDB queries a Parquet file in a bucket without downloading it first.
• postgres and mysql attach a live Postgres or MySQL database so its tables can be queried, or copied, with DuckDB's engine. For a one-off look at production data this is remarkably handy.

ATTACH 'host=db.internal dbname=shop user=readonly' AS app (TYPE postgres, READ_ONLY);

-- A Postgres table, summarized by DuckDB.
SELECT status, count(*) FROM app.public.orders GROUP BY ALL;

A live attachment is not a warehouse, though. Every query reads from your production database while it runs, and you get today's rows with no history. Keeping a copy current on a schedule is a different job, and part four is about doing it well.

What DuckDB is not for

Being clear about this saves a lot of grief, and DuckDB's own documentation is refreshingly direct about it.

The second point deserves a sentence of explanation. A DuckDB database file belongs to one process at a time for writing. Inside that process, many threads can read and write concurrently; several processes can open the same file read-only; but two programs cannot both open it for writing. That is a sensible rule for a library, and an awkward one for a shared warehouse that a sync, a dashboard and three people all want to use.

When DuckDB needs an address: Quack

The fix for that is to give DuckDB a server after all. Since version 1.5.3, DuckDB ships a client-server protocol of its own, called Quack. One DuckDB runs quack_serve and owns the database file; other DuckDBs, anywhere on the network, ATTACH it and query it as though it were local, including several writers at once. It speaks HTTP underneath, so it passes through ordinary proxies and load balancers.

-- On any machine with DuckDB 1.5.3 or newer:
ATTACH 'quack:your-database.example.com:443' AS wh (TOKEN 'your_token');

SELECT count(*) FROM wh.orders;

Quack is new, and the DuckDB project labels it beta: the protocol and function names may still change, and it is due to become stable with DuckDB 2.0. Out of the box a Quack server only listens on the local machine, with a token generated at startup and no encryption, and the documentation recommends putting it behind a proxy that adds TLS before exposing it to a network.

We have taken DuckDB's speed on trust so far: columns instead of rows, compression, batches, every core. Those are not marketing words; each is a specific idea with a specific payoff, and once you can see them, you start writing tables and queries that work with the engine rather than against it. Part three goes inside, with a million rows and a look at what DuckDB skips.