ClickHouse

ClickHouse is an open-source column-oriented database management system. It can efficiently filter, aggregate, and query across large data sets.

GitLab uses Clickhouse as a secondary data store to enable advanced analytics features such as GitLab Duo, SDLC trends, and CI Analytics. GitLab only stores data that supports these features in Clickhouse.

You should use ClickHouse Cloud to connect ClickHouse to GitLab.

Alternatively, you can bring your own ClickHouse. For more information, see ClickHouse recommendations for GitLab Self-Managed.

Analytics available with ClickHouse

After you configure ClickHouse, you can use the following analytics features:

Supported ClickHouse versions

The supported ClickHouse version differs depending on your GitLab version:

• GitLab 17.7 and later supports ClickHouse 23.x. To use either ClickHouse 24.x or 25.x, use the workaround.
• GitLab 18.1 and later supports ClickHouse 23.x, 24.x, and 25.x.
• GitLab 18.8 and later supports ClickHouse 23.x, 24.x, 25.x, and the Replicated database engine.

ClickHouse Cloud is always compatible with the latest stable GitLab release.

Set up ClickHouse

Choose your deployment type based on your operational requirements:

• ClickHouse Cloud (Recommended): Fully managed service with automatic upgrades, backups, and scaling.
• ClickHouse for GitLab Self-Managed (BYOC): Complete control over your infrastructure and configuration.

After setting up your ClickHouse instance:

1. Create the GitLab database and user.
2. Configure the GitLab connection.
3. Verify the connection.
4. Run ClickHouse migrations.
5. Enable ClickHouse for Analytics.

Set up ClickHouse Cloud

Prerequisites:

• Have a ClickHouse Cloud account.
• Enable network connectivity from your GitLab instance to ClickHouse Cloud.
• Be an administrator your GitLab instance.

To set up ClickHouse Cloud:

1. Sign in to ClickHouse Cloud.
2. Select New Service.
3. Choose your service tier:
   • Development: For testing and development environments.
   • Production: For production workloads with high availability.
4. Select your cloud provider and region. Choose a region close to your GitLab instance for optimal performance.
5. Configure your service name and settings.
6. Select Create Service.
7. Once provisioned, note your connection details from the service dashboard:
   • Host
   • Port (usually 9440 for secure connections)
   • Username
   • Password

After you create your ClickHouse Cloud service, you then create the GitLab database and user.

Set up ClickHouse for GitLab Self-Managed (BYOC)

Prerequisites:

• Have a ClickHouse instance installed and running. If ClickHouse is not installed, see:
  • ClickHouse official installation guide.
  • ClickHouse recommendations for GitLab Self-Managed.
• Have a supported ClickHouse version.
• Enable network connectivity from your GitLab instance to ClickHouse.
• Be an Administrator for both ClickHouse and your GitLab instance.

Configure High Availability

For a multi-node, high-availability (HA) setup, GitLab supports the Replicated table engine in ClickHouse.

Prerequisites:

• Have a ClickHouse cluster with multiple nodes. A minimum of three nodes is recommended.
• Define a cluster in the remote_servers configuration section.
• Configure the following macros in your ClickHouse configuration:
  • cluster
  • shard
  • replica

When configuring the database for HA, you must run the statements with the ON CLUSTER clause.

For more information, see ClickHouse Replicated database engine documentation.

Configure Load balancer

The GitLab application communicates with the ClickHouse cluster through the HTTP/HTTPS interface. For HA deployments, use an HTTP proxy or load balancer to distribute requests across ClickHouse cluster nodes.

Recommended load balancer options:

• chproxy - ClickHouse-specific HTTP proxy with built-in caching and routing.
• HAProxy - General-purpose TCP/HTTP load balancer.
• NGINX - Web server with load balancing capabilities.
• Cloud provider load balancers (AWS Application Load Balancer, GCP Load Balancer, Azure Load Balancer).

Basic chproxy configuration example:

server:
  http:
    listen_addr: ":8080"

clusters:
  - name: "clickhouse_cluster"
    nodes: [
      "http://ch-node1:8123",
      "http://ch-node2:8123",
      "http://ch-node3:8123"
    ]

users:
  - name: "gitlab"
    password: "your_secure_password"
    to_cluster: "clickhouse_cluster"
    to_user: "gitlab"

When using a load balancer, configure GitLab to connect to the load balancer URL instead of individual ClickHouse nodes.

For more information, see chproxy documentation.

After you configure your ClickHouse for GitLab Self-Managed instance, create the GitLab database and user.

Verify ClickHouse installation

Before configuring the database, verify ClickHouse is installed and accessible:

1. Check ClickHouse is running:

   clickhouse-client --query "SELECT version()"

   If ClickHouse is running, you see the version number (for example, 24.3.1.12).

2. Verify you can connect with credentials:

   clickhouse-client --host your-clickhouse-host --port 9440 --secure --user default --password 'your-password'

   If you have not configured TLS yet, use port 9000 without the --secure flag for initial testing.

Create database and user

To create the necessary user and database objects:

1. Generate a secure password and save it.
2. Sign in to:
   • For ClickHouse Cloud, the ClickHouse SQL console.
   • For ClickHouse for GitLab Self-Managed, the clickhouse-client.
3. Run the following commands, replacing PASSWORD_HERE with the generated password.

CREATE DATABASE gitlab_clickhouse_main_production;
CREATE USER gitlab IDENTIFIED WITH sha256_password BY 'PASSWORD_HERE';
CREATE ROLE gitlab_app;
GRANT SELECT, INSERT, ALTER, CREATE, UPDATE, DROP, TRUNCATE, OPTIMIZE ON gitlab_clickhouse_main_production.* TO gitlab_app;
GRANT SELECT ON information_schema.* TO gitlab_app;
GRANT gitlab_app TO gitlab;

CREATE DATABASE gitlab_clickhouse_main_production ON CLUSTER CLUSTER_NAME_HERE ENGINE = Replicated('/clickhouse/databases/{cluster}/gitlab_clickhouse_main_production', '{shard}', '{replica}');
CREATE USER gitlab IDENTIFIED WITH sha256_password BY 'PASSWORD_HERE' ON CLUSTER CLUSTER_NAME_HERE;
CREATE ROLE gitlab_app ON CLUSTER CLUSTER_NAME_HERE;
GRANT SELECT, INSERT, ALTER, CREATE, UPDATE, DROP, TRUNCATE, OPTIMIZE ON gitlab_clickhouse_main_production.* TO gitlab_app ON CLUSTER CLUSTER_NAME_HERE;
GRANT SELECT ON information_schema.* TO gitlab_app ON CLUSTER CLUSTER_NAME_HERE;
GRANT gitlab_app TO gitlab ON CLUSTER CLUSTER_NAME_HERE;

Configure the GitLab connection

Verify the connection

To verify that your connection is set up successfully:

1. Sign in to the Rails console.

2. Execute the following command:

   ClickHouse::Client.select('SELECT 1', :main)

   If successful, the command returns [{"1"=>1}].

If the connection fails, verify:

• ClickHouse service is running and accessible.
• Network connectivity from GitLab to ClickHouse. Check that firewalls and security groups allow connections.
• Connection URL is correct (host, port, protocol).
• Credentials are correct.
• For HA cluster deployments: Load balancer is properly configured and routing requests.

Run ClickHouse migrations

sudo gitlab-rake gitlab:clickhouse:migrate

gitlab-rake gitlab:clickhouse:migrate

Enable ClickHouse for Analytics

After your GitLab instance is connected to ClickHouse, you can enable features that use ClickHouse:

Prerequisites:

• You must have administrator access to the instance.
• ClickHouse connection is configured and verified.
• Migrations have been successfully completed.

To enable ClickHouse for Analytics:

1. On the left sidebar, at the bottom, select Admin.
2. Select Settings > General.
3. Expand ClickHouse.
4. Select Enable ClickHouse for Analytics.
5. Select Save changes.

Disable ClickHouse for Analytics

To disable ClickHouse for Analytics:

Prerequisites:

• You must have administrator access to the instance.

To disable:

1. On the left sidebar, at the bottom, select Admin.
2. Select Settings > General.
3. Expand ClickHouse.
4. Clear the Enable ClickHouse for Analytics checkbox.
5. Select Save changes.

Upgrade ClickHouse

ClickHouse Cloud

ClickHouse Cloud automatically handles version upgrades and security patches. No manual intervention is required.

For information about upgrade scheduling and maintenance windows, see the ClickHouse Cloud documentation.

ClickHouse for GitLab Self-Managed (BYOC)

For ClickHouse for GitLab Self-Managed, you are responsible for planning and executing version upgrades.

Prerequisites:

• Have administrator access to the ClickHouse instance.
• Back up your data before upgrading. See Disaster recovery.

Before upgrading:

1. Review the ClickHouse release notes for breaking changes.
2. Check compatibility with your GitLab version.
3. Test the upgrade in a non-production environment.
4. Plan for potential downtime, or use a rolling upgrade strategy for HA clusters.

To upgrade ClickHouse:

1. For single-node deployments, follow the ClickHouse upgrade documentation.
2. For HA cluster deployments, perform a rolling upgrade to minimize downtime:
   • Upgrade one node at a time.
   • Wait for the node to rejoin the cluster.
   • Verify cluster health before proceeding to the next node.

For detailed upgrade procedures, see the ClickHouse documentation on updates.

Operations

Check migration status

Prerequisites:

• You must have administrator access to the instance.

To check the status of ClickHouse migrations:

1. On the left sidebar, at the bottom, select Admin.
2. Select Settings > General.
3. Expand ClickHouse.
4. Review the Migration status section if available.

Alternatively, check for pending migrations using the Rails console:

# Sign in to Rails console
# Run this to check migrations
ClickHouse::MigrationSupport::Migrator.new(:main).pending_migrations

Retry failed migrations

If a ClickHouse migration fails:

1. Check the logs for error details. ClickHouse-related errors are logged in the GitLab application logs.

2. Address the underlying issue (for example, insufficient memory, connectivity problems).

3. Retry the migration:

   # For installations that use the Linux package
   sudo gitlab-rake gitlab:clickhouse:migrate
   
   # For self-compiled installations
   bundle exec rake gitlab:clickhouse:migrate RAILS_ENV=production

ClickHouse Rake tasks

GitLab provides several Rake tasks for managing your ClickHouse database.

The following Rake tasks are available:

Common task examples

Verify ClickHouse connection and schema

To verify your ClickHouse connection is working:

# For installations that use the Linux package
sudo gitlab-rake gitlab:clickhouse:info

# For self-compiled installations
bundle exec rake gitlab:clickhouse:info RAILS_ENV=production

This task outputs debugging information about the ClickHouse connection and configuration.

Re-run all migrations

To run all pending migrations:

# For installations that use the Linux package
sudo gitlab-rake gitlab:clickhouse:migrate

# For self-compiled installations
bundle exec rake gitlab:clickhouse:migrate RAILS_ENV=production

Reset the database

To drop and recreate the database:

# For installations that use the Linux package
sudo gitlab-rake gitlab:clickhouse:drop
sudo gitlab-rake gitlab:clickhouse:setup

# For self-compiled installations
bundle exec rake gitlab:clickhouse:drop RAILS_ENV=production
bundle exec rake gitlab:clickhouse:setup RAILS_ENV=production

Environment variables

You can use environment variables to control Rake task behavior:

Performance tuning

For information about ClickHouse architecture and performance tuning, see the ClickHouse documentation on architecture.

Disaster recovery

Backup and Restore

You should perform a full backup before upgrading the GitLab application. ClickHouse data is not included in GitLab backup tooling.

Backup and restore strategy depends on the choice of deployment.

ClickHouse Cloud

ClickHouse Cloud automatically:

• Manages the backups and restores.
• Create and retains daily backups.

You do not have to do any additional configuration.

For more information, see ClickHouse Cloud backups.

ClickHouse for GitLab Self-Managed

If you manage your own ClickHouse instance, you should take regular backups to ensure data safety:

• Take initial full backups of tables (excluding system tables like metrics or logs) to a object storage bucket, for example AWS S3.
• Take incremental backups after this initial full backup.

This duplicates data for every full backup, but is the easiest approach to restore data.

Alternatively, use clickhouse-backup. This is a third-party tool that provides similar functionality with additional features like scheduling and remote storage management.

Monitoring

To ensure the stability of the GitLab integration, you should monitor the health and performance of your ClickHouse cluster.

ClickHouse Cloud

ClickHouse Cloud provides a native Prometheus integration that exposes metrics through a secure API endpoint.

After generating the API credentials, you can configure collectors to scrape metrics from ClickHouse Cloud. For example, a Prometheus deployment.

ClickHouse for GitLab Self-Managed

ClickHouse can expose metrics in Prometheus format. To enable this:

1. Configure the prometheus section in your config.xml to expose metrics on a dedicated port (default is 9363).

   <prometheus>
       <endpoint>/metrics</endpoint>
       <port>9363</port>
       <metrics>true</metrics>
       <events>true</events>
       <asynchronous_metrics>true</asynchronous_metrics>
   </prometheus>

2. Configure Prometheus or a similar compatible server to scrape http://<clickhouse-host>:9363/metrics.

Metrics to monitor

You should set up alerts for the following metrics to detect issues that may impact GitLab features:

Liveness check

If ClickHouse is available behind a load balancer, you can use the HTTP /ping endpoint to check for liveness. The expected response is Ok with HTTP Code 200.

Security and auditing

To ensure the security of your data and ensure audit ability, use the following security practices.

Network security

• TLS Encryption: Configure ClickHouse servers to use TLS encryption to validate connections.

  When configuring the connection URL in GitLab, you should use the https:// protocol (for example, https://clickhouse.example.com:8443) to specify this.

• IP Allow lists: Restrict access to the ClickHouse port (default 8443 or 9440) to only the GitLab application nodes and other authorized networks.

Audit logging

GitLab application does not maintain a separate audit log for individual ClickHouse queries. In order to satisfy specific requirements regarding data access (who queried what and when), you can enable logging on the ClickHouse side.

ClickHouse Cloud

In ClickHouse Cloud, query logging is enabled by default. You can access these logs by querying the system.query_log table.

ClickHouse for GitLab Self-Managed

For self-managed instances, ensure the query_log configuration parameter is enabled in your server configuration:

1. Verify that the query_log section exists in your config.xml or users.xml:

   <query_log>
       <database>system</database>
       <table>query_log</table>
       <partition_by>toYYYYMM(event_date)</partition_by>
       <flush_interval_milliseconds>7500</flush_interval_milliseconds>
       <ttl>event_date + INTERVAL 30 DAY</ttl>  <!-- Keep only 30 days -->
   </query_log>

2. Once enabled, all executed queries are recorded in the system.query_log table, allowing for audit trail.

System requirements

The recommended system requirements change depending on the number of users.

Deployment decision matrix quick reference

1K Users

Recommendation: ClickHouse Cloud Basic as it provides good cost efficiency with no operational complexity.

2K Users

Recommendation: ClickHouse Cloud Basic as it offers best value with no operational complexity.

Alternative recommendation for ClickHouse for GitLab Self-Managed deployment:

• AWS: m8g.xlarge (4 vCPU, 16 GB)
• GCP: c4a-standard-4 or n4-standard-4 (4 vCPU, 16 GB)
• Storage: 20 GB with low-medium performance tier

3K Users

Recommendation: ClickHouse Cloud Scale

Alternative recommendation for ClickHouse for GitLab Self-Managed deployment:

• AWS: m8g.2xlarge (8 vCPU, 32 GB)
• GCP: c4a-standard-8 or n4-standard-8 (8 vCPU, 32 GB)
• Storage: 100 GB with medium performance tier

Note: HA deployments not cost-effective at this scale.

5K Users

Recommendation: ClickHouse Cloud Scale

Alternative recommendation for ClickHouse for GitLab Self-Managed deployment:

• AWS: m8g.4xlarge (16 vCPU, 64 GB)
• GCP: c4a-standard-16 or n4-standard-16 (16 vCPU, 64 GB)
• Storage: 100 GB with high performance tier
• Deployment: Single node recommended

10K Users

Recommendation: ClickHouse Cloud Scale

Alternative recommendation for ClickHouse for GitLab Self-Managed deployment:

• AWS: m8g.4xlarge (16 vCPU, 64 GB)
• GCP: c4a-standard-16 or n4-standard-16 (16 vCPU, 64 GB)
• Storage: 200 GB with high performance tier
• HA Option: 3-node cluster becomes viable for critical workloads

25K Users

Recommendation: ClickHouse Cloud Scale or ClickHouse for GitLab Self-Managed. Both options are economically feasible at this scale.

Recommendations for ClickHouse for GitLab Self-Managed deployment:

• Single Node:

  • AWS: m8g.8xlarge (32 vCPU, 128 GB)
  • GCP: c4a-standard-32 or n4-standard-32 (32 vCPU, 128 GB)

• HA Deployment:

  • AWS: 3 × m8g.4xlarge (16 vCPU, 64 GB each)
  • GCP: 3 × c4a-standard-16 or 3 × n4-standard-16 (16 vCPU, 64 GB each)

• Storage: 400 GB per node with high performance tier.

50K Users

Recommendation: ClickHouse for GitLab Self-Managed HA or ClickHouse Cloud Scale. The self-managed option is slightly more cost-effective at this scale.

Recommendations for ClickHouse for GitLab Self-Managed deployment:

• Single Node:

  • AWS: m8g.8xlarge (32 vCPU, 128 GB)
  • GCP: c4a-standard-32 or n4-standard-32 (32 vCPU, 128 GB)

• HA Deployment (Preferred):

  • AWS: 3 × m8g.4xlarge (16 vCPU, 64 GB each)
  • GCP: 3 × c4a-standard-16 or 3 × n4-standard-16 (16 vCPU, 64 GB each)

• Storage: 1000 GB per node with high performance tier.

HA considerations for ClickHouse for GitLab Self-Managed deployment

HA setup becomes cost effective only at 10k users or above.

• Minimum: Three ClickHouse nodes for quorum.
• ClickHouse Keeper: Three nodes for coordination (can be co-located or separate).
• LoadBalancer: Recommended for distributing queries.
• Network: Low-latency connectivity between nodes is critical.

Glossary

• Cluster: A collection of nodes (servers) that work together to store and process data.
• MergeTree: MergeTree is a table engine in ClickHouse designed for high data ingest rates and large data volumes. It is the core storage engine in ClickHouse, providing features such as columnar storage, custom partitioning, sparse primary indexes, and support for background data merges.
• Parts: A physical file on a disk that stores a portion of the table’s data. A part is different from a partition, which is a logical division of a table’s data that is created using a partition key.
• Replica: A copy of the data stored in a ClickHouse database. You can have any number of replicas of the same data for redundancy and reliability. Replicas are used in conjunction with the ReplicatedMergeTree table engine, which enables ClickHouse to keep multiple copies of data in sync across different servers.
• Shard: A subset of data. ClickHouse always has at least one shard for your data. If you do not split the data across multiple servers, your data is stored in one shard. Sharding data across multiple servers can be used to divide the load if you exceed the capacity of a single server.
• TTL (Time To Live): Time To Live (TTL) is a ClickHouse feature that automatically moves, deletes, or rolls up columns/rows after a certain time period. This allows you to manage storage more efficiently because you can delete, move, or archive the data that you no longer need to access frequently.

Troubleshooting

Database schema migrations on GitLab 18.0.0 and earlier

Code: 344. DB::Exception: Projection is fully supported in ReplacingMergeTree with deduplicate_merge_projection_mode = throw. Use 'drop' or 'rebuild' option of deduplicate_merge_projection_mode

To work around this issue and run the migrations:

1. Sign in to the Rails console.

2. Execute the following command:

   ClickHouse::Client.execute("INSERT INTO schema_migrations (version) VALUES ('20231114142100'), ('20240115162101')", :main)

3. Migrate the database again:

   sudo gitlab-rake gitlab:clickhouse:migrate

This time the database migration should successfully finish.