Обзор

Отличия между обновлением данных в ClickHouse и OLTP-базах данных

При работе с обновлениями ClickHouse и OLTP-базы данных существенно расходятся из-за различий в их базовых принципах проектирования и целевых сценариях использования. Например, PostgreSQL — это ориентированная на строки реляционная база данных с поддержкой ACID, обеспечивающая надёжные и транзакционные операции обновления и удаления, гарантирующие согласованность и целостность данных с помощью таких механизмов, как Multi-Version Concurrency Control (MVCC). Это позволяет безопасно и предсказуемо вносить изменения даже в средах с высокой конкуррентностью.

Напротив, ClickHouse — это колонко-ориентированная база данных, оптимизированная для аналитических нагрузок с преобладанием чтения и высокопроизводительных операций добавления (append-only). Хотя она изначально поддерживает обновления и удаления «на месте», их необходимо использовать осторожно, чтобы избежать высокой нагрузки на I/O. В качестве альтернативы таблицы можно реорганизовать так, чтобы операции удаления и обновления превращались в операции добавления, обрабатываемые асинхронно и/или во время чтения, что отражает ориентацию на высокопроизводительную ингестию данных и эффективное выполнение запросов, а не на изменение данных в режиме реального времени.

Методы обновления данных в ClickHouse

Существует несколько способов обновления данных в ClickHouse, каждый из которых имеет свои преимущества и особенности производительности. Необходимо выбирать подходящий метод в зависимости от вашей модели данных и объема данных, который вы планируете обновлять.

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

В целом операции обновления следует выполнять осторожно, а очередь мутаций необходимо внимательно отслеживать с помощью таблицы system.mutations. Не выполняйте обновления слишком часто, как это делается в OLTP-базах данных. Если вам необходимы частые обновления, см. ReplacingMergeTree.

Метод	Синтаксис	Когда использовать
Мутация обновления	`ALTER TABLE [table] UPDATE`	Используйте, когда данные необходимо немедленно записать на диск (например, для соблюдения требований регуляторов). Негативно влияет на производительность запросов `SELECT`.
Легковесные обновления	`UPDATE [table] SET ... WHERE`	Используйте для обновления небольших объемов данных (до ~10% таблицы). Создает патч-части для немедленной видимости без перезаписи целых столбцов. Добавляет накладные расходы к запросам `SELECT`, но обеспечивает предсказуемую задержку. В настоящее время экспериментальная возможность.
Обновления на лету	`ALTER TABLE [table] UPDATE`	Включите с помощью `SET apply_mutations_on_fly = 1;`. Используйте при обновлении небольших объемов данных. Строки немедленно возвращаются с обновленными данными во всех последующих запросах `SELECT`, но изначально на диске лишь внутренне помечаются как обновленные.
ReplacingMergeTree	`ENGINE = ReplacingMergeTree`	Используйте при обновлении больших объемов данных. Этот движок таблицы оптимизирован для дедупликации данных при слияниях.
CollapsingMergeTree	`ENGINE = CollapsingMergeTree(Sign)`	Используйте при частых обновлениях отдельных строк или в сценариях, когда необходимо поддерживать актуальное состояние объектов, изменяющихся со временем. Например, отслеживание активности пользователей или статистики по статьям.

Мутации UPDATE

Мутации UPDATE можно выполнять с помощью команды ALTER TABLE ... UPDATE, например:

ALTER TABLE posts_temp
        (UPDATE AnswerCount = AnswerCount + 1 WHERE AnswerCount = 0)

Это чрезвычайно ресурсоёмкие с точки зрения операций ввода-вывода, так как переписываются все части, которые соответствуют выражению WHERE. В этом процессе нет атомарности — части подменяются на изменённые, как только они готовы, и SELECT‑запрос, который начинает выполняться во время мутации, будет видеть данные как из уже изменённых частей, так и из тех, которые ещё не были изменены. Пользователи могут отслеживать ход выполнения через таблицу systems.mutations. Это операции с высокой нагрузкой на подсистему ввода-вывода, и их следует использовать умеренно, поскольку они могут повлиять на производительность кластерных SELECT‑запросов.

Подробнее см. в разделе update mutations.

Облегчённые обновления

Облегчённые обновления — это функция ClickHouse, которая обновляет строки с помощью «patch parts» (частей‑патчей) — специальных частей данных, содержащих только изменённые столбцы и строки, вместо перезаписи целых столбцов, как в классических мутациях. Облегчённый оператор UPDATE характеризуется следующим:

Ключевые характеристики:

Использует стандартный синтаксис UPDATE и создаёт части‑патчи сразу, не дожидаясь слияний
Обновлённые значения сразу видны в запросах SELECT за счёт применения патчей, но физически материализуются только во время последующих слияний
Предназначен для небольших обновлений (до ~10% таблицы) с предсказуемой задержкой
Увеличивает накладные расходы для запросов SELECT, которым нужно применять патчи, но позволяет избежать перезаписи целых столбцов

Для получения дополнительной информации см. "Оператор Lightweight UPDATE"

Обновления «на лету»

Обновления «на лету» обеспечивают механизм изменения строк таким образом, что изменения применяются немедленно, и последующие запросы SELECT автоматически возвращают уже изменённые значения (это влечёт дополнительные накладные расходы и замедляет выполнение запросов). Это эффективно устраняет ограничение по атомарности, присущее обычным мутациям. Ниже приведён пример:

SET apply_mutations_on_fly = 1;

SELECT ViewCount
FROM posts
WHERE Id = 404346

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

1 row in set. Elapsed: 0.115 sec. Processed 59.55 million rows, 238.25 MB (517.83 million rows/s., 2.07 GB/s.)
Peak memory usage: 113.65 MiB.

-инкремент счётчика
ALTER TABLE posts
        (UPDATE ViewCount = ViewCount + 1 WHERE Id = 404346)

SELECT ViewCount
FROM posts
WHERE Id = 404346

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

1 row in set. Elapsed: 0.149 sec. Processed 59.55 million rows, 259.91 MB (399.99 million rows/s., 1.75 GB/s.)

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

Подробнее об обновлениях «на лету».

`CollapsingMergeTree`

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

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8 -- Специальный столбец для движка таблицы CollapsingMergeTree
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1) -- sign = -1 указывает на обновление состояния этой строки
INSERT INTO UAct VALUES (4324182021466249494, 6, 185, 1) -- строка заменяется на новое состояние

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0

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

Примечание

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

Мы рекомендуем ознакомиться с документацией по CollapsingMergeTree для более подробного обзора.

Дополнительные материалы

Обработка обновлений и удалений в ClickHouse

Отличия между обновлением данных в ClickHouse и OLTP-базах данных​

Методы обновления данных в ClickHouse​

Мутации UPDATE​

Облегчённые обновления​

Обновления «на лету»​

CollapsingMergeTree​

Дополнительные материалы​