Wednesday, 19 August 2026

DML Operations in ClickHouse: Inserts, Updates, Deletes Explained

  

ClickHouse is a high-performance OLAP (Online Analytical Processing) database system built for speed and analytical workloads. Unlike traditional OLTP databases, ClickHouse is optimized for append-only and bulk insert operations. As such, Data Manipulation Language (DML) operations like UPDATE and DELETE are handled differently and come with specific performance considerations.

 

This blog post explains how DML works in ClickHouse, including INSERT, UPDATE, DELETE, operations, and clarifies what developers and data engineers need to know when working with data in ClickHouse.

 

1. Introduction to DML in ClickHouse

DML stands for Data Manipulation Language, it is a subset of SQL used to insert, update, delete, and select data from tables.

 

Typical DML operations include:

·      INSERT: Add new rows.

·      UPDATE: Modify existing rows.

·      DELETE: Remove rows.

 

In OLTP (Online Transaction Processing) systems like MySQL or PostgreSQL, DML operations are fast, row-oriented, and designed for frequent updates/inserts/deletes. But OLAP systems works completely in different way.

 

2. Why DML behaves differently in OLAP systems like ClickHouse

OLAP systems like ClickHouse are optimized for reading massive amounts of data quickly, not for transactional workloads.

 

Here’s why DML behaves differently in ClickHouse.

Aspect

OLTP

OLAP (ClickHouse)

Row updates

Fast, supported via UPDATE

Expensive, discouraged

 

Deletes

Fast, transactional

Slow, batched, non-transactional

Storage model

Row-based

Columnar

 

Workload

Read/write-heavy

Read-heavy, append-only

 

For example, if you use a MergeTree table in Clickhouse while creating the table

 

·      Data is stored in immutable files called parts.

·      You can’t modify a part in place.

·      When you run a DELETE or UPDATE, ClickHouse:

o   Creates a new version of the data without the deleted/updated rows.

o   Marks old parts as obsolete.

o   Relies on background merges to clean them up.

 

3. INSERT Operations

ClickHouse is append-only and columnar, which makes INSERT operations extremely fast and efficient. It's designed to ingest millions of rows per second, especially in batch mode.

 

·      Data is appended to the table as new parts.

·      INSERTS are non-blocking, asynchronous, and highly concurrent.

·      You can insert single row, multiple rows, or insert from SELECT.

 

Basic Syntax

INSERT INTO [db.]table_name [(column1, column2, ...)] VALUES (value1, value2, ...);

   

Batch Insert

 

INSERT INTO table_name VALUES 
(value_set_1),
(value_set_2),
(value_set_3);

   

Insert from SELECT

 

INSERT INTO target_table SELECT * FROM source_table WHERE condition;

   

3.1 Step-by-Step Demo: Create Database, Table, and Insert Rows

Step 1: Create a Database

 

CREATE DATABASE IF NOT EXISTS demo_db;

krishna :) CREATE DATABASE IF NOT EXISTS demo_db;

CREATE DATABASE IF NOT EXISTS demo_db

Query id: 5f0fef4f-07b1-45a8-82fb-71270b49c004

Ok.

0 rows in set. Elapsed: 0.006 sec.

   

Step 2: Create a Table

Let’s use the MergeTree engine (most common in ClickHouse).

 

CREATE TABLE demo_db.user_activity
(
    user_id UInt64,
    event_type String,
    event_time DateTime,
    session_duration UInt32
)
ENGINE = MergeTree
ORDER BY (event_time);


krishna :) CREATE TABLE demo_db.user_activity
(
    user_id UInt64,
    event_type String,
    event_time DateTime,
    session_duration UInt32
)
ENGINE = MergeTree
ORDER BY (event_time);

CREATE TABLE demo_db.user_activity
(
    `user_id` UInt64,
    `event_type` String,
    `event_time` DateTime,
    `session_duration` UInt32
)
ENGINE = MergeTree
ORDER BY event_time

Query id: e869c0bb-ebe7-4218-a418-491bd729c5b8

Ok.

0 rows in set. Elapsed: 0.012 sec.

   

Step 3: Insert Single Row

 

INSERT INTO demo_db.user_activity 
VALUES (1, 'login', '2025-05-09 10:00:00', 120);


krishna :) INSERT INTO demo_db.user_activity 
VALUES (1, 'login', '2025-05-09 10:00:00', 120);

INSERT INTO demo_db.user_activity FORMAT Values

Query id: 85693a17-aaf8-4cb3-a20a-679962d224f9

Ok.

1 row in set. Elapsed: 0.016 sec.

   

Let’s query the records of user_activity table.

 

krishna :) SELECT * FROM demo_db.user_activity;

SELECT *
FROM demo_db.user_activity

Query id: 960d2a5c-76fb-4192-9230-571b69104d83

   ┌─user_id─┬─event_type─┬──────────event_time─┬─session_duration─┐
1. │       1 │ login      │ 2025-05-09 10:00:00 │              120 │
   └─────────┴────────────┴─────────────────────┴──────────────────┘

1 row in set. Elapsed: 0.004 sec.

   

Step 4: Insert Data (Multiple Rows)

 

INSERT INTO demo_db.user_activity 
VALUES 
(2, 'click', '2025-05-09 10:05:00', 45),
(3, 'logout', '2025-05-09 10:10:00', 30),
(4, 'login', '2025-05-09 10:20:00', 90);


krishna :) INSERT INTO demo_db.user_activity 
VALUES 
(2, 'click', '2025-05-09 10:05:00', 45),
(3, 'logout', '2025-05-09 10:10:00', 30),
(4, 'login', '2025-05-09 10:20:00', 90);

INSERT INTO demo_db.user_activity FORMAT Values

Query id: 682b5584-1bbe-4cc1-b0d4-7c2d063a6eb6

Ok.

3 rows in set. Elapsed: 0.011 sec.

   

Let’s print all the records from user_activity table.

 

krishna :) SELECT * FROM demo_db.user_activity;

SELECT *
FROM demo_db.user_activity

Query id: bc875ade-c09b-44fa-8074-9853ef38f88e

   ┌─user_id─┬─event_type─┬──────────event_time─┬─session_duration─┐
1. │       1 │ login      │ 2025-05-09 10:00:00 │              120 │
2. │       2 │ click      │ 2025-05-09 10:05:00 │               45 │
3. │       3 │ logout     │ 2025-05-09 10:10:00 │               30 │
4. │       4 │ login      │ 2025-05-09 10:20:00 │               90 │
   └─────────┴────────────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.005 sec.

   

Step 5: Insert from SELECT (e.g., filter only "login" events)

Create user_login_activity table.

 

CREATE TABLE demo_db.user_login_activity
(
    user_id UInt64,
    event_time DateTime,
    session_duration UInt32
)
ENGINE = MergeTree
ORDER BY (event_time);

   

Let’s populate user_id, event_time and session_duration values from user_activity table.

 

INSERT INTO demo_db.user_login_activity
SELECT user_id, event_time, session_duration
FROM demo_db.user_activity
WHERE event_type = 'login';


krishna :) CREATE TABLE demo_db.user_login_activity
(
    user_id UInt64,
    event_time DateTime,
    session_duration UInt32
)
ENGINE = MergeTree
ORDER BY (event_time);

CREATE TABLE demo_db.user_login_activity
(
    `user_id` UInt64,
    `event_time` DateTime,
    `session_duration` UInt32
)
ENGINE = MergeTree
ORDER BY event_time

Query id: 95d482bb-dc9b-4909-9109-667c9b4ccd9b

Ok.

0 rows in set. Elapsed: 0.017 sec. 

krishna :) ;

Empty query

krishna :) ;

Empty query

krishna :) INSERT INTO demo_db.user_login_activity
SELECT user_id, event_time, session_duration
FROM demo_db.user_activity
WHERE event_type = 'login';

INSERT INTO demo_db.user_login_activity SELECT
    user_id,
    event_time,
    session_duration
FROM demo_db.user_activity
WHERE event_type = 'login'

Query id: 9d80404c-b56a-42cd-a77e-8a7a90666a64

Ok.

0 rows in set. Elapsed: 0.012 sec. 

krishna :) ;

Empty query

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.user_login_activity;

SELECT *
FROM demo_db.user_login_activity

Query id: 73247355-1267-4025-b9ac-3f7f460c9fd8

   ┌─user_id─┬──────────event_time─┬─session_duration─┐
1. │       1 │ 2025-05-09 10:00:00 │              120 │
2. │       4 │ 2025-05-09 10:20:00 │               90 │
   └─────────┴─────────────────────┴──────────────────┘

2 rows in set. Elapsed: 0.006 sec.

4. UPDATE Operation

ClickHouse does support UPDATE, but:

·      Only on tables using MergeTree family engines.

·      Not designed for frequent updates (not OLTP-style).

·      Internally, it uses mutations, which are costly, asynchronous, and non-instantaneous.

 

4.1 What is Mutation?

A mutation in ClickHouse is a background task that modifies parts (immutable files) of a MergeTree table. Under the hood, when you trigger UPDATE operation,

·      Clickhouse scan parts

·      Apply transformation

·      Rewrite parts

·      Mark old ones as obsolete

 

Why ClickHouse mutaitons are slow?

ClickHouse is designed around an immutable storage model where data is written once and never modified in place. This design choice provides several benefits:

 

·      Better compression (data is sorted and compressed in large chunks)

·      Faster reads (no need for complex locking mechanisms)

·      Simpler concurrency model

 

But this model comes with its own drawbacks given below.

 

·      Write amplification: Even changing a single row requires rewriting entire parts (typically thousands or millions of rows).

·      Asynchronous processing: The actual deletion of old data happens later during background merges.

·      No in-place updates: Unlike traditional row-oriented databases, ClickHouse can't just modify a single row in a data file.

·      Merge scheduling: The background merge process operates on its own schedule, not immediately when the mutation is issued.

Performance Characteristics

·      Mutation speed depends on the size of the affected parts, not just the number of changed rows

·      Small, frequent mutations can be particularly inefficient

·      The operation appears to complete quickly, but the actual disk space isn't freed until merges occur

 

4.2 Syntax Of UPDATE

 

ALTER TABLE [db.]table_name
UPDATE column1 = expr1, column2 = expr2
WHERE condition;

   

Example: Increase session duration by 20, for all the login even types.

 

ALTER TABLE demo_db.user_activity
UPDATE session_duration = session_duration + 20
WHERE event_type = 'login';

 

krishna :) SELECT * FROM demo_db.user_activity;

SELECT *
FROM demo_db.user_activity

Query id: eec66a3b-0425-41e2-a0be-bd1bfe05931d

   ┌─user_id─┬─event_type─┬──────────event_time─┬─session_duration─┐
1. │       2 │ click      │ 2025-05-09 10:05:00 │               45 │
2. │       3 │ logout     │ 2025-05-09 10:10:00 │               30 │
3. │       4 │ login      │ 2025-05-09 10:20:00 │               90 │
4. │       1 │ login      │ 2025-05-09 10:00:00 │              120 │
   └─────────┴────────────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.004 sec. 

krishna :) ;

Empty query

krishna :) ;

Empty query

krishna :) ALTER TABLE demo_db.user_activity
UPDATE session_duration = session_duration + 20
WHERE event_type = 'login';

ALTER TABLE demo_db.user_activity
    (UPDATE session_duration = session_duration + 20 WHERE event_type = 'login')

Query id: 4c245179-b595-4917-8ff3-6c7861101efa

Ok.

0 rows in set. Elapsed: 0.008 sec. 

krishna :) ;

Empty query

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.user_activity;

SELECT *
FROM demo_db.user_activity

Query id: 19224433-86f8-44e9-921a-30db30cf7cf0

   ┌─user_id─┬─event_type─┬──────────event_time─┬─session_duration─┐
1. │       2 │ click      │ 2025-05-09 10:05:00 │               45 │
2. │       3 │ logout     │ 2025-05-09 10:10:00 │               30 │
3. │       4 │ login      │ 2025-05-09 10:20:00 │              110 │
4. │       1 │ login      │ 2025-05-09 10:00:00 │              140 │
   └─────────┴────────────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.010 sec.

Check Mutation Progress

SELECT * FROM system.mutations WHERE table = 'user_activity' AND database = 'demo_db';


krishna :) SELECT * FROM system.mutations WHERE table = 'user_activity' AND database = 'demo_db';

SELECT *
FROM system.mutations
WHERE (`table` = 'user_activity') AND (database = 'demo_db')

Query id: 874671ae-7191-40d7-96c3-ac984176b6fc

Row 1:
──────
database:                    demo_db
table:                       user_activity
mutation_id:                 mutation_3.txt
command:                     (UPDATE session_duration = session_duration + 20 WHERE event_type = 'login')
create_time:                 2025-05-09 11:49:27
block_numbers.partition_id:  ['']
block_numbers.number:        [3]
parts_to_do_names:           []
parts_to_do:                 0
is_done:                     1
is_killed:                   0
latest_failed_part:          
latest_fail_time:            1970-01-01 05:30:00
latest_fail_reason:          
latest_fail_error_code_name: 

1 row in set. Elapsed: 0.003 sec.

   

Following table summarizes the output of mutation status.

Column

Description

database

The database where the mutated table resides (demo_db)

table

The table undergoing mutation (user_activity)

mutation_id

A unique identifier for the mutation (mutation_3.txt), corresponds to mutation log file

command 

The exact DML command used to mutate the data:

(UPDATE session_duration = session_duration + 20 WHERE event_type = 'login')

create_time

When the mutation was created (2025-05-09 11:49:27)

block_numbers.partition_id

Internal partitions affected by the mutation. Empty string means unpartitioned or default partition used.

block_numbers.number

Internally assigned block number of parts affected ([3])

parts_to_do_names

List of part names still pending mutation. Empty means all parts have been processed.

parts_to_do

Number of parts remaining to mutate, 0 means nothing left to mutate

is_done 

1 means mutation is complete

is_killed  

0 means the mutation was not manually killed

latest_failed_part  

Name of the part that last failed (empty = no failure)

latest_fail_time 

If there was a failure, this would show when (default is 1970-01-01 = no failure)

latest_fail_reason  

Reason for last failure (empty = no error)

latest_fail_error_code_name  

Internal ClickHouse error code (empty = no error)

 

Final summary of the mutation operation that we executed is given below.

Mutation was successful? 

Yes (is_done = 1)

 

All data updated? 

Yes (parts_to_do = 0)

 

Any errors/failures? 

No (latest_fail_reason is empty)

 

Parts affected 

Part with block number [3]

 

How to Monitor Long-Running Mutations?

When working with large tables or complex UPDATE/DELETE, you'd keep an eye on following query.

 

SELECT mutation_id, is_done, parts_to_do, latest_fail_reason
FROM system.mutations
WHERE table = 'your_table' AND database = 'database_name';


krishna :) SELECT mutation_id, is_done, parts_to_do, latest_fail_reason
FROM system.mutations
WHERE table = 'user_activity' AND database = 'demo_db';

SELECT
    mutation_id,
    is_done,
    parts_to_do,
    latest_fail_reason
FROM system.mutations
WHERE (`table` = 'user_activity') AND (database = 'demo_db')

Query id: 04df0323-74e5-47ef-b3f2-10fc37de4c89

   ┌─mutation_id────┬─is_done─┬─parts_to_do─┬─latest_fail_reason─┐
1. │ mutation_3.txt │       1 │           0 │                    │
   └────────────────┴─────────┴─────────────┴────────────────────┘

1 row in set. Elapsed: 0.003 sec.

   

5. DELETE Operation

Like UPDATE, the DELETE operation in ClickHouse

·      Works only on tables using MergeTree engines.

·      Not instantaneous or cheap.

·      Implemented as a mutation, a background process.

·      Deletes are not done row-by-row but by rewriting parts.

 

Here’s how a DELETE works internally:

·      ClickHouse scans parts that match the WHERE clause.

·      It rewrites those parts excluding the deleted rows.

·      This rewrite is handled as a mutation and runs asynchronously.

·      The old parts are marked obsolete and eventually removed during merges.

 

Syntax

 

ALTER TABLE [db.]table_name
DELETE WHERE condition;

Example

ALTER TABLE demo_db.user_activity DELETE WHERE event_type='login';

   

Above statement delete the rows where event_type is login.

 

krishna :) SELECT * FROM demo_db.user_activity;

SELECT *
FROM demo_db.user_activity

Query id: 634c7b18-d058-4cc7-a969-237c104ce9c6

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.5.1.

   ┌─user_id─┬─event_type─┬──────────event_time─┬─session_duration─┐
1. │       2 │ click      │ 2025-05-09 10:05:00 │               45 │
2. │       3 │ logout     │ 2025-05-09 10:10:00 │               30 │
3. │       4 │ login      │ 2025-05-09 10:20:00 │              110 │
4. │       1 │ login      │ 2025-05-09 10:00:00 │              140 │
   └─────────┴────────────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.012 sec. 

krishna :) ;

Empty query

krishna :) ALTER TABLE demo_db.user_activity DELETE WHERE event_type='login';

ALTER TABLE demo_db.user_activity
    (DELETE WHERE event_type = 'login')

Query id: 2b18f5b0-443a-4a5e-bbf0-f5ed239e9b09

Ok.

0 rows in set. Elapsed: 0.006 sec. 

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.user_activity;

SELECT *
FROM demo_db.user_activity

Query id: c7a3685c-4858-4416-9164-321df27cb291

   ┌─user_id─┬─event_type─┬──────────event_time─┬─session_duration─┐
1. │       2 │ click      │ 2025-05-09 10:05:00 │               45 │
2. │       3 │ logout     │ 2025-05-09 10:10:00 │               30 │
   └─────────┴────────────┴─────────────────────┴──────────────────┘

2 rows in set. Elapsed: 0.013 sec.

   

6. Lightweight Deletes (Masking Deletes) in ClickHouse

Lightweight deletes in ClickHouse are a form of row-level masking rather than immediate physical deletion.

 

How They Work Internally?

Instead of rewriting part files immediately, ClickHouse:

 

·      Adds a hidden system column: _row_exists

·      Marks affected rows with _row_exists = false

·      These rows are excluded from query results, even though they're still on disk temporarily.

 

Reference: https://clickhouse.com/docs/operations/settings/settings#enable_lightweight_delete

 

In summary, ClickHouse is not built for frequent updates or deletes like OLTP systems. While you can perform inserts quickly and efficiently, updates and deletes are slower and can impact performance due to how the system merges data in the background. So, it's best to use these operations carefully and only when necessary.


  

Previous                                                    Next                                                    Home
Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)