Clickhouse

What is ClickHouse

ClickHouse is an open-source column-oriented database management system designed for online analytical processing (OLAP).

It's known for its exceptional query performance on large datasets, making it ideal for real-time analytics and data warehousing applications.

ClickHouse uses a column-oriented storage format and employs various optimizations like vectorized query execution to achieve high performance.

It supports SQL with extensions and can handle both batch and stream data ingestion, making it versatile for various analytical workloads.

To connect

Map the port 8123:8123 for the HTTP interface.

In DBeaver, connect to http://__ip_address__:8123 and use the default username plus the password in the UI.

If you also want the native TCP protocol (used by clickhouse-client CLI), publish 9000:9000 as well.

Currently, Coolify does not support modifying ClickHouse configurations, which means certain native backup options (e.g., backing up to a local Disk or using ALTER TABLE ... FREEZE PARTITION ...) are not possible. Instead, the recommended approach is to use S3 for backups.

How to Backup ClickHouse

To backup a table or an entire database, use the following SQL command:

Backup a Table:

BACKUP TABLE <table_name> TO S3('<your_s3_endpoint_com>/<unique_folder_for_table_backup>', '<s3_access_key>', '<s3_secret_key>')

Backup a Database: Replace TABLE with DATABASE to backup the whole database:

BACKUP DATABASE <database_name> TO S3('<your_s3_endpoint_com>/<unique_folder_for_database_backup>', '<s3_access_key>', '<s3_secret_key>')

How to Restore ClickHouse

To restore a table or database from an S3 backup, use the corresponding RESTORE command:

Restore a Table:

RESTORE TABLE <table_name> FROM S3('<your_s3_endpoint_com>/<unique_folder_from_table_backup>', '<s3_access_key>', '<s3_secret_key>')

Restore a Database: Replace TABLE with DATABASE to restore the whole database:

RESTORE DATABASE <database_name> FROM S3('<your_s3_endpoint_com>/<unique_folder_from_database_backup>', '<s3_access_key>', '<s3_secret_key>')

What Doesn't Work

Disk Backups:

BACKUP TABLE test.table TO Disk('backups', '1.zip')

Does not work due to Coolify not allowing modifications to ClickHouse configurations.

Native Partition Freezes:

ALTER TABLE ... FREEZE PARTITION ...

May not work because of limitations in the Docker/Coolify file structure.

clickhouse-backup Tool: External tools like clickhouse-backup might not function properly within the Docker/Coolify setup due to similar configuration restrictions.

Performance Notes

A community member shared that backing up a 145GB database took around 12 minutes, while restoring it took roughly 17 minutes.