Incremental backup is doing full backup when multiple parts merge in case of remote backup to s3

[manojbvn4u]

Hi Team,

I have a table with ENGINE = MergeTree() partitioned by month and I want an incremental backup instead of a full backup, do we have support for that?

Below is my observation

1. Insert 1000 rows into the table
2. Clickhouse creates a single part for these 1000 rows for ex: 202306_01_1_1
3. Perform a backup of this table (it uploads this file 202306_01_1_1 to S3 along with other metadata)
4. Insert another 1000 rows into the table
5. Clickhouse creates a new part for these 1000 rows for ex: 202306_01_1_2
6. Perform optimize (to make them merge) which will eventually happen and create a single file with 2000 rows ex: 202306_01_2_1
7. Perform an incremental backup of this table (it uploads this new file 202306_01_2_1 to s3 with 2000 rows instead of only the new 1000 rows)

Can we avoid this full backup otherwise it will end up doing a full backup post every part merge operation.

[Slach]

Increment will caclulate on part name level only,
parts is immutable,
we can't calculate diff between part without full reading, because data parts content compressed, and it is not have a pages like other MySQL\PostgreSQL.

[manojbvn4u]

Hi @Slach so if I use Merge Tree we cannot do incremental backups with S3. Is my understanding correct?

[Slach]

No your understanding is incorrect.

In your case you merge two parts, and make new big one.
And diff backup was calculated but your new part contains whole table content.

In normal cases, usually when you make backup once per day. You have new parts for new day, and old parts. So data increment could be calculated and only new data parts will uploaded.

The worst case happens for increment backup when you use OPTIMIZE TABLE FINAL or use many mutations like ALTER TABLE ... UPDATE \ DELETE which could fully rewrite table on clickhouse-server side