Skip to content
LayerbaseLayerbase

What developers should know about DuckDB

6 min readDuckDBAnalyticsPostgreSQL

Short version: DuckDB is to analytics what SQLite is to application data. It is a library, not a server. It stores data by column, so it scans and aggregates millions of rows fast, and it can query a CSV, a JSON file, a Parquet file or a live PostgreSQL database without importing anything first. It is not a replacement for the database behind your app, and it has a concurrency rule that surprises people the first time they hit it.

In the Layerbase walkthrough video I join four sources in one DuckDB query. This post explains why that works and what to know before you build on it.

Contents

It runs inside your process

There is no DuckDB server to install. You add the library to a Python, Node, Go or Rust project, or run the duckdb binary, and it opens a file. The whole database is that one .duckdb file. Copy it and you have copied the database.

That is why it feels so fast for local work. A query never crosses a network or waits on a connection pool. It is also why it has no users, roles or port of its own.

Columns, not rows

PostgreSQL and MySQL store each row together, which is what you want when the app reads or updates one order at a time. DuckDB stores each column together and processes a batch of values at once. A query like "total revenue by month for the last three years" only touches two columns and reads them in large, compressed chunks. On analytical queries the gap is often an order of magnitude or more.

The trade goes the other way for single-row work. Updating one row at a time, thousands of times a second, is what row stores are built for and what DuckDB is not.

It reads files where they are

You can point SQL at a file without creating a table:

sql
SELECT * FROM read_csv_auto('exports/refunds.csv');
SELECT * FROM read_json_auto('exports/campaigns.json');
SELECT * FROM read_parquet('https://example.com/sales.parquet');

DuckDB works out the column names and types itself. For a one-off question about a vendor export, that skips the whole create-table-and-import step. When you query the same file every day, load it into a table once.

It attaches other databases

With the postgres and mysql extensions, DuckDB can attach a running PostgreSQL or MySQL database and query its tables as if they were local. MariaDB works through the mysql extension.

sql
INSTALL postgres;
LOAD postgres;
ATTACH 'postgresql://reader:PASSWORD@db.example.com:5432/shop' AS pg_source (TYPE postgres, READ_ONLY);

SELECT count(*) FROM pg_source.public.orders;

DuckDB pushes filters down where it can, but a big scan still pulls rows over the network. Give it a read-only login, and for heavy, repeated analysis copy the data into DuckDB rather than hammering production every time.

One query, four sources

This is the query from the video, cut down so you can read it. Orders live in PostgreSQL, customers in MariaDB, refunds in a CSV file and ad spend in a JSON file.

The setup, if you want to follow along. In PostgreSQL:

sql
CREATE TABLE public.orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10, 2)
);

INSERT INTO public.orders VALUES
  (1, 1, 80.00), (2, 1, 45.00), (3, 2, 120.00), (4, 3, 60.00), (5, 3, 90.00);

In MariaDB:

sql
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100),
  country CHAR(2)
);

INSERT INTO customers VALUES (1, 'Ada', 'US'), (2, 'Bea', 'US'), (3, 'Cleo', 'DE');

refunds.csv:

text
order_id,refund
2,15.00
5,40.00

campaigns.json:

json
[
  { "country": "US", "spend": 120.0 },
  { "country": "DE", "spend": 60.0 }
]

With PostgreSQL attached as pg_source and MariaDB as maria_source, one DuckDB query answers "net revenue and ad spend by country":

sql
SELECT
  c.country,
  sum(o.amount) AS gross,
  coalesce(sum(r.refund), 0) AS refunded,
  sum(o.amount) - coalesce(sum(r.refund), 0) AS net,
  any_value(s.spend) AS spend
FROM pg_source.public.orders AS o
JOIN maria_source.customers AS c ON c.id = o.customer_id
LEFT JOIN read_csv_auto('https://example.com/refunds.csv') AS r ON r.order_id = o.id
LEFT JOIN read_json_auto('https://example.com/campaigns.json') AS s ON s.country = c.country
GROUP BY ALL
ORDER BY net DESC;
text
country  gross   refunded  net    spend
US       245.00  15.0      230.0  120.0
DE       150.00  40.0      110.0  60.0

No export, no staging table, no ETL job. For a question you ask once, that is the whole appeal. For a dashboard that runs it every five minutes, save the result as a table instead (more on that below).

The single-writer rule

This is the one that catches people. Only one process can open a DuckDB file for writing at a time. Inside that process, many threads can read and write safely. A second process can open the same file read-only, but not while the first one holds it for writes.

So DuckDB fits "one job builds the data, many people read it" well, and fits "five web servers write to the same file" not at all. If you need many independent writers, you want a server database, or a layout where writers go somewhere else and DuckDB reads the result.

The SQL is nicer than you expect

DuckDB's SQL is close to PostgreSQL's, with some shortcuts that are hard to go back from:

sql
-- Group by every non-aggregated column without listing them
SELECT country, plan, count(*) FROM signups GROUP BY ALL;

-- Everything except a couple of columns
SELECT * EXCLUDE (password_hash, api_key) FROM users;

-- FROM first, for quick looks
FROM orders LIMIT 10;

It also has PIVOT, QUALIFY for filtering window functions, and friendly string and date functions. Getting Started with DuckDB works through rollups, window functions and PIVOT on a sample dataset.

What changes when DuckDB is hosted

DuckDB has no network protocol of its own, so "DuckDB in the cloud" always means someone put a server in front of it. On Layerbase, that server speaks the PostgreSQL wire protocol. You connect with psql or any Postgres driver, and the SQL you send is still DuckDB SQL.

Three things follow from that:

  • The DuckDB client libraries do not connect to it. A Postgres driver does, and so does almost every BI tool, which is usually the better trade for a shared dashboard.
  • The hosted database cannot see your laptop's files. Upload a CSV, JSON or Parquet file through the query console's import, or read it from an https:// URL as in the example above.
  • Attaching account databases is a click. The DuckDB Sources tab attaches your Layerbase PostgreSQL and MariaDB databases with a read-only login made for the purpose, so the analytical side cannot change source rows. For a database hosted somewhere else, a plain ATTACH to any PostgreSQL or MySQL host reachable over the internet works too.

When a result is worth keeping, DuckLake stores it as Parquet files in object storage with a PostgreSQL catalog, so the saved tables outgrow a single file without you running a warehouse. Introducing DuckLake on Layerbase covers the setup, and DuckDB from file to cloud covers moving an existing .duckdb file.

When not to use it

  • As your app's main database. Logins, carts, orders: keep those in PostgreSQL or MySQL. Point DuckDB at them for the analysis.
  • For many concurrent writers. See the single-writer rule.
  • For data that already lives in a warehouse. If the data sits in BigQuery or Snowflake, query it there; pulling it down to DuckDB only makes sense for a subset.
  • For real-time counters at high write rates. A time-series engine or ClickHouse fits better. ClickHouse vs DuckDB draws that line.

DuckDB is at its best sitting next to the database your app already has, answering the questions that database is slow at. Create a DuckDB database on Layerbase, attach the PostgreSQL you already run, and ask it the query your dashboard has been timing out on.

Keep reading