MergeTree table engine

You can also combine local and S3 volumes in a tiered policy, for example moving data from a local SSD to S3 as it ages:

```
<storage_configuration>
    <disks>
        <local_ssd>
            <path>/mnt/fast_ssd/clickhouse/</path>
        </local_ssd>
        <s3_cold>
            <type>s3</type>
            <endpoint>https://s3.amazonaws.com/cold-storage/data/</endpoint>
            <access_key_id>your_access_key_id</access_key_id>
            <secret_access_key>your_secret_access_key</secret_access_key>
        </s3_cold>
    </disks>
    <policies>
        <local_to_s3>
            <volumes>
                <hot>
                    <disk>local_ssd</disk>
                    <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
                </hot>
                <cold>
                    <disk>s3_cold</disk>
                </cold>
            </volumes>
            <move_factor>0.2</move_factor>
        </local_to_s3>
    </policies>
</storage_configuration>
```

Note

When using `use_environment_credentials` for S3 authentication, the environment credentials (`AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY`, `AWS_SESSION_TOKEN`) are shared across all S3 disks. It is not possible to use different environment credentials for different disks. If you need different credentials for each S3 disk, use explicit `access_key_id` and `secret_access_key` settings per disk instead.

It is possible to set up non-replicated MergeTree tables with a one-writer, many-readers scenario on shared storage. This is provided by the automatic refresh of the parts list, which can be set up on readers. Note that this requires shared filesystem metadata across replicas (or `table_disk = true` with a table-local disk). See [refresh\_parts\_interval and table\_disk](/docs/operations/storing-data#refresh-parts-interval-and-table-disk).

cache configuration

ClickHouse versions 22.3 through 22.7 use a different cache configuration, see [using local cache](/docs/operations/storing-data#using-local-cache) if you are using one of those versions.

## Virtual columns[​](#virtual-columns "Direct link to Virtual columns")

- `_part` — Name of a part.
- `_part_index` — Sequential index of the part in the query result.
- `_part_starting_offset` — Cumulative starting row of the part in the query result.
- `_part_offset` — Number of row in the part.
- `_part_granule_offset` — Number of granule in the part.
- `_partition_id` — Name of a partition.
- `_part_uuid` — Unique part identifier (if enabled MergeTree setting `assign_part_uuids`).
- `_part_data_version` — Data version of part (either min block number or mutation version).
- `_partition_value` — Values (a tuple) of a `partition by` expression.
- `_sample_factor` — Sample factor (from the query).
- `_block_number` — Original number of block for row that was assigned at insert, persisted on merges when setting `enable_block_number_column` is enabled.
- `_block_offset` — Original number of row in block that was assigned at insert, persisted on merges when setting `enable_block_offset_column` is enabled.
- `_disk_name` — Disk name used for the storage.

## Column statistics[​](#column-statistics "Direct link to Column statistics")

Not supported in ClickHouse Cloud

The statistics declaration is in the columns section of the `CREATE` query for tables from the `*MergeTree*` Family:

```
CREATE TABLE tab
(
    a Int64 STATISTICS(TDigest, Uniq),
    b Float64
)
ENGINE = MergeTree
ORDER BY a
```

We can also manipulate statistics with `ALTER` statements:

```
ALTER TABLE tab ADD STATISTICS b TYPE TDigest, Uniq;
ALTER TABLE tab DROP STATISTICS a;
```

These lightweight statistics aggregate information about distribution of values in columns. Statistics are stored in every part and updated when every insert comes.
They can be used for prewhere optimization only if we enable `set use_statistics = 1`.

#### Part Pruning with Statistics[​](#part-pruning-with-statistics "Direct link to Part Pruning with Statistics")

When `use_statistics_for_part_pruning` is enabled, statistics can be used for part pruning.
Currently, only `MinMax` statistics support part pruning. When MinMax statistics are defined on a column, ClickHouse tracks the minimum and maximum values for that column in each part.
Part pruning allows to skip reading entire data parts when the query filter condition cannot match any rows in that part.

**Example:**

```
-- Create a table with MinMax statistics on the 'value' column
CREATE TABLE test_stats
(
    id UInt64,
    value Int64 STATISTICS(MinMax)
)
ENGINE = MergeTree
ORDER BY id;

SYSTEM STOP MERGES test_stats;

-- Insert data in separate inserts to create multiple parts
INSERT INTO test_stats SELECT number, number FROM numbers(1000); -- Part 1: value range [0, 999]
INSERT INTO test_stats SELECT number, number + 10000 FROM numbers(1000); -- Part 2: value range [10000, 10999]

SET use_statistics_for_part_pruning = 1;

-- This query will skip Part 1 entirely because its max value (999) < 5000
SELECT count() FROM test_stats WHERE value > 5000;

-- Use EXPLAIN to see the pruning effect
EXPLAIN indexes = 1 SELECT count() FROM test_stats WHERE value > 5000;
-- The output will show "Parts: 1/2" indicating one part was pruned
```

### Available types of column statistics[​](#available-types-of-column-statistics "Direct link to Available types of column statistics")

- `MinMax`

  The minimum and maximum column value which allows to estimate the selectivity of range filters on numeric columns.

  Syntax: `minmax`
- `TDigest`

  [TDigest](https://github.com/tdunning/t-digest) sketches which allow to compute approximate percentiles (e.g. the 90th percentile) for numeric columns.

  Syntax: `tdigest`
- `Uniq`

  [HyperLogLog](https://en.wikipedia.org/wiki/HyperLogLog) sketches which provide an estimation how many distinct values a column contains.

  Syntax: `uniq`
- `CountMin`

  [CountMin](https://en.wikipedia.org/wiki/Count%E2%80%93min_sketch) sketches which provide an approximate count of the frequency of each value in a column.

  Syntax `countmin`

### Supported data types[​](#supported-data-types "Direct link to Supported data types")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| (U)Int\*, Float\*, Decimal(*), Date*, Boolean, Enum\* String or FixedString|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | CountMin ✔ ✔|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | MinMax ✔ ✗|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | TDigest ✔ ✗|  |  |  | | --- | --- | --- | | Uniq ✔ ✔ | | | | | | | | | | | | | | |

### Supported operations[​](#supported-operations "Direct link to Supported operations")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Equality filters (==) Range filters (`>, >=, <, <=`)|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | CountMin ✔ ✗|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | MinMax ✗ ✔|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | TDigest ✗ ✔|  |  |  | | --- | --- | --- | | Uniq ✔ ✗ | | | | | | | | | | | | | | |

## Column-level settings[​](#column-level-settings "Direct link to Column-level settings")

Certain MergeTree settings can be overridden at column level:

- `max_compress_block_size` — Maximum size of blocks of uncompressed data before compressing for writing to a table.
- `min_compress_block_size` — Minimum size of blocks of uncompressed data required for compression when writing the next mark.

Example:

```
CREATE TABLE tab
(
    id Int64,
    document String SETTINGS (min_compress_block_size = 16777216, max_compress_block_size = 16777216)
)
ENGINE = MergeTree
ORDER BY id
```

Column-level settings can be modified or removed using [ALTER MODIFY COLUMN](/docs/sql-reference/statements/alter/column), for example:

- Remove `SETTINGS` from column declaration: