ClickHouse vs Postgres: when to add an analytics database

Postgres is a system of record. ClickHouse is an analytical database. They get compared because a lot of teams start with everything in Postgres, watch the reporting queries get slow, and have to decide whether to fix Postgres or add something next to it. That is the real question, and the answer for most teams is not "replace Postgres" — it is "keep Postgres, and stop asking it to do the other job."

I run both. On my own platform, Ansaar, Postgres holds pipeline metadata and ClickHouse is the analytical store — 50-plus tables feeding a live API. So this is written from operating the pair, not from a benchmark table.

The short answer

Stay on Postgres while the analytical tables are moderate, the queries are selective, and the reporting load is not hurting your application. Postgres has more room than people assume: the right indexes, range partitioning on time, BRIN indexes for append-only time-ordered data, rollup tables or materialized views, and a read replica so analysts are not competing with your users.

Add ClickHouse when the analytical tables are large and still growing, the queries scan rather than look up, and someone expects sub-second answers over that history while data keeps arriving. Then put it beside Postgres, not in front of it: Postgres stays the source of truth, and the analytical copy flows into ClickHouse.

The mistake is jumping straight to the second paragraph. The other mistake is refusing to, for two years, while every dashboard gets slower.

What each one is

Postgres is a row-oriented relational database built for transactions. Rows are stored together, MVCC lets many readers and writers work at once without blocking each other, and updates and deletes are ordinary, correct, cheap operations. It enforces primary keys, unique constraints and foreign keys. It has roles, permissions, and an extension ecosystem that is genuinely unmatched. When you need one true copy of the data your application writes, this is the shape you want.

ClickHouse is a columnar analytical server. Its MergeTree family stores each column in its own compressed file, sorted by the table's ORDER BY, which also drives a sparse primary index — so a query reads only the columns it names and only the blocks that can contain matching rows. Execution is vectorised. It is built for inserts arriving in batches and scans over billions of rows, and its materialized views can aggregate data on the way in, so the rollup is ready before anybody asks for it.

Those are not two competing products. They are two different jobs.

At a glance

	Postgres	ClickHouse
Storage	Row-oriented	Columnar, compressed, sorted
Built for	Transactions, correctness, many concurrent writers	Scans and aggregates over huge tables
Updates and deletes	Cheap and immediate	Background mutations; lightweight deletes mark rows
Constraints	Primary keys, unique, foreign keys, checks	None enforced — dedup happens at merge time
Insert pattern	Row at a time is normal	Batches; row-at-a-time is an anti-pattern
Joins	Strong planner, normalised schemas work well	Work, but the model rewards wide denormalised tables
Transactions	Full	Limited
Typical role	Source of truth	Analytical copy

Why analytics hurts on Postgres

The failure is usually the same one. A query that finds a handful of rows by index is fine forever. A query that aggregates across a large fraction of a table is not a lookup, so the planner picks a sequential scan — and in a row store that means reading every column of every row it visits, including the wide jsonb payload the query never mentions. Compression cannot save you either, because rows of mixed types do not compress the way a column of one type does.

Then a second problem lands on top: the reporting query is running on the same database that serves your application. It competes for memory, for I/O and for the buffer cache, and your users feel a dashboard nobody told them about.

Adding indexes rarely fixes this. It makes writes slower and leaves the scan a scan.

What to try on Postgres first

Do these before you add a second database. Plenty of teams never need one.

• Get the indexes right, not numerous. Partial indexes for the filter you actually use, covering indexes with INCLUDE so a query can be answered from the index alone, and a pass to drop the ones nothing uses.
• Partition on time. Declarative range partitioning by day or month lets the planner prune everything outside the window, and dropping old data becomes detaching a partition instead of a DELETE that leaves bloat behind.
• BRIN indexes for append-only, time-ordered tables. They store a summary per block range rather than an entry per row, so they are tiny, and they work precisely because inserts arrive in roughly timestamp order. On an events table they are often the cheapest win available.
• Precompute. A materialized view or a plain rollup table refreshed on a schedule turns "scan a year" into "read 365 rows". This is the single most effective Postgres analytics technique and the most often skipped.
• Move reporting to a read replica. Streaming replication is built in. It does not make the query faster, but it stops the query hurting your application, which is frequently the actual complaint.
• Then consider an extension. TimescaleDB is the mature option for time-series inside Postgres: hypertables handle chunking and partitioning, older chunks can be compressed into a columnar form, and continuous aggregates keep rollups up to date incrementally. Citus is the option when you want to distribute Postgres across nodes, and it also offers columnar storage for append-only tables. Check the current docs before you design around a version-specific detail.

If that list holds your latency where you need it, you are done, and you have avoided running two databases and the pipeline between them.

What changes when you adopt ClickHouse

None of it is a bug, and all of it surprises people arriving from Postgres.

ORDER BY is the design. It is the sort key and the sparse primary index in one. Queries that filter on its leading columns are close to instant; queries that filter on something else scan the table. Choose it for the questions you will actually serve. Partition coarsely — by month, usually — because many small partitions create many small parts and slow merges down.

There are no enforced keys. No unique constraint, no foreign key. A primary key in ClickHouse is an index, not a guarantee. ReplacingMergeTree removes duplicate rows with the same sorting key, but it does it during background merges, whenever those happen — so a query can see both the old and the new row until then. You handle that at read time, with FINAL or with an explicit newest-wins aggregation.

Updates and deletes are not row operations. ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE are asynchronous mutations that rewrite affected parts in the background. Lightweight DELETE marks rows so they stop being returned and clears them at merge time, and recent versions keep making updates lighter — read the docs for the version you run. What stays true is the mental model: changing rows in place is expensive here, so append a new version instead.

Small frequent inserts are the classic mistake. Each insert creates a part on disk. Insert one row at a time from six services and parts pile up faster than merges can combine them, until the server refuses writes with a "too many parts" error. Batch upstream, enable asynchronous inserts so the server does the batching, or stream through a queue.

Transactions are limited. An insert of a single block into a single table is atomic, and that is roughly the guarantee to plan around. Anything needing multi-statement transactional correctness belongs in Postgres.

Joins work, but the model rewards width. A normalised schema that Postgres plans beautifully will often be slower here than one wide table. Small dimension tables are better served by ClickHouse dictionaries than by repeated joins.

Some SQL Postgres accepts, ClickHouse rejects. I have written up one of mine: a window function nested inside an aggregate, which ClickHouse refuses outright, and which was silently falling back to a worse code path for weeks because the error was being swallowed. Expect to rewrite some queries rather than port them.

Here is the shape that makes the trade worth it — a sorted table, and a materialized view that aggregates on insert:

CREATE TABLE events
(
    tenant_id  LowCardinality(String),
    event_type LowCardinality(String),
    event_at   DateTime,
    user_id    UInt64,
    revenue    Decimal(18, 4)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_at)
ORDER BY (tenant_id, event_type, event_at);

CREATE TABLE events_daily
(
    tenant_id  LowCardinality(String),
    event_type LowCardinality(String),
    day        Date,
    events     UInt64,
    revenue    Decimal(38, 4)
)
ENGINE = SummingMergeTree
ORDER BY (tenant_id, event_type, day);

CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT
    tenant_id,
    event_type,
    toDate(event_at) AS day,
    count()          AS events,
    sum(revenue)     AS revenue
FROM events
GROUP BY tenant_id, event_type, day;

Nothing schedules that view. It fires on every insert into events, so the daily rollup is current by construction. One detail people miss: SummingMergeTree collapses rows during merges, which have not necessarily happened yet, so still write sum() and GROUP BY when you read events_daily. Treat the engine as an optimisation, not a promise of one row per key.

Running both, with Postgres as the source of truth

The standard architecture is boring and correct. Postgres keeps the data your application writes and corrects. An analytical copy lands in ClickHouse. Nothing is authored in ClickHouse.

Three ways to move it, in rough order of freshness:

1. Change data capture from logical replication. This is the near-real-time path. PeerDB and ClickPipes are built specifically for Postgres into ClickHouse; Debezium into Kafka, read by a ClickHouse Kafka table, is the general-purpose version. Watch the Postgres side: a replication slot whose consumer stops will hold WAL until the disk fills.
2. Batch loads. If hourly or nightly is fine, export to Parquet and read it in. Fewer moving parts, and easy to re-run.
3. Query Postgres directly for small things. For lookup and dimension tables, you may not need a pipeline at all:

SELECT *
FROM postgresql('pg-host:5432', 'app', 'plans', 'analytics_reader', 'password', 'public');

The PostgreSQL table engine and dictionaries with a Postgres source do the same job with different ergonomics. This is for small tables — every query goes over the wire to Postgres, so it is not a way to analyse your biggest table.

CDC streams give you a row per change, which is the case ReplacingMergeTree exists for. Keep the source's update timestamp as the version, and read the newest row per key:

CREATE TABLE orders
(
    order_id   UInt64,
    status     LowCardinality(String),
    amount     Decimal(18, 2),
    updated_at DateTime64(3)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

-- newest version per order, without waiting for a merge
SELECT
    order_id,
    argMax(status, updated_at) AS status,
    argMax(amount, updated_at) AS amount
FROM orders
GROUP BY order_id;

FINAL does the same job with less typing and more work at query time. Either way, decide which one you use before you build reports on the table, not after two numbers disagree.

That pattern also makes re-ingesting a day safe: the row replaces rather than duplicates, which is what makes a backfill safe to run at all.

When Postgres alone is enough

• The analytical tables are millions of rows, not billions, and growth is slow.
• Queries are mostly selective, and the aggregates you run are already covered by a rollup table.
• The reporting audience is small, and a read replica keeps it away from your users.
• Time-series is a feature of your application rather than a system of its own — TimescaleDB inside Postgres is likely a better trade than a second database.
• Correctness, constraints and transactions matter more than latency. That never stops being Postgres.

When ClickHouse earns its place

• Analytical tables in the hundreds of millions or billions of rows, still growing.
• Events, logs, metrics or market data arriving continuously and needing to be queryable in seconds.
• Dashboards your customers see, where a four-second chart reads as a broken product.
• Reporting load that is now a real risk to the application database.
• A pre-aggregation habit: you want rollups maintained on insert rather than by a scheduled job.

If you are also weighing a managed warehouse instead, ClickHouse vs Snowflake covers that side — the operating burden and the cost shape, rather than the engine.

One more option: maybe you need neither

If the analytical data fits on one machine and only a few people or one service query it, a server may be more than the problem needs. An in-process engine reads Parquet directly, has nothing to run, and costs nothing when idle — that is the case in DuckDB vs Postgres. DuckDB vs ClickHouse draws the same line from the other end: once many clients need fresh data at once, you want the server after all.

The order I would keep: fix Postgres first, reach for an in-process engine if the work is one program's job, and add ClickHouse when concurrency, volume and freshness all point the same way at once. Do you actually need a data warehouse? is that question one step earlier.

If you want someone who runs Postgres and ClickHouse side by side to look at your queries and say which fix yours needs — including the one where the answer is "none of this, tune what you have" — a Data Platform Audit is a week of that work and a written roadmap you keep. The scoping call below is free.