ClickHouse SummingMergeTree: Pre-Aggregation

Tip: Auto-Sum Columns

CREATE TABLE sales (
    product_id UInt64,
    date Date,
    amount UInt64,
    quantity UInt64
) ENGINE = SummingMergeTree()
ORDER BY (product_id, date);

Gotcha: Summing During Merge

Sums happen during merges, not on insert.

Tip: Specify Sum Columns

ENGINE = SummingMergeTree((amount, quantity))

Only sums specified columns. Others use last value.

Gotcha: Non-Key Columns

Columns not in ORDER BY and not specified for summing use the last value.

Tip: Query Pre-Aggregated Data

SELECT product_id, sum(amount) FROM sales GROUP BY product_id;

Even with SummingMergeTree, you still need GROUP BY.

Gotcha: Partial Sums

Before merge completes, you may see multiple rows per key.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

SummingMergeTree automatically aggregates numeric columns with the same sorting key during merges. I use it for counter data — page views, API calls, revenue — aggregated by time and dimension. Instead of querying 100 million raw rows for 'total revenue today', SummingMergeTree pre-aggregates to one row per combination of date and product_id. Storage drops by 100x and queries become instant. The trade-off: you lose individual row-level detail.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)