ClickHouse Explained: Why Columnar Analytics Beats Postgres at Scale

Every analytics feature starts out fine. A count here, a GROUP BY there, and Postgres handles it without complaint. Then the events table crosses a few hundred million rows, someone asks for a year over year breakdown by country, and the planner quietly gives up. Queries time out. The connection pool drains. The dashboard becomes the tab nobody opens.

The instinct is to add an index. A composite index, then a partial one, then a covering one. It buys a few months. The reason it eventually stops working is structural. A B-tree is built to find a handful of rows fast, not to help you read a hundred million of them. Once a query touches most of the table, the index is overhead, not acceleration. Your problem is the storage layout, not the absence of the right index.

ClickHouse is an open source, column-oriented OLAP database built for exactly that shape of problem: billions of rows, wide tables, and aggregations that need to come back in under a second. It is not a faster Postgres. It is a different set of tradeoffs for a different question, and knowing which question you are asking is most of the work.

1. Columnar Storage: Read Only What You Need

In a row store, every column of a record sits together on disk. That is perfect when you need the whole record, which is what an application does all day. It is wasteful when you need one column, because the smallest unit of IO still drags along every other field in the row.

ClickHouse inverts the layout. Each column is stored in its own set of files, sorted and compressed independently. A SUM(revenue) over a table with 45 columns reads exactly one column and never touches the other 44. As tables get wider, that gap grows rather than shrinks.

Compression compounds the win. Values in a single column share a type and tend to repeat, so they compress far better than mixed row data. ClickHouse defaults to LZ4 for speed and offers ZSTD when you want a higher ratio on colder data. Better compression is not just cheaper storage. It means less data crossing the disk and memory bus, which is where scan time actually goes.

On top of that sits vectorized execution. Instead of evaluating one row at a time through a chain of function calls, ClickHouse processes blocks of column values in tight loops that stay in CPU cache and map cleanly onto SIMD instructions. Columnar storage delivers the data in exactly the shape the CPU wants to consume it.

2. MergeTree: The Engine Behind the Speed

The MergeTree family is where most of the engineering lives. An insert never updates anything in place. It writes a new immutable part with the rows already sorted by the table's ORDER BY key.

That ORDER BY clause is the primary key, and it is a sparse index. ClickHouse does not store an entry per row. It stores one mark per granule (8192 rows by default), which keeps the index small enough to live in memory even for very large tables. A query filtering on the leading key columns skips entire ranges of granules before reading a single byte of column data.

Background merges continuously combine small parts into larger sorted ones, much like compaction in an LSM tree. That keeps the part count bounded and reads efficient, and it is the reason inserts should arrive in large batches rather than a trickle of single rows.

PARTITION BY adds a coarser pruning layer and, just as usefully, turns dropping old data into a metadata operation instead of a mass delete. TTL clauses let the table expire or tier its own data, so retention policy lives in the schema rather than in a cron job.

1CREATE TABLE events
2(
3    site_id      UInt32,
4    timestamp    DateTime,
5    event_name   LowCardinality(String),
6    user_id      UUID,
7    path         String,
8    country      LowCardinality(String),
9    duration_ms  UInt32,
10    revenue      Decimal(18, 4)
11)
12ENGINE = MergeTree
13PARTITION BY toYYYYMM(timestamp)
14ORDER BY (site_id, timestamp)
15TTL timestamp + INTERVAL 18 MONTH DELETE
16SETTINGS index_granularity = 8192;

Two decisions in that table matter more than everything else. The ORDER BY should mirror how you actually filter, with the lower cardinality column first. And LowCardinality(String) turns repetitive string columns into dictionary encoded ones, which compress smaller and group dramatically faster.

3. Materialized Views and Aggregating Tables

A materialized view in Postgres is a snapshot you refresh on a schedule. In ClickHouse it is closer to an insert trigger. Every block written to the source table flows through the view's SELECT, and the result lands in a target table right away.

Pair that with an aggregating engine and you get rollups that are always current. SummingMergeTree collapses rows sharing a sorting key by adding their numeric columns. AggregatingMergeTree goes further and stores intermediate aggregate states, produced by functions like countState and uniqState, which merge correctly as parts are combined in the background.

1CREATE TABLE events_daily
2(
3    site_id     UInt32,
4    day         Date,
5    event_name  LowCardinality(String),
6    hits        AggregateFunction(count),
7    visitors    AggregateFunction(uniq, UUID),
8    revenue     AggregateFunction(sum, Decimal(18, 4))
9)
10ENGINE = AggregatingMergeTree
11PARTITION BY toYYYYMM(day)
12ORDER BY (site_id, day, event_name);
13
14CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
15SELECT
16    site_id,
17    toDate(timestamp) AS day,
18    event_name,
19    countState()       AS hits,
20    uniqState(user_id) AS visitors,
21    sumState(revenue)  AS revenue
22FROM events
23GROUP BY site_id, day, event_name;

Reading it back means merging those states, with countMerge(hits) and uniqMerge(visitors) grouped by day. The raw events table stays available for ad hoc questions, while the dashboard queries a table that is orders of magnitude smaller. That combination is what quietly deletes your nightly batch job, along with the data lag that came with it.

4. Wiring ClickHouse Into a Next.js Dashboard

The official @clickhouse/client package targets the Node runtime, which means route handlers and server components, not the browser. Keep the URL, username, and password in server only environment variables. Nothing prefixed with NEXT_PUBLIC, ever, because a ClickHouse endpoint is a full query interface, not a read only API.

Build queries with query parameters rather than string interpolation. ClickHouse supports typed placeholders like {siteId: UInt32}, passed separately as query_params. You get the same protection prepared statements give you in Postgres, plus a type check on the way in.

Ask for JSONEachRow. It streams one JSON object per row, which is cheap to parse and maps straight onto a typed array. For dashboards that do not need live numbers, export a revalidate value and let the Next.js data cache absorb the repeat traffic instead of your cluster.

1import { createClient } from '@clickhouse/client'
2import { NextResponse } from 'next/server'
3
4export const revalidate = 60
5
6const clickhouse = createClient({
7  url: process.env.CLICKHOUSE_URL,
8  username: process.env.CLICKHOUSE_USER,
9  password: process.env.CLICKHOUSE_PASSWORD,
10  database: 'analytics',
11})
12
13type DailyRow = {
14  day: string
15  hits: string
16  visitors: string
17}
18
19export async function GET(request: Request) {
20  const siteId = Number(new URL(request.url).searchParams.get('siteId'))
21
22  if (!Number.isInteger(siteId)) {
23    return NextResponse.json({ error: 'Invalid siteId' }, { status: 400 })
24  }
25
26  const resultSet = await clickhouse.query({
27    query: `
28      SELECT
29        day,
30        countMerge(hits)    AS hits,
31        uniqMerge(visitors) AS visitors
32      FROM events_daily
33      WHERE site_id = {siteId: UInt32}
34        AND day >= today() - {days: UInt16}
35      GROUP BY day
36      ORDER BY day
37    `,
38    query_params: { siteId, days: 30 },
39    format: 'JSONEachRow',
40  })
41
42  const rows = await resultSet.json<DailyRow>()
43
44  return NextResponse.json({ rows })
45}

The same client works inside a server component if you want to render the chart on the server and skip the round trip entirely. Route handlers earn their place when the client needs to change filters and date ranges without a full navigation.

5. Where ClickHouse Is the Wrong Choice

ClickHouse is fast partly because it refuses to do things Postgres treats as mandatory. Those refusals are the tradeoffs, and they are worth knowing before you commit.

• No transactional guarantees you can lean on. There are no multi-table ACID transactions, no foreign keys, no row level locking. Writes are atomic per inserted block, and that is the extent of it.
• Deduplication is eventual. ReplacingMergeTree removes duplicates during background merges, on a schedule you do not control. Until that happens you need FINAL or a deduplicating aggregation to read correct results.
• Updates and deletes are expensive. ALTER TABLE UPDATE and ALTER TABLE DELETE are asynchronous mutations that rewrite entire parts. Lightweight deletes soften the cost, but nothing here behaves like a row level UPDATE in an OLTP database.
• Joins need design. Joins work, but the right side of a hash join is held in memory, and large distributed joins punish naive queries. Denormalize at write time, or push lookups into dictionaries.
• Tiny inserts hurt. Thousands of single row inserts per second create thousands of parts and the merge scheduler falls behind. Batch on the client, or use asynchronous inserts or a Buffer table.

None of this makes ClickHouse fragile. It makes it specialized. Keep Postgres as the system of record for users, billing, orders, and anything you need to update in place. Stream the immutable event firehose into ClickHouse and let each database do the job it was designed for.

The Verdict: Pick the Storage Layout That Matches the Question

Pick your storage by the question you are asking. If the question is what the current state of one record is, you want a row store with real transactions. If the question is what happened across every record over the last 18 months, you want a column store that can scan and aggregate without apology.

The migration that works is rarely a migration. It is an addition. Postgres keeps the truth, a queue or a batched writer moves events across, and ClickHouse answers the questions that used to time out. Your dashboards get faster, and your primary database stops carrying analytics load it was never built for.

Analytics features have a way of becoming the product. Getting the storage layout right early is what lets you say yes to the next twenty questions your users ask, without another round of index archaeology.