Обновления в ClickHouse

Руководство по доступным методам обновления и выбору подходящего метода для вашей рабочей нагрузки.

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

В этом руководстве представлен обзор доступных в ClickHouse методов обновления, а также рекомендации по выбору подходящей стратегии обновления для вашей рабочей нагрузки.

​

Выбор стратегии обновления

Существует два основных подхода к обновлению данных в ClickHouse:

1. Использование специализированных движков таблиц, которые выполняют обновления через вставки
2. Использование декларативных обновлений, таких как операторы UPDATE ... SET или ALTER TABLE ... UPDATE

В каждой из этих двух категорий есть несколько способов обновления данных. У каждого из них свои преимущества и особенности производительности, поэтому выбирать подходящий метод следует исходя из модели данных и объема данных, который вы планируете обновлять.

​

Когда использовать специализированные движки таблиц

Специализированные движки таблиц — лучший выбор, если у вас большие объёмы обновлений, часто меняются отдельные строки или нужно обрабатывать непрерывный поток событий обновления и удаления.

Чаще всего встречаются следующие движки:

Движок	Синтаксис	Когда использовать
ReplacingMergeTree	ENGINE = ReplacingMergeTree	Используйте, когда обновляете большие объёмы данных. Этот движок таблицы оптимизирован для дедупликации данных при слияниях.
CoalescingMergeTree	ENGINE = CoalescingMergeTree	Используйте, когда данные поступают фрагментами и требуется коалесценция на уровне столбцов, а не полная замена строки.
CollapsingMergeTree	ENGINE = CollapsingMergeTree(Sign)	Используйте, когда отдельные строки часто обновляются или когда нужно поддерживать актуальное состояние объектов, которые меняются со временем. Например, для отслеживания активности пользователей или статистики статей.

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

​

Когда использовать декларативные обновления

Декларативные операторы UPDATE могут быть удобнее для простых обновлений, когда не нужно управлять логикой дедупликации, но в целом они лучше подходят для обновления меньшего числа строк и реже, чем специализированные движки.

Метод	Синтаксис	Когда использовать
Легковесные обновления	UPDATE [table] SET ... WHERE	Используйте в большинстве сценариев, особенно если часто выполняете небольшие UPDATE (до ~10% таблицы) как часть приложения или рабочих процессов. Например, пользователь хочет удалить свою историю событий, а сами события распределены по мультитенантной таблице со множеством пользователей. Этот подход создаёт патч-части, которые сразу становятся видимыми, без перезаписи целых столбцов. Он добавляет накладные расходы к запросам SELECT, но обеспечивает предсказуемую задержку.
UPDATE-мутация	ALTER TABLE [table] UPDATE	Используйте этот вариант для более масштабного управления данными, особенно когда обновление согласуется с партиционированием таблицы. Например, если нужно обновить столбец во всех строках за месяц в таблице, разбитой на партиции по месяцам.

​

Обновления с помощью специализированных движков таблиц

​

ReplacingMergeTree

ReplacingMergeTree выполняет дедупликацию строк с одинаковым ключом сортировки во время фоновых слияний, оставляя только самую новую версию.

CREATE TABLE posts
(
    Id UInt32,
    Title String,
    ViewCount UInt32,
    Version UInt32
)
ENGINE = ReplacingMergeTree(Version)
ORDER BY Id

Этот движок идеально подходит для высокочастотных обновлений отдельных строк, где обновления идентифицируются стабильным ключом. Бенчмарки показывают, что при обновлении одной строки он может быть до 4 700 раз быстрее, чем мутации.

Чтобы обновить строку, просто вставьте новую версию с теми же значениями ключа сортировки и более высоким номером версии. Старые версии удаляются во время фоновых слияний. Поскольку дедупликация происходит не сразу (только во время слияний), для получения корректных дедуплицированных результатов следует использовать модификатор FINAL или эквивалентную логику запроса. Модификатор FINAL добавляет к запросу дополнительную нагрузку в диапазоне 21–550% в зависимости от данных.

ReplacingMergeTree не может обновлять значения ключа сортировки. Он также поддерживает столбец Deleted для логических удалений.

Подробнее: руководство по ReplacingMergeTree | справочник по ReplacingMergeTree

​

CoalescingMergeTree

CoalescingMergeTree консолидирует разреженные записи, сохраняя при слиянии последнее значение, отличное от NULL, для каждого столбца. Это позволяет выполнять upsert на уровне столбцов, а не полностью заменять строку.

CREATE TABLE electric_vehicle_state
(
    vin String, -- идентификационный номер транспортного средства
    last_update DateTime64 Materialized now64(), -- необязательно (используется с argMax)
    battery_level Nullable(UInt8), -- в %
    lat Nullable(Float64), -- широта (°)
    lon Nullable(Float64), -- долгота (°)
    firmware_version Nullable(String),
    cabin_temperature Nullable(Float32), -- в °C
    speed_kmh Nullable(Float32) -- с датчика
)
ENGINE = CoalescingMergeTree
ORDER BY vin;

Этот движок предназначен для сценариев, в которых данные поступают фрагментами из нескольких источников или разные столбцы заполняются в разное время. Типичные примеры использования: IoT-телеметрия из разрозненных подсистем, обогащение пользовательских профилей и ETL-конвейеры с измерениями, поступающими с задержкой.

Когда строки с одинаковым ключом сортировки сливаются, CoalescingMergeTree сохраняет для каждого столбца последнее значение, отличное от NULL, вместо замены всей строки. Чтобы это работало как задумано, неключевые столбцы должны быть Nullable. Как и в случае с ReplacingMergeTree, для получения корректных результатов после коалесценции используйте FINAL.

Этот движок доступен начиная с ClickHouse 25.6.

Подробнее: CoalescingMergeTree

​

CollapsingMergeTree

Исходя из того, что обновления обходятся дорого, но вместо них можно использовать вставки, CollapsingMergeTree использует столбец Sign, чтобы указать ClickHouse, как обрабатывать строки во время слияний. Если в столбец Sign вставляется значение -1, строка схлопывается (удаляется) при наличии соответствующей строки с +1. Строки для обновления определяются на основе ключа сортировки, заданного в выражении ORDER BY при создании таблицы.

CREATE TABLE user_activity
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

-- Начальное состояние
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, 1)

-- Отменить старую строку и вставить новое состояние
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, -1)
INSERT INTO user_activity VALUES (4324182021466249494, 6, 185, 1)

-- Запрос с корректной агрегацией
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM user_activity
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

В отличие от ReplacingMergeTree, CollapsingMergeTree позволяет изменять значения ключа сортировки. Он хорошо подходит для обратимых операций с семантикой отмены, таких как финансовые транзакции или отслеживание состояния в игре.

Описанный выше подход к обновлению требует, чтобы приложение хранило состояние на стороне клиента, чтобы можно было вставить строку отмены. Хотя с точки зрения ClickHouse это наиболее эффективный вариант, при масштабировании работа с ним может быть сложной. Кроме того, для получения корректных результатов в запросах нужна агрегация с умножением на знак.

Подробнее: CollapsingMergeTree

​

Декларативные обновления

Эти методы работают с таблицами на движках семейства MergeTree.

Метод	Синтаксис	Лучше всего подходит для	Компромиссы
Мутации	ALTER TABLE ... UPDATE	Нечастые массовые обновления; особенно хорошо подходят, когда обновление соответствует партиционированию таблицы.	Высокая нагрузка на I/O; переписываются столбцы
Легковесные обновления	UPDATE ... SET ... WHERE	Небольшие обновления (~0.1-10% строк); частые обновления, где важна производительность	Увеличивают накладные расходы на SELECT; патч-части учитываются в лимитах

​

Мутации

Мутации (ALTER TABLE ... UPDATE) перезаписывают все части, содержащие строки, удовлетворяющие выражению WHERE.

ALTER TABLE posts UPDATE AnswerCount = AnswerCount + 1 WHERE AnswerCount = 0

Мутации создают высокую нагрузку на I/O, поскольку переписывают все части, соответствующие выражению WHERE. Этот процесс не является атомарным. Части заменяются мутированными версиями, как только те становятся готовы, и SELECT-запрос, который начинает выполняться во время мутации, увидит данные как из уже мутированных частей, так и из частей, которые еще не были мутированы. Отслеживать состояние прогресса можно через таблицу system.mutations.

Мутации создают высокую нагрузку на I/O, поэтому использовать их следует умеренно, так как они могут повлиять на производительность SELECT в кластере. Если мутации попадают в очередь быстрее, чем успевают обрабатываться, производительность запросов будет снижаться. Отслеживайте очередь через system.mutations.

Подробнее: ALTER TABLE UPDATE

​

Мутации на лету

При мутациях через ALTER TABLE ... UPDATE вам может потребоваться подождать, пока они будут применены фоновым процессом, прежде чем измененные значения отобразятся в результатах запросов. ClickHouse позволяет изменить это поведение с помощью “мутаций на лету”. Когда мутации на лету включены, обновленные строки сразу помечаются как обновленные, и последующие запросы SELECT автоматически возвращают измененные значения.

Мутации на лету можно включить для таблиц семейства MergeTree, включив настройку уровня запроса apply_mutations_on_fly.

SET apply_mutations_on_fly = 1;

Пример

Давайте создадим таблицу и выполним несколько мутаций:

CREATE TABLE test_on_fly_mutations (id UInt64, v String)
ENGINE = MergeTree ORDER BY id;

-- Отключим фоновую материализацию мутаций, чтобы показать
-- поведение по умолчанию, когда мутации на лету не включены
SYSTEM STOP MERGES test_on_fly_mutations;
SET mutations_sync = 0;

-- Вставим несколько строк в нашу новую таблицу
INSERT INTO test_on_fly_mutations VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- Обновим значения в строках
ALTER TABLE test_on_fly_mutations UPDATE v = 'd' WHERE id = 1;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'd';
ALTER TABLE test_on_fly_mutations UPDATE v = 'e' WHERE id = 2;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'e';

Проверим результат обновлений с помощью запроса SELECT:

-- Явно отключим мутации на лету
SET apply_mutations_on_fly = 0;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;

Обратите внимание: при запросе к новой таблице значения в строках еще не обновились:

┌─id─┬─v─┐
│  1 │ a │
│  2 │ b │
│  3 │ c │
└────┴───┘

Теперь посмотрим, что произойдет, если включить мутации на лету:

-- Включим мутации на лету
SET apply_mutations_on_fly = 1;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;

Теперь запрос SELECT сразу возвращает корректный результат, без ожидания применения мутаций:

┌─id─┬─v─┐
│  3 │ c │
└────┴───┘

Влияние на производительность

Когда включены мутации на лету, мутации материализуются не сразу, а применяются только во время SELECT-запросов. Однако обратите внимание: в фоновом режиме мутации по-прежнему асинхронно материализуются, а это ресурсоемкий процесс.

Если в течение некоторого интервала времени число отправленных мутаций постоянно превышает число мутаций, обрабатываемых в фоновом режиме, очередь нематериализованных мутаций, которые нужно применить, будет продолжать расти. В конечном итоге это приведет к снижению производительности SELECT-запросов.

Мы рекомендуем включать настройку apply_mutations_on_fly вместе с другими настройками уровня MergeTree, такими как number_of_mutations_to_throw и number_of_mutations_to_delay, чтобы ограничить бесконтрольный рост числа нематериализованных мутаций.

Поддержка подзапросов и недетерминированных функций

Мутации на лету имеют ограниченную поддержку подзапросов и недетерминированных функций. Поддерживаются только скалярные подзапросы с результатом разумного размера (это контролируется настройкой mutations_max_literal_size_to_replace). Поддерживаются только константные недетерминированные функции (например, функция now()).

Это поведение контролируется следующими настройками:

Настройка	Описание	По умолчанию
mutations_execute_nondeterministic_on_initiator	Если true, недетерминированные функции выполняются на реплике-инициаторе и подставляются в запросы UPDATE и DELETE как литералы.	false
mutations_execute_subqueries_on_initiator	Если true, скалярные подзапросы выполняются на реплике-инициаторе и подставляются в запросы UPDATE и DELETE как литералы.	false
mutations_max_literal_size_to_replace	Максимальный размер сериализованных литералов в байтах, подставляемых в запросы UPDATE и DELETE.	16384 (16 KiB)

​

Легковесные обновления

Легковесные обновления используют “патч-части” — специальные части данных, содержащие только обновлённые столбцы и строки, — а не переписывают столбцы целиком, как при традиционных мутациях.

UPDATE posts SET AnswerCount = AnswerCount + 1 WHERE Id = 404346

Этот подход использует стандартный синтаксис UPDATE и сразу создает патч-части, не дожидаясь слияний. Обновленные значения сразу видны в запросах SELECT за счет применения патчей, но физически материализуются только во время последующих слияний. Это делает легковесные обновления оптимальным вариантом для обновления небольшого процента строк (до ~10% таблицы) с предсказуемой latency. Бенчмарки показывают, что они могут быть до 23 раз быстрее, чем мутации.

Компромисс заключается в том, что запросы SELECT требуют дополнительных затрат при применении патчей, а патч-части учитываются в лимитах на части. При превышении порога ~10% накладные расходы на применение патчей при чтении растут пропорционально, поэтому для более крупных обновлений синхронные мутации эффективнее.

Подробнее: Легковесный UPDATE

​

Мутации на лету

Мутации на лету позволяют обновлять строки так, что последующие запросы SELECT автоматически возвращают изменённые значения, не дожидаясь фоновой обработки. Тем самым устраняется ограничение атомарности, присущее обычным мутациям.

SET apply_mutations_on_fly = 1;

SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26762 │
└───────────┘

-- Увеличиваем счётчик
ALTER TABLE posts UPDATE ViewCount = ViewCount + 1 WHERE Id = 404346

-- Обновлённое значение сразу видно
SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26763 │
└───────────┘

И для мутации, и для последующих запросов SELECT должен быть включен параметр apply_mutations_on_fly = 1. Условия мутации хранятся в ClickHouse Keeper, который держит всё в памяти, и применяются на лету во время выполнения запросов.

Обратите внимание, что для обновления данных всё равно используется мутация — просто она не материализуется сразу. Мутация по-прежнему будет применяться в фоновом режиме как асинхронный процесс и создаёт такую же существенную нагрузку, как и обычная мутация. Выражения, которые можно использовать с этой операцией, также ограничены (см. подробности).

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

Подробнее: Мутации на лету

​

Сводка сравнения

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

Метод	Замедление запросов	Накладные расходы по памяти	Примечания
Мутации	Базовый уровень	Базовый уровень	После завершения — полная скорость; данные физически перезаписываются
Мутации на лету	Переменное	Переменные	Мгновенная видимость; производительность падает, если накапливается много обновлений
Легковесные обновления	7–18% (в среднем ~12%)	+20–210%	Наиболее эффективны для запросов; лучше всего подходят для обновления ≤10% таблицы
ReplacingMergeTree + FINAL	21–550% (в среднем ~280%)	20–200× от базового уровня	Нужно читать все версии строк; самые высокие накладные расходы на запросы
CoalescingMergeTree + FINAL	Сопоставимо с ReplacingMergeTree	Сопоставимо с ReplacingMergeTree	Коалесценция на уровне столбцов добавляет сопоставимые накладные расходы
CollapsingMergeTree	Зависит от агрегации	Зависит от агрегации	Накладные расходы зависят от сложности запроса

​

Дополнительные ресурсы

Если вам интересен подробный разбор того, как со временем развивались обновления в ClickHouse, а также анализ бенчмарков, см.:

• Обновления в ClickHouse, часть 1: Специализированные движки
• Обновления в ClickHouse, часть 2: Обновления в стиле SQL
• Обновления в ClickHouse, часть 3: Бенчмарки