MergeTree table engine

Functions with a constant argument that is less than ngram size can't be used by `ngrambf_v1` for query optimization.

(\*) For `hasTokenCaseInsensitive` and `hasTokenCaseInsensitiveOrNull` to be effective, the `tokenbf_v1` index must be created on lowercased data, for example `INDEX idx (lower(str_col)) TYPE tokenbf_v1(512, 3, 0)`.

Note

Bloom filters can have false positive matches, so the `ngrambf_v1`, `tokenbf_v1`, `sparse_grams`, and `bloom_filter` indexes can not be used for optimizing queries where the result of a function is expected to be false.

For example:

- Can be optimized:
  - `s LIKE '%test%'`
  - `NOT s NOT LIKE '%test%'`
  - `s = 1`
  - `NOT s != 1`
  - `startsWith(s, 'test')`
- Can not be optimized:
  - `NOT s LIKE '%test%'`
  - `s NOT LIKE '%test%'`
  - `NOT s = 1`
  - `s != 1`
  - `NOT startsWith(s, 'test')`

## Projections[​](#projections "Direct link to Projections")

Projections are like [materialized views](/docs/sql-reference/statements/create/view) but defined in part-level. It provides consistency guarantees along with automatic usage in queries.

Note

When you are implementing projections you should also consider the [force\_optimize\_projection](/docs/operations/settings/settings#force_optimize_projection) setting.

Projections are not supported in the `SELECT` statements with the [FINAL](/docs/sql-reference/statements/select/from#final-modifier) modifier.

### Projection query[​](#projection-query "Direct link to Projection query")

A projection query is what defines a projection. It implicitly selects data from the parent table.
**Syntax**

```
SELECT <column list expr> [GROUP BY] <group keys expr> [ORDER BY] <expr>
```

Projections can be modified or dropped with the [ALTER](/docs/sql-reference/statements/alter/projection) statement.

### Projection indexes[​](#projection-index "Direct link to Projection indexes")

Projection indexes extend the projection subsystem by providing a lightweight and explicit way to define projection-level indexes.
Externally, a projection index is still a projection, but with simplified syntax and clearer intent: it defines an expression which is dedicated to filtering, rather than serving materialized data.
Internally, a projection index does not materialize the original table in permuted row order like a regular projection.
Instead, the permutation is stored in the form of a numeric permutation column `_part_offset`, i.e. `SELECT _part_offset ORDER BY <index_expr>`.

#### Syntax[​](#projection-index-syntax "Direct link to Syntax")

```
PROJECTION <name> INDEX <index_expr> TYPE <index_type>
```

Example:

```
CREATE TABLE example
(
    id UInt64,
    region String,
    user_id UInt32,
    PROJECTION region_proj INDEX region TYPE basic,
    PROJECTION uid_proj INDEX user_id TYPE basic
)
ENGINE = MergeTree
ORDER BY id;
```

#### Index types[​](#projection-index-types "Direct link to Index types")

Currently supported:

- **basic**: equivalent to a normal MergeTree index on the expression.

The framework allows adding more index types in the future.

### Projection storage[​](#projection-storage "Direct link to Projection storage")

Projections are stored inside the part directory. It's similar to an index but contains a subdirectory that stores an anonymous `MergeTree` table's part. The table is induced by the definition query of the projection. If there is a `GROUP BY` clause, the underlying storage engine becomes [AggregatingMergeTree](/docs/engines/table-engines/mergetree-family/aggregatingmergetree), and all aggregate functions are converted to `AggregateFunction`. If there is an `ORDER BY` clause, the `MergeTree` table uses it as its primary key expression. During the merge process the projection part is merged via its storage's merge routine. The checksum of the parent table's part is combined with the projection's part. Other maintenance jobs are similar to skip indices.

### Query analysis[​](#projection-query-analysis "Direct link to Query analysis")

1. Check if the projection can be used to answer the given query, that is, it generates the same answer as querying the base table.
2. Select the best feasible match, which contains the least granules to read.
3. The query pipeline which uses projections will be different from the one that uses the original parts. If the projection is absent in some parts, we can add the pipeline to "project" it on the fly.

## Concurrent data access[​](#concurrent-data-access "Direct link to Concurrent data access")

For concurrent table access, we use multi-versioning. In other words, when a table is simultaneously read and updated, data is read from a set of parts that is current at the time of the query. There are no lengthy locks. Inserts do not get in the way of read operations.

Reading from a table is automatically parallelized.

## TTL for columns and tables[​](#table_engine-mergetree-ttl "Direct link to TTL for columns and tables")

Determines the lifetime of values.

The `TTL` clause can be set for the whole table and for each individual column. Table-level `TTL` can also specify the logic of automatic moving data between disks and volumes, or recompressing parts where all the data has been expired.

Expressions must evaluate to [Date](/docs/sql-reference/data-types/date), [Date32](/docs/sql-reference/data-types/date32), [DateTime](/docs/sql-reference/data-types/datetime) or [DateTime64](/docs/sql-reference/data-types/datetime64) data type.

Avoid non-deterministic functions in TTL expressions

TTL is evaluated during background merges, and not at insert time.
Functions like `rand()`, `now()`, or `now64()` will be re-evaluated on every merge, leading to unpredictable deletion behavior.
ClickHouse blocks expressions with no column dependency at all, but does not currently reject non-deterministic functions mixed with a column reference (e.g. `ts + rand()`). TTL expressions should be based solely on deterministic, column-derived values for predictable results.

**Syntax**

Setting time-to-live for a column:

```
TTL time_column
TTL time_column + interval
```

To define `interval`, use [time interval](/docs/sql-reference/operators#operators-for-working-with-dates-and-times) operators, for example:

```
TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR
```

### Column TTL[​](#mergetree-column-ttl "Direct link to Column TTL")

When the values in the column expire, ClickHouse replaces them with the default values for the column data type. If all the column values in the data part expire, ClickHouse deletes this column from the data part in a filesystem.

The `TTL` clause can't be used for key columns.

**Examples**

#### Creating a table with `TTL`:[​](#creating-a-table-with-ttl "Direct link to creating-a-table-with-ttl")

```
CREATE TABLE tab
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;
```

#### Adding TTL to a column of an existing table[​](#adding-ttl-to-a-column-of-an-existing-table "Direct link to Adding TTL to a column of an existing table")

```
ALTER TABLE tab
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;
```

#### Altering TTL of the column[​](#altering-ttl-of-the-column "Direct link to Altering TTL of the column")

```
ALTER TABLE tab
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 MONTH;
```

### Table TTL[​](#mergetree-table-ttl "Direct link to Table TTL")

Table can have an expression for removal of expired rows, and multiple expressions for automatic move of parts between [disks or volumes](#table_engine-mergetree-multiple-volumes). When rows in the table expire, ClickHouse deletes all corresponding rows. For parts moving or recompressing, all rows of a part must satisfy the `TTL` expression criteria.

```
TTL expr
    [DELETE|RECOMPRESS codec_name1|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|RECOMPRESS codec_name2|TO DISK 'aaa'|TO VOLUME 'bbb'] ...
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]
```