> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# max_bytes_* session settings

> ClickHouse session settings in the max_bytes_* generated group.

export const VersionHistory = ({rows = []}) => {
  if (rows.length === 0) {
    return null;
  }
  const headers = ["Version", "Default value", "Comment"];
  const border = "1px solid rgba(128, 128, 128, 0.3)";
  const cell = {
    border,
    padding: "0.25rem 0.5rem",
    textAlign: "start",
    verticalAlign: "top"
  };
  return <details className="not-prose" style={{
    border,
    borderRadius: "0.5rem",
    margin: "0.5rem 0",
    padding: "0.5rem 0.75rem",
    fontSize: "0.8125rem",
    lineHeight: "1.125rem"
  }}>
      <summary style={{
    cursor: "pointer",
    fontWeight: 600,
    opacity: 0.72
  }}>
        Version history
      </summary>
      <table style={{
    borderCollapse: "collapse",
    width: "100%",
    margin: "0.5rem 0 0"
  }}>
        <thead>
          <tr>
            {headers.map(header => <th key={header} style={{
    ...cell,
    fontWeight: 600,
    opacity: 0.72
  }}>
                {header}
              </th>)}
          </tr>
        </thead>
        <tbody>
          {rows.map((row, row_index) => <tr key={row.id ?? row_index}>
              {(row.items ?? []).map((item, item_index) => <td key={item_index} style={{
    ...cell,
    overflowWrap: "anywhere"
  }}>
                  {item?.label}
                </td>)}
            </tr>)}
        </tbody>
      </table>
    </details>;
};

export const SettingsInfoBlock = ({type, default_value, changeable_without_restart}) => {
  return <div className="not-prose" style={{
    display: "flex",
    flexWrap: "wrap",
    alignItems: "baseline",
    columnGap: "0.5rem",
    rowGap: "0.125rem",
    margin: "0.375rem 0",
    fontSize: "0.8125rem",
    lineHeight: "1.125rem"
  }}>
      <div style={{
    fontWeight: 600,
    opacity: 0.72
  }}>Type</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{type}</div>
      <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>Default</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{default_value}</div>
      {changeable_without_restart && <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>
          Changeable without restart
        </div>}
      {changeable_without_restart && <div style={{
    overflowWrap: "anywhere"
  }}>
          {changeable_without_restart}
        </div>}
    </div>;
};

These settings are available in [system.settings](/reference/system-tables/settings) and are autogenerated from [source](https://github.com/ClickHouse/ClickHouse/blob/master/src/Core/Settings.cpp).

<h2 id="max_bytes_before_external_distinct">
  max\_bytes\_before\_external\_distinct
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.9"},{"label": "0"},{"label": "New setting to enable spilling of `DISTINCT` to disk when memory usage exceeds the given threshold in bytes. If 0, only `max_bytes_ratio_before_external_distinct` applies."}]}]} />

Query memory threshold, in bytes, for spilling `DISTINCT` data to disk. Actual memory usage can exceed
this threshold.

`0` disables this threshold. If `max_bytes_ratio_before_external_distinct` also provides a threshold,
the smaller is used. Set both settings to `0` to disable spilling.

See [DISTINCT in external memory](/reference/statements/select/distinct#distinct-in-external-memory).

<h2 id="max_bytes_before_external_group_by">
  max\_bytes\_before\_external\_group\_by
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

Cloud default value: half the memory amount per replica.

Enables or disables execution of `GROUP BY` clauses in external memory.
(See [GROUP BY in external memory](/reference/statements/select/group-by#group-by-in-external-memory))

Possible values:

* Maximum volume of RAM (in bytes) that can be used by the single [GROUP BY](/reference/statements/select/group-by) operation.
* `0` — `GROUP BY` in external memory disabled.

<Note>
  If memory usage during GROUP BY operations is exceeding this threshold in bytes,
  activate the 'external aggregation' mode (spill data to disk).

  The recommended value is half of the available system memory.
</Note>

<h2 id="max_bytes_before_external_join">
  max\_bytes\_before\_external\_join
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.4"},{"label": "0"},{"label": "New setting to control automatic spilling of hash joins to disk. Non-zero value enables spilling and sets the byte threshold."}]}]} />

If set to a non-zero value and `join_algorithm` is `hash`, `parallel_hash`, `default`, or `auto`, the hash join will automatically be converted to grace hash join to enable spilling to disk when the right-side data exceeds this many bytes. Together with `max_bytes_ratio_before_external_join`, this is the threshold-based spill trigger for every hash-based `join_algorithm`, including `grace_hash`, which requires one of the two to be non-zero. Once a non-zero threshold makes a join spill-capable, `enable_adaptive_memory_spill_scheduler` can force it to spill under memory pressure before the threshold is reached. When set to `0` (default), this absolute byte threshold is disabled, but automatic spilling may still occur via `max_bytes_ratio_before_external_join` (which defaults to `0.5`). With both settings at `0`, the join never spills, so the scheduler has nothing to trigger. The exception is `legacy_join_size_limits_trigger_spilling`: with it on, standalone `grace_hash` ignores both settings and spills on `max_rows_in_join` / `max_bytes_in_join` instead. It prevents read in order through join optimization. The threshold also bounds the right side materialized by the [block nested loop join](/reference/statements/select/join#join-with-an-arbitrary-on-condition), whatever `join_algorithm` is set to.

<h2 id="max_bytes_before_external_sort">
  max\_bytes\_before\_external\_sort
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

Cloud default value: half the memory amount per replica.

Enables or disables execution of `ORDER BY` clauses in external memory. See [ORDER BY Implementation Details](/reference/statements/select/order-by#implementation-details)
If memory usage during ORDER BY operation exceeds this threshold in bytes, the 'external sorting' mode (spill data to disk) is activated.

Possible values:

* Maximum volume of RAM (in bytes) that can be used by the single [ORDER BY](/reference/statements/select/order-by) operation.
  The recommended value is half of available system memory
* `0` — `ORDER BY` in external memory disabled.

<h2 id="max_bytes_before_remerge_sort">
  max\_bytes\_before\_remerge\_sort
</h2>

<SettingsInfoBlock type="UInt64" default_value="1000000000" />

In case of ORDER BY with LIMIT, when memory usage is higher than specified threshold, perform additional steps of merging blocks before final merge to keep just top LIMIT rows.

<h2 id="max_bytes_for_lazy_final">
  max\_bytes\_for\_lazy\_final
</h2>

<SettingsInfoBlock type="UInt64" default_value="256000000" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.4"},{"label": "256000000"},{"label": "New setting for maximum number of bytes in the set for lazy FINAL optimization"}]}]} />

Maximum number of bytes in the set for lazy FINAL optimization. If exceeded, falls back to normal FINAL.

<h2 id="max_bytes_in_distinct">
  max\_bytes\_in\_distinct
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum number of bytes of the state (in uncompressed bytes) in memory, which
is used by a hash table when using DISTINCT.

<h2 id="max_bytes_in_join">
  max\_bytes\_in\_join
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum size in bytes of the right-side data structure (typically a hash
table) used when joining tables.

This setting applies to [SELECT ... JOIN](/reference/statements/select/join)
operations and the [Join table engine](/reference/engines/table-engines/special/join).

If a query contains multiple joins, ClickHouse checks this setting for every
intermediate result. It is a hard cap for every hash-based `join_algorithm`: when the limit
is reached the query throws or breaks according to
[`join_overflow_mode`](/reference/settings/session-settings/join#join_overflow_mode).
It never makes a join spill to disk — that decision belongs to
[`max_bytes_before_external_join`](/reference/settings/session-settings/max-bytes#max_bytes_before_external_join)
and
[`max_bytes_ratio_before_external_join`](/reference/settings/session-settings/max-bytes#max_bytes_ratio_before_external_join).
Because it is a cap rather than a trigger, setting it at or below the spill
threshold normally makes the query fail before the join can spill at all —
unless the join is spill-capable and `enable_adaptive_memory_spill_scheduler`
forces a spill first, or
`legacy_join_size_limits_trigger_spilling` turns this limit back into a spill
trigger for the part of a join that already runs on disk.

The limit counts what the hash tables hold, so a join that spilled reaches it as
each bucket is loaded rather than while the right side is read: it can read more
of the right side before stopping than an in-memory hash join would.

When an `ON` section determines no join key at all, there is no algorithm to choose: the limit
applies to the right side materialized by the
[block nested loop join](/reference/statements/select/join#join-with-an-arbitrary-on-condition),
spilled blocks included, and the action on overflow is
[`join_overflow_mode`](/reference/settings/session-settings/join#join_overflow_mode).

Possible values:

* Positive integer.
* 0 — Memory control is disabled.

<h2 id="max_bytes_in_set">
  max\_bytes\_in\_set
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum number of bytes (of uncompressed data) used by a set in the IN clause
created from a subquery.

<h2 id="max_bytes_ratio_before_external_distinct">
  max\_bytes\_ratio\_before\_external\_distinct
</h2>

<SettingsInfoBlock type="Double" default_value="0.5" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.9"},{"label": "0.5"},{"label": "New setting to enable spilling of `DISTINCT` to disk when memory usage exceeds the given ratio of available memory. If 0, only `max_bytes_before_external_distinct` applies."}]}]} />

Fraction of available server or user memory used to calculate the external `DISTINCT` threshold at
the start of execution. For example, `0.5` uses half of the available memory.

Values must be at least `0` and less than `1`. `0` disables this threshold. Without an applicable
server or user memory limit, the ratio has no effect.

`max_memory_usage` does not affect this calculation. To configure spilling relative to that limit,
use `max_bytes_before_external_distinct`, leaving room for additional memory usage.

<h2 id="max_bytes_ratio_before_external_group_by">
  max\_bytes\_ratio\_before\_external\_group\_by
</h2>

<SettingsInfoBlock type="Double" default_value="0.5" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.1"},{"label": "0.5"},{"label": "Enable automatic spilling to disk by default."}]}, {"id": "row-2","items": [{"label": "24.12"},{"label": "0"},{"label": "New setting."}]}]} />

The ratio of available memory that is allowed for `GROUP BY`. Once reached,
external memory is used for aggregation.

For example, if set to `0.6`, `GROUP BY` will allow using 60% of the available memory
(to server/user/merges) at the beginning of the execution, after that, it will
start using external aggregation.

<h2 id="max_bytes_ratio_before_external_join">
  max\_bytes\_ratio\_before\_external\_join
</h2>

<SettingsInfoBlock type="Double" default_value="0.5" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.5"},{"label": "0.5"},{"label": "New setting: ratio of available memory used as the spill threshold for hash joins. Enabled by default at `0.5`, mirroring `max_bytes_ratio_before_external_group_by` and `max_bytes_ratio_before_external_sort`. Combined with the absolute `max_bytes_before_external_join` (the smaller of the two applies)."}]}]} />

The ratio of available memory that is allowed for `JOIN`. Once reached, the hash join will be converted to grace hash join to spill the right-side data to disk.

For example, if set to `0.6`, `JOIN` will allow using `60%` of the available memory (to server/user/merges) for the right-side hash table at the beginning of the execution; after that, it starts spilling to disk.

If both `max_bytes_before_external_join` and `max_bytes_ratio_before_external_join` are set, the smaller resulting threshold is used. If the ratio is `0`, only the absolute setting applies.

Has effect for every hash-based `join_algorithm`, including `grace_hash`, provided a temporary data path is configured.

<h2 id="max_bytes_ratio_before_external_sort">
  max\_bytes\_ratio\_before\_external\_sort
</h2>

<SettingsInfoBlock type="Double" default_value="0.5" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.1"},{"label": "0.5"},{"label": "Enable automatic spilling to disk by default."}]}, {"id": "row-2","items": [{"label": "24.12"},{"label": "0"},{"label": "New setting."}]}]} />

The ratio of available memory that is allowed for `ORDER BY`. Once reached, external sort is used.

For example, if set to `0.6`, `ORDER BY` will allow using `60%` of available memory (to server/user/merges) at the beginning of the execution, after that, it will start using external sort.

Note, that `max_bytes_before_external_sort` is still respected, spilling to disk will be done only if the sorting block is bigger then `max_bytes_before_external_sort`.

<h2 id="max_bytes_to_read">
  max\_bytes\_to\_read
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum number of bytes (of uncompressed data) that can be read from a table when running a query.
The restriction is checked for each processed chunk of data, applied only to the
deepest table expression and when reading from a remote server, checked only on
the remote server.

<h2 id="max_bytes_to_read_leaf">
  max\_bytes\_to\_read\_leaf
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum number of bytes (of uncompressed data) that can be read from a local
table on a leaf node when running a distributed query. While distributed queries
can issue a multiple sub-queries to each shard (leaf) - this limit will
be checked only on the read stage on the leaf nodes and will be ignored on the
merging of results stage on the root node.

For example, a cluster consists of 2 shards and each shard contains a table with
100 bytes of data. A distributed query which is supposed to read all the data
from both tables with setting `max_bytes_to_read=150` will fail as in total it
will be 200 bytes. A query with `max_bytes_to_read_leaf=150` will succeed since
leaf nodes will read 100 bytes at max.

The restriction is checked for each processed chunk of data.

<Note>
  This setting is unstable with `prefer_localhost_replica=1`.
</Note>

<h2 id="max_bytes_to_sort">
  max\_bytes\_to\_sort
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum number of bytes before sorting. If more than the specified amount of
uncompressed bytes have to be processed for ORDER BY operation, the behavior will
be determined by the `sort_overflow_mode` which by default is set to `throw`.

<h2 id="max_bytes_to_transfer">
  max\_bytes\_to\_transfer
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

The maximum number of bytes (uncompressed data) that can be passed to a remote
server or saved in a temporary table when the GLOBAL IN/JOIN section is executed.