ClickHouse for OLAP

What is OLAP?

OLAP stands for Online Analytical Processing. It describes workloads where the goal is to analyze large volumes of data rather than process individual transactions. Think dashboards, reports, aggregations over millions of rows, and time-series analysis.

The opposite is OLTP (Online Transaction Processing), which handles things like inserting a new order, updating a user's email, or deleting a single record. Most traditional databases (PostgreSQL, MySQL) are designed for OLTP first.

OLAP queries tend to share a few traits:

• They scan many rows but only a few columns
• They aggregate data with GROUP BY, COUNT, SUM, AVG
• They filter by time ranges or categorical dimensions
• They care more about read throughput than write latency

ClickHouse was built from the ground up for this kind of work.

Why columnar storage matters

Traditional row-oriented databases store each row as a contiguous block on disk. If you have a table with 50 columns and you run SELECT AVG(price) FROM orders, the database still reads all 50 columns from disk for every row. Most of that I/O is wasted.

Columnar databases flip this around. Each column is stored separately. When you query AVG(price), ClickHouse reads only the price column. The other 49 columns stay untouched on disk.

This has several benefits for analytical queries:

• Less I/O. Reading one column instead of 50 means roughly 1/50th of the disk reads.
• Better compression. Values in a single column tend to be similar (all integers, all timestamps, all from a small set of categories). Similar data compresses well. ClickHouse routinely achieves 5-10x compression ratios.
• CPU cache efficiency. Processing a contiguous array of integers is far faster than jumping between fields in a row-oriented layout. Modern CPUs process columnar data with SIMD instructions.

The tradeoff is that inserting or updating individual rows is more expensive. Each insert touches every column file. This is fine for OLAP workloads, where data arrives in batches and single-row updates are rare.

The MergeTree engine family

ClickHouse supports multiple table engines, but the MergeTree family is what you will use for almost everything in production. It is the backbone of ClickHouse's analytical performance.

Basic MergeTree

CREATE TABLE events
(
    event_date Date,
    user_id UInt64,
    event_type String,
    duration_ms UInt32,
    country LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_date);

MergeTree stores data in sorted "parts." When you insert data, ClickHouse writes a new part. In the background, it merges smaller parts into larger ones (hence the name). This merge process keeps data sorted and compacted.

The ORDER BY clause defines the primary sort order. This is the single most important decision in your schema design, because it determines which queries can skip large chunks of data.

ReplacingMergeTree

OLAP databases generally do not support traditional UPDATE operations. ReplacingMergeTree provides a workaround. It deduplicates rows with the same primary key during background merges.

CREATE TABLE user_profiles
(
    user_id UInt64,
    name String,
    email String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

When merges happen, ClickHouse keeps only the row with the highest updated_at for each user_id. Note that deduplication is eventual, not immediate. Until a merge runs, you may see duplicate rows. Use the FINAL keyword to get deduplicated results at query time:

SELECT * FROM user_profiles FINAL WHERE user_id = 12345;

SummingMergeTree

SummingMergeTree automatically sums numeric columns during merges for rows with the same primary key. This is useful for pre-aggregated counters.

CREATE TABLE daily_page_views
(
    date Date,
    page_path String,
    views UInt64,
    unique_users UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (date, page_path);

Insert raw events, and ClickHouse collapses them into summed rows over time:

-- These two inserts for the same (date, page_path)
INSERT INTO daily_page_views VALUES ('2026-03-01', '/home', 100, 80);
INSERT INTO daily_page_views VALUES ('2026-03-01', '/home', 50, 30);

-- After a merge, becomes one row: ('2026-03-01', '/home', 150, 110)

AggregatingMergeTree

AggregatingMergeTree takes this concept further. Instead of summing, it stores intermediate aggregation states. This is the engine behind ClickHouse's materialized views that maintain running aggregates.

CREATE TABLE sessions_agg
(
    date Date,
    country LowCardinality(String),
    sessions AggregateFunction(count, UInt64),
    avg_duration AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, country);

You insert into this table using -State combinator functions and query using -Merge combinators:

-- Inserting aggregate states
INSERT INTO sessions_agg
SELECT
    toDate(started_at) AS date,
    country,
    countState(session_id) AS sessions,
    avgState(duration_ms) AS avg_duration
FROM raw_sessions
GROUP BY date, country;

-- Querying merged results
SELECT
    date,
    country,
    countMerge(sessions) AS total_sessions,
    avgMerge(avg_duration) AS avg_duration
FROM sessions_agg
GROUP BY date, country;

Data types and schema design for analytics

Choosing the right data types in ClickHouse has a direct impact on storage size and query speed. Smaller types mean less data to read from disk, better compression, and faster scans.

Use the smallest integer type that fits

ClickHouse offers UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, and Int64. If a value fits in a UInt16 (0 to 65,535), don't use UInt64.

CREATE TABLE metrics
(
    timestamp DateTime,
    sensor_id UInt32,         -- Not UInt64, sensors won't exceed 4 billion
    temperature Int16,        -- Celsius * 100, range -327 to 327 degrees
    humidity UInt8,           -- 0-255, perfect for percentage values
    status Enum8('ok' = 1, 'warning' = 2, 'error' = 3)
)
ENGINE = MergeTree()
ORDER BY (sensor_id, timestamp);

LowCardinality for repeated strings

Strings with a limited set of values (country names, status codes, categories) should use LowCardinality(String). ClickHouse dictionary-encodes these values, storing integer IDs instead of full strings.

-- Instead of: country String
-- Use:
country LowCardinality(String)

This can reduce storage by 10x or more for columns like country codes, browser names, or event types.

Dates and times

Use Date for date-only values and DateTime for timestamps with second precision. If you need sub-second precision, use DateTime64(3) for milliseconds or DateTime64(6) for microseconds.

Avoid storing timestamps as strings or large integers. ClickHouse's native date types enable time-based functions and partitioning.

Nullable: use sparingly

Nullable(T) wraps any type to allow NULL values, but it adds storage overhead (an extra bitmask column) and makes queries slower. If you can represent missing data with a default value (0, empty string, a sentinel date), prefer that over Nullable.

-- Prefer this:
revenue Float64 DEFAULT 0

-- Over this:
revenue Nullable(Float64)

Denormalization

In OLTP databases, normalization reduces redundancy. In OLAP databases, joins are expensive. ClickHouse can do joins, but they are not its strongest feature. Denormalize your data where practical.

Instead of joining an orders table with a products table on every query, store the product name and category directly in the orders table. The extra storage cost is small compared to the join cost at query time.

-- OLTP-style (normalized): requires JOIN at query time
CREATE TABLE orders (order_id UInt64, product_id UInt64, quantity UInt32, ...);
CREATE TABLE products (product_id UInt64, name String, category String, ...);

-- OLAP-style (denormalized): faster queries, no JOIN needed
CREATE TABLE orders_denormalized
(
    order_id UInt64,
    product_id UInt64,
    product_name String,
    product_category LowCardinality(String),
    quantity UInt32,
    price Decimal(10, 2),
    order_date DateTime
)
ENGINE = MergeTree()
ORDER BY (product_category, order_date);

Ordering keys and primary keys

The ORDER BY clause in a MergeTree table definition is the most important performance lever you have. It determines how data is physically sorted on disk, and ClickHouse uses this sort order to skip irrelevant data during queries.

How data skipping works

ClickHouse divides each column into "granules" (default 8,192 rows). It maintains a sparse index that records the minimum and maximum values of the ORDER BY columns for each granule. When a query filters on an ORDER BY column, ClickHouse checks the index and skips granules that cannot contain matching rows.

For example, with ORDER BY (country, event_date):

-- This query skips all granules where country != 'US'
-- Then within US data, skips granules outside the date range
SELECT count()
FROM events
WHERE country = 'US'
  AND event_date >= '2026-01-01'
  AND event_date < '2026-02-01';

Choosing the right ordering key

Put the columns you filter on most frequently first. Within those, put low-cardinality columns before high-cardinality ones.

Good ordering key design:

ORDER BY (event_type, country, event_date, user_id)

A common mistake is putting a high-cardinality column first. If user_id is the first column, ClickHouse can only skip data efficiently when filtering by user_id. Queries that filter by event_type or country without specifying user_id will scan everything.

Primary key vs ordering key

In ClickHouse, PRIMARY KEY and ORDER BY are separate concepts, though they often match. The primary key defines which columns go into the sparse index. The ordering key defines the physical sort order. If you omit PRIMARY KEY, it defaults to ORDER BY.

You can set a shorter primary key than the ordering key to reduce index size:

CREATE TABLE events
(
    event_type LowCardinality(String),
    country LowCardinality(String),
    event_date Date,
    user_id UInt64,
    payload String
)
ENGINE = MergeTree()
ORDER BY (event_type, country, event_date, user_id)
PRIMARY KEY (event_type, country, event_date);
-- The sparse index only includes the first 3 columns
-- Data is still sorted by all 4

Partitioning

Partitioning splits a table into separate physical units based on a partition expression. The most common approach is to partition by month.

PARTITION BY toYYYYMM(event_date)

Each partition is a separate directory on disk. This gives you two main benefits:

1. Partition pruning. Queries that filter on the partition key skip entire partitions. A query for March 2026 data does not touch January or February partitions.
2. Data management. You can drop old partitions instantly, without a heavy DELETE operation.

-- Drop all data from January 2025 in one operation
ALTER TABLE events DROP PARTITION 202501;

Partitioning guidelines

Keep the number of partitions reasonable. Having thousands of partitions creates overhead. A good rule: aim for partitions that contain at least a few million rows each.

• Monthly partitioning works well for most use cases
• Daily partitioning is fine if you ingest tens of millions of rows per day
• Avoid partitioning by high-cardinality columns (like user_id)

Do not confuse partitioning with ordering. Partitioning controls physical file organization at a coarse level. Ordering controls row sorting within each partition.

Materialized views for pre-aggregation

Materialized views in ClickHouse work differently from materialized views in PostgreSQL or other databases. They are not periodic snapshots. They are triggers that transform and insert data incrementally as new rows arrive.

How materialized views work

When you insert data into a source table, ClickHouse runs the materialized view's SELECT query against the newly inserted block and inserts the result into a target table. This happens at insert time, not at query time.

-- Source table: raw event data
CREATE TABLE raw_events
(
    timestamp DateTime,
    event_type LowCardinality(String),
    user_id UInt64,
    duration_ms UInt32
)
ENGINE = MergeTree()
ORDER BY (event_type, timestamp);

-- Target table for the materialized view
CREATE TABLE hourly_event_stats
(
    hour DateTime,
    event_type LowCardinality(String),
    event_count UInt64,
    total_duration UInt64,
    unique_users UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (event_type, hour);

-- Materialized view: transforms inserts into raw_events
-- and writes aggregated rows into hourly_event_stats
CREATE MATERIALIZED VIEW hourly_event_stats_mv
TO hourly_event_stats
AS
SELECT
    toStartOfHour(timestamp) AS hour,
    event_type,
    count() AS event_count,
    sum(duration_ms) AS total_duration,
    uniq(user_id) AS unique_users
FROM raw_events
GROUP BY hour, event_type;

Now every insert into raw_events automatically populates hourly_event_stats. Querying the aggregated table is orders of magnitude faster than scanning raw data.

Using AggregateFunction states

For more complex aggregations (percentiles, approximate counts, averages), combine AggregatingMergeTree with -State and -Merge functions:

CREATE TABLE event_stats_agg
(
    date Date,
    event_type LowCardinality(String),
    count_state AggregateFunction(count, UInt64),
    uniq_users_state AggregateFunction(uniq, UInt64),
    p95_duration_state AggregateFunction(quantile(0.95), UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_type, date);

CREATE MATERIALIZED VIEW event_stats_agg_mv
TO event_stats_agg
AS
SELECT
    toDate(timestamp) AS date,
    event_type,
    countState() AS count_state,
    uniqState(user_id) AS uniq_users_state,
    quantileState(0.95)(duration_ms) AS p95_duration_state
FROM raw_events
GROUP BY date, event_type;

-- Query the aggregated data
SELECT
    date,
    event_type,
    countMerge(count_state) AS total_events,
    uniqMerge(uniq_users_state) AS unique_users,
    quantileMerge(0.95)(p95_duration_state) AS p95_duration_ms
FROM event_stats_agg
GROUP BY date, event_type
ORDER BY date DESC;

This pattern lets you maintain running percentiles, distinct counts, and other stateful aggregations without re-scanning raw data.

When to use materialized views

Materialized views are best when:

• You have a known set of queries or dashboards that run frequently
• Raw data is too large to aggregate on the fly at acceptable latency
• You can tolerate slight delays in aggregation (insert-time processing)

They are not a replacement for ad-hoc querying. Keep the raw data around for exploratory analysis, and use materialized views to speed up known, repeated query patterns.

Query optimization tips

ClickHouse is fast out of the box, but there are several ways to make your queries even faster.

Filter early and filter on ordering key columns

The biggest performance gains come from reducing the amount of data scanned. Filters on ordering key columns enable granule skipping. Filters on partition key columns enable partition pruning. Always include these filters when possible.

-- Good: filters on partition key (event_date) and ordering key (event_type)
SELECT count(), avg(duration_ms)
FROM events
WHERE event_date >= '2026-03-01'
  AND event_date < '2026-03-15'
  AND event_type = 'page_view';

-- Bad: no filter on ordering key, scans everything within the date range
SELECT count(), avg(duration_ms)
FROM events
WHERE duration_ms > 5000;

Select only the columns you need

Columnar storage means ClickHouse reads only the columns you reference. A SELECT * reads all columns. If you only need three columns, name them.

-- Reads 3 columns from disk
SELECT event_type, count(), avg(duration_ms)
FROM events
GROUP BY event_type;

-- Reads all columns from disk (wasteful)
SELECT *
FROM events
LIMIT 100;

Use PREWHERE for additional filtering

PREWHERE is a ClickHouse-specific clause that filters data before reading non-filtered columns. ClickHouse applies PREWHERE automatically in many cases, but you can force it:

SELECT user_id, event_type, duration_ms
FROM events
PREWHERE duration_ms > 1000
WHERE event_type = 'purchase';

ClickHouse first reads only the duration_ms column, eliminates rows below 1000ms, and only then reads user_id and event_type for surviving rows. This reduces I/O when the PREWHERE filter eliminates a large percentage of rows.

Approximate functions

For dashboards where exact precision is not critical, approximate functions are much faster:

-- Exact count of distinct users (expensive)
SELECT count(DISTINCT user_id) FROM events;

-- Approximate count (much faster, typically within 2% accuracy)
SELECT uniq(user_id) FROM events;

-- Approximate percentiles
SELECT quantile(0.95)(duration_ms) FROM events;

-- Approximate median
SELECT median(duration_ms) FROM events;

uniq() uses HyperLogLog under the hood. For even faster results with lower accuracy, try uniqHLL12() or uniqCombined().

Sampling

For exploratory queries on massive tables, ClickHouse supports sampling. You need to define a sampling key in the table:

CREATE TABLE events_sampled
(
    event_date Date,
    user_id UInt64,
    event_type String,
    duration_ms UInt32
)
ENGINE = MergeTree()
ORDER BY (event_type, user_id)
SAMPLE BY user_id;

-- Query 10% of the data
SELECT event_type, count(), avg(duration_ms)
FROM events_sampled
SAMPLE 0.1
GROUP BY event_type;

This reads approximately 10% of the rows, giving you faster results for exploratory analysis. ClickHouse adjusts the result to estimate the full-table answer.

Avoid large JOINs

ClickHouse can perform JOINs, but they work differently from traditional databases. The right side of a JOIN is loaded into memory by default. Large right-side tables can cause memory issues.

Strategies to minimize JOIN costs:

• Denormalize data at ingestion time (preferred)
• Use dictionaries for dimension lookups
• Put the smaller table on the right side of the JOIN
• Use JOIN with ANY instead of ALL when you only need one matching row

-- Dictionary-based lookup (fast, no JOIN needed)
SELECT
    event_type,
    dictGet('country_dict', 'country_name', country_id) AS country_name,
    count()
FROM events
GROUP BY event_type, country_name;

Use LIMIT and OFFSET wisely

ClickHouse processes data in a streaming fashion. Adding LIMIT to an aggregation query does not reduce scan time, because the aggregation must process all matching rows first. But LIMIT on a non-aggregated query can short-circuit early.

-- LIMIT helps here: ClickHouse stops after finding 10 rows
SELECT * FROM events WHERE user_id = 12345 LIMIT 10;

-- LIMIT does not reduce scan time here: all rows must be aggregated first
SELECT event_type, count() FROM events GROUP BY event_type LIMIT 10;

ClickHouse vs OLTP databases for analytics

If you are coming from PostgreSQL, MySQL, or another OLTP database, the shift to ClickHouse involves a few mental model changes.

What ClickHouse does better

A query that takes 30 seconds in PostgreSQL might take 200 milliseconds in ClickHouse, given the same data and hardware. The difference grows with data volume.

What OLTP databases do better

ClickHouse is not a replacement for your OLTP database. It is a complement. A common architecture has PostgreSQL or MySQL handling transactional workloads, with data replicated or streamed into ClickHouse for analytics.

Common migration patterns

When moving analytical workloads from an OLTP database to ClickHouse:

1. Identify slow analytical queries. Look for queries that scan large amounts of data, use GROUP BY extensively, or aggregate over time ranges.
2. Design ClickHouse tables around those queries. Choose ordering keys based on the most common WHERE clauses. Denormalize JOINs.
3. Set up data ingestion. Use tools like Kafka, Debezium, or custom ETL to move data from your OLTP database to ClickHouse.
4. Create materialized views for your most common dashboard queries.
5. Keep raw data in ClickHouse for ad-hoc analysis, with TTL policies to expire old data automatically.

Common OLAP patterns in ClickHouse

Here are several query patterns that show up frequently in analytical workloads. These examples demonstrate how to structure common analyses.

Time-series aggregation

-- Hourly metrics over the last 7 days
SELECT
    toStartOfHour(timestamp) AS hour,
    count() AS events,
    uniq(user_id) AS unique_users,
    avg(duration_ms) AS avg_duration,
    quantile(0.95)(duration_ms) AS p95_duration
FROM events
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

Funnel analysis

-- Conversion funnel: view -> add_to_cart -> purchase
SELECT
    level,
    count() AS users
FROM
(
    SELECT
        user_id,
        windowFunnel(86400)(timestamp,
            event_type = 'product_view',
            event_type = 'add_to_cart',
            event_type = 'purchase'
        ) AS level
    FROM events
    WHERE event_date >= '2026-03-01'
      AND event_date < '2026-03-15'
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;

The windowFunnel function tracks how far each user progressed through the funnel within a 24-hour window (86400 seconds). Level 0 means the user did not trigger the first event. Level 3 means they completed all three steps.

Retention analysis

-- Weekly retention: what percentage of users from week 0 came back in weeks 1-4?
WITH first_seen AS (
    SELECT
        user_id,
        toMonday(min(event_date)) AS cohort_week
    FROM events
    GROUP BY user_id
)
SELECT
    cohort_week,
    count(DISTINCT fs.user_id) AS cohort_size,
    countIf(DISTINCT e.user_id,
        toMonday(e.event_date) = cohort_week + INTERVAL 1 WEEK
    ) AS week_1,
    countIf(DISTINCT e.user_id,
        toMonday(e.event_date) = cohort_week + INTERVAL 2 WEEK
    ) AS week_2,
    countIf(DISTINCT e.user_id,
        toMonday(e.event_date) = cohort_week + INTERVAL 3 WEEK
    ) AS week_3,
    countIf(DISTINCT e.user_id,
        toMonday(e.event_date) = cohort_week + INTERVAL 4 WEEK
    ) AS week_4
FROM first_seen fs
LEFT JOIN events e ON fs.user_id = e.user_id
WHERE cohort_week >= '2026-02-01'
GROUP BY cohort_week
ORDER BY cohort_week;

Top-N analysis

-- Top 10 pages by unique visitors, with breakdown by country
SELECT
    page_path,
    uniq(user_id) AS unique_visitors,
    topK(5)(country) AS top_countries,
    avg(load_time_ms) AS avg_load_time
FROM page_views
WHERE event_date >= today() - 30
GROUP BY page_path
ORDER BY unique_visitors DESC
LIMIT 10;

The topK(5) function returns the 5 most frequent values in the country column for each group.

Rolling averages

-- 7-day rolling average of daily revenue
SELECT
    date,
    revenue,
    avg(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM
(
    SELECT
        toDate(order_timestamp) AS date,
        sum(amount) AS revenue
    FROM orders
    WHERE date >= '2026-01-01'
    GROUP BY date
)
ORDER BY date;

Sessionization

-- Group events into sessions with a 30-minute inactivity timeout
SELECT
    user_id,
    session_id,
    min(timestamp) AS session_start,
    max(timestamp) AS session_end,
    dateDiff('second', min(timestamp), max(timestamp)) AS session_duration_seconds,
    count() AS events_in_session
FROM
(
    SELECT
        user_id,
        timestamp,
        event_type,
        sum(new_session) OVER (
            PARTITION BY user_id
            ORDER BY timestamp
        ) AS session_id
    FROM
    (
        SELECT
            user_id,
            timestamp,
            event_type,
            if(
                dateDiff('minute',
                    lagInFrame(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp),
                    timestamp
                ) > 30
                OR lagInFrame(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) IS NULL,
                1, 0
            ) AS new_session
        FROM events
        WHERE event_date = today()
    )
)
GROUP BY user_id, session_id
ORDER BY user_id, session_start;

TTL: automatic data lifecycle management

ClickHouse can automatically delete or move data based on age. This is essential for OLAP systems where you want to keep recent data hot and expire old data.

CREATE TABLE events_with_ttl
(
    timestamp DateTime,
    event_type String,
    user_id UInt64,
    data String
)
ENGINE = MergeTree()
ORDER BY (event_type, timestamp)
TTL timestamp + INTERVAL 90 DAY;

Rows older than 90 days are deleted during background merges. You can also use TTL to move data between storage tiers:

-- Move data to cold storage after 30 days, delete after 365 days
CREATE TABLE events_tiered
(
    timestamp DateTime,
    event_type String,
    data String
)
ENGINE = MergeTree()
ORDER BY (event_type, timestamp)
TTL
    timestamp + INTERVAL 30 DAY TO VOLUME 'cold',
    timestamp + INTERVAL 365 DAY DELETE;

Inserting data efficiently

ClickHouse performs best with batch inserts. Inserting one row at a time creates many small parts that ClickHouse must merge later, causing overhead. Aim for inserts of at least 1,000 rows, ideally 10,000 to 100,000 at a time.

-- Good: batch insert
INSERT INTO events VALUES
    ('2026-03-14 10:00:00', 'page_view', 1001, 120),
    ('2026-03-14 10:00:01', 'click', 1002, 45),
    ('2026-03-14 10:00:01', 'page_view', 1003, 200),
    -- ... thousands more rows
;

-- Avoid: single-row inserts in a loop
INSERT INTO events VALUES ('2026-03-14 10:00:00', 'page_view', 1001, 120);
INSERT INTO events VALUES ('2026-03-14 10:00:01', 'click', 1002, 45);

For streaming use cases, consider using a buffer table that batches inserts automatically:

CREATE TABLE events_buffer AS events
ENGINE = Buffer(currentDatabase(), events,
    16,     -- number of buffers
    10, 100, -- min/max seconds before flush
    10000, 1000000, -- min/max rows before flush
    10000000, 100000000 -- min/max bytes before flush
);

-- Insert into the buffer; it flushes to the main table automatically
INSERT INTO events_buffer VALUES (...);

Monitoring query performance

ClickHouse provides tools to understand how your queries perform. Tools like DB Pro give you a visual interface for monitoring database performance, but you can also query ClickHouse's system tables directly.

The query log

-- Find the slowest queries in the last hour
SELECT
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    result_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

EXPLAIN for query analysis

EXPLAIN PLAN
SELECT event_type, count()
FROM events
WHERE event_date = '2026-03-14'
GROUP BY event_type;

-- For more detail on data skipping:
EXPLAIN ESTIMATE
SELECT count()
FROM events
WHERE event_type = 'purchase'
  AND event_date >= '2026-03-01';

System metrics

-- Current merges in progress
SELECT
    table,
    elapsed,
    progress,
    num_parts,
    result_part_name
FROM system.merges;

-- Table sizes and row counts
SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS total_rows,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

Putting it all together

A well-designed ClickHouse OLAP setup follows a pattern:

1. Ingest raw data into a MergeTree table with a carefully chosen ordering key and monthly partitioning.
2. Create materialized views that pre-aggregate data for your most common queries and dashboards.
3. Use appropriate data types. Favor small integers, LowCardinality strings, and native date types.
4. Set TTL policies to manage data lifecycle automatically.
5. Batch your inserts. Avoid single-row inserts. Use buffer tables or external batching if needed.
6. Monitor performance using the query log and system tables. Look for queries that scan too many rows and add or adjust ordering keys.

ClickHouse handles OLAP workloads at a speed that traditional databases cannot match. By understanding how columnar storage, ordering keys, and materialized views work together, you can build analytical systems that return answers in milliseconds over billions of rows.