We added the Backup Database engine to ClickHouse^®

Recently, we introduced a new Database engine to ClickHouse® called Backup. It is available with the latest ClickHouse version 25.2. This engine allows you to instantly attach tables/databases from your backup and work with them in read-only mode as with usual tables without any performance degradation. It supports incremental/non incremental backups and all backup engines. 

Motivation

When you need to upgrade a client to the new ClickHouse version you almost always have some issues. Performance of certain functionality can change, there can be documented/undocumented backward incompatible changes, some settings can be introduced/changed, and some settings can become completely unnecessary.

More importantly, some of these issues only reproduce in very rare edge cases with your production data or your particular use case.

We saw this happen to one of Tinybird's customers after we migrated to the latest ClickHouse LTS version. Some operations started to run significantly slower, and we had to rollback the upgrade. In this particular case, it was not possible to reproduce issues using just customer tables and queries without actual data; we needed to run those operations against real customer data to understand and debug the issues.

We don't want to upgrade a client's cluster just to understand why operations are slower and try to debug them, because that can potentially affect the clients' production workload.

One idea we came up with was to run these operations against data that was in backup. But, if you have database backups with terabytes of data, restoring data from backup can take a long time. And if you're using cloud storage, this can also cost you a lot of money if you need to run a lot of S3 copy operations.

It is possible to just use backups as a source of data and instantly attach them to ClickHouse. But this introduces a lot of complications, because backups have a weird format, and incremental backups can have any amount of parent base backups making it impossible to just attach it to ClickHouse.

Implementation

I had thought that the backups implementation was very similar to disk implementation internally in ClickHouse. And it can work similar to web disks, except for one thing: If you try to adapt the backup engine to a disk interface, you cannot just use this disk in MergeTree table storage policy, because the underlying data in backup is stored in a format similar to DatabaseOrdinary, and you cannot attach such a table to a DatabaseAtomic that expects that the table data path will be store/first_uuid_characters/uuid.

So, I decided it made  sense to implement a new Backup database engine. That way, we could actually solve a lot of problems:

1. You can have backup with any amount of databases and tables. You just need to specify a concrete database name in the Backup database engine.
2. The internal backup layout for a table produced using the BACKUP TABLE query and a database produced using the BACKUP DATABASE query are exactly the same. That way, this feature can work with both table and database backups in a generic way.
3. You do not need to specify table types, because all metadata for all tables is already stored in backup.
4. During database load, it is possible to properly bootstrap tables when you read table metadata, so you can change engines in ReplicatedMergeTree tables, change storage policies, change views/materialized views queries, etc.

All this is good, but there's still one problem: the database relative path will still be completely different from the backup database relative path. So, we need to also have a path translation layer somewhere inside the disk implementation.

So, the current design has following components:

1. BackupDisk - adapts the backup interface to the disk interface and performs path translation.
2. DatabaseBackup - uses backup to load table metadata, changes table metadata to properly bootstrap tables, creates a storage policy with backup disk, and sets this storage policy to bootstrap tables.

When the MergeTree table inside the database Backup tries to read a file, it uses the relative path of the Backup database that gets translated inside the DiskBackup to a proper path that can be used to query files/directories in backup.

Client interface

I wanted the client interface for this feature to be as simple as possible and familiar to people who work with backups in ClickHouse. 

Here's how you can use database Backup from a client perspective.

Imagine you have backup query:

BACKUP_ENGINE can be anything like S3, Azure, File, or Disk. With Disk, the backup engine looks like this:

To create a database Backup you can just use a query such as this:

For example with Disk, the backup engine query will be like this:

The goal was for the client to specify exactly the same engine that it used during backup creation. For all backup engines database, Backup will work.

Usage example

Database Backup works with both incremental and non-incremental backups.

Let’s look at an example with a Disk engine, because this looks more like production configuration. First, we setup backups disk in storage.xml:

Then, we create the test database and tables, insert some data, and create a backup:

We now have the test_database_backup backup, so we can create a database Backup:

Now we can query any table from the database Backup:

It is also possible to work with this database as with any ordinary database. For example, we can query system.tables to get its tables:

More complex potential examples of usage

As I mentioned at the beginning, it is possible to use this functionality to validate upgrades between different ClickHouse versions and run client queries/operations against actual client data. But it is also possible to do a lot of other interesting things on top of DatabaseBackup.

1. Build Time Machine functionality. You can have a daily/weekly backup and allow the client to specify from which time it wants to query data. You can also query data from multiple database backups that represent data snapshots at different times and combine/join them together to analyze and get valuable insights.
2. Share datasets with multiple tables. Database Backup is one of the easiest ways to share datasets that contain multiple tables in ClickHouse. You can attach such datasets instantly to analyze them without needing to create any tables manually or know column types of the tables in advance, because database Backup will get the table definitions from backup.
3. Run ad hoc queries or operations off of production tables. You can run ad-hoc queries/operations against data in backups without needing a big ClickHouse cluster. For example, you can spawn ClickHouse machines and run queries against backups. This now works for a single ClickHouse machine, but for multiple machines it still not very convenient, because you will need to implement sharding on the query level so that each machine processes different data. Still, it is possible to do.
4. Attach tables from backup for writes. In the future,  it could be possible to implement functionality that would allow you to instantly attach tables in read-only mode from backup and use another disk for writes, so that clients would be able to write to these tables. All writes would be handled by writable disk, and eventually ClickHouse would merge all data into writable disk. 

Conclusion

Database Backup is available in the latest ClickHouse 25.2 release. We already have many plans on how to use this functionality in Tinybird, especially with our upcoming “self-managed Tinybird” that will allow you to spin up a new Tinybird region in seconds in your favorite K8S or Docker provider. (🤫 Stay tuned)

We also have a lot of ideas for future database Backup improvements, which we will implement soon.