Learning Objectives
- Explain why ClickHouse exposes its own internal state as ordinary, queryable tables instead of a separate monitoring API
- Read system.tables to determine a table's engine, size, and definition
- Read system.processes to see what queries are running right now, and how much memory or time they're consuming
- Predict when a just-run query will and won't show up in system.query_log, based on how ClickHouse buffers and flushes it
Why This Matters
Every database eventually forces the same question on you: what is this server actually doing right now, and what did it just do? A query is slow — is it stuck, or almost done? A table is huge — which engine is it, and how did it get that big? A report ran at 2am and something looks wrong — what did it actually read? Most databases answer these questions with a bolted-on toolbox: a separate admin CLI, a proprietary monitoring dashboard, a special "explain" mode with its own syntax. ClickHouse answers them with SELECT. Once you know the three tables in this lesson, you can answer almost any "what is this server doing" question with the same SQL you already know — no new tool to learn, no context switch. That's the mechanism this lesson builds a correct mental model of, because every later lesson on debugging and performance in this course assumes you already have it.
A database that reports on itself
Most database systems keep their own operational state — running queries, table metadata, execution history — behind a wall. You get at it through a special admin command, a separate monitoring product, or a vendor-specific API that looks nothing like the SQL you write against your own data. That's a reasonable design, but it means "how do I see what's running" and "how do I query my data" are two different skills.
ClickHouse doesn't draw that wall. It stores its own internal state — the queries running this second, the queries that ran an hour ago, the definition and size of every table on the server — in ordinary tables, collected in a database named system. You read them with the exact same SELECT you'd use against any table you created yourself. You can filter them with WHERE, aggregate them with GROUP BY, and join them against each other, because to the query engine they aren't special — they're just tables that happen to describe the server instead of your application.
This isn't a minor convenience. It means the skill you're building in this entire course — reading data with SQL — is the same skill you use to operate ClickHouse. There's no second interface to learn for "now show me what's slow."
Not every table in system behaves the same way underneath, though, and the difference matters for predicting what you'll see:
- Some system tables — like
system.processesandsystem.tables— are rebuilt from the server's live in-memory state every time you query them. They reflect this instant, and nothing about them persists if the server restarts. - Others — like
system.query_log— are regular on-disk tables (backed by the same MergeTree family you'll study in the next module) that ClickHouse writes to over time, so they persist across restarts and can grow large enough that you'll eventually want a retention policy for them.
That distinction — "live snapshot" versus "persisted history" — is the thread connecting the three tables below.
system.tables — the catalog
system.tables answers "what tables exist, and what are they?" One row per table the server knows about, in every database, with columns describing its engine, its size, and its definition.
SELECT
database,
name,
engine,
total_rows,
total_bytes
FROM system.tables
WHERE database = 'default'
ORDER BY total_bytes DESC;
┌─database─┬─name────────────┬─engine────┬─total_rows─┬─total_bytes─┐
│ default │ page_views │ MergeTree │ 842991213 │ 918234112 │
│ default │ sessions │ MergeTree │ 12933012 │ 41822011 │
│ default │ raw_events │ MergeTree │ 50213890 │ 12009331 │
└──────────┴─────────────────┴───────────┴────────────┴─────────────┘
Notice what you're not doing here: you're not running SELECT count() FROM page_views three times and cross-referencing disk usage by hand. total_rows and total_bytes (the table's compressed size on disk) are metadata ClickHouse already tracks about the table — reading them from system.tables costs nothing close to a full scan, which is exactly why it's the first place to look when you're sizing up an unfamiliar database. (For a small number of table engines that don't track row counts as metadata, total_rows comes back NULL — that's ClickHouse telling you honestly "I'd have to scan to know," not a sign the table is empty.)
system.tables also carries create_table_query, which holds the original CREATE TABLE statement — useful when you've inherited a table and want its exact definition without asking anyone.
system.processes — what's running right this second
system.processes answers a completely different question: not "what exists," but "what's executing, right now, on this server." It's the table that powers the SHOW PROCESSLIST statement you may already know from MySQL or Postgres — same idea, just a queryable table instead of a special command.
SELECT
query_id,
user,
elapsed,
memory_usage,
read_rows,
query
FROM system.processes
ORDER BY elapsed DESC;
┌─query_id─────┬─user────┬─elapsed─┬─memory_usage─┬─read_rows─┬─query───────────────────────────────┐
│ 3f9a-…-8e21 │ analyst │ 4.82 │ 287457280 │ 61230000 │ SELECT country, uniq(user_id) FROM… │
└──────────────┴─────────┴─────────┴──────────────┴───────────┴──────────────────────────────────────┘
The row above exists only because that query is still running when the SELECT executes. That's the mechanic worth internalizing: system.processes isn't a log, it isn't history, and it isn't a queue. It's a live reflection of the server's in-memory execution state at the exact instant you query it. The moment that uniq(user_id) query finishes — successfully or with an error — its row is gone from system.processes, full stop. If you want to know what it did after the fact, system.processes can't tell you. You need the next table.
system.query_log — what already ran
system.query_log answers "what happened," not "what's happening." It's a persisted, on-disk record of every query ClickHouse has executed — when it started, how long it took, how many rows and bytes it read, how much memory it used, and whether it succeeded or threw an exception.
SELECT
event_time,
query_duration_ms,
read_rows,
memory_usage,
type,
query
FROM system.query_log
WHERE query_id = '3f9a-…-8e21'
ORDER BY event_time;
┌──────────event_time─┬─query_duration_ms─┬─read_rows─┬─memory_usage─┬─type────────┬─query──────────────┐
│ 2026-07-12 14:03:01 │ 0 │ 0 │ 0 │ QueryStart │ SELECT country, … │
│ 2026-07-12 14:03:07 │ 6210 │ 61230000 │ 301989888 │ QueryFinish │ SELECT country, … │
└──────────────────────┴───────────────────┴───────────┴──────────────┴─────────────┴────────────────────┘
Two things worth noticing. First, one query can produce more than one row in query_log — a QueryStart row when it began, and a QueryFinish row (or an ExceptionWhileProcessing row, if it failed mid-execution) when it ended. You usually want the finish row for timing and resource numbers, since the start row hasn't measured anything yet.
Second — and this is the mechanic most likely to trip you up in practice — query_log is not written the instant a query finishes. ClickHouse buffers query-log entries in memory and flushes them to disk on a timer, controlled by a flush_interval_milliseconds setting (7500 milliseconds, by default). Query a fresh query_log immediately after your query finishes and you may see nothing at all, not because logging failed, but because the write simply hasn't happened yet. (You can force it early with SYSTEM FLUSH LOGS, which is exactly what it sounds like — flush now, don't wait for the timer.) Query logging itself is controlled by the log_queries setting, which is enabled by default; a query only fails to show up in query_log at all if that's been explicitly turned off for the session.
A worked question: "did that ETL job actually run at 2am?"
Say a scheduled job is supposed to insert a batch every night, and this morning you want to confirm it actually ran. You don't have a dashboard for this job — you have system.query_log.
SELECT event_time, query_duration_ms, written_rows, type
FROM system.query_log
WHERE query LIKE 'INSERT INTO raw_events%'
AND event_time >= yesterday()
AND type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 5;
┌──────────event_time─┬─query_duration_ms─┬─written_rows─┬─type────────┐
│ 2026-07-12 02:00:11 │ 8340 │ 1200500 │ QueryFinish │
└──────────────────────┴───────────────────┴──────────────┴─────────────┘
One row, one QueryFinish, 1.2 million rows written — the job ran, and you confirmed it with the same SELECT skill you use for everything else in this course. No separate job-monitoring tool, no login to a different system.
Common Misconception
"system.processes and system.query_log are basically two views of the same thing — one shows me my queries."
They cover different, non-overlapping slices of a query's life, and the gap between them is exactly where confusion happens. system.processes shows a query only while it's still executing — the row is gone the instant the query ends, whether it succeeded or failed. system.query_log shows a query only after it has ended, and only once the next periodic flush (or a manual SYSTEM FLUSH LOGS) has actually written it to disk. That leaves a real window — often a few seconds — where a query that just finished appears in neither table: too late for system.processes, too early for system.query_log. If you go looking for a query in that window and find nothing, the query didn't disappear or fail silently; you're just looking between the two windows.
Predict, Then Verify
You run a query that takes about 3 seconds to execute. The server's query_log flush interval is left at its default. For each moment below, predict whether the query's row would show up in system.processes, in system.query_log, in both, or in neither:
- One second after you started the query (it's still running).
- Immediately — within the same second — after the query finishes.
- Fifteen seconds after the query finishes.
Reveal the answer →
system.processesonly. The query is still executing, so it has a live row there. It hasn't finished, so it has noQueryFinishrow inquery_logyet (at most aQueryStartrow, which won't appear until the next flush either).- Neither. The query just ended, so its row is already gone from
system.processes. But the default flush interval is 7500 milliseconds, so the finished query's row almost certainly hasn't been written toquery_logyet. system.query_logonly. Fifteen seconds is well past the default 7500-millisecond flush interval, so theQueryFinishrow has been written by now. The query is long done, so it was never going to reappear insystem.processes.
Summary
ClickHouse exposes its own internal state as ordinary tables in a system database, so the same SELECT skill you use on your own data is also how you operate the server — no separate admin API to learn. system.tables is a live catalog: what tables exist, what engine each uses, and how large they are, read from cheap metadata rather than a scan. system.processes is a live snapshot of execution: a row exists only while its query is still running, and disappears the instant that query ends. system.query_log is the persisted history: it records every query's timing, resource usage, and outcome, but only after the query finishes and after the log's periodic flush (default 7500 milliseconds) writes it to disk — which means a just-finished query can briefly appear in neither table. Knowing which of these three questions you're actually asking — "what exists," "what's running now," or "what already happened" — tells you which table to reach for.
Quiz
1. Why does ClickHouse expose things like running queries and table metadata as ordinary SQL tables in a `system` database, instead of through a separate admin command or monitoring tool?
2. You query system.tables and see total_rows come back NULL for one table, while every other table shows a real number. What's the most reasonable read of that NULL?
3. A query's row is present in system.processes at 10:00:01, then gone by the time you query system.processes again at 10:00:04. What does that tell you on its own?
4. You run a fast query, and 2 seconds later — well within the default 7500-millisecond query_log flush interval — you check both system.processes and system.query_log for it. You find it in neither. What's the most likely explanation?
5. You want to know how much disk space every table in the `default` database is using, without running a scan against each one. Which system table gets you there directly, and why?