> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/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 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](/docs/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_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](/docs/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](/docs/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. 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`); set both to `0` to fully disable automatic spilling. It prevents read in order through join optimization.

<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](/docs/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](/docs/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](/docs/reference/statements/select/join)
operations and the [Join table engine](/docs/reference/engines/table-engines/special/join).

If a query contains multiple joins, ClickHouse checks this setting for every
intermediate result. When the limit is reached, the action depends on the
chosen [`join_algorithm`](/docs/reference/settings/session-settings/join#join_algorithm) — see
that setting for the per-algorithm behavior (spill, re-partition, switch, or
throw/break per [`join_overflow_mode`](/docs/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_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 only when `join_algorithm` is `hash`, `parallel_hash`, `default`, or `auto` and 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.