External DISTINCT - #116569

DISTINCT keeps a hash set of all the distinct keys, so its memory usage grows with cardinality: a query with many distinct values can fail with MEMORY_LIMIT_EXCEEDED. This PR lets the final DISTINCT spill to disk, the same way external aggregation and external sort already do.

Settings. max_bytes_before_external_distinct (absolute, 0 = unset) and max_bytes_ratio_before_external_distinct (a ratio of the available memory, 0.5 by default — enabled by default). Both thresholds combine by minimum, mirroring Aggregator::Params::getMaxBytesBeforeExternalGroupBy. The thresholds are compared with the tracked memory usage of the whole query. The available memory is what remains, when the execution pipeline is built, under the strictest applicable server or user memory limit; when neither supplies a limit, the ratio has no effect and only the absolute threshold applies. max_memory_usage does not participate in this calculation. Setting both thresholds to 0 disables external DISTINCT and its associated preliminary memory shedding.

How the spill works (ExternalDistinctTransform). The spill reuses the external sort pipeline of MergeSortingTransform. Sorted chunks are accumulated, merged into runs that are written to temporary files, and merged back at the end. The pipeline is expanded at runtime, and the file sink/source pair is extracted into the shared BufferingFileTransforms. Each of the three sorting stages is replaced by a variant that also deduplicates. Duplicates are therefore dropped wherever the rows are touched anyway, and they never reach the disk. Sorting first and deduplicating once would write every duplicate and read it back, which on skewed input is most of the data.

• Before spilling, the first occurrence of every key streams downstream immediately. This uses the same filtering core as DistinctTransform.
• Before inserting each chunk, DistinctSetFilter::prepareForInsert estimates the additional hash-buffer allocations that growth could require. It treats the input rows as potential new keys. If those allocations would leave insufficient server/user memory for preparing a spill, spilling starts before insertion. The pending chunk is retained and processed through the external path. The estimate uses the actual hash-table grower, and fixed-size key tables report no growth.
• When spilling starts, the keys emitted so far are recovered incrementally from the hash set through DistinctSetFilter::extractKeys. A resumable extractor prepares sorted suppression runs with an approximately 16 MiB byte target. Each of them carries an “already emitted” flag. Extraction waits for each run to finish writing before preparing another, and the set is freed after its last keys are extracted. Only the keys are needed for these rows, since they are never emitted again. Suppression runs are sorted with sortBlock and written as they are. Keys that the hash set distinguishes may compare equal in the sort order, and all of them have to suppress. Key shapes that the in-memory DISTINCT stores only as a 128-bit hash use the serialized set method in the spilling transform. The set keeps the serialized keys in its arena, so they can be recovered without retaining previously emitted chunks.
• From that point nothing is emitted until the input is exhausted. External sort runs sortBlock on every incoming chunk. The spill runs sortBlockAndDeduplicate instead, which computes the stable permutation and compacts the equal key ranges in the same pass. A chunk therefore carries no duplicates into the accumulated chunks. The chunks are accumulated and written out as further runs when memory exceeds the threshold. Ordinary runs have a minimum accumulated size to avoid writing a file for every small chunk.
• External sort merges the accumulated chunks into a run with MergeSorter. The spill uses MergeSorter::Mode::MergeUniqueChunks, which relies on the chunks being unique within themselves. Only the first row of a batch can then repeat the last emitted key, and the surviving ranges are copied directly. Every run on disk is unique on the keys. The final in-memory tail is merged the same way. The runs contain only the non-constant input columns, and constants are re-attached from the header after the merge.
• External sort reads the runs back through MergingSortedTransform. The spill uses DistinctSortedTransform, which fuses merging, deduplication, and suppression in one stage. Runs are ordered by distinct keys followed by the already-emitted flag descending, so suppression rows precede equal ordinary rows independently of their input position. Ordinary runs retain arrival precedence through the input-index tie-break, which preserves the first payload among equal keys. Surviving ranges are copied together, and whole chunks are forwarded when possible.

ORDER BY is preserved across the spill. The final DISTINCT of a query with ORDER BY at the same level runs above the sort, so it has to return the rows in the sorted order. When it spills, every row carries its arrival number into the runs, and after the merge and deduplication the rows are sorted back by it (PartialSortingTransform + MergeSortingTransform, which can use the disk as well, under the same thresholds). DistinctStep carries this requirement as a flag: the planners set it for the query’s own ORDER BY, and the applyOrder optimization sets it whenever it propagates a global sort order through the step, because the steps above may rely on that order. The flag is serialized with the query plan under the plan version that also carries the external DISTINCT settings (and omitted from the hash-table cache key), so a remote server executing a serialized plan preserves the order too; EXPLAIN PLAN actions = 1 shows it as Preserve input order: 1.

The preliminary DISTINCT never spills. It is best-effort, so under the same threshold it frees its hash set and switches to pass-through, leaving exact deduplication to the downstream step. It can also release the set before projected hash-table growth exceeds available server/user memory. This policy is independent of whether the downstream step uses hashing or sorted input; the switch is counted by the DistinctTransformsSwitchedToPassThrough profile event.

Shared deduplication core. The hash-set filtering, the LowCardinality fast path, and size-limit enforcement are extracted from DistinctTransform into DistinctSetFilter, shared by both transforms, so a semantic change to hashing is a change in one place. DistinctSpillLayout owns the column conversions, headers, and service-column positions, while ExternalDistinctTransform schedules extraction, writing, and merging. Already-emitted flags remain constant columns during run preparation and local sorting. Serialization scratch is reused after each value is copied into its destination column.

Scope and semantics:

• The external path is used only for the final hash-based DISTINCT, and only when at least one key column is non-constant. Every key type is supported: a key column whose type is not comparable (e.g. AggregateFunction) is spilled as its serialized values in a String column of the same name, so the runs are sorted and merged by comparing the bytes, and it is deserialized back after the merge. The existing sorted-stream DISTINCT paths remain unchanged.
• Values that compare equal in the sort order but differ in the binary representation (0. and -0., NaN payloads) may be deduplicated as one value once the data is spilled — the same equality DISTINCT over sorted data uses; a value class fully processed in memory keeps the binary distinction.
• DISTINCT ... LIMIT over an unbounded stream no longer terminates early once a spill happened: after the spill nothing can be emitted until the input is exhausted, so the limit cannot short-circuit reading, as with external sort.
• The byte size checked against max_bytes_in_distinct also counts the bitmaps of the LowCardinality fast path. Size limits are checked even when a chunk adds no new keys.
• The final merge checks the row limit before applying the LIMIT hint, including when both are crossed in the same chunk. BREAK retains chunk-level behavior.
• Introspection: ExternalDistinctWritePart, ExternalDistinctMerge, ExternalDistinctCompressedBytes, ExternalDistinctUncompressedBytes and DistinctTransformsSwitchedToPassThrough profile events and the TemporaryFilesForDistinct metric; temporary files respect the existing temporary-data size limits, min_free_disk_space_for_temporary_data, temporary_files_codec, temporary_files_buffer_size, and temporary data disk policies.

Changelog category (leave one):

• New Feature

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

DISTINCT can now spill data to disk, like external aggregation and external sort: the new settings max_bytes_before_external_distinct and max_bytes_ratio_before_external_distinct set the threshold; by default, spilling is triggered once query memory usage exceeds half the available memory under applicable server or user limits. Before growing the hash table, ClickHouse checks whether enough memory would remain to write its contents to disk. If not, it spills first. A DISTINCT that follows an ORDER BY keeps the sorted order when it spills.

Workflow [PR]
Sync PR [sync-upstream/pr/116569]