ReplacingMergeTree

Транзакционные базы данных оптимизированы для рабочих нагрузок с частыми транзакционными операциями обновления и удаления, тогда как OLAP-базы данных предоставляют ослабленные гарантии для таких операций. Вместо этого они оптимизированы под работу с неизменяемыми данными, вставляемыми пакетами, что обеспечивает значительно более высокую скорость аналитических запросов. Хотя ClickHouse предоставляет операции обновления через мутации, а также облегчённый механизм удаления строк, его колоночная структура означает, что эти операции следует планировать с осторожностью, как было описано выше. Эти операции обрабатываются асинхронно, выполняются в одном потоке и требуют (в случае обновлений) перезаписи данных на диске. Поэтому их не следует использовать для большого количества небольших изменений. Чтобы обрабатывать поток операций обновления и удаления строк, избегая описанных выше сценариев использования, мы можем использовать табличный движок ClickHouse ReplacingMergeTree.

Автоматические upsert-операции для вставленных строк

Движок таблицы ReplacingMergeTree позволяет применять операции обновления к строкам без необходимости использовать неэффективные операторы ALTER или DELETE. Это достигается за счёт того, что пользователи могут вставлять несколько копий одной и той же строки и помечать одну из них как последнюю версию. Фоновый процесс, в свою очередь, асинхронно удаляет более старые версии той же строки, эффективно имитируя операцию обновления за счёт использования неизменяемых вставок. Это основано на способности движка таблицы определять дубликаты строк. Это реализуется с помощью предложения ORDER BY, используемого для определения уникальности, т. е. если две строки имеют одинаковые значения для столбцов, указанных в ORDER BY, они считаются дубликатами. Столбец version, задаваемый при определении таблицы, позволяет сохранить последнюю версию строки, когда две строки определены как дубликаты, то есть сохраняется строка с наибольшим значением версии. Мы иллюстрируем этот процесс на примере ниже. Здесь строки однозначно идентифицируются столбцом A (ORDER BY для таблицы). Мы предполагаем, что эти строки были вставлены двумя партиями, в результате чего на диске образовались две части данных. Позже, в ходе асинхронного фонового процесса, эти части объединяются.

ReplacingMergeTree дополнительно позволяет указать столбец deleted. Он может содержать 0 или 1, где значение 1 указывает, что строка (и её дубликаты) была удалена, а ноль используется в противном случае. Примечание: удалённые строки не будут удалены во время слияния.

Во время этого процесса при слиянии частей происходит следующее:

• Строка, идентифицируемая значением 1 для столбца A, имеет и строку обновления с версией 2, и строку удаления с версией 3 (и значением столбца deleted, равным 1). Поэтому сохраняется последняя строка, помеченная как удалённая.
• Строка, идентифицируемая значением 2 для столбца A, имеет две строки обновления. Сохраняется последняя строка со значением 6 в столбце price.
• Строка, идентифицируемая значением 3 для столбца A, имеет строку с версией 1 и строку удаления с версией 2. Сохраняется эта строка удаления.

В результате этого процесса слияния у нас есть четыре строки, представляющие конечное состояние:

Обратите внимание, что удалённые строки никогда не удаляются. Их можно принудительно удалить с помощью OPTIMIZE table FINAL CLEANUP. Для этого требуется экспериментальная настройка allow_experimental_replacing_merge_with_cleanup=1. Эту операцию следует выполнять только при соблюдении следующих условий:

1. Вы уверены, что строки со старыми версиями (для тех, которые удаляются при очистке) не будут вставлены после выполнения операции. Если они всё же будут вставлены, они будут ошибочно сохранены, так как удалённые строки больше не будут присутствовать.
2. Убедитесь, что все реплики синхронизированы перед выполнением очистки. Этого можно добиться с помощью команды:

Таблица SYSTEM SYNC REPLICA

Мы рекомендуем приостановить вставки после того, как выполнение условия (1) гарантировано, и до завершения этой команды и последующей очистки.

Обработка удалений с использованием ReplacingMergeTree рекомендуется только для таблиц с небольшим или умеренным числом удалений (менее 10%), за исключением случаев, когда можно планировать периоды очистки при соблюдении вышеуказанных условий.

Совет: пользователи также могут выполнить OPTIMIZE FINAL CLEANUP для выборочных партиций, которые больше не подвержены изменениям.

Выбор первичного/дедупликационного ключа

Выше мы выделили важное дополнительное ограничение, которое также должно соблюдаться в случае ReplacingMergeTree: значения столбцов, указанных в ORDER BY, должны однозначно идентифицировать строку при любых изменениях. Если выполняется миграция из транзакционной базы данных, такой как Postgres, исходный первичный ключ Postgres, таким образом, должен быть включён в предложение ORDER BY в ClickHouse.

Пользователи ClickHouse знакомы с выбором столбцов в предложении ORDER BY своих таблиц для оптимизации производительности запросов. Как правило, эти столбцы следует выбирать на основе часто выполняемых запросов и перечислять в порядке возрастания кардинальности. Важно, что ReplacingMergeTree накладывает дополнительное ограничение — эти столбцы должны быть неизменяемыми, т.е. при репликации из Postgres добавляйте столбцы в это предложение только в том случае, если они не изменяются в исходных данных Postgres. Хотя другие столбцы могут меняться, именно эти столбцы должны оставаться постоянными для уникальной идентификации строк.

Для аналитических нагрузок первичный ключ Postgres, как правило, мало полезен, поскольку пользователи редко выполняют точечный поиск отдельных строк. Учитывая, что мы рекомендуем упорядочивать столбцы в порядке возрастания кардинальности, а также тот факт, что сравнения по столбцам, идущим раньше в ORDER BY, обычно выполняются быстрее, первичный ключ Postgres следует добавлять в конец ORDER BY (если только он не имеет аналитической ценности). Если в Postgres первичный ключ составной (несколько столбцов), их также следует добавить в ORDER BY, учитывая кардинальность и ожидаемую полезность для запросов. Пользователи также могут сгенерировать уникальный первичный ключ с помощью конкатенации значений в столбце MATERIALIZED.

Рассмотрим таблицу posts из набора данных Stack Overflow.

CREATE TABLE stackoverflow.posts_updateable
(
       `Version` UInt32,
       `Deleted` UInt8,
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime64(3, 'UTC'),
        `Score` Int32,
        `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
        `Body` String,
        `OwnerUserId` Int32,
        `OwnerDisplayName` String,
        `LastEditorUserId` Int32,
        `LastEditorDisplayName` String,
        `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
        `LastActivityDate` DateTime64(3, 'UTC'),
        `Title` String,
        `Tags` String,
        `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
        `CommentCount` UInt8,
        `FavoriteCount` UInt8,
        `ContentLicense` LowCardinality(String),
        `ParentId` String,
        `CommunityOwnedDate` DateTime64(3, 'UTC'),
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = ReplacingMergeTree(Version, Deleted)
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

Мы используем ключ ORDER BY вида (PostTypeId, toDate(CreationDate), CreationDate, Id). Столбец Id, уникальный для каждого поста, обеспечивает возможность удаления дубликатов строк. В схему при необходимости добавляются столбцы Version и Deleted.

Выполнение запросов к ReplacingMergeTree

При слиянии данных движок ReplacingMergeTree определяет дублирующиеся строки, используя значения столбцов ORDER BY как уникальный идентификатор, и либо сохраняет только строку с наибольшей версией, либо удаляет все дубликаты, если последняя версия помечает строку как удалённую. Однако это даёт лишь корректность «со временем» — нет гарантии, что строки действительно будут дедуплицированы, и на это не следует полагаться. В результате запросы могут возвращать некорректные ответы, поскольку строки с обновлениями и удалениями продолжают участвовать в выборках.

Чтобы получать корректные ответы, пользователям необходимо дополнять фоновые слияния дедупликацией и удалением на этапе выполнения запроса. Это можно сделать с помощью оператора FINAL.

Рассмотрим приведённую выше таблицу posts. Мы можем воспользоваться обычным методом загрузки этого набора данных, но указать столбцы deleted и version, задав для них значение 0. В качестве примера мы загружаем только 10000 строк.

INSERT INTO stackoverflow.posts_updateable SELECT 0 AS Version, 0 AS Deleted, *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet') WHERE AnswerCount > 0 LIMIT 10000

0 строк в наборе. Время: 1,980 с. Обработано 8,19 тыс. строк, 3,52 МБ (4,14 тыс. строк/с, 1,78 МБ/с).

Давайте проверим количество строк:

SELECT count() FROM stackoverflow.posts_updateable

┌─count()─┐
│   10000 │
└─────────┘

1 строка в наборе. Время выполнения: 0.002 сек.

Теперь обновим нашу статистику по публикациям и ответам. Вместо того чтобы изменять существующие значения, мы вставляем новые копии 5000 строк и увеличиваем их номер версии на единицу (это означает, что в таблице будет существовать 150 строк). Это можно смоделировать с помощью простого INSERT INTO SELECT:

INSERT INTO posts_updateable SELECT
        Version + 1 AS Version,
        Deleted,
        Id,
        PostTypeId,
        AcceptedAnswerId,
        CreationDate,
        Score,
        ViewCount,
        Body,
        OwnerUserId,
        OwnerDisplayName,
        LastEditorUserId,
        LastEditorDisplayName,
        LastEditDate,
        LastActivityDate,
        Title,
        Tags,
        AnswerCount,
        CommentCount,
        FavoriteCount,
        ContentLicense,
        ParentId,
        CommunityOwnedDate,
        ClosedDate
FROM posts_updateable --выбор 100 случайных строк
WHERE (Id % toInt32(floor(randUniform(1, 11)))) = 0
LIMIT 5000

0 строк в результирующем наборе. Время выполнения: 4.056 с. Обработано 1.42 млн строк, 2.20 ГБ (349.63 тыс. строк/с, 543.39 МБ/с).

Кроме того, мы удаляем 1000 случайных постов, повторно вставляя строки, но уже со значением столбца deleted, равным 1. Это также можно смоделировать с помощью простого INSERT INTO SELECT.

INSERT INTO posts_updateable SELECT
        Version + 1 AS Version,
        1 AS Deleted,
        Id,
        PostTypeId,
        AcceptedAnswerId,
        CreationDate,
        Score,
        ViewCount,
        Body,
        OwnerUserId,
        OwnerDisplayName,
        LastEditorUserId,
        LastEditorDisplayName,
        LastEditDate,
        LastActivityDate,
        Title,
        Tags,
        AnswerCount + 1 AS AnswerCount,
        CommentCount,
        FavoriteCount,
        ContentLicense,
        ParentId,
        CommunityOwnedDate,
        ClosedDate
FROM posts_updateable --выборка 100 случайных строк
WHERE (Id % toInt32(floor(randUniform(1, 11)))) = 0 AND AnswerCount > 0
LIMIT 1000

0 строк в наборе. Прошло: 0.166 сек. Обработано 135.53 тыс. строк, 212.65 МБ (816.30 тыс. строк/с, 1.28 ГБ/с.)

Результатом вышеописанных операций будет 16 000 строк, т.е. 10 000 + 5 000 + 1 000. Однако правильный итог должен быть таким: у нас должно быть всего на 1 000 строк меньше исходного количества, т.е. 10 000 − 1 000 = 9 000.

SELECT count()
FROM posts_updateable

┌─count()─┐
│   10000 │
└─────────┘
1 строка в выборке. Затрачено: 0.002 сек.

Ваши результаты здесь будут различаться в зависимости от выполненных слияний. Мы видим, что общий итог отличается, так как у нас есть дублирующиеся строки. Применение FINAL к таблице даёт корректный результат.

SELECT count()
FROM posts_updateable
FINAL

┌─count()─┐
│    9000 │
└─────────┘

1 строка в наборе. Время выполнения: 0.006 сек. Обработано 11.81 тысячи строк, 212.54 KB (2.14 миллиона строк/с, 38.61 MB/с.)
Пиковое потребление памяти: 8.14 MiB.

Производительность с FINAL

Оператор FINAL действительно оказывает небольшое влияние на производительность запросов. Это будет наиболее заметно, когда запросы не фильтруют по столбцам первичного ключа, что приводит к чтению большего объёма данных и увеличивает накладные расходы на дедупликацию. Если пользователи фильтруют по столбцам ключа с помощью условия WHERE, объём данных, загружаемых и передаваемых для дедупликации, будет уменьшен.

Если условие WHERE не использует столбец ключа, ClickHouse в текущей реализации не использует оптимизацию PREWHERE при использовании FINAL. Эта оптимизация направлена на сокращение количества строк, читаемых для столбцов, по которым не происходит фильтрация. Примеры эмуляции такого поведения PREWHERE и, соответственно, потенциального улучшения производительности можно найти здесь.

Эффективное использование партиций с ReplacingMergeTree

Слияние данных в ClickHouse происходит на уровне партиций. При использовании ReplacingMergeTree мы рекомендуем разбивать таблицу на партиции в соответствии с лучшими практиками при условии, что ключ партиционирования для строки не изменяется. Это гарантирует, что обновления, относящиеся к одной и той же строке, будут попадать в одну и ту же партицию ClickHouse. Вы можете использовать тот же ключ партиционирования, что и в Postgres, при условии соблюдения лучших практик, описанных здесь.

Если это выполняется, можно использовать настройку do_not_merge_across_partitions_select_final=1 для повышения производительности запросов с FINAL. Эта настройка приводит к тому, что партиции сливаются и обрабатываются независимо друг от друга при использовании FINAL.

Рассмотрим следующую таблицу posts, в которой мы не используем партиционирование:

CREATE TABLE stackoverflow.posts_no_part
(
        `Version` UInt32,
        `Deleted` UInt8,
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        ...
)
ENGINE = ReplacingMergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

INSERT INTO stackoverflow.posts_no_part SELECT 0 AS Version, 0 AS Deleted, *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

0 строк в наборе. Время: 182.895 сек. Обработано 59.82 млн строк, 38.07 ГБ (327.07 тыс. строк/с., 208.17 МБ/с.)

Чтобы гарантировать, что FINAL вынужден выполнять некоторую работу, мы обновляем 1 млн строк — увеличиваем их AnswerCount, вставляя дублирующиеся строки.

INSERT INTO posts_no_part SELECT Version + 1 AS Version, Deleted, Id, PostTypeId, AcceptedAnswerId, CreationDate, Score, ViewCount, Body, OwnerUserId, OwnerDisplayName, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, Title, Tags, AnswerCount + 1 AS AnswerCount, CommentCount, FavoriteCount, ContentLicense, ParentId, CommunityOwnedDate, ClosedDate
FROM posts_no_part
LIMIT 1000000

Вычисление суммы ответов за каждый год с использованием FINAL:

SELECT toYear(CreationDate) AS year, sum(AnswerCount) AS total_answers
FROM posts_no_part
FINAL
GROUP BY year
ORDER BY year ASC

┌─year─┬─total_answers─┐
│ 2008 │        371480 │
...
│ 2024 │        127765 │
└──────┴───────────────┘

17 строк в выборке. Время выполнения: 2.338 с. Обработано 122.94 млн строк, 1.84 ГБ (52.57 млн строк/с, 788.58 МБ/с).
Пиковое потребление памяти: 2.09 ГиБ.

Повторите те же шаги для таблицы, секционированной по годам, и снова выполните приведённый выше запрос с do_not_merge_across_partitions_select_final=1.

CREATE TABLE stackoverflow.posts_with_part
(
        `Version` UInt32,
        `Deleted` UInt8,
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        ...
)
ENGINE = ReplacingMergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

// загрузка данных и обновление опущены

SELECT toYear(CreationDate) AS year, sum(AnswerCount) AS total_answers
FROM posts_with_part
FINAL
GROUP BY year
ORDER BY year ASC

┌─year─┬─total_answers─┐
│ 2008 │       387832  │
│ 2009 │       1165506 │
│ 2010 │       1755437 │
...
│ 2023 │       787032  │
│ 2024 │       127765  │
└──────┴───────────────┘

17 строк в наборе результатов. Время выполнения: 0,994 с. Обработано 64,65 млн строк, 983,64 МБ (65,02 млн строк/с, 989,23 МБ/с.)

Как видно, секционирование в данном случае значительно улучшило производительность запроса, позволив выполнять процесс дедупликации параллельно на уровне партиций.

Особенности поведения слияний

Механизм выбора слияний в ClickHouse выходит за рамки простого объединения частей. Ниже мы рассматриваем это поведение в контексте ReplacingMergeTree, включая параметры конфигурации для более агрессивного слияния старых данных и аспекты, касающиеся крупных частей.

Логика выбора слияний

Хотя цель слияния — минимизировать количество частей, при этом также учитывается цена эффекта write amplification. В результате некоторые диапазоны частей исключаются из слияния, если оно, согласно внутренним вычислениям, приведёт к чрезмерному write amplification. Такое поведение помогает избежать ненужного расхода ресурсов и продлевает срок службы компонентов хранилища.

Поведение слияний для крупных частей

Движок ReplacingMergeTree в ClickHouse оптимизирован для управления дублирующимися строками путём слияния частей данных, сохраняя только последнюю версию каждой строки на основе заданного уникального ключа. Однако когда объединённая часть достигает порога max_bytes_to_merge_at_max_space_in_pool, она больше не будет выбираться для дальнейшего слияния, даже если установлен min_age_to_force_merge_seconds. В результате на автоматические слияния больше нельзя полагаться для удаления дублей, которые могут накапливаться при непрерывной вставке данных.

Для решения этой проблемы пользователи могут вызывать OPTIMIZE FINAL для ручного слияния частей и удаления дублей. В отличие от автоматических слияний, OPTIMIZE FINAL игнорирует порог max_bytes_to_merge_at_max_space_in_pool и выполняет слияние частей, основываясь только на доступных ресурсах, в первую очередь дисковом пространстве, до тех пор, пока в каждом разделе не останется одна часть. Однако такой подход может потреблять много памяти на больших таблицах и может требовать повторного выполнения по мере добавления новых данных.

Более практичным и производительным решением является разбиение таблицы на разделы (partitioning). Это помогает предотвратить достижение частью максимального размера для слияния и снижает потребность в постоянных ручных оптимизациях.

Разбиение на разделы и слияние между разделами

Как обсуждается в разделе Exploiting Partitions with ReplacingMergeTree, мы рекомендуем разбиение таблиц на разделы как наилучшую практику. Разбиение изолирует данные для более эффективных слияний и предотвращает слияния между разделами, особенно во время выполнения запросов. Начиная с версий 23.12 это поведение улучшено: если ключ раздела является префиксом ключа сортировки, слияния между разделами не выполняются на этапе выполнения запроса, что приводит к более высокой производительности запросов.

Настройка слияний для улучшения производительности запросов

По умолчанию параметры min_age_to_force_merge_seconds и min_age_to_force_merge_on_partition_only установлены в 0 и false соответственно, что отключает эти функции. В такой конфигурации ClickHouse применяет стандартное поведение слияний без принудительного слияния на основе возраста раздела.

Если указано значение для min_age_to_force_merge_seconds, ClickHouse будет игнорировать обычные эвристики слияния для частей, возраст которых превышает заданный период. Хотя это, как правило, эффективно только в том случае, если цель — минимизировать общее количество частей, в ReplacingMergeTree это может улучшить производительность запросов за счёт уменьшения количества частей, которые нужно сливать во время выполнения запроса.

Это поведение можно дополнительно настроить, установив min_age_to_force_merge_on_partition_only=true, что требует, чтобы все части в разделе были старше min_age_to_force_merge_seconds для агрессивного слияния. Такая конфигурация позволяет старым разделам со временем сливаться до одной части, что консолидирует данные и поддерживает производительность запросов.

Рекомендуемые настройки

Примечание

Настройка поведения слияний — это продвинутая операция. Мы рекомендуем проконсультироваться со службой поддержки ClickHouse перед включением этих настроек в продакшн-средах.

В большинстве случаев предпочтительно устанавливать min_age_to_force_merge_seconds в небольшое значение — значительно меньше периода раздела. Это минимизирует количество частей и предотвращает ненужные слияния во время выполнения запроса с оператором FINAL.

Например, рассмотрим ежемесячный раздел, который уже был слит в одну часть. Если небольшая, разовая вставка создаёт новую часть в этом разделе, производительность запросов может ухудшиться, поскольку ClickHouse должен читать несколько частей до завершения слияния. Установка min_age_to_force_merge_seconds может обеспечить агрессивное слияние этих частей, предотвращая деградацию производительности запросов.