DuckDB vs Postgres: a division of labour, not a choice

Postgres and DuckDB get set against each other as if you have to pick one. In almost every real system you don't. Postgres is the system of record: many concurrent writers, transactions, roles, replication. DuckDB is an analytical engine: one process, columnar, built to scan and aggregate. The interesting question is not which one wins. It's how to stop your application database moonlighting as your warehouse.

I run this split myself. Postgres is the primary store for this site's own app — leads, sessions, users. The one place that genuinely needed an analytical server got ClickHouse. Different jobs, different engines.

The short answer

Keep Postgres. It is your source of truth and nothing here replaces it.

Try Postgres first for the analytics too. An index, a partition scheme, or a materialised view refreshed on a schedule solves a surprising share of "our reports are slow", and it costs you no new infrastructure.

Reach for DuckDB when the analytical queries scan wide, aggregate hard, and are hurting the application they share a server with. Then the shape is: Postgres stays the system of record, data lands in Parquet on a schedule, and DuckDB reads the Parquet. Your primary database goes back to serving the product.

If many people need to query fresh analytical data at the same time, neither of these is the answer — that is a job for an analytical server like ClickHouse.

What each one is

Postgres is a client-server, row-oriented relational database built for transactions. It stores a row's columns together on a page. It uses MVCC, so readers and writers don't block each other, and it supports many concurrent writers, roles and privileges, row-level security, streaming replication, foreign keys and constraints. It has a huge extension ecosystem. It is the default right answer for an application's primary database, and has been for years.

DuckDB is an in-process analytical database — "SQLite for analytics". You import it as a library into Python, Node, Go or the CLI, and a columnar, vectorised engine runs inside that process. It reads Parquet, CSV and JSON directly, including from object storage, with no load step. There is no server and no port. One process holds a database file read-write at a time; any number can read it if none is writing. It has no user model at all — the file and the process are the security boundary.

One is a database you connect to. The other is a database you import.

At a glance

	Postgres	DuckDB
Shape	Server that clients connect to	Library inside your process
Storage	Row-oriented	Columnar
Built for	Transactions, point reads and writes	Scans and aggregations
Concurrent writers	Many	One process at a time
Users, roles, row-level security	Yes	None — your code is the boundary
Replication, failover	Yes	Not applicable — it's a library
Reads files in place	Via extensions	Natively: Parquet, CSV, JSON, S3
Right as a system of record	Yes	No
Right for a heavy group-by	Workable, with effort	Yes

Why analytics hurts on a row store

Postgres stores a row's columns next to each other. That is exactly right when you want one order and all its fields. It works against you when you want one field of every order.

Take SELECT date_trunc('day', created_at), sum(amount_cents) FROM orders GROUP BY 1 over a table with sixty columns and a lot of history. The query names two columns. Postgres reads pages, and each page holds whole rows, so it pulls the other fifty-eight along for the ride. Add MVCC row headers and the dead tuples an update-heavy table carries, and the bytes moved are several times the bytes the query cares about.

A columnar engine reads the two columns and nothing else, compresses them well because a column of similar values compresses well, and processes them in batches rather than row at a time. That is the whole difference, and it is structural. No amount of tuning turns a row store into a column store.

The second, quieter cost is that the scan runs on the same server as your application's writes. It evicts your hot pages from the buffer cache, competes for I/O, and holds a snapshot open. The report gets slow and, worse, checkout gets slow with it.

Fix Postgres first

Before adding an engine, spend an afternoon on the one you have. In my experience this ends the problem more often than not.

• Index for the query you actually run. A B-tree on the filter column, or a BRIN index on an append-only table whose rows arrive in time order — BRIN is tiny and works well for "last 30 days" filters.
• Partition big tables by time. Declarative partitioning lets the planner skip whole partitions instead of scanning them.
• Materialise the repeated query. Dashboards ask the same handful of questions all day. Compute the answer once on a schedule and let the dashboard read a small table.
• Move reports to a read replica. This is the cheapest way to stop analytics competing with the application, and it needs no new technology.
• Look at what is actually slow. pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS) will usually tell you within minutes whether you have a missing index or a genuine scan problem. They are different problems with different fixes.

-- Append-only table, rows arriving in time order: BRIN is small and effective.
CREATE INDEX orders_created_at_brin ON orders USING brin (created_at);

-- Turn a repeated dashboard query into a table read.
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       count(*)                      AS orders,
       sum(amount_cents) / 100.0     AS revenue
FROM orders
GROUP BY 1;

-- The unique index is what allows a refresh that doesn't lock out readers.
CREATE UNIQUE INDEX daily_revenue_day ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

What these do not fix is ad-hoc exploration. Indexes and materialised views speed up questions you anticipated. When analysts keep asking new questions over the full history, you are paying to scan a row store every time, and that is the point where a columnar engine earns its keep.

Three ways to run both

1. Query Postgres directly from DuckDB

DuckDB's postgres extension attaches a live Postgres database. Its tables then behave like DuckDB tables, and you can join them against Parquet, CSV or DuckDB's own tables in one query.

INSTALL postgres;
LOAD postgres;

-- Attach a replica, not the primary. READ_ONLY is cheap insurance.
ATTACH 'dbname=app host=replica.internal user=analytics' AS pg (TYPE postgres, READ_ONLY);

SELECT date_trunc('day', o.created_at) AS day,
       count(*)                        AS orders,
       sum(o.amount_cents) / 100.0     AS revenue
FROM pg.public.orders o
WHERE o.created_at >= now() - INTERVAL 90 DAY
GROUP BY ALL
ORDER BY day;

This is excellent for exploration and for pipelines that read Postgres as one source among several. The caveat is the one people skip: the rows still come out of the Postgres server. DuckDB asks for the columns and rows it needs, but the aggregation happens in DuckDB, after the data has crossed the wire. A wide scan through this path is a wide scan on Postgres. Attach a read replica, and if the same query runs on a schedule, stop re-reading Postgres and use the next pattern instead.

2. Export to Parquet, query the Parquet

This is the one I reach for most, because it takes the analytical load off Postgres entirely. A scheduled job reads the new rows once, writes Parquet to object storage, and every analytical query after that touches files rather than your database.

-- In the scheduled job: read yesterday once, write it as a partition.
-- Writing to s3:// needs the httpfs extension and credentials (CREATE SECRET).
INSTALL httpfs;
LOAD httpfs;

COPY (
  SELECT * FROM pg.public.orders
  WHERE created_at >= DATE '2026-09-17'
    AND created_at <  DATE '2026-09-18'
) TO 's3://warehouse/orders/dt=2026-09-17/part-0.parquet' (FORMAT parquet);

-- Everything afterwards reads files. Postgres is not involved.
SELECT date_trunc('month', created_at) AS month,
       count(*)                        AS orders,
       sum(amount_cents) / 100.0       AS revenue
FROM read_parquet('s3://warehouse/orders/dt=*/*.parquet', hive_partitioning = true)
GROUP BY ALL
ORDER BY month;

The dt= directory layout matters: a query filtered to one month prunes whole directories before reading a byte. The cost is freshness — the data is as old as the last export — and a job you have to run. In exchange, no analyst can slow down checkout, the same files work from a laptop, CI and the server, and you are not locked into anything, because Parquet is an open format every other engine reads. That reversibility is the argument I make in is DuckDB safe for production and in DuckDB as a SaaS warehouse.

3. pg_duckdb: DuckDB inside Postgres

pg_duckdb is a Postgres extension that embeds DuckDB's engine in the Postgres process. Analytical queries are executed by DuckDB, while your clients keep speaking to Postgres over the same connection. It is an official DuckDB project, built in collaboration with Hydra and MotherDuck, and it also gives Postgres the ability to read Parquet and CSV from object storage.

CREATE EXTENSION pg_duckdb;

-- Postgres, reading Parquet in object storage through DuckDB's engine.
-- 'r' is the row object read_parquet() returns; columns are looked up by name.
SELECT r['country'] AS country, count(*) AS orders
FROM read_parquet('s3://warehouse/orders/dt=2026-09-*/*.parquet') r
GROUP BY r['country'];

On paper this is the best of both: no second system, no export, no change to how applications connect. Treat it carefully anyway. It is much younger than either engine, and which queries it routes through DuckDB, which Postgres versions it supports and how it behaves under memory pressure are all things that move between releases. Read the current documentation, test it against your own queries, and don't design a critical path around behaviour you read in a blog post — including this one.

When Postgres alone is enough

Be honest about this before adding anything. Postgres alone is the right answer when:

• The analytical tables are modest — think tens of gigabytes, not hundreds.
• The dashboards are internal and the questions are known, so they can be materialised.
• Reporting already runs on a replica, or the load is small enough not to matter.
• Nobody is waiting on a query long enough to stop asking.

A second engine is a second thing to schedule, monitor, version and explain to whoever is on call. If Postgres is doing the job, that cost buys you nothing.

When DuckDB earns its place

• Queries scan wide and aggregate over long histories, and new questions keep arriving.
• Reporting is measurably competing with the application for the same server.
• The work already lives in a batch job — a transformation, a nightly rollup, a report — where an in-process engine has nothing to operate.
• You want the same engine on a laptop, in CI and in production, running the same SQL over the same files.
• Data is arriving as files anyway, and loading it into Postgres just to query it is a step you could skip.

DuckDB's SQL is close to Postgres's, which makes this cheaper than it sounds. Most reporting queries move over with small changes, and DuckDB adds conveniences like GROUP BY ALL and SELECT * EXCLUDE (...). If you are weighing DuckDB against the other small-engine options rather than against Postgres, DuckDB vs SQLite and DuckDB vs Polars are the closer comparisons.

When you need neither

There is a third case, and it is worth naming so you don't build the wrong thing twice. If many people — or many customers — need to run analytical queries at the same time, over data that is seconds old, no arrangement of Postgres and DuckDB fits. DuckDB has no server to arbitrate between clients, and Postgres will be doing the thing it is worst at, under load, all day.

That is an analytical server's job. ClickHouse vs Postgres is the comparison to read if you are already there, and DuckDB vs ClickHouse is the one that decides whether you need the server at all. I run ClickHouse in production behind Ansaar for exactly this shape: many concurrent readers, data arriving continuously, numbers expected to be current.

Where to start

Measure before you move. Find the two or three slowest analytical queries with pg_stat_statements, look at what they actually scan, and try an index or a materialised view first. If they are genuine full-table aggregations that will keep growing, export the tables they touch to Parquet on a schedule and point DuckDB at the files. Keep Postgres as the system of record throughout — none of this is a migration.

The long version, with runnable code, is my book Local-First Analytics — DuckDB, Parquet and Arrow on hardware you already have.

If you'd like someone who runs both to look at your queries and say which side of the line they fall on, a Data Platform Audit is a week and a written roadmap you keep. The scoping call below is free.