# Overview This epic describes use cases and experiments for ClickHouse within ~"devops::manage". ClickHouse could be a great tool for OLAP style queries where a large volume of data needs to be accessed and aggregated in real-time. Within ~"devops::manage", probably ~"group::optimize" would benefit the most out of ClickHouse. ## Challenges ### Consistency Our main database is PostgreSQL, most of the data is being generated and stored there. Introducing another data storage will affect the overall consistency of the system. This is not a new problem, the database decomposition effort (Main and CI PG DBs) already addressed some of these issues. - No cross-database transactions. - Data cleanup, for example: when a user is deleted, clean up the user-related data in ClickHouse. - Async data ingestion (ClickHouse). - Access control. Many queries depend on the project visibility settings and the current user's authorized projects. ## Ideas ### Move `events` table to ClickHouse The `events` table is one of the top 10 DB tables in GitLab, its total size is close to 700GB. The table records various user activities and holds references to core domain objects: projects, groups, issues, merge requests, etc. To mitigate the data growth, there is a periodical worker to [prune the old event data](https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/workers/prune_old_events_worker.rb). It appears that the worker cannot keep up with the table growth, it deletes 10K rows every 6 hours. Performance issues (current): - Activity graph (it loads quite slowly for some users) - Recent activities, followed users (most of the performance issues have been addressed: https://gitlab.com/gitlab-org/gitlab/-/issues/346435) - Contribution analytics times out for some date ranges (https://gitlab.com/gitlab-org/gitlab/-/issues/55464) Challenges (CH): - Isolating the `Event` domain object (model) from the rest of the application. It would be no longer an ActiveRecord model. - Data migration and switchover. - Many queries require a project access check (example: activity graph), this would complicate the queries and increase the amount of data to be synced. Benefits: - Significantly reduced disk usage, the table could be completely removed from PostgreSQL. - Faster queries, reduced 500 errors due to statement timeout. - Possibility for building more complex queries and visualizations. #### Implementation idea - `events` table is used all over the application. Often accessed/modified within a transaction. - In some cases, an event `row` can be mutated or a duplicate event might be inserted (`wiki` event) - The pain of separating the `events` table could be eased and consistency properties could be preserved if we use the sliding partitioning strategy (already used for [loose foreign keys](https://docs.gitlab.com/ee/development/database/loose_foreign_keys#database-partitioning)) for collecting and syncing the `events` rows. How: 1. Introduce a new sliding partitioned table: `events_stream`. The table has the same schema as `events`. 2. Events records are going to accumulate in a PG DB partition. 3. A periodical worker queries the partition and bulk `upserts` the rows to ClickHouse. 4. After the query is finished, the records in the PG DB are marked "synced". 5. Old DB partitions where all records are synced can be dropped. (reclaiming disk space) Note: an initial migration of `events` table would be still required. Additional changes: - Introduce a unique identifier (fingerprint) which uniquely identifies an event. This can be useful when an event is updated or deleted. ClickHouse has table engines designed for changing/replacing data. - Deletion operation can be also implemented if we add an `operation` column to the `event_stream` table. #### PoC Experimental ClickHouse schema: ```sql DROP TABLE IF EXISTS events; CREATE TABLE events( project_id UInt64, author_id UInt64, target_id UInt64, created_at DateTime, updated_at DateTime, action UInt8, target_type String, group_id UInt64, fingerprint String, id UInt64 ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY (project_id, author_id, id); ``` Export some data from PG: ```sql \copy (SELECT coalesce(project_id, 0), author_id, coalesce(target_id, 0), extract(epoch from created_at)::bigint, extract(epoch from updated_at)::bigint, action, coalesce(target_type, ''), coalesce(group_id, 0), '', id FROM events where id > 1643752292) to 'events.csv' with csv ``` Import to CH: ```sql cat ~/events.csv | clickhouse-client -h 127.0.0.1 -q 'insert into events format CSV' ``` ### DORA Data for DORA metrics are collected after an environment has successful deployment. The data collection and aggregation happen in a background job. The aggregation calculates the metrics for a given day (UTC timezone) and stores the result in the DB. When querying DORA, the daily aggregations are aggregated further based on the requested interval. Issues (current): - Querying large date ranges is restricted due to possible performance issues (querying large volume of data). - The data might not be 100% accurate because the backend returns aggregates of aggregates. Challenges (CH): - How to send non-aggregated metrics (per deployment) instead of daily aggregates to ClickHouse. Benefits: - Accurate DORA metrics calculation, based on low-level data. - Fast aggregations using materialized views. ### Insights Insights feature uses the standard `Issueable` finders which allow flexible filtering on Merge Requests and Issues. The feature looks at the given time period and counts records using the provided filters. For a very large group, the insights charts could easily time out due to the data volume (scanning a large number of rows). By replicating a subset of the `issues` and `merge_requests` tables to CH, we can significantly improve the performance of these charts and avoid statement timeouts. Issues (current): - Slow, sometimes time-out for large groups Challenges (CH): - Keeping 2 high-traffic tables in sync. PG -> CH where PG is the source of truth. - Communicate the expected data delay. - The filters should respect the project visibility rules. To make this efficient, we might need to sync more tables (authorizations) with CH. Benefits: - Faster reporting without time-outs.