← Back to the section

Almost every project starts with PostgreSQL — and rightly so. But sooner or later the moment arrives: analytical queries start to crawl, dashboards take minutes to load, and the team says "let's bolt on ClickHouse." Let's work out when that's genuinely needed, and when it's just extra complexity.

Why PostgreSQL starts to crawl on analytics in the first place

PostgreSQL stores data in rows. That's convenient: if you need one record, you read one row. If you need to update a field, you find the row and change it.

But picture an analytical query: "sum up total revenue across all regions for the last two years." PostgreSQL has to read every row in full — even if only three fields out of twenty are needed to answer. Over millions of rows this becomes slow and hammers the disk.

ClickHouse stores data by columns. For the same query it reads only the columns it needs and skips the rest. On top of that, data in columns compresses well — similar values sit next to each other. The result: analytical aggregates over billions of rows in seconds.

But that's no reason to replace PostgreSQL. ClickHouse handles point queries poorly ("give me this specific order by ID"), barely knows how to update individual records, and doesn't support full transactions. So the question isn't "which one to choose," it's "when to add a second database alongside."

Which queries go to PostgreSQL, and which to ClickHouse

PostgreSQL is OLTP (Online Transaction Processing). Its strengths:

  • point reads and writes ("add an order," "find a user by email");
  • transactions — several operations as a single unit;
  • updating and deleting records;
  • complex relationships between tables via JOIN.

ClickHouse is OLAP (Online Analytical Processing). Its strengths:

  • aggregates over huge volumes: GROUP BY region/month/category;
  • scanning hundreds of millions and billions of rows in seconds;
  • storing events and logs for years at minimal cost;
  • concurrent queries from analysts and BI tools.

A good sign of a "ClickHouse query": it looks at the whole period, computes aggregates, and no single row matters. A good sign of a "PostgreSQL query": you need a specific record, or the data has to be modified right away.

When PostgreSQL copes on its own

Before adding a new component, it's worth making sure PostgreSQL has actually hit its ceiling.

A few tens of millions of rows — that's not much for PostgreSQL. Proper indexes and partitioning by date solve most tasks.

The service builds the reports itself with only a handful of fixed queries — here PostgreSQL with a read replica is plenty. The replica takes the analytical queries and doesn't disturb the main database.

The data changes — orders, balances, stock levels. ClickHouse is built for events that are inserted once and never touched again; after-the-fact edits are painful in it.

The team is small, and there's no one to operate a second stateful component. That's a fair reason to postpone ClickHouse — the data pipeline, monitoring, backups — these are all new responsibilities.

Signs it's time to move to two databases

A few signals that PostgreSQL is starting to get in the way:

Data volume. Analytical tables have passed 50–100 million rows or grow by millions daily. Reports take minutes.

Query nature. Analytics means aggregates over a month or a year, funnels, percentiles, unique users. Not lists of records with filters, but "compute over a period."

Analysts get in production's way. The data team or BI tools run ad-hoc queries that load the database and slow down product users. Or those queries have to be banned to keep them out of the way.

Long history. You need to store events for years: clicks, views, statuses. ClickHouse compresses such data by 5–20×, and a long history stops being expensive.

Data freshness isn't critical. Dashboards showing data "as of five minutes ago" satisfy all consumers. ClickHouse receives data with a small lag through a pipeline; there's no instant synchronization.

How data gets from PostgreSQL into ClickHouse

This is the key question people often overlook. You can't just "write to both databases" — that's a recipe for them drifting out of sync.

The right scheme: a pipeline through a queue.

  1. The service writes only to PostgreSQL. Changes are recorded in an outbox table or through CDC (Change Data Capture).
  2. Kafka picks up events from PostgreSQL.
  3. A consumer reads from Kafka and writes to ClickHouse in batches.

The lag of such a pipeline is usually seconds or minutes. In exchange, the data stays consistent: if a write went through in PostgreSQL, it will end up in ClickHouse sooner or later. This is described in detail in the article on pipeline integration.

The ClickHouse schema is different from the PostgreSQL one: denormalized tables, events, pre-aggregates — the things that speed up analytical queries. Simply pouring the normalized PostgreSQL schema over "as is" and then being surprised by slow JOINs is a common mistake.

Common mistakes

"Replace PostgreSQL with ClickHouse." Moving orders, balances, and stock levels into ClickHouse — and discovering there are no transactions, no cheap updates, and no point reads. ClickHouse is an addition; the primary store stays in PostgreSQL.

Too early. Standing up a sharded ClickHouse cluster for five million rows that PostgreSQL would chew through with a single index. An extra component and pipeline — for a problem that doesn't exist yet.

Writing to both databases straight from the code. Two calls in a row — to PostgreSQL and to ClickHouse — inside a single action. If the second one fails, the data diverges. You need a pipeline through a queue.

Porting the normalized schema as is. ClickHouse can't do JOINs the way PostgreSQL can. The schema is designed around the analytical queries: denormalization, events, aggregates.

Mistaking a PostgreSQL replica for OLAP. A replica offloads read traffic from the main database, but it doesn't turn row-based storage into columnar. A heavy query over two years will be just as slow. A replica is an intermediate step, not the endpoint.

In short

  • PostgreSQL stores by rows — fast for point operations. ClickHouse stores by columns — fast for analytical aggregates.
  • ClickHouse doesn't replace PostgreSQL, it complements it: PostgreSQL holds the domain and transactions, ClickHouse holds events and analytics.
  • It's time to think about ClickHouse if analytical tables run into the hundreds of millions of rows, queries are aggregates over a long period, and analysts get in production's way.
  • Data is transferred through a pipeline (outbox → Kafka → consumer), not by double-writing from the code.
  • The ClickHouse schema is built around the analytical queries, not copied from PostgreSQL.
  • Running two databases means new operational responsibilities. If the team is small, it's better to stay on PostgreSQL longer with proper indexes and a replica.
  • How ClickHouse works — why it's so fast and what you pay for it.
  • Integration: the PG → Kafka → ClickHouse pipeline — outbox, CDC, and the consumer.
  • PostgreSQL or MongoDB — choosing the primary store.
  • Search: PG FTS or Elasticsearch — a similar fork for search workloads.