Deduplication strategies

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/developer/deduplication.md)

**Deduplication** refers to the process of ***removing duplicate rows of a dataset***. In an OLTP database, this is done easily because each row has a unique primary key-but at the cost of slower inserts. Every inserted row needs to first be searched for and, if found, needs to be replaced.

ClickHouse is built for speed when it comes to data insertion. The storage files are immutable and ClickHouse doesn't check for an existing primary key before inserting a row-so deduplication involves a bit more effort. This also means that deduplication isn't immediate-it is **eventual**, which has a few side effects:

- At any moment in time your table can still have duplicates (rows with the same sorting key)
- The actual removal of duplicate rows occurs during the merging of parts
- Your queries need to allow for the possibility of duplicates

|  |  |  |  |
| --- | --- | --- | --- |
| |  |  | | --- | --- | | ClickHouse provides free training on deduplication and many other topics. The [Deleting and Updating Data training module](https://learn.clickhouse.com/visitor_catalog_class/show/1328954/?utm_source=clickhouse&utm_medium=docs) is a good place to start. | | | |

## Options for deduplication[​](#options-for-deduplication "Direct link to Options for deduplication")

Deduplication is implemented in ClickHouse using the following table engines:

1. `ReplacingMergeTree` table engine: with this table engine, duplicate rows with the same sorting key are removed during merges. `ReplacingMergeTree` is a good option for emulating upsert behavior (where you want queries to return the last row inserted).
2. Collapsing rows: the `CollapsingMergeTree` and `VersionedCollapsingMergeTree` table engines use a logic where an existing row is "canceled" and a new row is inserted. They're more complex to implement than `ReplacingMergeTree`, but your queries and aggregations can be simpler to write without worrying about whether or not data has been merged yet. These two table engines are useful when you need to update data frequently.

We walk through both of these techniques below. For more details, check out our free on-demand [Deleting and Updating Data training module](https://learn.clickhouse.com/visitor_catalog_class/show/1328954/?utm_source=clickhouse&utm_medium=docs).

## Using ReplacingMergeTree for Upserts[​](#using-replacingmergetree-for-upserts "Direct link to Using ReplacingMergeTree for Upserts")

Let's look at a simple example where a table contains Hacker News comments with a views column representing the number of times a comment was viewed. Suppose we insert a new row when an article is published and upsert a new row once a day with the total number of views if the value increases:

```
CREATE TABLE hackernews_rmt (
    id UInt32,
    author String,
    comment String,
    views UInt64
)
ENGINE = ReplacingMergeTree
PRIMARY KEY (author, id)
```

Let's insert two rows:

```
INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'This is post #1', 0),
   (2, 'ch_fan', 'This is post #2', 0)
```

To update the `views` column, insert a new row with the same primary key (notice the new values of the `views` column):

```
INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'This is post #1', 100),
   (2, 'ch_fan', 'This is post #2', 200)
```

The table now has 4 rows:

```
SELECT *
FROM hackernews_rmt
```

```
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │     0 │
│  1 │ ricardo │ This is post #1 │     0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   200 │
│  1 │ ricardo │ This is post #1 │   100 │
└────┴─────────┴─────────────────┴───────┘
```

The separate boxes above in the output demonstrate the two parts behind-the-scenes - this data hasn't been merged yet, so the duplicate rows haven't been removed yet. Let's use the `FINAL` keyword in the `SELECT` query, which results in a logical merging of the query result:

```
SELECT *
FROM hackernews_rmt
FINAL
```

```
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   200 │
│  1 │ ricardo │ This is post #1 │   100 │
└────┴─────────┴─────────────────┴───────┘
```

The result only has 2 rows, and the last row inserted is the row that gets returned.

Note

Using `FINAL` works okay if you have a small amount of data. If you're dealing with a large amount of data,
using `FINAL` is probably not the best option. Let's discuss a better option for
finding the latest value of a column.

### Avoiding FINAL[​](#avoiding-final "Direct link to Avoiding FINAL")

Let's update the `views` column again for both unique rows:

```
INSERT INTO hackernews_rmt VALUES
   (1, 'ricardo', 'This is post #1', 150),
   (2, 'ch_fan', 'This is post #2', 250)
```

The table has 6 rows now, because an actual merge hasn't happened yet (only the query-time merge when we used `FINAL`).

```
SELECT *
FROM hackernews_rmt
```

```
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   200 │
│  1 │ ricardo │ This is post #1 │   100 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │     0 │
│  1 │ ricardo │ This is post #1 │     0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│  2 │ ch_fan  │ This is post #2 │   250 │
│  1 │ ricardo │ This is post #1 │   150 │
└────┴─────────┴─────────────────┴───────┘
```

Instead of using `FINAL`, let's use some business logic - we know that the `views` column is always increasing, so we can select the row with the largest value using the `max` function after grouping by the desired columns:

```
SELECT
    id,
    author,
    comment,
    max(views)
FROM hackernews_rmt
GROUP BY (id, author, comment)
```

```
┌─id─┬─author──┬─comment─────────┬─max(views)─┐
│  2 │ ch_fan  │ This is post #2 │        250 │
│  1 │ ricardo │ This is post #1 │        150 │
└────┴─────────┴─────────────────┴────────────┘
```

Grouping as shown in the query above can actually be more efficient (in terms of query performance) than using the `FINAL` keyword.

Our [Deleting and Updating Data training module](https://learn.clickhouse.com/visitor_catalog_class/show/1328954/?utm_source=clickhouse&utm_medium=docs) expands on this example, including how to use a `version` column with `ReplacingMergeTree`.

## Using CollapsingMergeTree for updating columns frequently[​](#using-collapsingmergetree-for-updating-columns-frequently "Direct link to Using CollapsingMergeTree for updating columns frequently")

Updating a column involves deleting an existing row and replacing it with new values. As you have already seen, this type of mutation in ClickHouse happens *eventually* - during merges. If you have a lot of rows to update, it can actually be more efficient to avoid `ALTER TABLE..UPDATE` and instead just insert the new data alongside the existing data. We could add a column that denotes whether or not the data is stale or new... and there is actually a table engine that already implements this behavior very nicely, especially considering that it deletes the stale data automatically for you. Let's see how it works.

Suppose we track the number of views that a Hacker News comment has using an external system and every few hours, we push the data into ClickHouse. We want the old rows deleted and the new rows to represent the new state of each Hacker News comment. We can use a `CollapsingMergeTree` to implement this behavior.

Let's define a table to store the number of views:

```
CREATE TABLE hackernews_views (
    id UInt32,
    author String,
    views UInt64,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PRIMARY KEY (id, author)
```

Notice the `hackernews_views` table has an `Int8` column named sign which is referred to as the **sign** column. The name of the sign column is arbitrary, but the `Int8` data type is required, and notice the column name was passed in to the constructor of the `CollapsingMergeTree` table.