Modifications and deletions: expensive and not immediate

16 / 34Machine translation

← →

2 · MERGETREE · CHANGES

run

Mutation and lightweight DELETE

TTL — Expiration-Based Deletion

ALTER TABLE events UPDATE damage = 0
WHERE player_id = 777
SETTINGS mutations_sync = 2;  -- ждать

DELETE FROM events WHERE player_id = 1042;
-- system.mutations:
--   UPDATE _row_exists = 0 ...  is_done 1

CREATE TABLE events_ttl (
  ts DateTime, player_id UInt32
) ENGINE = MergeTree
ORDER BY (player_id, ts)
TTL ts + INTERVAL 90 DAY;
-- 2 строки -> OPTIMIZE FINAL -> 1
-- срок проверяется при слиянии

ALTER, UPDATE, DELETE

Lightweight DELETE

Lightweight UPDATE

A mutation overwrites data sections, asynchronously by default. For infrequent bulk updates.

The _row_exists filter: Queries do not see the row; the merge operation physically deletes it.

UPDATE … SET via patch parts.

Frequent UPDATEs of single rows mean the data does not belong in ClickHouse. Mutable state lives in PostgreSQL.