← 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.

the same query reads a different amount from disk SELECT region, sum(amount) FROM orders GROUP BY region PostgreSQL — stores by rowsidregionamountstatuscreated7msk120paid04-018spb340new04-019msk90paid04-0210spb55paid04-03unit of storage — a rowread 20 cells of 20 ClickHouse — stores by columnsidregionamountstatuscreated7msk120paid04-018spb340new04-019msk90paid04-0210spb55paid04-03unit of storage — a columnread 8 cells of 20 SELECT * FROM orders WHERE id = 9a row sits together → 1 readcolumns sit apart → 5 readsaggregates are cheaper by column, point lookups by row

The difference is not speed as such, it is the unit of storage. PostgreSQL puts a whole row on disk, so an aggregate over two columns still lifts all twenty cells. ClickHouse puts a whole column on disk: it reads only region and amount and touches eight cells out of twenty. For a point lookup the picture is mirrored — a row comes back in a single read, while column storage has to collect it from five separate places.

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 an analytical query — "total revenue by region over the last two years" — makes PostgreSQL read every row in full, even though only three fields out of twenty are needed for the answer. Over millions of rows that is slow and hammers the disk.

ClickHouse stores data by columns: for the same query it reads only the ones 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.

A small program shows both sides of the trade: the answer is the same, the amount read is not.

live example

import java.util.LinkedHashMap;
import java.util.Map;

public class StorageCost {
    public static void main(String[] args) {
        String[] header = {"id", "region", "amount", "status", "created"};
        String[][] rows = {{"7", "msk", "120", "paid", "04-01"},
                {"8", "spb", "340", "new", "04-01"},
                {"9", "msk", "90", "paid", "04-02"},
                {"10", "spb", "55", "paid", "04-03"}};
        Map<String, Integer> revenue = new LinkedHashMap<>();
        for (String[] row : rows) {
            revenue.merge(row[1], Integer.parseInt(row[2]), Integer::sum);
        }
        System.out.println("sum(amount) GROUP BY region -> " + revenue);
        System.out.println("cells read: by rows " + rows.length * header.length
                + ", by columns " + rows.length * 2);
        System.out.println("point SELECT * WHERE id = 9: by rows 1 read, by columns "
                + header.length);
    }
}
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

The last line of the output is the other side of that trade: row storage hands back a point lookup in one read, while column storage has to reassemble the record from five separate places.

So this is no reason to replace PostgreSQL. ClickHouse handles point queries poorly ("give me this specific order by ID"), updates and deletes individual records grudgingly, and doesn't support full transactions. 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.

The difference shows up in two queries against one table. The first is textbook OLAP: it aggregates over the whole period, and no single row matters in the answer.

live example

SELECT status,
       count(*) AS orders,
       round(sum(total_amount), 2) AS revenue
FROM orders
GROUP BY status
ORDER BY revenue DESC;
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

The second is textbook OLTP: one record is needed, in full and right now.

live example

SELECT id, customer_id, status, total_amount
FROM orders
WHERE id = 'ord-09';
Run

Running examples is part of paid access. There the same code runs inside the article: editor, run and check next to the paragraph. Three free days →

The first reads two or three columns across the whole table — that's the one worth moving to ClickHouse. The second fetches a row by index and stays in PostgreSQL.

When PostgreSQL copes on its own

First make 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: it 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 data store. That's a fair reason to postpone ClickHouse — the data pipeline, monitoring, backups 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. Not lists of records with filters, but aggregates over a month or a year: funnels, percentiles, unique users.

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.

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

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. A separate component reads those changes from PostgreSQL and publishes them to Kafka: either Debezium, which follows the replication log, or your own service that drains the outbox table.
  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. The details are in the article on ClickHouse from Java and Spring Boot.

The ClickHouse schema is different from the PostgreSQL one: denormalized tables, events, pre-aggregates — the things that speed up analytical queries.

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.

Too early. Standing up a sharded ClickHouse cluster for five million rows that PostgreSQL would chew through with a single index.

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.

Porting the normalized schema as is. ClickHouse does have JOINs, but they cost more than in PostgreSQL. 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.