ClickHouse is a column-oriented DBMS optimised for OLAP queries — 100-1000x faster than row-oriented DBs for analytics.
-- ClickHouse MergeTree engine family
CREATE TABLE orders (
order_id UInt64,
customer_id UInt64,
product_id UInt32,
status LowCardinality(String),
amount Decimal64(2),
created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (customer_id, created_at) -- primary key + sort key
TTL created_at + INTERVAL 2 YEAR; -- auto-delete old data
-- ReplacingMergeTree (deduplication for upserts)
ENGINE = ReplacingMergeTree(updated_at) -- keep row with max updated_at
-- SummingMergeTree (automatic aggregation)
ENGINE = SummingMergeTree(amount)
-- Materialized views for real-time aggregation
CREATE MATERIALIZED VIEW daily_revenue
ENGINE = SummingMergeTree()
ORDER BY (date, product_id)
AS SELECT
toDate(created_at) AS date,
product_id,
sum(amount) AS revenue
FROM orders GROUP BY date, product_id;
-- Inserts to orders auto-update daily_revenue!