ClickHouse Generates an Unbounded Number Stream in Constant Memory

TL;DR: numbers() is not a table with a billion rows — it is a source that fabricates one block at a time. All it keeps between blocks is a single UInt64 counter, so an infinite stream costs the same RAM as a one-row table: one ColumnUInt64 of max_block_size values (65 409 x 8 = 523 272 bytes by default). The interesting part is what happens when a WHERE clause narrows the universe: the filter is compiled into a set of ranges, and the source generates only the numbers inside them.

Ask any database engineer to materialise a table of the integers from 0 to 10^12 and they will tell you it needs a disk. ClickHouse refuses the framing: SELECT count() FROM numbers(10^12) is a legal, cheap query, because numbers() is not a table that was filled — it is a function of the query that produces rows on demand and forgets them.

The naive way to build that is to keep a cursor and emit rows one at a time. The naive way to build it well is to notice that the sequence 0, 1, 2, 3, ... is a straight line in memory, and to write the line with memset-like speed rather than a loop that increments a counter per row. ClickHouse does both: a cursor (next) and a vectorised iota.

The second half of the story is the opposite problem: SELECT * FROM numbers() WHERE number BETWEEN 10^15 AND 10^15 + 100 should not scan 10^15 rows to find 101 of them. ClickHouse pushes the filter into the source — but it is careful about which filters it is willing to trust, and the reason it is careful is one of the best bug-avoidance comments in the codebase.

A table function, not an engine

First, the plumbing. numbers is a table function: a name that appears where a table name would appear and produces a table on the fly. numbers and numbers_mt are the same class (TableFunctionNumbers<bool multithreaded>), instantiated twice; the multithreaded variant differs in exactly one place — whether the storage is allowed to split the stream across several parallel sources.

The interesting bit is what executeImpl returns. It does not return a Table; it returns a storage:

cpp
// src/TableFunctions/TableFunctionNumbers.cpp:92
            auto res = std::make_shared<StorageSystemNumbers>(
                StorageID(getDatabaseName(), table_name), multithreaded, std::string{"number"}, UInt128(length), offset, step);
            res->startup();
            return res;

StorageSystemNumbers is the same object that backs system.numbers. The table function is a front end for it. Note the StorageID: it is fabricated from the current database and the temporary table name, which is why the function needs the CREATE TEMPORARY TABLE privilege — the access check lives in the shared base class, not in numbers:

cpp
// src/TableFunctions/ITableFunction.cpp:77
    auto table_function_properties = TableFunctionFactory::instance().tryGetProperties(getName());
    if (check_create_temporary_table && (is_insert_query || !(table_function_properties && table_function_properties->allow_readonly)))
        context->checkAccess(AccessType::CREATE_TEMPORARY_TABLE);

numbers sets allow_readonly = true in its registration, so a read-only server can still serve SELECT ... FROM numbers(10) — the check is skipped for reads of functions that declare themselves safe.

The factory itself is a flat map from name to a creator lambda; a lookup instantiates a fresh object per call, so table functions are stateless.

The domain, not the row count

The StorageSystemNumbers constructor takes a limit that is not the number of rows the query will produce. The header says so explicitly:

cpp
// src/Storages/System/StorageSystemNumbers.h:43
    /// `limit` is a domain window size, not the output row count
    /// (e.g. `numbers(10, 5, 2)` has limit=5, offset=10, step=2 — the domain is
    /// 5 consecutive integers [10, 14], but only 3 values are produced: 10, 12, 14).
    /// Stored as UInt128 so that a full 2^64-element domain (e.g. generate_series(0, UInt64_MAX))
    /// can be represented without overflow.

The distinction matters because numbers(N, M, S) describes a domain [N, N+M) and a step through it, and the row count is ceil(M/S). A UInt128 domain size lets numbers(0, 2^64) be expressed at all.

The class comment in the same header is the design document for the whole feature, and it is worth reading in full because it describes the two strategies ClickHouse uses:

cpp
// src/Storages/System/StorageSystemNumbers.h:18
  * How to generate numbers?
  *
  * 1. First try a smart fashion:
  *
  * In this fashion we try to push filters and limit down to scanning.
  * Firstly extract plain ranges(no overlapping and ordered) by filter expressions.
  *
  * For example:
  *     where (numbers > 1 and numbers < 3) or (numbers in (4, 6)) or (numbers > 7 and numbers < 9)
  *
  * We will get ranges
  *     (1, 3), [4, 4], [6, 6], (7, 9)
  *
  * Then split the ranges evenly to one or multi-streams. With this way we will get result without large scanning.
  *
  * 2. If fail to extract plain ranges, fall back to ordinary scanning.

“Push filters and limit down to scanning” — that is the whole game, and it is implemented in ReadFromSystemNumbersStep::makePipe().

The generator

The core of it is 30 lines. NumbersSource is an ISource whose entire state is a block size, a cursor and a step:

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:36
class NumbersSource : public ISource
{
public:
    NumbersSource(
        UInt64 block_size_,
        UInt64 start_,
        const std::string & column_name,
        UInt64 step_between_chunks_)
        : ISource(createHeader(column_name))
        , block_size(block_size_)
        , next(start_)
        , step_between_chunks(step_between_chunks_)
    {
    }

and its generate() is the entire mechanism:

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:58
    Chunk generate() override
    {
        auto column = ColumnUInt64::create(block_size);
        ColumnUInt64::Container & vec = column->getData();

        UInt64 curr = next; /// The local variable for some reason works faster (>20%) than member of class.
        UInt64 * pos = vec.data(); /// This also accelerates the code.

        iota(pos, static_cast<size_t>(block_size), curr);

        next += step_between_chunks;

        progress(column->size(), column->byteSize());
        return {Columns{std::move(column)}, block_size};
    }

Three things to notice.

The state is one counter. next is the offset of the next block this source will produce. Nothing else survives between generate() calls, so the source is resumable: a pipeline that stops after three blocks can be restarted and continues at the right place.

The cursor is a local variable. The comment says it “works faster (>20%) than member of class”: the member would have to be re-loaded from memory after the call to iota (which the compiler cannot prove does not alias this), while the local lives in a register for the fill.

The fill is vectorised. iota is not std::iota. It is a hand-rolled loop that the compiler is explicitly allowed to auto-vectorise:

cpp
// src/Common/iota.cpp:7
/// NO_INLINE prevents LTO from inlining these into callers. When inlined, the
/// hot loop's alignment depends on the surrounding code and can land on a
/// 64-byte boundary crossing, causing ~5% regression on modern CPUs. As a
/// separate function the compiler aligns the loop independently.
/// (Previously the multi-target dispatch mechanism achieved this implicitly.)
///
/// LLVM's default interleave factor on AArch64 is 2, and a portable build (no `-mcpu=`) keeps that
/// default. It is already 4 at the default `x86-64-v3` baseline and on Darwin AArch64.

template <iota_supported_types T>
void NO_INLINE iota(T * begin, size_t count, T first_value)
{
    T value = first_value;
#if defined(__aarch64__) && !defined(OS_DARWIN)
#pragma clang loop interleave_count(4)
#endif
    for (size_t i = 0; i < count; i++)
    {
        *(begin + i) = value;
        ++value;
    }
}

NO_INLINE is the surprising part: normally you want the compiler to inline, and here you forbid it, because the alignment of the vectorised loop body matters more than the call overhead. On AArch64 the loop is manually interleaved 4-wide; on x86-64 the x86-64-v3 baseline already gives LLVM an interleave factor of 4.

This is where the “constant memory” claim comes from. max_block_size defaults to DEFAULT_BLOCK_SIZE = 65 409, so one ColumnUInt64 is 65 409 x 8 = 523 272 bytes. The source allocates that, fills it, hands it downstream, and the next call allocates a fresh one. The peak footprint of SELECT count() FROM numbers(10^18) is the same as the peak footprint of SELECT count() FROM numbers(10).

sql
-- Both are one 523 272-byte buffer at a time.
SELECT count() FROM numbers(1000000000000000000);
SELECT number FROM numbers() WHERE number >= 1000000000056095 LIMIT 1;

The second query is the canonical demonstration that the filter is the expensive part, not the generation.

Many streams, no overlap

numbers_mt splits the work across max_threads sources. The naive splitting — “give each thread a contiguous range” — needs the total count in advance, which an unbounded source does not have. ClickHouse instead interleaves: each source starts at a different offset and then jumps by a multiple of the block size.

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:566
        /// Distance between the starts of consecutive chunks in a single source.
        /// For multiple streams, each source jumps by `num_streams * block_range` to avoid overlap.
        const UInt64 step_between_chunks = num_streams * max_block_size;
        for (size_t i = 0; i < num_streams; ++i)
        {
            /// Offset between starting points of adjacent streams equals `max_block_size`,
            /// so streams generate disjoint chunks.
            const auto source_offset = i * max_block_size;
            const auto source_start = numbers_storage.offset + source_offset;

            auto source = std::make_shared<NumbersSource>(
                max_block_size, source_start, numbers_storage.column_name, step_between_chunks);

            pipe.addSource(std::move(source));
        }

With max_block_size = B and four streams, the generated values are:

text
   (with B = max_block_size = 65409, four streams)

   stream 0: 0        B        2B       3B   ...
   stream 1: B        2B       3B       4B   ...
   stream 2: 2B       3B       4B       5B   ...
   stream 3: 3B       4B       5B       6B   ...
             \_____ each source jumps by 4 * B _____/

Every value in [0, 2^64) is produced exactly once, no source needs to know the total, and the union is the whole universe. The cost is that the output is unordered — hence the documentation for numbers_mt says “the order of the rows is not deterministic”. The non-mt variant forces a single stream (num_streams = 1) to preserve ordering.

Pushing the filter into the generator

Now the smart path. The filter is turned into a KeyCondition — the same machinery MergeTree uses to prune granules — and asked for ranges:

cpp
// src/Processors/QueryPlan/numbersLikeUtils.cpp:36
ExtractedRanges extractRanges(const KeyCondition & condition)
{
    ExtractedRanges result;

    if (condition.extractPlainRanges(result.ranges))
    {
        result.kind = ExtractedRanges::Kind::ExactRanges;
        return result;
    }

    result.kind = ExtractedRanges::Kind::ConservativeRanges;
    result.ranges = condition.extractBounds();
    return result;
}

Two kinds of answer, and the difference is the whole safety story. ExactRanges means the filter is equivalent to a union of intervals: the generator can produce exactly those intervals. ConservativeRanges means the filter only implies a set of intervals — number % 3 = 1 is not an interval condition — so the generator produces a superset and the filter still has to run afterwards.

The “smart” path is only taken when the extracted ranges cover the entire universe minus nothing, or when a LIMIT can be pushed down safely:

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:555
    /// Fast path: for unbounded tables (`system.numbers(_mt)`, `numbers(_mt)()`) with no usable bounds in WHERE,
    /// keep the ordinary unbounded scan (NumbersSource).
    ///
    /// When we can safely push down query LIMIT/OFFSET (no filter / exact plain ranges), it can be more efficient to use
    /// `NumbersRangedSource` and shrink the universe to the requested amount. Otherwise, `NumbersSource` is the most
    /// efficient generator and we let downstream transforms (filter/limit) stop the pipeline.
    const bool can_pushdown_query_limit = exact_ranges && limit.has_value();
    if (!numbers_storage.limit.has_value() && NumbersLikeUtils::isUniverse(extracted_ranges.ranges) && !can_pushdown_query_limit)

isUniverse() means “the extracted ranges are the whole of UInt64” — i.e. the WHERE clause told us nothing. In that case, the plain NumbersSource runs and the downstream Filter/Limit stops the pipeline by closing the source. The generator never needs to know how much the consumer wanted.

Why LIMIT is only pushed down for exact ranges

This is the part worth memorising, because the naive implementation is wrong in a way that produces silently fewer rows:

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:484
    /// - LIMIT/OFFSET pushdown:
    ///   - Safe only when extracted ranges are exact. With conservative bounds, pre-filter LIMIT may stop too early.
    ///     Example: `SELECT number FROM system.numbers WHERE number % 3 = 1 AND number < 100 LIMIT 5`.
    ///     Here `number < 100` gives only a conservative bound, so pushing LIMIT first could return too few rows
    ///     (e.g. from `[0, 1, 2, 3, 4]` we keep only `[1, 4]` instead of `[1, 4, 7, 10, 13]`).

Read the example carefully. number % 3 = 1 AND number < 100 LIMIT 5 must return 1, 4, 7, 10, 13, but the only interval the planner can extract is [0, 100). Pushing LIMIT into the generator would emit the first five values of that range — 0, 1, 2, 3, 4 — and the filter would keep only 1 and 4. Two rows instead of five.

So the LIMIT pushdown is gated on exact_ranges:

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:656
    UInt128 total_size = sizeOfRanges(intersected_ranges);

    /// Only push down query LIMIT/OFFSET when the extracted ranges are exact.
    /// For conservative bounds, pushing down LIMIT could produce too few rows after applying the full WHERE filter.
    if (exact_ranges && limit && *limit < total_size)
    {
        total_size = *limit;
        /// We should shrink intersected_ranges for case:
        ///     intersected_ranges: [1, 4], [7, 100]; query_limit: 2
        shrinkRanges(intersected_ranges, static_cast<size_t>(total_size));
    }

shrinkRanges() truncates the range list so that the total number of generated values is exactly the LIMIT — not “at most” the LIMIT. It walks the ranges in order and cuts the last one.

When ranges are used, the generator is NumbersRangedSource, and the interesting part is how several parallel streams divide the work without overlapping and without a barrier. The answer: one shared cursor, guarded by a mutex, that hands out slices of the range list:

cpp
// src/Processors/QueryPlan/ReadFromSystemNumbersStep.cpp:677
    /// All streams share a single cursor into `intersected_ranges` to split work without overlaps.
    auto ranges_state = std::make_shared<NumbersRangedSource::RangesState>();
    for (size_t i = 0; i < num_streams; ++i)
    {
        auto source = std::make_shared<NumbersRangedSource>(
            intersected_ranges, ranges_state, max_block_size, numbers_storage.step, numbers_storage.column_name);

        if (i == 0)
            source->addTotalRowsApprox(static_cast<size_t>(total_size));

        pipe.addSource(std::move(source));
    }

The cursor is a pair of indices into the range vector (RangesPos { offset_in_ranges, offset_in_range }), and the mutex is held only for the duration of the “find my slice” operation, not for the fill. A stream that finds nothing left returns an empty chunk and the pipeline ends: there is no “one thread does the work” fallback, no barrier, and no work stealing — whoever gets there first takes the next slice.

One last guard: makePipe() will not start more streams than there are blocks of work, so LIMIT 10 on a 16-thread server does not spawn 16 threads that immediately find an empty slice.

Trade-offs

What the trick buys: an infinite, ordered, integer-valued relation with a fixed 523 272-byte working set, that parallelises without a coordinator, and that the optimiser can narrow with the same KeyCondition machinery it uses for MergeTree granules.

What it costs:

The vectorised fill is a special case, not a general generator. iota is only defined for UInt8, UInt32 and UInt64 (src/Common/iota.h:16). Anything else — dates, decimals, a step of 3 — falls back to iotaWithStep or a scalar loop, and the throughput drops accordingly.
Ordering is only preserved in the single-stream case. numbers_mt gives you an unordered set, so every query over it needs an explicit ORDER BY — a full sort of a potentially infinite relation.
Range extraction is not free. The KeyCondition is built from the filter DAG on every read, and the “exact vs conservative” classification is a heuristic. A filter that cannot be turned into intervals (a modulo, a function call) gets you a superset, and you still pay for the filter.
Pushing LIMIT into the source is a correctness hazard, as the number % 3 = 1 example shows. The guard is exact_ranges && limit, and every future change to the range extractor has to preserve it.
Descending series take a different path entirely (SimpleSteppedNumbersSource with a wrapping unsigned step) and do not support filter pushdown — the header says so with a TODO.

Wrapping up

The one thing to remember: numbers() is a generator with a cursor, not a table. Materialising a million integers into a MergeTree to test something means paying for storage and merges to avoid a 523 272-byte buffer.

The part people get wrong: the filter is not free. ClickHouse can narrow the universe to the rows you asked for, but only when your predicate is a union of intervals. WHERE number % 3 = 1 is not, and the engine will generate a superset and filter it afterwards.

What to go do about it: try the difference yourself.

sql
-- Unbounded scan, stopped by LIMIT.
SELECT count() FROM numbers() LIMIT 10;
-- Range-narrowed: only 31 values are ever generated.
SELECT count() FROM numbers() WHERE number BETWEEN 100 AND 130;