pg_duckdb: When Do I Actually Need It?

I came across pg_duckdb recently. Now I have been using Postgres for most of my career so far. I have used DuckDB once, in a school project in Jan 2025. I know DuckDB is columnar based and is good for analytics. I know Postgres is typically meant for OLTP. But, under what circumstances do I have to use pg_duckdb?

Driven by curiosity, I directed Claude Code to run an experiment on my MacBook Pro. The experiment is in a public repo, simkimsia/pg-duckdb-when-to-use, so you can run it on your own machine.

Here are the results.

The short answer

If you only remember one thing from this note, remember this table.

Your situation	Use pg_duckdb?
Tables under ~1M rows	No. Every query finished under 0.6 s either way.
~10M rows, reports that group by something with many values (per customer), COUNT(DISTINCT), window functions	Yes. 5 s became 1.2 s. About 4× faster.
Simple rollups with few groups (daily totals)	No. pg_duckdb was 2× slower.
Join + GROUP BY	Doesn’t matter. It was a tie.
~50M rows (3 GB) and up, on normal Postgres tables	Gains drop to about 2×. Put the data in Parquet and it’s 6 to 20×.
Normal app traffic: lookups by id, inserts, updates	No. That is what Postgres is for.

So “heavy” means about 10M rows and a query that does real work after reading the rows. Size alone is not enough.

Simple mantra: if the table is between 10M and 50M rows and you’re using GROUP BY with many values and/or window functions, use pg_duckdb. <=10M, use Postgres as per normal. >50M, Parquet is much better than pg_duckdb, even if pg_duckdb is still better than Postgres.

My setup

Machine	MacBook Pro (MacBookPro18,4), Apple M1 Max
CPU	10 cores (8 performance + 2 efficiency)
RAM	64 GB
OS	macOS 15.7.5
Docker	29.8.2 on OrbStack 2.2.5, all 10 CPUs and ~47 GB visible to containers
Database	Postgres 18.1 + pg_duckdb 1.1.0 (image pgduckdb/pgduckdb:18-v1.1.1)
Postgres settings	shared_buffers=4GB, work_mem=256MB, max_parallel_workers_per_gather=4

What I ran

A fake orders table at three sizes: 1M rows (57 MB), 10M rows (575 MB) and 50M rows (2.9 GB). Plus a 1M-row customers table to join against.

Four queries, the kind you find behind a dashboard:

1. Daily totals. GROUP BY order_date. Only about 2,000 groups.
2. Per customer. GROUP BY customer_id with COUNT(DISTINCT product_id). About 1M groups.
3. Join. Orders joined to customers, then grouped by region and status.
4. Window. rank() OVER (PARTITION BY customer_id ORDER BY amount DESC).

Each query ran 3 times per engine and I kept the middle time. The cache was warm.

Results

Seconds. Postgres → pg_duckdb (default settings) → pg_duckdb with the scan opened up (more on that below).

Query	1M rows	10M rows	50M rows
Daily totals	0.05 → 0.07 → 0.08	0.36 → 0.60 → 0.63	1.6 → 3.3 → 3.1
Per customer	0.59 → 0.14 → 0.15	5.0 → 1.2 → 1.0	13.9 → 7.9 → 6.0
Join	0.22 → 0.21 → 0.19	1.25 → 1.21 → 1.19	5.7 → 5.5 → 5.4
Window	0.50 → 0.11 → 0.09	5.4 → 1.5 → 0.8	11.8 → 5.0 → 4.3

And the same 50M rows saved as a Parquet file (808 MB), read with read_parquet():

Query	Postgres table	Parquet via pg_duckdb
Daily totals	1.6 s	0.08 s (20×)
Per customer	13.9 s	2.2 s (6×)

The raw CSV is in results/kimsia-m1max-2026-10-30.

Why it behaves like this

The surprise for me: pg_duckdb does not read your Postgres table itself. For each table, it writes a plain SELECT columns FROM table WHERE ... and asks Postgres to run it. Every row that comes back gets converted, value by value, into DuckDB’s columnar format. Only then does DuckDB do its fast part.

So think of it as two halves. Delivery (Postgres hands the rows over) and work (DuckDB groups, counts, ranks). Once you see it that way, the results make sense:

1. Daily totals lost. The work is cheap, only 2,000 groups. Plain Postgres already splits this across 5 processes and passes around a tiny result. pg_duckdb has to deliver all 50M rows first. Cheap work, expensive delivery.
2. Per customer and window won. Here the work is the expensive half. DuckDB does it on all cores, in batches. Postgres sorts the rows in parallel, but the COUNT(DISTINCT) and the window step itself run in one process. You can see it in EXPLAIN: the GroupAggregate and WindowAgg sit above the Gather Merge.
3. Join tied. Both engines spend most of the time on the same thing, reading and matching rows.
4. The gains shrank at 50M rows. Delivery became the bottleneck. By default pg_duckdb uses only 2 Postgres workers and 2 DuckDB threads to read a table, even on a 10-core machine. Opening that up to 4 workers and 8 threads (the third number in the table) helped the heavy queries, 7.9 s down to 6.0 s. It did nothing for daily totals.
5. Parquet flew. read_parquet() skips the Postgres delivery completely. That is DuckDB at full speed.

How pg_duckdb plugs into Postgres

1. It hooks into the Postgres planner and executor. When DuckDB takes a query, the whole query becomes one node in the Postgres plan. You see it as a single Custom Scan (DuckDBScan) in EXPLAIN.
2. With duckdb.force_execution off, DuckDB still takes queries that use DuckDB-only things, like read_parquet() or a DuckDB table.
3. With it on, DuckDB tries every SELECT with a FROM. If DuckDB cannot plan it, you get a WARNING and Postgres runs the query instead.
4. Table reads go through the normal Postgres executor, under your normal snapshot, so you see the same data your transaction sees. Column choice and simple WHERE filters are pushed down to Postgres. Joins and GROUP BY are not.
5. Each connection gets its own in-memory DuckDB. By default that is 4 GB and every CPU core, per connection.

I read this from the source at commit a9457c9 (28 Oct 2026). That is a few commits newer than the 1.1.0 I benchmarked.

Gotchas I hit

1. A plain numeric column quietly turns pg_duckdb off. If the column has no precision (numeric, not numeric(10,2)), DuckDB refuses the query, prints a WARNING, and Postgres runs it instead. You still get the right answer, so you won’t notice unless you read the warnings. My first run was all Postgres because of this. Fix it by giving the column a precision. The other option, duckdb.convert_unsupported_numeric_to_double, turns it into a float and you lose exact decimals. Not what you want for money.
2. The table scan is capped at 2 workers and 2 threads by default. See duckdb.max_workers_per_postgres_scan and duckdb.threads_for_postgres_scan.
3. DuckDB tables on your own server are TEMP only. CREATE TABLE ... USING duckdb errors out unless it’s TEMP or you use MotherDuck.
4. One transaction cannot write to both a Postgres table and a DuckDB table. The docs say so, and the switch to allow it is marked unsafe.
5. Your managed Postgres may not let you install it. It needs shared_preload_libraries, and by default only superusers can use it. Check with your provider first.

Run it yourself

You need Docker and about 10 GB of free disk. The whole run takes 15 to 20 minutes.

Step 1: Clone the repo

git clone https://github.com/simkimsia/pg-duckdb-when-to-use.git
cd pg-duckdb-when-to-use

Step 2: Run everything

./run.sh

This starts Postgres 18 with pg_duckdb in Docker, builds the tables, runs every query on all three engines, and writes your hardware specs next to the numbers.

Step 3: Compare with mine

Your numbers land in results/<your-machine>-<date>/results.csv. Put them next to mine. If you see a different pattern, open an issue on the repo. I want to know.

Step 4: Clean up

docker rm -f pgduck-bench

The container holds about 4 GB of RAM while it runs.

Conclusion

It doesn’t take a lot for Claude to run an experiment on my laptop. It’s not the most rigorous experiment, but it doesn’t have to be. I cannot understand why the entire README for pg_duckdb doesn’t simply include a “when to use this” and a “when not to use this”.

DevEx is a real thing.