title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/mergetree-family/collapsingmergetree.md)

## Description[​](#description "Direct link to Description")

The `CollapsingMergeTree` engine inherits from [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree)
and adds logic for collapsing rows during the merge process.
The `CollapsingMergeTree` table engine asynchronously deletes (collapses)
pairs of rows if all the fields in a sorting key (`ORDER BY`) are equivalent except for the special field `Sign`,
which can have values of either `1` or `-1`.
Rows without a pair of opposite valued `Sign` are kept.

For more details, see the [Collapsing](#table_engine-collapsingmergetree-collapsing) section of the document.

Note

This engine may significantly reduce the volume of storage,
increasing the efficiency of `SELECT` queries as a consequence.

## Parameters[​](#parameters "Direct link to Parameters")

All parameters of this table engine, with the exception of the `Sign` parameter,
have the same meaning as in [`MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree).

- `Sign` — The name given to a column with the type of row where `1` is a "state" row and `-1` is a "cancel" row. Type: [Int8](/docs/sql-reference/data-types/int-uint).

## Creating a table[​](#creating-a-table "Direct link to Creating a table")

```
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) 
ENGINE = CollapsingMergeTree(Sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
```

Deprecated Method for Creating a Table

Note

The method below is not recommended for use in new projects.
We advise, if possible, to update old projects to use the new method.

```
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) 
ENGINE [=] CollapsingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity, Sign)
```

`Sign` — The name given to a column with the type of row where `1` is a "state" row and `-1` is a "cancel" row. [Int8](/docs/sql-reference/data-types/int-uint).

- For a description of query parameters, see [query description](/docs/sql-reference/statements/create/table).
- When creating a `CollapsingMergeTree` table, the same [query clauses](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-creating-a-table) are required, as when creating a `MergeTree` table.

## Collapsing[​](#table_engine-collapsingmergetree-collapsing "Direct link to Collapsing")

### Data[​](#data "Direct link to Data")

Consider the situation where you need to save continually changing data for some given object.
It may sound logical to have one row per object and update it anytime something changes,
however, update operations are expensive and slow for the DBMS because they require rewriting the data in storage.
If we need to write data quickly, performing large numbers of updates is not an acceptable approach,
but we can always write the changes of an object sequentially.
To do so, we make use of the special column `Sign`.

- If `Sign` = `1` it means that the row is a "state" row: *a row containing fields which represent a current valid state*.
- If `Sign` = `-1` it means that the row is a "cancel" row: *a row used for the cancellation of state of an object with the same attributes*.

For example, we want to calculate how many pages users checked on some website and how long they visited them for.
At some given moment in time, we write the following row with the state of user activity:

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

At a later moment in time, we register the change of user activity and write it with the following two rows:

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
```

The first row cancels the previous state of the object (representing a user in this case).
It should copy all the sorting key fields for the "canceled" row except for `Sign`.
The second row above contains the current state.

As we need only the last state of user activity, the original "state" row and the "cancel"
row that we inserted can be deleted as shown below, collapsing the invalid (old) state of an object:

```
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │ -- old "state" row can be deleted
│ 4324182021466249494 │         5 │      146 │   -1 │ -- "cancel" row can be deleted
│ 4324182021466249494 │         6 │      185 │    1 │ -- new "state" row remains
└─────────────────────┴───────────┴──────────┴──────┘
```

`CollapsingMergeTree` carries out precisely this *collapsing* behavior while merging of the data parts takes place.

Note

The reason for why two rows are needed for each change
is further discussed in the [Algorithm](#table_engine-collapsingmergetree-collapsing-algorithm) paragraph.

**The peculiarities of such an approach**

1. The program that writes the data should remember the state of an object to be able to cancel it. The "cancel" row should contain copies of sorting key fields of the "state" and the opposite `Sign`. This increases the initial size of storage but allows us to write the data quickly.
2. Long growing arrays in columns reduce the efficiency of the engine due to the increased load for writing. The more straightforward the data, the higher the efficiency.
3. The `SELECT` results depend strongly on the consistency of the object change history. Be accurate when preparing data for inserting. You can get unpredictable results with inconsistent data. For example, negative values for non-negative metrics such as session depth.

### Algorithm[​](#table_engine-collapsingmergetree-collapsing-algorithm "Direct link to Algorithm")

When ClickHouse merges data [parts](/docs/concepts/glossary#parts),
each group of consecutive rows with the same sorting key (`ORDER BY`) is reduced to no more than two rows,
the "state" row with `Sign` = `1` and the "cancel" row with `Sign` = `-1`.
In other words, in ClickHouse entries collapse.

For each resulting data part ClickHouse saves:

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | 1. The first "cancel" and the last "state" rows, if the number of "state" and "cancel" rows matches and the last row is a "state" row.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 2. The last "state" row, if there are more "state" rows than "cancel" rows.|  |  |  |  | | --- | --- | --- | --- | | 3. The first "cancel" row, if there are more "cancel" rows than "state" rows.|  |  | | --- | --- | | 4. None of the rows, in all other cases. | | | | | | | | | |

Additionally, when there are at least two more "state" rows than "cancel"
rows, or at least two more "cancel" rows than "state" rows, the merge continues.
ClickHouse, however, treats this situation as a logical error and records it in the server log.
This error can occur if the same data is inserted more than once.
Thus, collapsing should not change the results of calculating statistics.
Changes are gradually collapsed so that in the end only the last state of almost every object is left.

The `Sign` column is required because the merging algorithm does not guarantee
that all the rows with the same sorting key will be in the same resulting data part and even on the same physical server.
ClickHouse processes `SELECT` queries with multiple threads, and it cannot predict the order of rows in the result.