Transition to ClickHouse for Borg Catalog

[marcpope]

Over the last week, we've completely rewritten the way catalogs work. Catalogs store all the borg changes to a file so it's fast to search and pick what you want to recover without the need to lock/mount the borg repo. Plus it gives us lots of stats on your backups to make into nice dashboards.

The first release of Borg Backup Server used a relational design where each path backed up was a row and there was transactions against that path in the database. While fairly efficient, the speed was insufferable. After doing a lot of testing, we migrated to a more flat design where every borg action (update, delete, new file, etc) was added and indexable. This ended up using way too much space. We had 10 servers backing up and some of the servers had 5 million files. With a decent history of 24 recovery points, that catalog alone was growing to 30GB by itself. It also was slow, it was taking 10-20 minutes to injest and caused record locks.

After a lot of research, we came across ClickHouse. This database seemed to check all the boxes, open source, low memory footprint, compression/de-dupe, and super fast. It's made for this exact thing! So another revision. If you are already running, no worry, the catalogs will auto-rebuild into ClickHouse when you upgrade. So far, for us, it's been very seamless and the speed is incredible. 20 minute operations are now 10-12 seconds. Searching is near instantaneous. Database storage went from 120Gb on our server to just 10Gb. A massive savings.

Let us know how things are going and if you've encountered any bugs as this was a major change. We're still tweaking the dashboard stats to have more ClickHouse data. MySQL is still used for the other database functions but the databases will be much smaller now.

Please make sure to update as this is new software and a lot of changes are happening.