ClickHouse Is Not Just for Big Data - When to Replace PostgreSQL for Analytics

Most teams do not choose ClickHouse. They run away from PostgreSQL until it stops working, panic, and reach for whatever managed analytics service their cloud vendor is advertising that quarter. That is an expensive way to make a decision.

Here is the part nobody tells you: ClickHouse is not a “big data” tool. You do not need a cluster, a data engineering team, or a petabyte of logs. A single machine with 16 cores and a couple hundred gigabytes of disk will answer aggregation queries over a few billion rows in milliseconds. The same query on Postgres takes seconds, saturates a core, and evicts your OLTP working set from cache while it runs.

The interesting question is not “is ClickHouse faster.” It obviously is for analytics. The question is where the crossover point sits, what you give up to cross it, and how you keep both databases in sync afterward. That is what this post is about.

Start with the naive setup, because everyone does

You have one Postgres instance. It holds users, orders, sessions, and an events table that logs everything the product does. The events table looked innocent at 10 million rows. Now it has 2 billion and the analytics dashboard runs this:

SELECT
  date_trunc('day', created_at) AS day,
  country,
  count(*) AS events,
  count(DISTINCT user_id) AS uniques
FROM events
WHERE created_at >= now() - interval '30 days'
GROUP BY 1, 2
ORDER BY 1;

On Postgres this query reads every row in the 30-day window. Even with a btree index on created_at, it still has to fetch full rows from the heap to get country, user_id, and do the count(DISTINCT). That means loading hundreds of columns worth of data off disk to answer a question that touches four fields.

The dashboard takes 8 seconds. During those 8 seconds, the query grabs shared buffers, pushes your hot OLTP pages out of cache, and your checkout endpoint gets slower because it is now hitting disk. This is the real cost of analytics on your transactional database. It is not just that the dashboard is slow. It is that the dashboard makes everything else slow.

The usual patches, and why they run out

The first instinct is to fix it inside Postgres, and for a while you can:

• Add a covering index. CREATE INDEX ON events (created_at, country, user_id) lets some of these run as index-only scans. Helps until the index itself is 40 GB and does not fit in RAM.
• Add a read replica. Moves the load off the primary. Does not make the query faster, and now you are paying for a second instance mostly to run one dashboard.
• Pre-aggregate into rollup tables. A cron job writes daily summaries. Works great until someone asks a question your rollup did not anticipate, and now you are backfilling.
• Reach for pg_partman or TimescaleDB. Partitioning by time genuinely helps time-range pruning and retention. But you are still row-oriented, so a GROUP BY country still reads every column of every matching row.

Every one of these is real engineering effort spent making a row store pretend to be a column store. At some point the honest move is to use an actual column store.

Why the row layout is the whole problem

Postgres stores a row contiguously. All of id, user_id, country, created_at, payload, ... for one event sit together on a page. That is exactly what you want for OLTP, where you fetch and update whole rows by primary key.

Analytics does the opposite. It reads a few columns across millions of rows. In a row store, to sum one column you drag every other column along for the ride through the disk and the CPU cache.

ClickHouse stores each column in its own file, sorted and compressed.

Row store (Postgres page):
[id|user|country|ts|payload][id|user|country|ts|payload][id|...]
  read one column -> touch every byte of every row

Column store (ClickHouse):
id:      [1][2][3][4]...            <- one file
user:    [7][7][9][2]...            <- one file
country: [IN][IN][US][IN]...        <- one file, compresses ~20x
ts:      [.....]...                 <- one file, delta-encoded
  read one column -> touch only that file

Two things fall out of this layout for free:

Compression is dramatic. A country column is a handful of distinct values repeated billions of times. Row stores compress poorly because adjacent bytes are unrelated. Column stores put like values next to like values, so LZ4 or ZSTD crush it. Real ratios of 10x to 30x on low-cardinality columns are normal. Less disk read means less time.

Only touched columns are read. The 30-day aggregation above reads created_at, country, and user_id. ClickHouse reads three column files and ignores the rest of the row entirely. Vectorized execution then processes those columns in tight batches that stay in CPU cache. The same query that took 8 seconds returns in 40 to 80 milliseconds on the same hardware.

The schema is where people trip

You do not translate a Postgres schema to ClickHouse one-to-one. The engine you pick and the sort key you choose matter more than any index decision you have ever made in Postgres.

Here is the events table as ClickHouse wants it:

CREATE TABLE events
(
    event_time   DateTime,
    event_date   Date DEFAULT toDate(event_time),
    user_id      UInt64,
    country      LowCardinality(String),
    event_type   LowCardinality(String),
    payload      String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, country, event_time)
SETTINGS index_granularity = 8192;

The parts that are unfamiliar coming from Postgres:

ORDER BY is not a query clause here, it is the physical sort order. ClickHouse has no b-tree index on arbitrary columns. It stores data sorted by the ORDER BY key and keeps a sparse index (one entry per 8192 rows by default). Queries that filter or group on the leading columns of the sort key are fast because ClickHouse skips whole blocks. Put your most common filter columns first. This is the single most important decision in the schema.

PARTITION BY is for data management, not query speed. Partition by month so you can drop old data with ALTER TABLE ... DROP PARTITION instantly instead of a slow DELETE. Do not over-partition. Partitioning by day on a high-traffic table creates thousands of tiny parts and hurts.

LowCardinality(String) is a dictionary encoding. For columns with under a few tens of thousands of distinct values (country, event_type, status), it stores an integer per row and a small dictionary. Faster and smaller than raw strings. Use it liberally.

There are no unique constraints and no real primary key enforcement. ClickHouse will happily store duplicate rows. INSERT is append-only and does not check anything. This is a genuine mental shift, covered below.

Rollups are a table engine, not a cron job

The rollup table you were hand-building in Postgres becomes a materialized view that maintains itself on insert:

CREATE TABLE events_daily
(
    event_date  Date,
    country     LowCardinality(String),
    event_type  LowCardinality(String),
    events      UInt64,
    uniques     AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (event_date, country, event_type);

CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT
    event_date,
    country,
    event_type,
    count() AS events,
    uniqState(user_id) AS uniques
FROM events
GROUP BY event_date, country, event_type;

Every insert into events updates the daily aggregate automatically. The dashboard query now hits events_daily, which has thousands of rows instead of billions, and returns instantly. uniqState stores a probabilistic sketch so count(DISTINCT user_id) stays cheap; you finalize it at read time with uniqMerge(uniques). No backfill cron, no drift.

The behavior differences that will bite you

Speed is the easy part. These are the things that surprise teams a month in.

Read that insert row again, because it is the most common way people misuse ClickHouse. It is built for large batched inserts. Sending one row per INSERT from your app in a loop will create a storm of tiny parts, trigger constant background merges, and tank performance. Buffer rows in your application or use the async insert setting, and flush in batches of thousands to tens of thousands.

De-duplication is the other trap. Because inserts are append-only and unenforced, a retried insert after a network blip silently duplicates data. The standard fixes: use ReplacingMergeTree with a version column to collapse duplicates during merges, or FINAL at query time (slower), or deduplicate at the ingestion layer with an idempotency key. Pick one before you go to production, not after your numbers look 3% too high and nobody knows why.

Keeping two databases in sync

You are not replacing Postgres. Orders, users, and anything you need to update transactionally stay there. ClickHouse becomes the analytics sink alongside it. The data flow looks like this:

                +--------------+
   app writes-->|  PostgreSQL  |  <-- OLTP: users, orders, sessions
                +------+-------+
                       | logical replication / CDC
                       v
                +--------------+
                |   Debezium   |  (or PeerDB, or a queue)
                +------+-------+
                       | batched
                       v
                +--------------+
   dashboards-->|  ClickHouse  |  <-- OLAP: events, rollups
                +--------------+

Three common ways to fill ClickHouse:

• Dual-write from the app. Write events to ClickHouse directly, batched, from your ingestion service. Simplest when events originate in your code and do not need to be transactional with Postgres.
• CDC from Postgres. Use Debezium or PeerDB to stream the Postgres write-ahead log into ClickHouse. Best when the source of truth is Postgres tables that already exist. PeerDB is purpose-built for the Postgres-to-ClickHouse path and handles the type mapping and batching for you.
• Kafka in between. Point a Kafka table engine in ClickHouse at a topic and let it consume. Good if you already run Kafka and want a buffer that absorbs bursts.

Whichever you pick, ClickHouse is eventually consistent with Postgres. Accept a few seconds of lag on the analytics side. If your dashboard needs to be transactionally consistent with a checkout that happened 200ms ago, you have a different and much harder problem, and the answer is usually “the dashboard does not actually need that.”

Where the migration actually pays off

Do not move to ClickHouse because it is fashionable. Move when you cross a real threshold. In my experience these are the signals that the migration pays for itself:

• Your largest analytics table is past roughly 100 million rows and growing, and full-table aggregations are part of the product, not a once-a-week report.
• Analytics queries are measurably slowing down your OLTP workload through cache pollution or replica load.
• You are maintaining hand-rolled rollup tables and backfill jobs just to keep dashboards responsive.
• Query patterns are aggregation-heavy and scan-heavy: GROUP BY, count, sum, time-bucketing, funnels, retention. Few single-row lookups.
• You care about cost. A single ClickHouse node routinely does work that would need a large managed data warehouse cluster, at a fraction of the bill.

And the signals that you should stay on Postgres:

• Under ~50 million rows in your biggest table. Postgres with a good covering index and pg_stat_statements discipline is completely fine, and a second database is operational overhead you do not need.
• Your analytics are actually lots of small point lookups and joins across many normalized tables. That is Postgres home turf.
• You need transactional consistency between the write and the read. ClickHouse cannot give you that.
• You do not have anyone who will own a second datastore. Two databases is two things to back up, monitor, and reason about.

Honest assessment

ClickHouse is not a Postgres replacement. It is a specialist that is world-class at one thing: scanning and aggregating huge columnar tables on modest hardware. Used for that, it is genuinely a step-change, and the “you need a cluster” reputation is wrong. A single box goes very far.

What works: aggregation queries over billions of rows in milliseconds, insane compression, self-maintaining rollups through materialized views, and a hardware bill that makes managed warehouses look silly.

What does not: point lookups, updates, deletes, transactions, and any workflow that assumes enforced constraints. If you fight the engine on these, you will lose, and you will blame ClickHouse for a problem you created by using it as an OLTP database.

What to actually do: keep Postgres as your source of truth. When your analytics table crosses roughly 100 million rows and starts hurting your transactional workload or forcing you into rollup-table maintenance, stand up a single ClickHouse node, stream data into it with CDC or batched writes, model the schema around the sort key and LowCardinality from day one, and pick a de-duplication strategy before launch. Do not do it before you feel the pain. Do not wait until you have panic-bought a warehouse cluster to escape it either.