ClickHouse for analytics: when PostgreSQL is no longer enough

You need ClickHouse when analytical queries — reports, dashboards, aggregates over events — start slowing your main database or take minutes on hundreds of millions of rows. It is a column-oriented database that computes sums and groupings over huge tables in seconds, but it handles single-row updates and transactions poorly. So it sits next to PostgreSQL, not in its place.

Here is how to tell it is time, how to set up the data flow and which mistakes are common.

Why does PostgreSQL struggle with analytics?

PostgreSQL struggles with analytics because it stores data row by row and is optimised for many short operations on individual records. An analytical query does the opposite: it reads a few columns across millions of rows, and a row store has to pull everything off disk.

Signs analytics has outgrown your main database:

• reports and dashboards take tens of seconds or longer;
• heavy analytical queries slow the site down for users;
• the events table has grown to hundreds of millions of rows and keeps growing;
• you keep adding pre-aggregated tables and nightly recalculations just for reports.

Before adding a new database, make sure indexes and materialised views cannot solve the problem — see PostgreSQL indexes.

How does ClickHouse differ from PostgreSQL?

ClickHouse differs through columnar storage and query execution built for aggregation: it reads only the columns it needs, compresses them well and processes data in batches across all CPU cores. The price is limits on updates and transactions.

Criterion	PostgreSQL	ClickHouse
Workload type	Transactional (OLTP)	Analytical (OLAP)
Storage	Row-based	Columnar, heavily compressed
Aggregating billions of rows	Slow	Seconds or less
Updating and deleting single rows	Fast and familiar	Expensive, via asynchronous mutations
Transactions	Full	Limited
Inserts	Row by row is fine	Best in large batches
Typical data	Orders, users, payments	Events, logs, metrics, clicks

Photo: Unsplash

How do you store events in ClickHouse?

Store events in a MergeTree-family table with a sort key that matches your most common queries and monthly partitions. The right sort key affects query speed more than anything else.

CREATE TABLE events
(
    event_time  DateTime,
    event_name  LowCardinality(String),
    user_id     UInt64,
    order_id    Nullable(UInt64),
    amount      Decimal(18, 2),
    properties  String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_name, event_time, user_id);

-- Daily revenue for the quarter
SELECT toDate(event_time) AS day, sum(amount) AS revenue
FROM events
WHERE event_name = 'order_paid'
  AND event_time >= now() - INTERVAL 90 DAY
GROUP BY day
ORDER BY day;

Rules for event tables

1. Start the sort key with the columns you filter on most.
2. Insert in batches — thousands of rows at a time, not one by one.
3. Use LowCardinality for repetitive string values.
4. Set a TTL for raw events if you do not need them forever.

How do you get data from the main database into ClickHouse?

There are three ways: the application writes events straight to ClickHouse through a queue, the main database exports changes in periodic batches, or changes stream through change data capture (CDC). The choice depends on how fresh the reports must be.

Options:

• events from the application — via a job queue or broker, in batches; suits clicks, views and user actions;
• batch exports — hourly or daily from PostgreSQL; simple and reliable when reports need not be real-time;
• CDC — a stream of changes from PostgreSQL's log; fresh data, but harder to operate.

We covered reliable event delivery through queues in job queues in web applications.

What mistakes are common when adopting ClickHouse?

The most common mistake is using ClickHouse like a transactional database: updating individual records, inserting one row at a time, expecting strict consistency. The second is adopting it too early, when PostgreSQL handles the data volume easily.

Other issues:

• a sort key that does not match real filters, so queries scan the whole table;
• partitions that are too small — thousands of tiny parts slow everything down;
• no TTL, so the disk fills up with raw logs;
• duplicates from repeated delivery — you need deduplication or idempotent loading;
• reports built in both PostgreSQL and ClickHouse with numbers that disagree.

ClickHouse shines for product metrics — retention, funnels, cohorts; we covered which to track in product metrics after launch.

In short

• ClickHouse is a columnar analytics database: aggregates over billions of rows in seconds.
• It runs next to PostgreSQL, not instead of it: orders and users stay in the main database.
• Try indexes and materialised views in PostgreSQL first.
• Pick a sort key for common filters, insert in batches and set a TTL.
• Deliver data via queued events, batch exports or CDC, depending on required freshness.