DuckDB vs ClickHouse vs PostgreSQL for Analytics: How to Choose

The question behind the question

"Which database should I use for analytics?" is usually really three questions: how much data do I have, how many concurrent readers, and how much operational work am I willing to take on. Answer those and the choice mostly makes itself.

PostgreSQL: the default that is often enough

PostgreSQL is a row store. It reads whole rows off disk, which is perfect for SELECT * FROM events WHERE id = 42 and wasteful for SELECT country, count(*) FROM events GROUP BY country over 20 million rows — that query touches two columns but pays for all thirty.

Postgres is still the right answer when:

• Your event table fits comfortably in a few million rows.
• You already run Postgres and do not want a second system.
• Your dashboards are heavily filtered (a day at a time, one site at a time) so indexes do the heavy lifting.

Extensions change the calculus. pg_stat_statements will tell you which aggregations hurt, and columnar extensions exist — but at that point you are adopting a second storage engine anyway.

DuckDB: columnar without the server

DuckDB is an embedded columnar engine — the analytics equivalent of SQLite. It runs inside your application process, reads and writes a single file, and needs no daemon, no cluster, and no configuration.

For a dashboard workload it is a remarkably good fit:

• Vectorized columnar execution. A GROUP BY country reads the country column and nothing else.
• Zero operations. No service to monitor, restart, or upgrade separately from your app.
• Reads Parquet natively. You can keep cold history as files and query it in place.

Its constraint is the flip side of its strength: it is a single process. One writer, and readers share the host's CPU and RAM with your application. That is fine for a self-hosted analytics dashboard where a handful of people query at once. It is not a multi-tenant query service for hundreds of concurrent analysts.

ClickHouse: built for scale, priced in operations

ClickHouse is a distributed columnar database designed for very large event volumes and high read concurrency. It has excellent compression, materialized views that maintain rollups on insert, and a mature sharding/replication story.

The cost is real: it is a service you run. Cluster sizing, replication topology, merge tuning, schema migrations that behave differently from Postgres, and a query dialect with its own sharp edges. That is a reasonable trade when you are ingesting billions of rows — and heavy overhead when you are ingesting a few million.

Side by side

Why InsightsTrack uses Postgres and DuckDB

Analytics has two workloads with opposite shapes. Writes are single-row, frequent, and must be durable — that is Postgres. Reads are wide aggregations over date ranges — that is DuckDB. Trying to serve both from one engine means one of them is always compromised.

So writes land in PostgreSQL, a background job syncs new rows into DuckDB, and every dashboard query reads from DuckDB — see the full architecture and the sync implementation. You get transactional durability and columnar read speed without running a data warehouse.

Frequently asked questions

When is PostgreSQL alone enough for analytics?

Longer than most people assume. With sensible indexes and pre-aggregated rollups, PostgreSQL serves dashboards well into millions of rows. If your queries return in acceptable time today, adding an analytical engine buys complexity you do not yet need.

Is DuckDB a replacement for ClickHouse?

Not at scale. DuckDB is an embedded single-node engine with no server to run; ClickHouse is a distributed database built for horizontal scale and high-concurrency serving. DuckDB wins on operational simplicity, ClickHouse wins once one machine is no longer enough.

Can DuckDB handle concurrent users?

In-process, yes — many readers against one instance is the normal pattern. What it does not do is serve many independent processes writing the same file, so it fits an embedded read layer rather than a shared database server.

Why use two databases instead of picking one?

Because ingestion and analysis want opposite storage layouts. Row storage makes single-row writes cheap; columnar storage makes wide aggregations cheap. Running both lets each side use the layout that suits it, at the cost of a sync.

How much data before I outgrow a single node?

There is no fixed number, but the signal is consistent: when your hot working set no longer fits in memory and rollups have stopped helping. Tiering cold data to Parquet usually defers a distributed system by a long way.

A decision rule

• Under ~5 million events, dashboards feel fine, and you already run Postgres: stay on Postgres, add indexes.
• Self-hosted product, single node, aggregations feel slow: add DuckDB as a read layer.
• Billions of events, many concurrent analysts, a team to operate it: ClickHouse.

If you want the underlying mechanics, read columnar vs row storage. The mistake is reaching for the third option while you are still living in the first. Pick the smallest system that comfortably serves your query shape, and revisit when the numbers actually change.