ClickHouse System Tables Are Storages That Generate Their Own Rows

TL;DR: system.parts is not a view over a table. It is an IStorage attached to the system database, and every query against it builds a new source object that walks the catalog and emits rows. Two tricks make that affordable: a column mask (only the columns you asked for are ever computed) and a filter split (your WHERE is pushed down into the enumeration, so a condition on database prunes the walk itself).

The obvious way to implement system.parts is to make it a view: a table in the system database that runs a query against the catalog when you read it. Postgres does roughly this with pg_class and friends.

ClickHouse does something different, and the difference is instructive. system.parts is a full IStorage, registered in the system database like any other table. When you SELECT from it, the query planner asks the storage for a reading step, and the reading step builds a source object that walks the catalog and produces blocks.

The consequences of that choice ripple through the whole design:

The system table is a real table in system.tables, with a real ColumnsDescription, a real engine name, a real SHOW CREATE TABLE.
The WHERE clause can be pushed into the enumeration.
The columns you do not select are never computed.
Every query sees fresh data, because the source is built per query.

Attaching a system table

System tables are attached at startup by attachSystemTables.cpp. The attachment helper is small and does exactly three things: make a StorageID, construct the storage, and attach it.

cpp
// src/Storages/System/attachSystemTablesImpl.h:19
template<typename StorageT, bool with_description, typename... StorageArgs>
void attachImpl(ContextPtr context, IDatabase & system_database, const String & table_name, std::string_view comment, StorageArgs && ... args)
{
    chassert(comment.size() > 15);
    chassert(system_database.getDatabaseName() == DatabaseCatalog::SYSTEM_DATABASE);

    auto table_id = StorageID::createEmpty();
    String path;
    if (system_database.getUUID() == UUIDHelpers::Nil)
    {
        /// Attach to Ordinary database.
        table_id = StorageID(DatabaseCatalog::SYSTEM_DATABASE, table_name);
    }
    else
    {
        /// Attach to Atomic database.
        /// NOTE: UUIDs are not persistent, but it's ok since no data are stored on disk for these storages
        /// and path is actually not used
        table_id = StorageID(DatabaseCatalog::SYSTEM_DATABASE, table_name, UUIDHelpers::generateV4());
        DatabaseCatalog::instance().addUUIDMapping(table_id.uuid);
        path = DatabaseCatalog::getStoreDirPath(table_id.uuid);
    }

    std::shared_ptr<StorageT> storage;
    if constexpr (with_description)
        storage = std::make_shared<StorageT>(table_id, StorageT::getColumnsDescription(), std::forward<StorageArgs>(args)...);
    else
        storage = std::make_shared<StorageT>(table_id, std::forward<StorageArgs>(args)...);

    /// Set the comment on the storage before attaching it, so that we neither look the table back up in
    /// `DatabaseCatalog` nor copy its whole metadata (including the full `ColumnsDescription`) an extra time.
    storage->setInMemoryMetadataComment(String(comment));

    system_database.attachTable(context, table_name, storage, path);
    registerSystemTableDocumentationSource(table_name, ATTACHED_SYSTEM_TABLE_DOCUMENTATION_SOURCE, comment);
}

So system.parts is literally a StorageSystemParts object living in the system database. SELECT * FROM system.parts is a SELECT against a MergeTree-like storage whose “data” is generated on the fly.

The system database is a normal Atomic database, so the system tables get real UUIDs and real directory paths even though nothing is ever written there. The comment in the code says so explicitly: “no data are stored on disk for these storages and path is actually not used”.

The two shapes of system table

Not every system table is built the same way. There are two common shapes.

Shape 1: the one-block table. The whole result fits in a single chunk, built synchronously. The base class declares a single virtual method:

cpp
// src/Storages/System/IStorageSystemOneBlock.h:23
class IStorageSystemOneBlock : public StorageWithCommonVirtualColumns
{
protected:
    /// If this method uses `predicate`, getFilterSampleBlock() must list all columns to which
    /// it's applied. (Otherwise there'll be a LOGICAL_ERROR "Not-ready Set is passed" on subqueries.)
    virtual void fillData(MutableColumns & res_columns, ContextPtr context, const ActionsDAG::Node * predicate, std::vector<UInt8> columns_mask) const = 0;

    /// Columns to which fillData() applies the `predicate`.
    virtual Block getFilterSampleBlock() const
    {
        return {};
    }

    virtual bool supportsColumnsMask() const { return false; }

    friend class ReadFromSystemOneBlock;

fillData is called once, and its output becomes a single chunk:

cpp
// src/Storages/System/IStorageSystemOneBlock.cpp:83
void ReadFromSystemOneBlock::initializePipeline(QueryPipelineBuilder & pipeline, const BuildQueryPipelineSettings &)
{
    auto sample_block = getOutputHeader();
    MutableColumns res_columns = sample_block->cloneEmptyColumns();
    const ActionsDAG::Node * predicate = filter ? filter->getOutputs().at(0) : nullptr;
    storage->fillData(res_columns, context, predicate, std::move(columns_mask));

    UInt64 num_rows = res_columns.at(0)->size();

    Chunk chunk;
    if (num_rows > 0)
        chunk = Chunk(std::move(res_columns), num_rows);

    pipeline.init(Pipe(std::make_shared<SourceFromSingleChunk>(sample_block, std::move(chunk))));
}

Shape 2: the streaming source. The result is generated lazily, in chunks, by an ISource. system.columns is the canonical example:

cpp
// src/Storages/System/StorageSystemColumns.cpp:100
class ColumnsSource final : public ISource
{
public:
    ColumnsSource(
        std::vector<UInt8> columns_mask_,
        SharedHeader header_,
        UInt64 max_block_size_,
        ColumnPtr databases_,
        ColumnPtr tables_,
        Storages storages_,
        ContextPtr context_)
        : ISource(header_)
        , columns_mask(std::move(columns_mask_))
        , max_block_size(max_block_size_)
        , databases(std::move(databases_))
        , tables(std::move(tables_))
        , storages(std::move(storages_))
        , context(std::move(context_))
        , client_info_interface(context->getClientInfo().interface)
        , total_tables(tables->size())
        , access(context->getAccess())
        , query_id(context->getCurrentQueryId())
        , lock_acquire_timeout(std::chrono::milliseconds(context->getSettingsRef()[Setting::lock_acquire_timeout].totalMilliseconds()))
    {
        need_to_check_access_for_tables = !access->isGranted(AccessType::SHOW_COLUMNS);
    }

    String getName() const override { return "Columns"; }

protected:
    Chunk generate() override
    {
        if (db_table_num >= total_tables)
            return {};

        MutableColumns res_columns = getPort().getHeader().cloneEmptyColumns();
        size_t rows_count = 0;

        while (rows_count < max_block_size && db_table_num < total_tables)
        {
            const std::string database_name = (*databases)[db_table_num].safeGet<std::string>();
            const std::string table_name = (*tables)[db_table_num].safeGet<std::string>();
            ++db_table_num;

The generate() method is the source’s produce loop: it is called repeatedly until it returns an empty chunk. It walks a pre-computed list of (database, table) pairs, and for each one it takes a share lock on the table, reads its in-memory metadata, and emits one row per column.

The source is rebuilt for every query

Here is the trick that makes the whole thing work, and it is worth stating plainly: the source object is created inside initializePipeline, i.e. per query. There is no cached result, no snapshot of “the parts as of query start”.

For system.columns, the source is built in the pipeline initializer:

cpp
// src/Storages/System/StorageSystemColumns.cpp:588
    ColumnPtr filtered_database_column = block_to_filter.getByName("database").column;
    ColumnPtr filtered_table_column = block_to_filter.getByName("table").column;

    pipes.emplace_back(std::make_shared<ColumnsSource>(
            std::move(columns_mask), std::move(header), max_block_size,
            std::move(filtered_database_column), std::move(filtered_table_column),
            std::move(storages), context));

    pipeline.init(Pipe::unitePipes(std::move(pipes)));

Note that the databases and tables columns and the storages map are moved into the source. They were computed a moment earlier, in the same function, by walking the catalog. If the source were cached, that walk would be stale.

For system.parts and friends, the same idea takes a slightly different shape: a StoragesInfoStream is created per query, and it enumerates storages lazily, one at a time, taking a share lock on each:

cpp
// src/Storages/System/StorageSystemPartsBase.cpp:517
void ReadFromSystemPartsBase::initializePipeline(QueryPipelineBuilder & pipeline, const BuildQueryPipelineSettings &)
{
    auto stream = storage->getStoragesInfoStream(std::move(filter_by_database), std::move(filter_by_other_columns), context);
    auto header = getOutputHeader();

    MutableColumns res_columns = header->cloneEmptyColumns();

    /// The whole result is built eagerly here, so without this check a cancelled or timed out
    /// query would keep building rows over every storage until the very end.
    /// If the time limit is exceeded in the 'break' mode, stop instead of failing the query.
    QueryStatusPtr query_status = context->getProcessListElement();

    while (StoragesInfo info = stream->next())
    {
        if (query_status && !query_status->checkTimeLimit())
            break;

        storage->processNextStorage(context, res_columns, columns_mask, info, has_state_column);
    }

    UInt64 num_rows = res_columns.at(0)->size();
    Chunk chunk(std::move(res_columns), num_rows);

    pipeline.init(Pipe(std::make_shared<SourceFromSingleChunk>(std::move(header), std::move(chunk))));
}

Two things to notice:

The whole result is one chunk. system.parts does not stream. It builds every row in memory and hands over a single chunk. For a table with 10 000 parts and 60 columns, that is 600 000 rows in one Block. That is a deliberate trade: the enumeration is a walk over in-memory structures, and streaming it would mean yielding between storages and paying the pipeline’s per-chunk overhead for no benefit.
Cancellation is checked inside the loop. The comment says exactly why: without the check, a cancelled query would keep building rows over every storage until the very end.

The column mask

Now the trick that makes these tables cheap. system.parts has 68 columns. Almost nobody selects all of them. Computing all of them for every part of every table in the server — including the hash_of_all_files SipHash over the whole checksum map, the TTL info maps, the projections list — is a lot of work for columns that will be thrown away.

So the storage computes a mask of the requested columns and skips everything else:

cpp
// src/Storages/System/getQueriedColumnsMaskAndHeader.cpp:8
std::pair<std::vector<UInt8>, Block> getQueriedColumnsMaskAndHeader(const Block & sample_block, const Names & column_names)
{
    std::vector<UInt8> columns_mask(sample_block.columns());
    Block header;

    NameSet names_set(column_names.begin(), column_names.end());
    for (size_t i = 0; i < columns_mask.size(); ++i)
    {
        const auto & column_with_type_and_name = sample_block.getByPosition(i);
        if (names_set.contains(column_with_type_and_name.name))
        {
            columns_mask[i] = 1;
            header.insert(column_with_type_and_name);
        }
    }

    return std::make_pair(columns_mask, header);
}

The mask is a vector<UInt8>, one byte per column of the table, and the fillData / processNextStorage implementations check it before every single insert:

cpp
// src/Storages/System/StorageSystemParts.cpp:208
        size_t src_index = 0;
        size_t res_index = 0;
        if (columns_mask[src_index++])
            columns[res_index++]->insert(part->partition.serializeToString(partition_key_samples.get(*part)));
        if (columns_mask[src_index++])
            columns[res_index++]->insert(part->name);
        if (columns_mask[src_index++])
            columns[res_index++]->insert(part->uuid);
        if (columns_mask[src_index++])
            columns[res_index++]->insert(part->getTypeName());
        if (columns_mask[src_index++])
            columns[res_index++]->insert(part->getDataPartStorage().getType().toString());
        if (columns_mask[src_index++])
            columns[res_index++]->insert(part_state == State::Active);

and, for the expensive ones, the mask is consulted before the expensive computation rather than after:

cpp
// src/Storages/System/StorageSystemParts.cpp:311
        {
            MinimalisticDataPartChecksums helper;
            if (columns_mask[src_index] || columns_mask[src_index + 1] || columns_mask[src_index + 2])
                helper.computeTotalChecksums(part->checksums);

            if (columns_mask[src_index++])
            {
                auto checksum = helper.hash_of_all_files;
                columns[res_index++]->insert(getHexUIntLowercase(checksum));
            }

computeTotalChecksums walks the whole checksum map and runs three SipHash-128 computations over it. If you did not ask for the hash columns, it is not called at all.

The same pattern shows up in system.columns, where the mask indices are hard-coded (which is why there is a warning comment at the top of the class):

cpp
// src/Storages/System/StorageSystemColumns.cpp:176
                const bool needs_column_metadata = columns_mask[7] || columns_mask[8] || columns_mask[9] || columns_mask[21];
                bool can_expose_any_column_metadata = !needs_column_metadata;

The filter is pushed into the enumeration

The second trick. system.parts covers every MergeTree table in the server. Walking all of them to answer SELECT ... FROM system.parts WHERE database = 'default' is absurd. So the WHERE clause is split into pieces, and the pieces are used to prune the walk.

SourceStepWithFilter hands the storage a predicate, and the storage splits it by which columns it can evaluate early:

cpp
// src/Storages/System/StorageSystemPartsBase.cpp:460
void ReadFromSystemPartsBase::applyFilters(ActionDAGNodes added_filter_nodes)
{
    SourceStepWithFilter::applyFilters(std::move(added_filter_nodes));

    if (filter_actions_dag)
    {
        const auto * predicate = filter_actions_dag->getOutputs().at(0);

        Block block;
        block.insert(ColumnWithTypeAndName({}, std::make_shared<DataTypeString>(), database_column_name));

        filter_by_database = VirtualColumnUtils::splitFilterDagForAllowedInputs(predicate, &block, context);
        if (filter_by_database)
            VirtualColumnUtils::buildSetsForDAG(*filter_by_database, context);

        block.insert(ColumnWithTypeAndName({}, std::make_shared<DataTypeString>(), table_column_name));
        block.insert(ColumnWithTypeAndName({}, std::make_shared<DataTypeString>(), engine_column_name));
        block.insert(ColumnWithTypeAndName({}, std::make_shared<DataTypeUInt8>(), active_column_name));
        block.insert(ColumnWithTypeAndName({}, std::make_shared<DataTypeUUID>(), storage_uuid_column_name));

        filter_by_other_columns = VirtualColumnUtils::splitFilterDagForAllowedInputs(predicate, &block, context);
        /// `StoragesInfoStream` reads the condition on `table` back from this filter to narrow the
        /// enumeration, which needs the elements of an `IN` over a subquery: keep them.
        if (filter_by_other_columns)
            VirtualColumnUtils::buildSetsForDAGKeepingElements(*filter_by_other_columns, context);
    }
}

Two DAGs come out: one that only mentions database, and one that mentions database, table, engine, active and storage_uuid. The first is applied to a block of database names before the table enumeration even starts. The second is applied to a block of (table, engine, active) tuples before the parts are read.

The enumeration itself is driven by the filter:

cpp
// src/Storages/System/StorageSystemPartsBase.cpp:264
StoragesInfoStream::StoragesInfoStream(std::optional<ActionsDAG> filter_by_database, std::optional<ActionsDAG> filter_by_other_columns, ContextPtr context)
    : StoragesInfoStreamBase(context)
{
    /// Will apply WHERE to subset of columns and then add more columns.
    /// This is kind of complicated, but we use WHERE to do less work.

    Block block_to_filter;

    /// A condition on `table` prunes the enumeration below, which resolves every storage of
    /// every database that survived the condition on `database`. The sets of `filter_by_other_columns`
    /// were built in `applyFilters` with their elements kept, so an `IN` over a subquery is readable
    /// here too. A condition that names the database together with the table, such as
    /// `(database, table) IN (...)`, cannot be split off into `filter_by_database`; the exact
    /// database names are read from the same filter so such a condition also shortlists the databases.
    const auto * other_columns_predicate = filter_by_other_columns ? filter_by_other_columns->getOutputs().at(0) : nullptr;
    const auto table_name_filter = extractNameFilter(other_columns_predicate, table_column_name, context);
    const auto database_name_filter = extractNameFilter(other_columns_predicate, database_column_name, context);

extractNameFilter is the key: it recognises the shape column = 'literal' or column IN (...) and turns it into a predicate function that the database’s table iterator can use to skip tables without resolving them.

cpp
// src/Storages/System/StorageSystemPartsBase.cpp:328
            for (size_t i = 0; i < rows; ++i)
            {
                offsets[i] = offsets[i - 1];

                if (time_limit_exceeded)
                    continue;

                String database_name = (*database_column_for_filter)[i].safeGet<String>();
                const DatabasePtr & database = databases.at(database_name);
                const bool check_access_for_tables_in_db
                    = check_access_for_tables && !access->isGranted(AccessType::SHOW_TABLES, database_name);

                offsets[i] = offsets[i - 1];
                auto iterator = database->getTablesIterator(context, table_name_filter, /* skip_not_loaded */ false);

getTablesIterator takes the filter, so a database with 10 000 tables resolves only the one you named. And the access check is folded into the same walk — SHOW TABLES permission is checked per database and per table while enumerating, not after.

The virtual columns

Every system table also exposes _database and _table as virtual columns, plus _state for the parts tables. They are declared in the base class:

cpp
// src/Storages/System/StorageSystemPartsBase.cpp:568
VirtualColumnsDescription StorageSystemPartsBase::createVirtuals()
{
    VirtualColumnsDescription desc;
    desc.addEphemeral("_state", std::make_shared<DataTypeString>(), "", VirtualsMaterializationPlace::Reader);
    desc.addEphemeral("_table", std::make_shared<DataTypeLowCardinality>(std::make_shared<DataTypeString>()), "", VirtualsMaterializationPlace::Plan);
    desc.addEphemeral("_database", std::make_shared<DataTypeLowCardinality>(std::make_shared<DataTypeString>()), "", VirtualsMaterializationPlace::Plan);
    return desc;
}

VirtualsMaterializationPlace::Plan means “compute this in the query planner, before the source runs” — _table and _database are constants for the whole query, so they are substituted into the plan rather than emitted per row. _state is Reader place, because its value depends on the part.

The documentation source trick

One last detail, and it is a nice one. ClickHouse wants to show, for each system table, the source file that implements it. The obvious approach — __builtin_FILE() in the base-class constructor — gives the wrong file, because the base class constructor lives in the base class’s translation unit.

So each system table registers itself from its own .cpp:

cpp
// src/Storages/System/SystemTableSourceRegistry.h:38
/// Place at file scope in a system table's `.cpp` (so that `__builtin_FILE()` resolves to that file). Accepts a type,
/// including a template instantiation, e.g. `REGISTER_SYSTEM_TABLE_SOURCE(::DB::SystemMergeTreeSettings<false>)`.
#define REGISTER_SYSTEM_TABLE_SOURCE_CONCAT_IMPL(a, b) a##b
#define REGISTER_SYSTEM_TABLE_SOURCE_CONCAT(a, b) REGISTER_SYSTEM_TABLE_SOURCE_CONCAT_IMPL(a, b)
/// `gnu::used` + `gnu::retain` keep the registration's static initializer from being discarded by the compiler or by
/// the linker's `--gc-sections`/`--icf` (the variable has internal linkage and is otherwise never referenced).
#define REGISTER_SYSTEM_TABLE_SOURCE(...) \
    namespace \
    { \
        [[maybe_unused, gnu::used, gnu::retain]] const bool REGISTER_SYSTEM_TABLE_SOURCE_CONCAT(registered_system_table_source_, __LINE__) \
            = (::DB::registerSystemTableSource(typeid(__VA_ARGS__), __builtin_FILE()), true); \
    }

and the invocation is one line at the bottom of the file:

cpp
// src/Storages/System/StorageSystemParts.cpp:429
namespace DB { REGISTER_SYSTEM_TABLE_SOURCE(StorageSystemParts) }

gnu::used and gnu::retain are needed because the variable has internal linkage and nothing references it — without them the linker would drop it.

The whole picture

text
  SELECT name, rows FROM system.parts WHERE database = 'default'
       |
       v
  StorageSystemParts (an IStorage in the `system` database)
       |
       |  readImpl() -> build a ReadFromSystemPartsBase step
       |                compute the columns_mask from the requested names
       v
  ReadFromSystemPartsBase
       |
       |  applyFilters() -> split the WHERE into
       |                    filter_by_database      (mentions only `database`)
       |                    filter_by_other_columns (mentions table/engine/active/uuid)
       v
  StoragesInfoStream (built per query)
       |
       |  walk databases -> filter by database -> walk tables (with a name filter)
       |  -> filter by table/engine/active -> take a share lock -> collect the parts
       v
  processNextStorage() -> for each part, for each column:
       if (columns_mask[i]) compute and insert
       |
       v
  ONE Chunk, handed to the pipeline

Trade-offs

What the storage-with-a-source design buys you:

Fresh data, trivially. There is no cache to invalidate. Two consecutive SELECT count() FROM system.parts see two different states if a merge happened in between, and that is the correct behaviour.
Predicate pushdown for free. Because the storage is a step in the query plan and not a view, the planner hands it the WHERE clause as a DAG, and the storage can use it however it likes — including to prune the catalog walk.
The column mask. This is the biggest win. system.parts computes 68 columns; the ones you did not ask for cost nothing. A SELECT count() FROM system.parts on a server with 100 000 parts does not compute a single checksum.
Access control integrated with the walk. Permissions are checked while enumerating, so a user who cannot see a table never causes it to be resolved, let alone locked.

What it costs:

The result is materialised. system.parts builds the entire result in one chunk. A server with a million parts and a SELECT * will allocate a lot of memory in one go. (The cancellation checkpoints exist precisely because of this.)
Hard-coded column positions. system.columns refers to columns_mask[7], columns_mask[21] and friends. There is a comment warning that the column list must be kept in sync, and the mask indices are a genuine footgun: insert a column in the middle of the ColumnsDescription and the mask silently misaligns.

The lock dance. Each table is locked for share with a timeout, in 100 ms slices, so that a cancelled query does not sit inside the lock acquisition:

cpp
// src/Storages/System/StorageSystemPartsBase.cpp:577
bool StoragesInfoStreamBase::tryLockTable(StoragesInfo & info)
{
    const Poco::Timespan acquire_timeout(lock_timeout.count() * 1000);

    if (!query_status)
    {
        info.table_lock = info.storage->tryLockForShare(query_id, acquire_timeout);
        // nullptr means table was dropped while acquiring the lock
        return info.table_lock != nullptr;
    }

    /// Wait for the lock in short slices, polling the query status between them, so that a killed
    /// or soft-timed-out query does not sit inside RWLockImpl::getLock for the whole
    /// lock_acquire_timeout while a concurrent DDL query holds the drop lock. The slicing is done
    /// by IStorage below its throwing API boundary: an expired slice is a plain retry, not an
    /// exception, and only the exhaustion of the whole lock_acquire_timeout throws DEADLOCK_AVOIDED.
    static const Poco::Timespan cancellation_check_period(100 * 1000);

Two shapes to learn. IStorageSystemOneBlock for the small tables, a hand-written ISource for the big ones, and StorageSystemPartsBase for the family that needs a catalog walk. There is no single base class for “a system table”.

Wrapping up

The one thing to remember: a ClickHouse system table is a storage, and the rows are generated by a source that is constructed anew for every query. Nothing is cached, so nothing can be stale.

The part people get wrong: the system table does not compute all its columns and then throw some away. The column mask is checked before each expensive computation, so SELECT count() FROM system.parts never computes a checksum, never builds a TTL map, and never serialises a partition.

What to go do about it: measure the difference.

sql
SELECT count() FROM system.parts;
SELECT count() FROM system.parts WHERE database = 'default';
SELECT sum(hash_of_all_files) != '' FROM system.parts;

The third one is the expensive one. If your monitoring queries the hash columns on every scrape, you are paying for three SipHash-128 computations over every part’s checksum map, every scrape.