이 문서는 PostgreSQL 변경 데이터 캡처(CDC) 도구인 Siphon을 활용하여 ClickHouse에서 PostgreSQL 데이터로 분석 기능을 설계하는 방법을 개괄합니다.

용어집#

• Organization: 프로젝트와 그룹을 담는 상위 컨테이너입니다.
• Namespace: 그룹, 하위 그룹, 사용자 네임스페이스 또는 프로젝트 네임스페이스(프로젝트의 섀도우 레코드로 동작)를 가리킬 수 있습니다.
• Project: GitLab 프로젝트를 나타냅니다.

아키텍처 개요#

Siphon은 확장성과 안정성을 보장하기 위해 발행-구독 모델을 사용하여 PostgreSQL에서 ClickHouse로 데이터를 이동합니다.

1. Siphon Producer: PostgreSQL 논리 복제 슬롯을 모니터링하고 행 수준 변경(INSERT, UPDATE, DELETE)을 NATS에 발행합니다.
2. NATS: 메시지 브로커 역할을 하며 Producer를 Consumer로부터 분리합니다.
3. Siphon Consumer: NATS 토픽을 구독하고 ReplacingMergeTree 엔진을 사용하여 수신 데이터를 ClickHouse에 씁니다.

요구 사항#

이 가이드는 다음 GitLab 구성 요소가 실행 중이라고 가정합니다.

• wal_level=logical 구성이 적용된 PostgreSQL.
• ClickHouse
• NATS
• Siphon Producer 및 Siphon Consumer(ClickHouse용)

테이블 복제 예시#

Siphon을 사용하면 PostgreSQL 테이블 전체를 ClickHouse로 복제할 수 있습니다. 이 과정에는 최소 두 단계가 필요합니다.

1. ClickHouse 테이블 생성: Siphon이 데이터를 복제할 대상 테이블을 ClickHouse에 생성합니다.
2. 구성 변경: Siphon이 해당 테이블을 인식하도록 Siphon 구성을 업데이트합니다.

이 예시에서는 labels 테이블을 ClickHouse로 복제합니다.

ClickHouse 테이블 생성#

Rake 태스크로 ClickHouse 데이터베이스 마이그레이션과 Siphon 구성을 생성할 수 있습니다. Rake 태스크는 지정한 테이블의 PostgreSQL 스키마를 Siphon 호환 ClickHouse 테이블 정의로 변환합니다.

bundle exec rails generate gitlab:click_house:siphon labels

생성기는 다음 두 파일을 만듭니다.

• db/click_house/migrate/main/_create_siphon_labels.rb: 테이블 정의가 담긴 ClickHouse 마이그레이션입니다.
• db/siphon/tables/labels.yml: 복제 대상으로 테이블을 등록하는 Siphon 구성 메타데이터 파일입니다.

생성된 CREATE TABLE 문입니다.

      CREATE TABLE IF NOT EXISTS siphon_labels
      (
        id Int64,
        title Nullable(String),
        color Nullable(String),
        project_id Nullable(Int64),
        created_at Nullable(DateTime64(6, 'UTC')),
        updated_at Nullable(DateTime64(6, 'UTC')),
        template Nullable(Bool) DEFAULT false,
        description Nullable(String),
        description_html Nullable(String),
        type Nullable(String),
        group_id Nullable(Int64),
        cached_markdown_version Nullable(Int64),
        lock_on_merge Bool DEFAULT false,
        archived Bool DEFAULT false,
        organization_id Nullable(Int64),
        _siphon_watermark DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
        _siphon_replicated_at DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
        _siphon_deleted Bool DEFAULT FALSE
      )
      ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
      PRIMARY KEY id

생성기는 PostgreSQL 테이블의 모든 칼럼을 포함하고, 레코드 업데이트를 추적하기 위한 세 개의 추가 필드(_siphon_watermark, _siphon_replicated_at, _siphon_deleted)를 더합니다. ClickHouse 스키마에서 일부 칼럼을 제외하려면 생성된 CREATE TABLE 문을 수정하면 됩니다. Siphon은 ClickHouse에 존재하는 칼럼에 대해서만 데이터를 삽입합니다.

• _siphon_watermark: 행 변경 타임스탬프이며, 변경된 행을 증분으로 가져올 때 사용할 수 있습니다(WHERE _siphon_watermark > 'LAST_WATERMARK_TIMESTAMP').
• _siphon_replicated_at: Siphon이 설정하는 복제 타임스탬프이며, 이 칼럼의 값은 대부분 _siphon_watermark와 상관관계를 가집니다. ReplacingMergeTree 테이블 엔진이 행 버전 관리에 사용합니다.
• _siphon_deleted: ReplacingMergeTree 테이블 엔진이 사용하는 행 삭제 표시자입니다.

기본적으로 생성기는 PostgreSQL과 동일한 기본 키를 사용합니다. 다음 두 규칙을 지키는 한, 쿼리 요구에 맞게 이를 수정할 수 있습니다.

• PostgreSQL 테이블의 모든 기본 키 칼럼이 ClickHouse 기본 키에 포함되어야 합니다.
• 값이 절대 변경되지 않는 칼럼이라면 기본 키 정의에 칼럼을 추가할 수 있습니다.

그러한 칼럼의 예시입니다.

• 샤딩 키.
• 애플리케이션에서 수정할 수 없는 칼럼. 예를 들어 notes 레코드의 target_id 및 target_type 값 업데이트가 그렇습니다.

Siphon 구성#

생성기는 Siphon 메타데이터 파일을 db/siphon/tables/<table>.yml에 씁니다. GDK는 gdk reconfigure 중에 이 파일을 자동으로 감지합니다. 복제 대상을 조정하려면 파일을 편집합니다.

생성된 db/siphon/tables/labels.yml은 다음과 같습니다.

table: labels
database: main
replication_targets:
  - name: clickhouse_main
    target: siphon_labels

구성 필드는 다음과 같습니다.

• table: PostgreSQL의 소스 테이블 이름입니다.
• database: 데이터베이스 식별자(main, ci, sec)입니다.
• ignored_columns (선택 사항): 복제에서 제외할 칼럼입니다.
• replication_targets: 대상 세부 정보를 지정합니다.
  • name: ClickHouse 복제에는 clickhouse_main이어야 합니다.
  • target: ClickHouse의 대상 테이블 이름입니다.
  • priority (선택 사항): 값이 작을수록 초기 스냅샷에서 먼저 복제됩니다(GDK에서만 사용).
  • dedup_by (선택 사항): Consumer가 UPDATE 및 DELETE 이벤트를 적용할 때 기존 행을 찾는 데 사용하는 칼럼입니다. PostgreSQL 기본 키 칼럼과 일치해야 합니다. ClickHouse 기본 키가 PostgreSQL 기본 키를 넘어서는 칼럼을 포함할 때(예: traversal_path) 필요합니다.
  • dedup_by_table (선택 사항): target이 행을 저장하는 테이블이 아닐 때, 일치하는 기본 키를 확인할 다른 ClickHouse 테이블입니다. 예를 들어 target이 Null 랜딩 테이블이고 실제 저장 테이블의 이름이 다른 경우가 그렇습니다. 구성한 테이블의 기본 키 끝에 PostgreSQL 기본 키 칼럼이 포함되어야 합니다.
  • dedup_by_columns_lookup_table (선택 사항): PostgreSQL 기본 키 칼럼으로 전체 기본 키를 조회하는 데 사용하는 보조 ClickHouse 테이블입니다. 조회 테이블의 기본 키는 PostgreSQL 기본 키 칼럼으로 시작해야 합니다. 주 target 테이블의 기본 키가 PostgreSQL 기본 키에 속하지 않는 칼럼으로 시작할 때 사용합니다. 예를 들어 PostgreSQL 기본 키는 (merge_request_diff_id, relative_order)인데 정렬 기준이 (merge_request_diff_id, relative_order, traversal_path)인 경우입니다.
  • downstream_materialized_views (선택 사항): target 테이블에 연결된 구체화된 뷰(materialized view)의 대상 테이블입니다. 여기에 나열하면 gitlab:siphon:clean_clickhouse Rake 태스크가 비우는 테이블에 포함됩니다. 자세한 내용은 구체화된 뷰와 재스냅샷을 참조합니다.
  • reconcile (선택 사항): 비정규화 칼럼을 복구하는 주기적 일관성 검사 job의 구성입니다.
    • column: 조정할 비정규화 칼럼입니다(예: traversal_path).
    • expression_key_columns: 비정규화 값을 계산하는 데 사용하는 소스 칼럼입니다. db/docs/<table>.yml에 정의된 샤딩 키 칼럼과 일치해야 합니다.

다음 예시는 merge_requests를 복제하고 target_project_id에서 traversal_path 칼럼을 조정합니다.

table: merge_requests
database: main
replication_targets:
  - name: clickhouse_main
    target: siphon_merge_requests
    downstream_materialized_views:
      - merge_requests_base
    dedup_by_table: merge_requests
    dedup_by:
      - id
    reconcile:
      column: traversal_path
      expression_key_columns:
        - target_project_id

다음 예시는 별도의 조회 테이블을 사용하여 PostgreSQL 기본 키 칼럼에서 전체 ClickHouse 기본 키를 확인합니다.

table: merge_request_diff_files_99208b8fac
database: main
replication_targets:
  - name: clickhouse_main
    target: siphon_merge_request_diff_files
    dedup_by_columns_lookup_table: siphon_merge_request_diff_files_pg_pkey_ordered
    dedup_by:
      - merge_request_diff_id
      - relative_order

파일을 만든 후에는 구성을 적용하고 Siphon을 다시 시작합니다.

gdk reconfigure
gdk restart siphon

업데이트된 구성은 Siphon이 다음 두 가지를 수행하도록 트리거합니다.

1. 초기 스냅샷: siphon_labels 테이블을 채우기 위해 현재 데이터의 스냅샷을 만듭니다.
2. 연속 복제: 이후의 모든 레코드 변경을 논리 복제로 실시간 캡처하여 siphon_labels 테이블에 적용합니다.

rails 콘솔에서 테이블을 확인할 수 있습니다.

ClickHouse::Client.select('SELECT * FROM siphon_labels', :main)

또는 ClickHouse 클라이언트를 시작해 다음 쿼리를 실행할 수 있습니다.

SELECT * FROM siphon_labels;

구체화된 뷰와 재스냅샷#

재스냅샷은 CDC 파이프라인이 끊겨 Siphon이 PostgreSQL의 일관된 복제 지점을 잃을 때 발생합니다. 이 경우 ClickHouse의 데이터를 더 이상 신뢰할 수 없으므로 gitlab:siphon:clean_clickhouse Rake 태스크가 데이터를 비우고 Siphon이 테이블을 처음부터 다시 재생합니다.

구체화된 뷰의 대상 테이블은 뷰의 소스 테이블이 비워져도 행을 유지합니다. downstream_materialized_views가 없으면 재스냅샷 후 해당 테이블에는 오래된 행과 재생된 행이 섞여 남습니다. 뷰 이름이 아니라 대상 테이블을 나열해야 Rake 태스크가 둘을 함께 비웁니다. 예를 들어 db/siphon/tables/events.yml은 contributions_new_mv 뷰의 대상인 contributions_new를 나열합니다.

Siphon이 복제하는 테이블이 뷰에 데이터를 공급하는 경우에는 항상 구성합니다. 해당 테이블이 유일한 소스일 때도, 뷰가 다른 테이블과도 조인할 때도 마찬가지입니다. 추가 조인이 있어도 대상 테이블은 예외가 되지 않습니다.

재생으로 다시 만들 수 있는 테이블만 나열합니다. 뷰가 Siphon이 복제하지 않는 테이블과 조인하면 그 테이블이 해당 데이터의 유일한 사본을 보관하므로, 비우면 영구적으로 손실됩니다.

Rake 태스크는 비우기만 하며 뷰를 다시 실행하지는 않습니다. 증분 뷰는 재생이 소스 테이블에 행을 다시 삽입하면서 다시 채워집니다. 새로 고침형(refreshable) 뷰는 다음 예약된 새로 고침에서만 다시 채워지므로 그때까지 대상 테이블은 비어 있습니다.

계층 구조 비정규화 예시#

GitLab의 가장 큰 과제 중 하나는 PostgreSQL에서 Namespace/Group 계층 구조의 데이터를 쿼리하는 일입니다. 이를 효율적으로 처리할 수 없는 경우가 많고, 더 넓은 데이터 범위를 쿼리하면 데이터베이스 쿼리가 타임아웃될 수 있습니다.

ClickHouse의 계층 기반 조회에 관해서는 광범위한 연구가 진행되었으며, Siphon과 ClickHouse로 이를 가능하게 하는 여러 접근 방식이 있습니다.

PostgreSQL에는 traversal_ids라는 특수한 비정규화 배열 칼럼을 도입했습니다. 이 칼럼은 모든 상위 네임스페이스 ID를 담고 있어 계층 구조의 모든 엔티티를 더 효율적으로 조회할 수 있게 합니다. ClickHouse의 계층 최적화는 네임스페이스나 프로젝트를 직접 참조하는 어떤 테이블에서도 이 비정규화 칼럼을 포함할 수 있다는 뜻입니다.

traversal_path 구조#

ClickHouse에서 traversal_ids는 슬래시(/)로 구분된 문자열인 traversal_path로 변환됩니다.

1/9970/../92345/

• 첫 번째 정수: organization_id입니다(조직 수준 조회를 가능하게 합니다).
• 두 번째 정수: 최상위 네임스페이스 ID입니다(이 예시에서는 gitlab-org).
• 마지막 정수: Group(또는 하위 그룹)이나 ProjectNamespace(Project 레코드를 참조)를 가리키는 포인터입니다.

traversal_path 결정 방법#

빠른 traversal_path 조회를 위해 세 개의 ClickHouse 딕셔너리(해시맵 캐시)가 데이터베이스에 추가되었습니다.

namespaces.id 값으로 traversal_path를 조회합니다.

SELECT dictGet('namespace_traversal_paths_dict', 'traversal_path', 9970);

projects.id 값으로 traversal_path를 조회합니다.

SELECT dictGet('project_traversal_paths_dict', 'traversal_path', 278964); -- GitLab project id

organizations.id 값으로 traversal_path를 조회합니다.

SELECT dictGet('organization_traversal_paths_dict', 'traversal_path', 1); -- "main" organization id

이 딕셔너리는 traversal_path 값 조회에 대부분 O(1) 접근을 제공합니다. 반환되는 데이터는 캐시되므로 항상 일관되지는 않을 수 있다는 점에 유의합니다. 이 문서 뒤쪽의 일관성 보장 섹션을 참고합니다.

계층 조회 최적화 테이블 생성#

다음 Rake 태스크로 계층 조회에 최적화된 Siphon 테이블을 만들 수 있습니다.

bundle exec rails generate gitlab:click_house:siphon labels --with-traversal-path

그 결과 생성된 테이블에는 딕셔너리 조회를 기본값으로 갖는 추가 칼럼(traversal_path) 하나가 포함됩니다. 스크립트는 $table_name.yml 테이블 설명 파일을 사용해 구성된 샤딩 키를 자동으로 감지하고 올바른 딕셔너리 조회를 설정합니다.

생성된 CREATE TABLE 문입니다.

      CREATE TABLE IF NOT EXISTS siphon_labels
      (
        id Int64,
        title Nullable(String),
        color Nullable(String),
        project_id Nullable(Int64),
        created_at Nullable(DateTime64(6, 'UTC')),
        updated_at Nullable(DateTime64(6, 'UTC')),
        template Nullable(Bool) DEFAULT false,
        description Nullable(String),
        description_html Nullable(String),
        type Nullable(String),
        group_id Nullable(Int64),
        cached_markdown_version Nullable(Int64),
        lock_on_merge Bool DEFAULT false,
        archived Bool DEFAULT false,
        organization_id Nullable(Int64),
        traversal_path String DEFAULT multiIf(
          coalesce(group_id, 0) != 0, dictGetOrDefault('namespace_traversal_paths_dict', 'traversal_path', group_id, '0/'),
          coalesce(project_id, 0) != 0, dictGetOrDefault('project_traversal_paths_dict', 'traversal_path', project_id, '0/'),
          coalesce(organization_id, 0) != 0, dictGetOrDefault('organization_traversal_paths_dict', 'traversal_path', organization_id, '0/'),
          '0/'
        ),
        _siphon_watermark DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
        _siphon_replicated_at DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
        _siphon_deleted Bool DEFAULT FALSE
      )
      ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
      PRIMARY KEY (traversal_path, id)

이 테이블의 기본 키는 (traversal_path, id)입니다. 생성기는 보조 테이블 siphon_labels_pg_pkey_ordered와 구체화된 뷰 siphon_labels_pg_pkey_ordered_mv도 만듭니다. 구체화된 뷰는 주 테이블의 기본 키 칼럼을 보조 테이블로 동기화하며, 보조 테이블은 PostgreSQL 기본 키 칼럼을 앞에 두고 정렬됩니다. Siphon은 DELETE 및 UPDATE 이벤트를 복제할 때 이 보조 테이블로 PostgreSQL 기본 키에 해당하는 기존 행을 찾습니다.

테이블 쿼리#

계층 최적화 테이블을 쿼리할 때는 traversal_path 필터가 쿼리에 포함되고 쿼리 시점에 중복이 걸러지도록 하는 것이 중요합니다.

traversal_path 정확히 일치 쿼리: Group 내 항목 개수 세기

SELECT COUNT(*)
FROM (
  SELECT
    argMax(title, _siphon_replicated_at) AS title,
    argMax(_siphon_deleted, _siphon_replicated_at) AS _siphon_deleted,
    id,
    traversal_path
  FROM siphon_labels
  WHERE
    traversal_path = '1/9970/'
  GROUP BY traversal_path, id
)
WHERE _siphon_deleted = false

group_id만 알고 있다면 딕셔너리 조회를 필터로 사용할 수 있습니다.

SELECT COUNT(*)
FROM (
  SELECT
    argMax(title, _siphon_replicated_at) AS title,
    argMax(_siphon_deleted, _siphon_replicated_at) AS _siphon_deleted,
    id,
    traversal_path
  FROM siphon_labels
  WHERE
    traversal_path = dictGetOrDefault('namespace_traversal_paths_dict', 'traversal_path', 9970, '0/')
  GROUP BY traversal_path, id
)
WHERE _siphon_deleted = false

자신과 하위 항목 쿼리: Group 계층 구조 내 항목 개수 세기

SELECT COUNT(*)
FROM (
  SELECT
    argMax(title, _siphon_replicated_at) AS title,
    argMax(_siphon_deleted, _siphon_replicated_at) AS _siphon_deleted,
    id,
    traversal_path
  FROM siphon_labels
  WHERE
    startsWith(traversal_path, '1/9970/')
  GROUP BY traversal_path, id
)
WHERE _siphon_deleted = false

쿼리 시간 중복 제거#

Siphon 테이블은 ReplacingMergeTree 테이블 엔진을 사용합니다. PostgreSQL과 달리 ClickHouse는 성능상의 이유로 삽입 시점에 고유성 제약을 강제하지 않습니다. 대신 엔진이 백그라운드에서 동일한 기본 키를 가진 행을 병합합니다.

이 병합은 비동기이므로 쿼리가 같은 행의 여러 버전을 반환할 수 있습니다. 이를 처리하려면 항상 쿼리 시점 중복 제거를 적용합니다.

• 기본 키를 기준으로 Group By를 수행합니다.
• argMax(column, _siphon_replicated_at)를 사용해 최신 값을 선택합니다.
• _siphon_deleted가 true인 행을 걸러냅니다.

Siphon 테이블에 대한 쿼리를 작성할 때는 결과를 어떻게 중복 제거할지 항상 고려합니다.

SELECT title, id FROM labels ORDER BY title LIMIT 5

위 쿼리는 중복 제거가 되어 있지 않아 중복 행을 반환할 수 있습니다. 이 쿼리를 중복 제거하는 한 가지 방법은 다음과 같습니다.

SELECT title, id
FROM (
  SELECT
    argMax(title, _siphon_replicated_at) AS title, -- take the latest `title` value
    argMax(_siphon_deleted, _siphon_replicated_at) AS _siphon_deleted, -- take the latest deletion status
    id,
    traversal_path
  FROM siphon_labels
  GROUP BY traversal_path, id -- GROUP BY primary key
)
WHERE
  _siphon_deleted = false -- Filter out deleted rows
ORDER BY title
LIMIT 5

버전 칼럼과 삭제 칼럼이 무엇인지 확실하지 않다면 언제든 테이블 정의를 확인할 수 있습니다.

SHOW CREATE TABLE siphon_labels;

ENGINE 절을 찾아 인수를 확인합니다.

ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)

NULL 엔진을 통한 준 실시간 비정규화#

GitLab PostgreSQL 스키마는 정규화된 트랜잭션 워크로드에 최적화되어 있습니다. 그러나 ClickHouse 성능은 비정규화된(넓은) 테이블에서 가장 잘 확장됩니다. ClickHouse는 JOINs를 지원하지만 계산 비용이 큽니다. 경험적으로, JOIN이 쿼리 I/O를 세 배 이상 늘린다면 수집 시점에 비정규화해야 합니다.

이 전략은 관계가 1:1 또는 1:N(N이 작은 경우, 예를 들어 500행 미만)인 보강에 가장 적합합니다. Merge Request 레코드에 Assignee 및 Reviewer 데이터를 직접 추가하는 경우가 그렇습니다.

고수준 개요#

원시 데이터를 두 번 저장하는 비용을 피하기 위해 "통과(pass-through)" 파이프라인을 활용합니다.

1. 수집 지점(Siphon 테이블): NULL 엔진으로 "랜딩" 테이블을 정의합니다. 이 엔진은 Siphon에서 데이터를 받아 다운스트림 로직을 트리거하고, 원시 바이트는 즉시 폐기해 디스크 공간을 절약하는 버퍼 없는 파이프 역할을 합니다.
2. 스토리지 테이블(대상): 최종 ReplacingMergeTree 테이블을 정의합니다. 이 스키마에는 소스 칼럼과 비정규화 데이터를 위한 추가 칼럼이 모두 포함됩니다.
3. 로직 엔진(구체화된 뷰): NULL 테이블을 "감시"하는 구체화된 뷰(MV)를 정의합니다. MV는 삽입이 발생할 때마다 기존 참조 테이블을 대상으로 JOIN 로직을 수행해 보강 데이터를 가져옵니다.
4. 랜딩: MV는 보강된 "넓은" 행을 스토리지 테이블로 푸시합니다.

구현 예시: 비정규화된 머지 리퀘스트 리뷰어#

아키텍처 개요:

graph TD
    subgraph Ingestion_Source [Data Source]
        CDC[Siphon / CDC Data]
    end
subgraph ClickHouse_Engine [Pipeline]
    NullTable[<b>siphon_merge_requests</b><br/>ENGINE = Null]

    MV{<b>merge_requests_mv</b><br/>Materialized View}

    RefTable[(<b>siphon_merge_request_reviewers</b><br/>ENGINE = ReplacingMergeTree)]
end

subgraph Storage [Destination]
    FinalTable[(<b>merge_requests</b><br/>ENGINE = ReplacingMergeTree)]
end

%% Data Flow
CDC -->|INSERT| NullTable
NullTable -->|Triggers| MV
RefTable -.->|Lookup/JOIN/Format| MV
MV -->|Enriched Data| FinalTable

%% Styling
style NullTable fill:#f9f,stroke:#333,stroke-width:2px
style FinalTable fill:#00ff00,color:#000,stroke:#333,stroke-width:2px
style MV fill:#fff4dd,stroke:#d4a017,stroke-width:2px</code></pre></details></div>

Note
단순화를 위해 merge_requests 테이블에서 불필요한 칼럼은 생략했습니다.

1. NULL siphon_merge_requests 테이블 생성#
먼저 merge_requests용 Siphon 테이블을 생성합니다.
bundle exec rails generate gitlab:click_house:siphon merge_requests --with-traversal-path

필수적이지 않은 칼럼을 제거한 결과 스키마는 다음과 같습니다.
CREATE TABLE IF NOT EXISTS siphon_merge_requests
(
  id Int64 CODEC(DoubleDelta, ZSTD),
  target_branch String,
  source_branch String,
  source_project_id Nullable(Int64),
  author_id Nullable(Int64),
  title Nullable(String),
  created_at DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
  updated_at DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
  target_project_id Int64,
  iid Nullable(Int64),
  description Nullable(String),
  traversal_path String DEFAULT multiIf(coalesce(target_project_id, 0) != 0, dictGetOrDefault('project_traversal_paths_dict', 'traversal_path', target_project_id, '0/'), '0/') CODEC(ZSTD(3)),
  _siphon_watermark DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
  _siphon_replicated_at DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC') CODEC(ZSTD(1)),
  _siphon_deleted Bool DEFAULT FALSE CODEC(ZSTD(1))
)
ENGINE = Null;

주요 참고 사항:

ENGINE이 명시적으로 Null로 설정됩니다.
칼럼 압축은 정의된 상태로 남지만, 이 엔진은 데이터를 디스크에 쓰지 않으므로 성능에 영향이 없습니다.

2. siphon_merge_request_reviewers 테이블 생성#
리뷰어 테이블을 생성합니다.
bundle exec rails generate gitlab:click_house:siphon merge_request_reviewers --with-traversal-path

생성된 스키마:
CREATE TABLE IF NOT EXISTS siphon_merge_request_reviewers
(
  id Int64 CODEC(DoubleDelta, ZSTD),
  user_id Int64,
  merge_request_id Int64,
  created_at DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
  state Int8 DEFAULT 0,
  project_id Int64,
  traversal_path String DEFAULT multiIf(coalesce(project_id, 0) != 0, dictGetOrDefault('project_traversal_paths_dict', 'traversal_path', project_id, '0/'), '0/') CODEC(ZSTD(3)),
  _siphon_watermark DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
  _siphon_replicated_at DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC') CODEC(ZSTD(1)),
  _siphon_deleted Bool DEFAULT FALSE CODEC(ZSTD(1))
)
ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
PRIMARY KEY (traversal_path, merge_request_id, id)
SETTINGS index_granularity = 1024;

변경한 내용:

MR별 리뷰어 조회를 최적화하기 위해 merge_request_id를 기본 키에 포함했습니다.
MV 트리거 중 JOIN 성능을 높이기 위해 index_granularity를 1024로 낮췄습니다.

3. merge_requests 대상 테이블 생성#
이 테이블에는 siphon_merge_requests의 모든 칼럼과 추가 비정규화 칼럼(이 경우 리뷰어용)이 포함됩니다. 리뷰어는 Tuple 배열로 모델링되며, 각 튜플에는 user_id와 리뷰 state가 담깁니다.
CREATE TABLE IF NOT EXISTS merge_requests
(
  id Int64 CODEC(DoubleDelta, ZSTD),
  target_branch String,
  source_branch String,
  source_project_id Nullable(Int64),
  author_id Nullable(Int64),
  title Nullable(String),
  created_at DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
  updated_at DateTime64(6, 'UTC') CODEC(Delta, ZSTD(1)),
  target_project_id Int64,
  iid Nullable(Int64),
  description Nullable(String),
  traversal_path String DEFAULT multiIf(coalesce(target_project_id, 0) != 0, dictGetOrDefault('project_traversal_paths_dict', 'traversal_path', target_project_id, '0/'), '0/') CODEC(ZSTD(3)),
  reviewers Array(Tuple(UInt64, Int8)),
  _siphon_watermark DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC'),
  _siphon_replicated_at DateTime64(6, 'UTC') DEFAULT now64(6, 'UTC') CODEC(ZSTD(1)),
  _siphon_deleted Bool DEFAULT FALSE CODEC(ZSTD(1))
)
ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
PRIMARY KEY (traversal_path, id)

4. JOIN 구체화된 뷰 생성#
구체화된 뷰는 siphon_merge_requests에 대한 모든 삽입이 현재 리뷰어 데이터로 보강되도록 보장합니다.
CREATE MATERIALIZED VIEW merge_requests_mv TO merge_requests
AS WITH
    cte AS
    (
        -- Store the current INSERT block for siphon_merge_requests table
        SELECT *
        FROM siphon_merge_requests
    ),
    collected_reviewers AS
    (
        SELECT
            traversal_path,
            merge_request_id,
            groupArray((user_id, state)) AS reviewers -- Build the Array(Tuple(UInt64, Int8)) value
        FROM
        (
            -- Load the deduplicated reviewer records for the given merge request values
            SELECT
                traversal_path,
                merge_request_id,
                id,
                argMax(user_id, _siphon_replicated_at) AS user_id,
                argMax(state, _siphon_replicated_at) AS state,
                argMax(_siphon_deleted, _siphon_replicated_at) AS _siphon_deleted
            FROM siphon_merge_request_reviewers
            WHERE (traversal_path, merge_request_id) IN (
                SELECT traversal_path, id
                FROM cte
            )
            GROUP BY traversal_path, merge_request_id, id
            HAVING _siphon_deleted = false
        )
        GROUP BY traversal_path, merge_request_id
    )
SELECT
    cte.id AS id,
    cte.target_branch AS target_branch,
    cte.source_branch AS source_branch,
    cte.source_project_id AS source_project_id,
    cte.author_id AS author_id,
    cte.title AS title,
    cte.created_at AS created_at,
    cte.updated_at AS updated_at,
    cte.target_project_id AS target_project_id,
    cte.iid AS iid,
    cte.description AS description,
    cte.traversal_path AS traversal_path,
    collected_reviewers.reviewers AS reviewers,
    cte._siphon_replicated_at AS _siphon_replicated_at,
    cte._siphon_deleted AS _siphon_deleted
FROM cte
LEFT JOIN collected_reviewers ON collected_reviewers.merge_request_id = cte.id AND collected_reviewers.traversal_path = cte.traversal_path

Siphon 구성과 "닭-달걀" 문제#
구체화된 뷰는 소스 테이블(siphon_merge_requests)에 삽입이 발생할 때 트리거됩니다. 그러나 siphon_merge_request_reviewers 테이블에서 업데이트가 발생하면 비정규화된 merge_requests 테이블은 자동으로 업데이트되지 않습니다.
Siphon은 테이블을 독립적으로 복제하므로 PostgreSQL의 트랜잭션 의존성이 기본적으로 보존되지 않습니다. 이를 해결하기 위해 Siphon은 "refresh" 콜백 기능을 제공합니다. 다운스트림 레코드(리뷰어)가 변경되면 Siphon은 상위 레코드(머지 리퀘스트)의 비동기 "refresh"를 트리거할 수 있습니다. 이는 짧은 지연 후 상위 행을 Null 테이블에 다시 삽입해 MV를 다시 트리거하고, MV가 최신 리뷰어 상태를 반영하도록 합니다.
이는 개념적으로 Ruby on Rails의 .touch 메서드와 유사하며, 레코드 모음에 대해 비동기로 실행됩니다.
Siphon ClickHouse consumer 구성:
streams:
  # Note: to properly work with hierarchy de-normalization, the following tables also need to be replicated: namespaces, projects, organizations
  - identifier: merge_requests
    subject: merge_requests
    target: siphon_merge_requests
    # required for handling refresh callbacks from downstream tables
    enable_refresh_package: true
  - identifier: merge_request_reviewers
    subject: merge_request_reviewers
    target: siphon_merge_request_reviewers
    dedup_by:
      - id

clickhouse:
  # connectivity configuration is disabled
  refresh_on_change:
    - source_stream_identifier: merge_request_reviewers
      source_keys:
        - merge_request_id
      target_stream_identifier: merge_requests
      target_keys:
        - id
  # dedup_ configuration is needed for properly applying DELETE and UPDATE events
  dedup_config:
    - stream_identifier: merge_requests
      dedup_by_table: merge_requests
      dedup_by:
        - id
    - stream_identifier: merge_request_reviewers
      dedup_by:
        - id

테이블 쿼리#
구성이 완료되고 데이터가 ClickHouse 테이블로 복제되면 merge_requests 테이블에 쿼리를 실행할 수 있습니다. 다음 쿼리는 특정 프로젝트(id = 12)에서 특정 사용자(id = 73)가 "requested changes" 상태(state = 3)로 검토 중인 머지 리퀘스트 개수를 셉니다.
SELECT COUNT(*)
FROM (
  SELECT
    id,
    argMax(reviewers, _siphon_replicated_at) AS reviewers,
    argMax(_siphon_deleted, _siphon_replicated_at) AS _siphon_deleted
  FROM merge_requests
  WHERE
  -- Filter for the project
  startsWith(traversal_path, dictGetOrDefault('project_traversal_paths_dict', 'traversal_path', 12, '0/'))
  GROUP BY traversal_path, id
  HAVING _siphon_deleted = false
)
WHERE
-- x.1: user id filter
-- x.2: review state filter
arrayExists(x -> x.1 = 73 AND x.2 = 3, reviewers);


Note
배열 데이터 유형은 파싱과 필터링 중에 추가 오버헤드를 유발할 수 있습니다. 비정규화 데이터가 연관된 상태 없이 단순한 ID 목록인 경우, 구분자로 나눈 문자열 필드(예: '/user_id1/user_id2/')를 hasSubstr와 함께 사용하면 더 높은 성능을 얻을 수 있습니다.

일관성 보장#
모든 비정규화에는 트레이드오프가 있습니다. Siphon 기반 계층 구조 비정규화는 정상 운영 시(namespaces와 projects 테이블이 초기 데이터 스냅샷을 완료했다고 가정) 다음과 같은 일관성을 보장합니다.

계층 조회의 기준이 되는 칼럼이 변경된 경우에도(예: namespace_id가 업데이트된 경우) 계층 비정규화 테이블의 레코드는 올바르게 삽입, 업데이트, 삭제됩니다.

참고: 계층 조회 칼럼(namespace_id)이 샤딩 키라면, 설계상 이 칼럼은 PostgreSQL에서 절대 변경되지 않습니다.


traversal_path의 네임스페이스 레코드 참조 중 하나가 변경되는 경우(예: 하위 그룹이나 프로젝트가 이동 또는 삭제된 경우)

주기적 일관성 검사 job으로 최종적 일관성을 확보합니다. 대규모 테이블에서는 5분 내에 이러한 일관성 문제를 해결하는 것을 목표로 합니다(참고: 이는 이 이슈에서 구현될 예정입니다).



개발 환경에서는 레코드 생성이 프로젝트나 그룹 생성과 매우 가까운 시점에 일어날 수 있어 일관성 문제가 더 자주 나타날 수 있습니다. 이런 경우 최종적 일관성 보장 장치가 몇 초 또는 몇 분(구성 가능) 안에 문제를 해결합니다.
행 업데이트#
Siphon 테이블에서는 ALTER TABLE ... UPDATE(뮤테이션)나 ClickHouse 행을 제자리에서 수정하는 다른 문을 사용하지 않습니다.
제자리 변경은 Siphon 데이터 동기화의 데이터 정확성에 영향을 줄 수 있습니다.
행을 변경하려면 더 큰 _siphon_replicated_at 값(1마이크로초를 더한 값)으로 행을 다시 삽입합니다.
ReplacingMergeTree는 _siphon_replicated_at이 가장 큰 행을 유지하므로 다시 삽입한 행이 우선합니다.
1마이크로초는 Siphon이 복제하는 이후 변경이 여전히 우선할 만큼 작은 값이므로 동시 수정 문제를 방지합니다.
Null 엔진 패턴을 사용하는 테이블에서는 스토리지 테이블에서 행을 읽어 Null 테이블에 삽입합니다.
그러면 구체화된 뷰가 다시 실행되어 비정규화 칼럼을 다시 계산합니다.
칼럼 목록에는 Null 테이블의 모든 칼럼이 포함되어야 하며, 이는 system.columns에서 읽을 수 있습니다.
이 쿼리에는 중복 제거나 _siphon_deleted 필터가 필요하지 않습니다.
모든 버전이 같은 1마이크로초만큼 앞으로 이동하므로 최신 버전이 여전히 우선하고 삭제된 행은 삭제된 상태로 유지됩니다.
-- Abbreviated: list every column of siphon_merge_requests in both clauses
INSERT INTO siphon_merge_requests (id, title, traversal_path, ..., _siphon_replicated_at, _siphon_deleted)
SELECT id, title, traversal_path, ..., addMicroseconds(_siphon_replicated_at, 1) AS _siphon_replicated_at, _siphon_deleted
FROM merge_requests
WHERE id IN (1, 2, 3)

또는 PostgreSQL에서 영향을 받는 행을 업데이트할 수 있습니다. 예를 들어
배포 후 마이그레이션에서 레코드를 건드려(updated_at을 갱신) 변경할 수 있습니다.
Siphon은 이 변경을 다른 업데이트와 똑같이 복제합니다.
테이블 파티셔닝#
ClickHouse 파티셔닝은 쿼리 최적화를 위한 주된 수단이 아니라, 오래된 데이터 삭제와 같은 데이터 수명 주기 작업을 위한 데이터 관리 도구입니다.
파티션 세분성#
지나치게 세분화된 파티셔닝 방식은 피합니다. 경험적으로, 연 단위 파티셔닝(예: toYear(created_at))보다 더 세분화하지 않아야 합니다.
파티션이 너무 많으면 ClickHouse 성능이 심각하게 저하될 수 있습니다.

수집 속도: 각 INSERT는 해당 INSERT 블록이 건드리는 모든 파티션에 대해 별도의 데이터 "파트"를 만듭니다. 파티션 수가 많으면 "파트 폭발"로 이어져 메타데이터 오버헤드와 병합 부담이 커집니다.
쿼리 성능: 여러 파티션에 걸친 쿼리는 다수의 작은 파일에 대한 메타데이터를 열고 처리해야 하므로 실행 속도가 크게 느려질 수 있습니다.

제약 사항과 Siphon 스냅샷#
단일 insert 문에서 건드릴 수 있는 파티션 수에는 하드 한계가 있습니다. max_partitions_per_insert_block=100입니다. 이 한계는 ClickHouse Cloud에서 강제되며 늘릴 수 없습니다.
Siphon이 PostgreSQL 테이블의 초기 스냅샷을 수행할 때 데이터는 ClickHouse 파티션 키와 일치하지 않는 순서로 읽히는 경우가 많습니다. 스냅샷에 100개가 넘는 서로 다른 월의 데이터가 포함되면 삽입이 실패합니다.
다음 조건이 충족되는 경우에만 월 단위와 같이 더 세분화된 파티셔닝을 고려합니다.

PostgreSQL 소스 테이블이 동일한 파티셔닝 논리를 따릅니다. Siphon은 각 PostgreSQL 파티션을 개별적으로 스냅샷하므로 데이터 블록이 자연스럽게 ClickHouse 한계 안에 유지됩니다.
데이터 양이 매우 많아 파티션이 늘어나는 관리 부담을 정당화할 수 있습니다.

Cells 환경에서의 작업#
Siphon으로 복제되는 데이터베이스 테이블은 설계상 Cells 아키텍처를 지원합니다. 모든 cell에 Siphon이 구성되어 있다고 가정하면, 조직이나 그룹이 다른 cell로 이동할 때 ClickHouse를 위한 추가 데이터 마이그레이션은 필요하지 않습니다.
진실 공급원은 항상 PostgreSQL이며, 이는 재배치 과정(조직 이동)에서 발생하는 모든 레코드 변경이 복제된다는 뜻입니다.

Cell A에서 Cell B로 데이터 삽입.
Cell A에서 오래되거나 더 이상 유효하지 않은 데이터 제거.

이러한 변경은 논리 복제 스트림을 통해 Siphon Producer가 감지하며, 최종적으로 Siphon Consumer가 ClickHouse에 적용합니다.