ClickHouse Isn't Postgres With Columns: Index Habits to Unlearn

Here’s a story that plays out over and over. Your Postgres handles transactions beautifully, but the quarterly sales report takes forty seconds and the executive dashboard loads like it’s 1999. The fix seems obvious: move the analytics to ClickHouse, a columnar database built for exactly this — big scans, big aggregations.

So you recreate your schema, almost line for line from Postgres. You add the indexes you always add. And nothing gets faster. Some queries are exactly as slow as before, your inserts have gotten worse, and a few of the indexes you created are quietly ignored — the query plan reads granules: 1000/1000, scanning everything.

The problem isn’t ClickHouse, and it isn’t you. It’s that ClickHouse is not “Postgres with the rows turned sideways.” It stores and finds data on a fundamentally different principle, and the instincts that made you good at Postgres are now working against you. Five of those instincts cause most of the pain. But to see why, you have to start with the one idea everything else hangs on.

The one thing to understand first: rows vs granules

In Postgres, a B-tree index is a precise instrument. Each leaf points at a specific row — a heap tuple, addressable down to its physical location. Ask for WHERE id = 42 and the index walks you straight to that one row without touching the rest of the table. Point lookups are the whole game, and Postgres is superb at them.

ClickHouse does not do this, because for analytics it would be pointless. It stores each column separately and sorts the whole table on disk by a key you choose. Over that sorted data it keeps a sparse primary index: not one entry per row, but one mark per granule — a block of roughly 8,192 rows. The index doesn’t find a row; it finds the granules that might contain your rows, with a fast binary search over the marks. Then ClickHouse reads those whole granules and scans them.

That single difference explains everything downstream. There is no such thing as a cheap single-row lookup in ClickHouse — the smallest unit it reads is a granule of ~8,192 rows. What it’s devastatingly good at instead is skipping: if your data is physically arranged so the rows you don’t want sit in granules you can prove are irrelevant, ClickHouse skips millions of rows without reading them. Your entire job, then, is arranging the data so that skipping works. Indexes you “add” are secondary; the physical sort order is the main event.

Hold that picture — point-lookup tool vs granule-skipping tool — and the five habits below stop being surprising.

Habit 1: reaching for CREATE INDEX

In Postgres, slow query on a column means CREATE INDEX idx ON t(col), done. So you do the same in ClickHouse — and it accepts the command, which is the cruel part. The query stays slow, the table grows, and the plan shows the column was scanned anyway.

CREATE INDEX in ClickHouse does not build a B-tree. There are no row-level B-trees in the engine. What you get is a data-skipping index — a small structure that can rule out whole granules but can never point at a single row. Used well, it’s useful; used as a B-tree replacement, it’s dead weight that just inflates the table with extra files.

The real instrument is the sort key, declared as ORDER BY, and the sparse primary index built on top of it. You don’t “add an index” for user_id; you sort the table by user_id so that filtering on it skips granules:

CREATE TABLE events (
    event_time DateTime,
    user_id    UInt64,
    action     String
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);

Now WHERE user_id = 123 is fast not because of an index you bolted on, but because the rows for user 123 are physically clustered together, so ClickHouse reads a handful of granules instead of the whole table.

Habit 2: treating the primary key as a unique id

In Postgres the primary key is sacred: a unique B-tree plus a constraint, one row per value, the identity of the record. So in ClickHouse you reflexively write PRIMARY KEY (id) — and your date-range queries crawl.

Two things to unlearn at once. First, ClickHouse’s primary key is not unique and enforces nothing. You can insert the same key a million times. Second, the primary key isn’t an identity — it’s the set of columns that go into the sparse index, and it’s normally a prefix of the ORDER BY. Its job is grouping, not uniqueness.

Lead with a high-cardinality column like id and you sabotage the whole mechanism: every granule holds a near-random spread of ids, so ClickHouse can’t prove any granule is skippable, and it falls back to reading everything. Choose the key from your query shape instead — the columns you filter on by equality, the ones that cluster data:

CREATE TABLE sales (
    sale_date  Date,
    product_id UInt64,
    amount     Decimal(10, 2),
    region     LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (region, sale_date, product_id)  -- group by region, then date
PRIMARY KEY (region, sale_date);          -- a shorter key for the sparse index

If you genuinely need a unique identifier, put it last in the key, where it won’t wreck clustering. And note LowCardinality(String) for region: when a string column has a limited set of distinct values, that type shrinks its footprint and speeds up processing — another Postgres habit (just use text/varchar) worth dropping.

Habit 3: ignoring the order of columns in ORDER BY

This is the subtle one, because the table looks indexed and still behaves like it isn’t. Say you wrote ORDER BY (product_id, sale_date) and your dashboards mostly filter WHERE sale_date BETWEEN ... AND ... without naming a product. The query plan shows granules: 1000/1000 — full scan — and you can’t see why. (You can confirm it directly with EXPLAIN indexes = 1, which prints how many granules survive each filtering step; when it’s the full count, your sort key is doing no pruning at all.)

The reason is that the sparse index prunes best on the leftmost columns of the sort key, especially under equality filters. Data is sorted by product_id first, so within any given range of granules the dates are scattered; ClickHouse can’t carve out your date range cleanly. The fix is to match the key to how you actually query: put the columns you filter by equality (=, IN) on the left, then the range columns (>, <, BETWEEN).

The effect of getting this right is not subtle. A logging table queried by recent time is a classic example: flipping ORDER BY (user_id, event_time) to ORDER BY (event_time, user_id) can turn a “last hour” query from a near-full scan into reading a few granules — routinely an order-of-magnitude difference. Same data, same hardware, same “index” — only the physical order changed.

Habit 4: partitioning like it’s Postgres

In Postgres, partitioning is a query-speed tool and you keep the partition count modest — they’re real child tables. So in ClickHouse you partition by day to “make it faster,” and either inserts collapse or the table is somehow still slow.

In ClickHouse a partition is a directory on disk, and each insert creates immutable parts that background merges stitch together. Partition too finely — by hour, by day on a busy table — and you get thousands of tiny parts, which bloats metadata, starves the merge process, and throttles inserts (the dreaded “too many parts”). Crucially, partitioning is not your speed tool. That’s what ORDER BY is for.

Partitioning in ClickHouse is for data management: dropping old data instantly, moving cold data to cheaper storage, applying TTLs. Pick a coarse granularity that matches your retention, usually months:

CREATE TABLE events (
    event_date Date,
    user_id    UInt64,
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)   -- by month: 12 partitions a year
ORDER BY (event_date, user_id);

-- retention is now a metadata operation, not a giant DELETE:
ALTER TABLE events DROP PARTITION '202501';

That DROP PARTITION drops a month in milliseconds — no row-by-row delete, no vacuum. That’s the payoff partitioning is actually for.

Habit 5: expecting a skip-index (or an MV) to act like a B-tree

The last trap has two flavors. You add a bloom_filter index on a url column for “fast exact lookups,” and it still reads most granules. Or you reach for a materialized view expecting it to behave like Postgres’s.

Skip-indexes work only at the granule level. A bloom filter can say “this granule definitely has no matching value, skip it” — and that’s powerful when the value is rare (a specific error code, a particular URL appearing in a handful of granules). But if the value is common, almost every granule contains it, the filter says “can’t skip” every time, and you’ve paid for an index that buys nothing. Selective filters: yes. Frequent values: no.

-- great for rare values, useless for common ones:
ALTER TABLE events
    ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 3;
-- 0.01 = 1% false-positive rate; GRANULARITY 3 = one filter per 3 granules

When you need fast aggregates over a common dimension, the answer isn’t an index — it’s either putting that column early in ORDER BY, or precomputing with a materialized view. But a ClickHouse MV is not Postgres’s or Oracle’s. It’s an insert-triggered pipeline: it runs only on rows newly inserted into the source table, there’s no automatic refresh, backfilling historical data is a manual job, and there’s no exactly-once guarantee, so a retried insert can duplicate. It’s powerful and it’s a footgun if you treat it like a cached query.

And there’s a specific landmine for aggregates that aren’t simple sums. To precompute a count of unique users, you must store partial aggregate states with AggregatingMergeTree and the -State/-Merge combinators — not a plain SummingMergeTree, which would silently give wrong answers:

CREATE MATERIALIZED VIEW daily_stats
ENGINE = AggregatingMergeTree
ORDER BY event_date
AS SELECT
    event_date,
    countState()        AS total_events,
    uniqState(user_id)  AS unique_users
FROM events
GROUP BY event_date;

-- read it back by MERGING the states:
SELECT event_date,
       countMerge(total_events) AS total_events,
       uniqMerge(unique_users)  AS unique_users
FROM daily_stats
GROUP BY event_date;

uniq is not additive — you can’t sum two days’ unique counts to get the two-day unique count — which is exactly why the state/merge dance exists. Reach for SummingMergeTree here and the numbers will be confidently wrong.

Two more reflexes worth dropping

Indexing is where most of the pain lives, but two non-index habits trip up Postgres refugees just as hard.

Inserting one row at a time. In Postgres, single-row INSERTs are perfectly normal. In ClickHouse they’re poison: every insert creates a new part, and a flood of tiny parts brings back the “too many parts” problem and drowns the background merges. ClickHouse wants big batches — thousands to tens of thousands of rows per insert. If your data genuinely trickles in a row at a time, don’t fight it by hand; let the server batch for you with async_insert, or stage writes through a Buffer table:

SET async_insert = 1, wait_for_async_insert = 1;
-- ClickHouse now groups many small inserts into larger parts on the server

Updating and deleting in place. There is no cheap UPDATE ... WHERE id = ... here. ALTER TABLE ... UPDATE and ... DELETE are mutations: asynchronous background jobs that rewrite whole parts, meant for occasional bulk corrections, not transactional row edits. If your workload needs frequent “change this one record,” that’s a signal the data belongs in Postgres — or that you want a pattern like ReplacingMergeTree, which collapses duplicates at merge time, instead of literal updates. ClickHouse is an analytics engine; pointwise mutation is the one thing it’s worst at.

The cheat sheet

If you remember nothing else, remember that almost every ClickHouse index decision is really a question about physical layout:

The thread running through all six rows is the same: ClickHouse rewards you for understanding how it physically stores and reads data, and punishes you for assuming it’s a row store wearing a column-store costume. You don’t win by adding more indexes. You win by sorting the data the way you query it, partitioning it the way you retire it, and reaching for skip-indexes and materialized views only where they genuinely fit. Bring that mindset instead of your Postgres reflexes, and the forty-second report becomes the sub-second one you came to ClickHouse for.