## Overview [In FY23 ClickHouse was selected](https://about.gitlab.com/company/team/structure/working-groups/clickhouse-datastore/#context) as GitLab's standard datastore for features with big data and insert-heavy requirements. Decision-makers in large enterprises need to aggregate this data from multiple stages, projects and groups into a single view, and PostgreSQL [can't handle such complex analytical tasks](https://gitlab.com/gitlab-org/gitlab/-/issues/389255). Based on https://gitlab.com/gitlab-org/opstrace/opstrace/-/issues/2168+ we have the go ahead to start experimenting with ClickHouse. [Contribution Analytics](https://docs.gitlab.com/ee/user/group/contribution_analytics/) takes an insanely long time to load and the data structure is a single table which would be perfect for a PoC for moving a feature to CH. Optimizing the retrieval of this data could also allow us to potentially iterate on VSD by introducing contribution metrics. ClickHouse is not intended to replace Postgres or Redis in GitLab's stack. ## POC Goal and Objectives: The goal of this POC is to examine if it's feasible and effective to sync data from PostgreSQL to ClickHouse, and evaluate if CH efficiently handles complex analytical queries with large volumes of data. #### POC Objectives: 1. Schema Design: Define a high-performing ClickHouse DB schema and DB queries for the Contribution Analytics feature (schema for the `events` table.) 1. Data pipeline: Define how to export and import the `events` table from PG to CH. 1. Data sync: Implement periodically data sync worker. 1. [Recommend a unified rollout strategy](https://gitlab.com/gitlab-org/opstrace/opstrace/-/issues/2168#goal) for Observability, Product Analytics, and Plan::Optimize that enables each team to have access to their own isolated CH instance. #### Success criteria for the POC: * Look at a few smaller groups and compare the query execution timings. * Percentage of total group data was read to answer the query (`Group %`): `(returned_events_in_the_group / total_events_in_the_group) * 100` * Percentage of global data was read to answer the query (`Total %`): `(returned_events_in_the_group / total_events) * 100` </details> ## Proposal We can split the PoC into 3 steps: #### Schema creation and data loading Assuming that we'll have a CH cluster configured on PRD we'll need to do the following: - Define and create a database schema for the `events` table based on this guide: https://docs.gitlab.com/ee/development/database/clickhouse/gitlab_activity_data.html - Create a change request issue to export and import the `events` table from PG to CH (some transformation might be required). This speeds up the data sync process since we'll only need to sync the rows between the import date and the current date. #### Backend changes Currently, the `Gitlab::ContributionAnalytics::DataCollector` class is responsible for providing data to the view layer (GraphQL). Essentially, we're returning a hash with counts which will be split/processed in Ruby. Let's split this class into two: - `Gitlab::ContributionAnalytics::DataCollector` => interface, builds the DB specific data collector (based on a flag) implementation and exposes `all_counts`. - `Gitlab::ContributionAnalytics::PostgresqlDataCollector` => PG implementation - `Gitlab::ContributionAnalytics::ClickHouseDataCollector` => CH implementation - `Gitlab::ContributionAnalytics::DataFormatter` => Implements the data formatting such as `merge_requests_merged_by_author_count` and `totals` methods. Note: A similar approach is described here for Elastic: https://gitlab.com/gitlab-org/gitlab/-/issues/407347 Example usage: ```ruby Gitlab::ContributionAnalytics::DataFormatter.new(group: group).merge_requests_approved_by_author_count ``` The initializer of the `DataFormatter` accepts an optional argument to "inject" the collector. ```ruby def initialize(group: group, data_collector: Gitlab::ContributionAnalytics::DataCollector.new(group: group)) end # later in tests Gitlab::ContributionAnalytics::DataFormatter.new(group: group, data_collector: mocked_data_collector) ``` Note: we assume that a connection to the CH will be available in the GitLab application. Based on this blueprint: https://gitlab.com/gitlab-com/www-gitlab-com/-/issues/14379 #### Keep data in sync Implement a background job that periodically (5 minutes) sends event records to CH. How should it work: - Keep the most recently processed `events.id` value in a variable on Redis (cursor). - If we manage to have a one-time import, take the latest `id` value and set it manually in Redis. (so we won't process the whole table) - Set up a background job that takes N rows (id > cursor_value) every 5 minute and inserts to CH. Note: this job ensures that new data will land on CH. However, updates/deletions of existing data will not be synced. Once we validated the PoC, we would need to implement a scheduled consistency worker. Or look into an alternative approach of keeping data in sync.