ClickHouse Incremental Backups Store Only the Changed Tail of a File

TL;DR: BACKUP ... SETTINGS BASE_BACKUP = ... does not do block-level or chunk-level diffing. It does something much simpler and much more robust: for every file it asks “is the base backup’s copy of this file a prefix of the current copy?” — verified by hashing the first base_size bytes and comparing against the base’s checksum. If yes, the backup records base_size and uploads only the remaining size - base_size bytes. At restore time the two halves are concatenated back into one file.

Take a database with 10 TB of data. Back it up: fine, once. Now back it up again tomorrow, after 10 GB of new data arrived. The obvious implementation uploads 10 TB again. That is not a bug, it is what “backup” means to most systems — a backup is a self-contained snapshot.

But 10 TB of re-upload per day is the kind of cost that makes people stop backing up. So ClickHouse has incremental backups, and the interesting thing about them is how little they actually do.

The naive incremental backup, and why it isn’t used

The textbook approach is a diff algorithm: rsync-style rolling checksum, FastCDC content-defined chunking, or a Merkle tree per part. Split both versions of the file into blocks, find the blocks that match, and upload only the unmatched ones.

ClickHouse does not do this. It does something cruder:

text
base:    [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA|]
new:     [AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA|BBBBBBBBBB]
                  ^ base_size = 30

backup stores: "BBBBBBBB"  (10 bytes)  +  base_size = 30
restore produces: base[0..30) ++ backup[0..10)  ==  new

The base backup’s file must be a prefix of the new file. Not “similar”, not “mostly the same” — literally the first N bytes are identical, and the backup stores the remaining size - N bytes.

That sounds restrictive. And for MergeTree data parts it is: a part is immutable, so a changed part is a completely different file and gets stored in full. But most of what a backup contains is not data parts. It is metadata, columns.txt files, system.users, access files, DDL scripts — and many of those are append-only: a log file that grows, a SQL file that gets a statement appended. For those, the prefix trick is exactly right.

Where the base backup comes from

The base is named in the SETTINGS clause, using the same syntax as the backup destination:

sql
BACKUP TABLE events TO Disk('s3', 'backups/daily-2026-10-23')
SETTINGS BASE_BACKUP = Disk('s3', 'backups/full-2026-10-01');

Which is parsed as a regular setting:

cpp
// src/Backups/BackupSettings.h:23
    /// Base backup, if it's set an incremental backup will be built. That means only differences made after the base backup will be put
    /// into a new backup.
    std::optional<BackupInfo> base_backup_info;

The base backup is opened lazily and — this is a nice touch — in a reduced mode when the backup is being written. Writing an incremental backup only needs the base’s (size, checksum) list, not its full file metadata, and for a base with millions of files that difference is gigabytes:

cpp
// src/Backups/BackupImpl.cpp:433
        BackupFactory::CreateParams base_params = params.getCreateParamsForBaseBackup(std::move(effective_base_backup_info), archive_params.password);
        /// A backup being written only looks up the sizes and checksums of the files of its base, to skip the files the base
        /// already has. For a base of millions of files, the rest of the information about its files takes gigabytes.
        base_params.for_file_lookups_only = (open_mode == OpenMode::WRITE);
        base_backup = BackupFactory::instance().createBackup(base_params);

And the lookup itself is a binary search over a sorted vector of (size, checksum) pairs:

cpp
// src/Backups/BackupImpl.cpp:1351
bool BackupImpl::fileExists(const SizeAndChecksum & size_and_checksum) const
{
    if (open_mode == OpenMode::WRITE)
        throw Exception(ErrorCodes::LOGICAL_ERROR, "The backup file should not be opened for writing. Something is wrong internally");

    std::lock_guard lock{mutex};
    if (params.for_file_lookups_only)
        return std::ranges::binary_search(sizes_and_checksums, size_and_checksum);
    return file_infos.contains(size_and_checksum);
}

The (size, checksum) vector is kept deduplicated, because a backup is full of files with identical content (columns.txt of every part of a table is byte-identical):

cpp
// src/Backups/BackupImpl.cpp:825
    /// Many files of a backup have the same size and checksum (e.g. `columns.txt` of the parts of a table), so the keys
    /// are deduplicated while they are read, whenever their number doubles since the previous time: this keeps the vector
    /// at about twice the number of distinct keys, however many duplicates there are.
    static constexpr size_t min_num_sizes_and_checksums_to_deduplicate = 65536;
    size_t num_sizes_and_checksums_after_deduplication = 0;
    auto deduplicate_sizes_and_checksums = [&]() TSA_NO_THREAD_SAFETY_ANALYSIS
    {
        std::ranges::sort(sizes_and_checksums);
        sizes_and_checksums.erase(std::ranges::unique(sizes_and_checksums).begin(), sizes_and_checksums.end());
        num_sizes_and_checksums_after_deduplication = sizes_and_checksums.size();
    };

65536 is the floor: below that many entries, deduplication is not worth the sort.

Deciding what to store

Now the core of it. For every file, buildFileInfoForBackupEntry asks three questions, in order.

Question 1: is this file smaller than, or the same size as, the base’s copy?

cpp
// src/Backups/BackupFileInfo.cpp:42
    CheckBackupResult checkBaseBackupForFile(const SizeAndChecksum & base_backup_info, const BackupFileInfo & new_entry_info)
    {
        /// We cannot reuse base backup because our file is smaller
        /// than file stored in previous backup
        if ((new_entry_info.size < base_backup_info.first) || !base_backup_info.first)
            return CheckBackupResult::HasNothing;

        if (base_backup_info.first == new_entry_info.size)
            return CheckBackupResult::HasFull;

        return CheckBackupResult::HasPrefix;

    }

Three possible answers: HasNothing (store the whole file), HasFull (the file is unchanged, or at least the same size — the checksum decides later), HasPrefix (the base is shorter, so maybe it’s a prefix).

Question 2: is the base’s copy actually a byte-exact prefix of the new one? This is where the hashing happens:

cpp
// src/Backups/BackupFileInfo.cpp:155
        /// File with the same name but smaller size exist in previous backup
        if (check_base == CheckBackupResult::HasPrefix)
        {
            auto checksums = calculateNewEntryChecksumsIfNeeded(backup_entry, base_backup_file_info->first, read_settings);
            info.checksum = checksums.full_checksum;

            /// We have prefix of this file in backup with the same checksum.
            /// In ClickHouse this can happen for StorageLog for example,
            /// or for .sql files that store DDL if only last part of the query was changed.
            if (checksums.prefix_checksum == base_backup_file_info->second)
            {
                LOG_TRACE(log, "Found prefix of file {} in the base backup, will write rest of the file to current backup", adjusted_path);
                info.base_size = base_backup_file_info->first;
                info.base_checksum = base_backup_file_info->second;
            }
            else
            {
                LOG_TRACE(log, "Prefix of file {} doesn't match the file in the base backup", adjusted_path);
            }
        }

The comparison is prefix_checksum == base_backup_file_info->second. The right-hand side is the base backup’s checksum for that file; the left-hand side is the checksum of the first base_size bytes of the current file. If they are equal, the base’s bytes are a prefix of the current bytes.

The comment names the two real-world cases: StorageLog (a table that only ever gets appended to) and .sql files that store DDL history (a file that gets a statement appended).

Question 3: what if the file was renamed? The prefix check above is keyed on the path. If a file moved, the path lookup fails — but the content may still be there under a different name:

cpp
// src/Backups/BackupFileInfo.cpp:206
    /// We don't have info about this file_name (sic!) in base backup,
    /// however file could be renamed, so we will check one more time using size and checksum
    if (base_backup && base_backup->fileExists(std::pair{info.size, info.checksum}))
    {
        LOG_TRACE(log, "Found a file in the base backup with the same size and checksum as {}", adjusted_path);
        info.base_size = info.size;
        info.base_checksum = info.checksum;
    }

Note info.base_size = info.size: the whole file comes from the base, and nothing at all is written for it. That is why the metadata distinguishes “base covers the whole file” from “base covers a prefix”.

Why partial checksums are only allowed for append-only files

There is a subtlety in the checksum computation that is easy to miss and expensive to get wrong. The prefix checksum is a hash of the first base_size bytes. But hashing a prefix of a file only makes sense if that prefix is stable — i.e. if the file’s earlier bytes cannot change under you.

ClickHouse encodes exactly that distinction:

cpp
// src/Backups/BackupEntryWithChecksumCalculation.h:23
    virtual bool isPartialChecksumAllowed() const = 0;

cpp
// src/Backups/BackupEntryFromImmutableFile.h:42
    bool isPartialChecksumAllowed() const override { return false; }

cpp
// src/Backups/BackupEntryFromAppendOnlyFile.h:41
    bool isPartialChecksumAllowed() const override { return true; }

So MergeTree data parts (immutable) never get a partial checksum; log tables and DDL files (append-only) do. If a partial checksum is not allowed, the computation refuses to produce one:

cpp
// src/Backups/BackupEntryWithChecksumCalculation.cpp:226
    /// Calculates a hash of the file contents at offsets [0..limit_).
    auto calculate_hash = [&](UInt64 limit_) -> std::optional<UInt128>
    {
        if ((limit_ != size) && !isPartialChecksumAllowed())
            return {};

and, symmetrically, refuses to accept a file that grew while it was being hashed:

cpp
// src/Backups/BackupEntryWithChecksumCalculation.cpp:250
        if ((limit_ == size) && !isPartialChecksumAllowed() && !hashing_read_buffer->eof())
        {
            /// We reached offset `size` which is expected to be the end of the file but the file surprisingly didn't end there.
            /// Perhaps it was appended after we got its size for backup but that's not allowed for immutable files
            /// (function `isPartialChecksumAllowed()` returns false for immutable files).
            throw Exception(ErrorCodes::EXPECTED_END_OF_FILE,
                            "Size of immutable file {} increased unexpectedly while calculating its checksum",
                            getFilePath());
        }

This is the correctness backbone of the whole scheme. If a file is not append-only, its prefix might change between the moment the backup computed the prefix checksum and the moment it wrote the suffix — and the restored file would be a chimera of two different versions of the same file. The isPartialChecksumAllowed() flag makes that impossible by construction.

There is one more wrinkle: the prefix checksum must be computed before the full checksum, because computing both in one pass over the file is cheaper than two passes:

cpp
// src/Backups/BackupFileInfo.cpp:67
    ChecksumsForNewEntry calculateNewEntryChecksumsIfNeeded(const BackupEntryPtr & entry, UInt64 prefix_size, const ReadSettings & read_settings)
    {
        ChecksumsForNewEntry res;
        /// The partial checksum should be calculated before the full checksum to enable optimization in BackupEntryWithChecksumCalculation.
        res.prefix_checksum = entry->getPartialChecksum(prefix_size, read_settings);
        res.full_checksum = entry->getChecksum(read_settings);
        return res;
    }

and getPartialChecksum remembers the full checksum it computed along the way, so the second pass is free:

cpp
// src/Backups/BackupEntryWithChecksumCalculation.cpp:55
std::optional<UInt128> BackupEntryWithChecksumCalculation::getPartialChecksum(UInt64 limit, const ReadSettings & read_settings) const
{
    UInt64 size = getSize();
    if (limit >= size)
        return getChecksum(read_settings);

    bool has_calculated_full_checksum = false;

    {
        std::lock_guard lock{mutex};
        has_calculated_full_checksum = calculated_checksum.has_value();
    }

    if (has_calculated_full_checksum)
        return calculateChecksum(limit, read_settings);

    auto [partial_checksum, full_checksum] = calculateOneOrTwoChecksums(limit, size, read_settings);
    chassert(full_checksum);

    {
        std::lock_guard lock{mutex};
        calculated_checksum = full_checksum.value();
    }

    return partial_checksum;
}

Writing only the suffix

Once the plan is known, the write side is trivial: skip the first base_size bytes.

cpp
// src/Backups/BackupImpl.cpp:1759
        if (src_disk && from_immutable_file)
        {
            LOG_TRACE(log, "Writing backup for file {} from {} (disk {}): data file #{}", info.data_file_name, src_file_desc, src_disk->getName(), info.data_file_index);
            writer->copyFileFromDisk(info.data_file_name, src_disk, src_file_path, info.encrypted_by_disk, info.base_size, info.size - info.base_size);
        }
        else
        {
            LOG_TRACE(log, "Writing backup for file {} from {}: data file #{}", info.data_file_name, src_file_desc, info.data_file_index);
            auto create_read_buffer = [entry, read_settings = writer->getReadSettings()] { return entry->getReadBuffer(read_settings); };
            writer->copyDataToFile(info.data_file_name, create_read_buffer, info.base_size, info.size - info.base_size);
        }

And copyDataToFile does the seek:

cpp
// src/Backups/BackupIO_Default.cpp:189
void BackupWriterDefault::copyDataToFile(const String & path_in_backup, const CreateReadBufferFunction & create_read_buffer, UInt64 start_pos, UInt64 length)
{
    auto read_buffer = create_read_buffer();

    if (start_pos)
        read_buffer->seek(start_pos, SEEK_SET);

    auto write_buffer = writeFile(path_in_backup);

    copyData(*read_buffer, *write_buffer, length);
    write_buffer->finalize();
}

Note the parameter names: start_pos and length. The “delta” is a byte range. There is no diff algorithm anywhere — the whole incremental backup machinery reduces to “upload bytes [base_size, size)”.

The accounting follows: the backup’s reported size is only the delta.

cpp
// src/Backups/BackupImpl.cpp:1784
    {
        std::lock_guard lock{mutex};
        ++num_entries;
        size_of_entries += info.size - info.base_size;
        uncompressed_size += info.size - info.base_size;
    }

which is why getTotalSize() (the logical size of all files) and getSizeOfEntries() (the bytes actually stored) differ for an incremental backup.

The manifest

The manifest (.backup, an XML file inside the archive) records the base relationship per file:

cpp
// src/Backups/BackupImpl.cpp:649
        if (info.size)
        {
            *out << "<checksum>" << hexChecksum(info.checksum) << "</checksum>";
            if (info.base_size)
            {
                *out << "<use_base>true</use_base>";
                if (info.base_size != info.size)
                {
                    *out << "<base_size>" << info.base_size << "</base_size>";
                    *out << "<base_checksum>" << hexChecksum(info.base_checksum) << "</base_checksum>";
                }
            }
            if (!info.data_file_name.empty() && (info.data_file_name != info.file_name))
                *out << "<data_file>" << xml << xml_string("data_file", info.data_file_name) << "</data_file>";
            if (info.encrypted_by_disk)
                *out << "<encrypted_by_disk>true</encrypted_by_disk>";
        }

Three tags, and the third one is optional by design: when base_size == size, the whole file comes from the base and the manifest does not repeat the size — it is implied by <use_base>true</use_base> and the file’s own <size>.

The read side mirrors it:

cpp
// src/Backups/BackupImpl.cpp:870
            bool use_base = get_bool("use_base", false);
            auto base_size_it = f.find("base_size");
            info.base_size = (base_size_it != f.end()) ? to_uint64(base_size_it->second, "base_size") : (use_base ? info.size : 0);
            if (info.base_size)
                use_base = true;

            if (info.base_size > info.size)
            {
                throw Exception(
                    ErrorCodes::BACKUP_DAMAGED,
                    "Backup {}: Base size must not be greater than the size of entry {}",
                    backup_name_for_logging,
                    quoteString(info.file_name));
            }

            if (use_base)
            {
                if (info.base_size == info.size)
                    info.base_checksum = info.checksum;
                else
                    info.base_checksum = unhexChecksum(req("base_checksum"));
            }

Note the validation: base_size > size is a BACKUP_DAMAGED error. A corrupted manifest cannot make the restore read past the end of a file.

Restoring: stitching the halves back together

Here is the payoff. When the restore reads a file, it may need two sources: the base backup’s prefix and this backup’s suffix.

cpp
// src/Backups/BackupImpl.cpp:1461
    if (info.size > info.base_size)
    {
        /// Make `read_buffer` if there is data for this backup entry in this backup.
        if (use_archive)
            read_buffer = archive_reader->readFile(info.data_file_name, /*throw_on_not_found=*/true);
        else
            read_buffer = reader->readFile(info.data_file_name);
    }

    if (info.base_size)
    {
        /// Make `base_read_buffer` if there is data for this backup entry in the base backup.
        auto base = getBaseBackup();
        if (!base)
        {
            throw Exception(
                ErrorCodes::NO_BASE_BACKUP,
                "Backup {}: Entry {} is marked to be read from a base backup, but there is no base backup specified",
                backup_name_for_logging, formatSizeAndChecksum(size_and_checksum));
        }

        if (!base->fileExists(std::pair(info.base_size, info.base_checksum)))
        {
            throw Exception(
                ErrorCodes::WRONG_BASE_BACKUP,
                "Backup {}: Entry {} is marked to be read from a base backup, but doesn't exist there",
                backup_name_for_logging, formatSizeAndChecksum(size_and_checksum));
        }

        base_read_buffer = base->readFile(info.file_name, std::pair{info.base_size, info.base_checksum});
    }

Two important details. First, the base is opened by the same name — the file is looked up in the base under the current file’s name, not under a recorded path. The base backup is expected to have the same layout.

Second, the base lookup is by (base_size, base_checksum), not by name. If the base backup no longer contains that exact content — because it was replaced, or because the wrong base was specified — the restore fails loudly with WRONG_BASE_BACKUP instead of silently producing a corrupt file.

And the final stitch:

cpp
// src/Backups/BackupImpl.cpp:1500
    if (!info.base_size)
    {
        /// Data comes completely from this backup, the base backup isn't used.
        return read_buffer;
    }
    if (info.size == info.base_size)
    {
        /// Data comes completely from the base backup (nothing comes from this backup).
        return base_read_buffer;
    }

    /// The beginning of the data comes from the base backup,
    /// and the ending comes from this backup.
    return std::make_unique<ConcatReadBufferFromFile>(
        info.data_file_name, std::move(base_read_buffer), info.base_size, std::move(read_buffer), info.size - info.base_size);

ConcatReadBufferFromFile is a ReadBufferFromFileBase that presents two buffers as one contiguous file. It is a real seekable buffer, not a one-shot stream — seek() walks the list of buffers to find the right one:

cpp
// src/IO/ConcatReadBufferFromFile.cpp:141
    off_t change_position = new_position - current_position;
    if ((working_buffer.begin() <= pos + change_position) && (pos + change_position <= working_buffer.end()))
    {
        /// Position is still inside the same working buffer.
        pos += change_position;
        chassert(pos >= working_buffer.begin());
        chassert(pos <= working_buffer.end());
        return new_position;
    }

    while (new_position < static_cast<off_t>(current_start_pos))
        current_start_pos -= buffers[--current].size;

    while (new_position >= static_cast<off_t>(current_start_pos + buffers[current].size))
        current_start_pos += buffers[current++].size;

    buffers[current].in->seek(new_position - current_start_pos, SEEK_SET);

That matters because a restore does not simply stream a file to a disk — it may read parts of it, checksum it, and copy it. The concatenated buffer has to behave like a normal file.

The disk-copy fast path

When the file needs no suffix (nothing new) the copy is forwarded to the base backup directly, so the base’s own sync and encryption handling are honoured:

cpp
// src/Backups/BackupImpl.cpp:1667
    else if (info.size && (info.size == info.base_size))
    {
        /// Data comes completely from the base backup (nothing comes from this backup). The base
        /// backup is itself a BackupImpl that honours `sync` and can read its own encrypted-by-disk
        /// entries, so forward the copy (and the `sync` request) there. Going through the generic
        /// branch below instead would read the base via the public readFile(), which always requests
        /// unencrypted data and would fail on an encrypted entry (CANNOT_RESTORE_TO_NONENCRYPTED_DISK).
        getBaseBackup()->copyFileToDisk(std::pair{info.base_size, info.base_checksum}, destination_disk, destination_path, write_mode, sync);
        file_copied = true;
    }

The prefix trick is only one of the two ways an incremental backup saves space. The other is deduplication by content, and it applies within a single backup as well as across backups: two files with the same (size, checksum) are stored once.

That is handled in the coordination layer, not the file layer:

cpp
// src/Backups/BackupCoordinationFileInfos.cpp:186
                SizeAndChecksum size_and_checksum{info.size, info.checksum};
                auto [it, inserted] = data_file_index_by_checksum.emplace(size_and_checksum, i);
                if (inserted)
                {
                    /// Found a new file.
                    info.data_file_name = getBackupDataFileName(info, config.data_file_name_generator, config.data_file_name_prefix_length);
                    info.data_file_index = i;
                    ++num_files;
                    total_size_of_files += info.size - info.base_size;
                }
                else
                {
                    /// Found a file with the same size and checksum as some file before, reuse old `data_file_index` and `data_file_name`.
                    info.data_file_index = it->second;
                    info.data_file_name = file_infos_for_all_hosts[it->second]->data_file_name;
                }

A file whose size equals its base_size gets no data file at all:

cpp
// src/Backups/BackupCoordinationFileInfos.cpp:178
            if (info.size == info.base_size)
            {
                /// A file is either empty or can be get from the base backup as a whole.
                info.data_file_name.clear();
                info.data_file_index = static_cast<size_t>(-1);
            }

An empty data_file_name means “there is no physical file for this entry”. That is also how empty files are handled — they have size == 0 and hence base_size == 0, so they fall into the same branch.

Trade-offs

What the design buys:

Dramatically smaller incremental backups for append-only workloads. A StorageLog table or a DDL-history file that grew by 1 KB stores 1 KB.
No diff algorithm, no chunk index, no content-defined chunking. The restore path is a seek and a concat; there is no library to get wrong.
Content-addressed safety. The prefix is verified by hash, not assumed. A base that does not match produces WRONG_BASE_BACKUP, not a corrupt restore.
The base is opened in a reduced mode. Looking up millions of files in the base costs a sorted (size, checksum) vector, not a full metadata parse.

What it costs:

The prefix must be byte-exact. A file that was rewritten in place — even if 99.9% of it is unchanged — is stored in full. There is no content-defined chunking to rescue the middle of the file.
The backup must read the file to compute the checksum. Even when the file is later skipped entirely, the checksum pass has already read it. This is the dominant cost of an incremental backup on a local disk: you pay a full read of every file to decide how much of it to write.
Immutable files never get the prefix treatment. A MergeTree part that was merged or rewritten is a new file, stored in full. So the incremental win is concentrated in the metadata, not in the data.
The restore needs the base. An incremental backup is not self-contained; losing the base means losing the backup. The manifest names the base, and RESTORE will open it, but the operational burden is on the user.
The base must keep the same file names. The restore looks the file up in the base under the current name. Rename a file between the base and the incremental backup and the prefix is lost (the file gets stored in full — safe, but wasteful).

Wrapping up

The one thing to remember: incremental backups in ClickHouse are not diffs. They are byte-range references. A backup entry is either “the whole file” or “bytes [N, size) of this file, plus bytes [0, N) from the base”.

The part people get wrong: the “prefix” is not a heuristic — it is verified with a hash, and the hash is only computed for files that are guaranteed append-only. isPartialChecksumAllowed() is the guard rail that makes the scheme safe, and it is the first thing to look at if you ever see an incremental backup produce a corrupt file.

What to go do about it: run a BACKUP ... SETTINGS BASE_BACKUP = ... twice in a row over a table with a StorageLog and compare the total_size and compressed_size columns of system.backups for the two runs. If the second run’s compressed_size is a small fraction of the first, the prefix path is working. If the two are about the same, your data is all in immutable MergeTree parts, and the prefix trick has nothing to work with — at which point you are paying the checksum read for nothing and should think about whether the base backup is worth it.