System Tables

Most databases make you reach for a separate CLI tool, an admin panel, or a vendor dashboard to see what the server itself is doing. ClickHouse exposes almost everything about its own internal state as ordinary tables in the system database — queryable with the same SQL you already know.

system.parts — the ground truth for storage

Every part described in Parts & Background Merges is a row here: its partition, size on disk, row count, and whether it's still active or waiting to be cleaned up after a merge.

SELECT
    table,
    partition,
    name,
    rows,
    formatReadableSize(bytes_on_disk) AS size,
    active
FROM system.parts
WHERE table = 'events' AND active
ORDER BY bytes_on_disk DESC
LIMIT 20;

A quick way to spot the "too many parts" problem before it becomes an error: count active parts per table and watch for numbers climbing into the thousands.

SELECT table, count() AS active_parts
FROM system.parts
WHERE active
GROUP BY table
ORDER BY active_parts DESC;

system.merges — merge pressure, live

Background merges don't run instantly or invisibly — while one is in progress, it shows up here with its progress percentage and which parts it's combining.

SELECT
    table,
    elapsed,
    round(progress * 100, 1) AS pct,
    num_parts,
    is_mutation
FROM system.merges;

An empty result isn't necessarily good news on a busy table — it can mean merges are keeping up comfortably, or that they've stalled. Cross-check against system.parts counts over time to tell the difference.

system.mutations — tracking UPDATE/DELETE progress

Every mutation you run is recorded here until it finishes, including whether it failed and why.

SELECT
    table,
    mutation_id,
    command,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE NOT is_done;

system.query_log — every query that ran, with a cost breakdown

When query_log is enabled (on by default in most setups), every query is logged with how long it took, how many rows and bytes it read, and how much memory it used — the natural follow-up to the EXPLAIN advice in Query Optimization: EXPLAIN tells you what a query plans to do; query_log tells you what it actually did.

SELECT
    query,
    query_duration_ms,
    read_rows,
    formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

system.processes — what's running right now

Currently executing queries, with their elapsed time and memory usage so far — and a query ID you can hand to KILL QUERY if one is misbehaving.

SELECT query_id, elapsed, memory_usage, query
FROM system.processes
ORDER BY elapsed DESC;

KILL QUERY WHERE query_id = '<id-from-above>';