이 문서는 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_replicated_at DateTime64(6, 'UTC') DEFAULT now(),
        _siphon_deleted Bool DEFAULT FALSE
      )
      ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
      PRIMARY KEY id

생성기는 PostgreSQL 테이블의 모든 열을 포함하고 레코드 업데이트를 추적하기 위한 두 개의 추가 필드(_siphon_replicated_at 및 _siphon_deleted)를 추가합니다. ClickHouse 스키마에서 일부 열을 생략하려면 생성된 CREATE TABLE 문을 수정하면 됩니다. Siphon은 ClickHouse에서 사용 가능한 열에만 데이터를 삽입합니다.

기본적으로 생성기는 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 (선택 사항): UPDATE 및 DELETE 이벤트를 적용할 때 컨슈머가 기존 행을 찾기 위해 사용하는 컬럼. PostgreSQL 기본 키 컬럼과 일치해야 합니다. ClickHouse 기본 키에 PostgreSQL 기본 키 이외의 컬럼이 포함된 경우(예: traversal_path) 필요합니다.

• dedup_by_table (선택 사항): target이 행을 저장하는 테이블이 아닌 경우(예: target이 Null 랜딩 테이블이고 실제 스토리지 테이블의 이름이 다른 경우) 일치하는 기본 키를 검사하기 위한 다른 ClickHouse 테이블. 구성된 테이블의 기본 키는 끝에 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) 순으로 정렬하는 경우).

• reconcile (선택 사항): 비정규화 컬럼을 복구하는 주기적인 일관성 검사 작업에 대한 구성.

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
    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이 두 가지 작업을 수행하도록 합니다:

• 초기 스냅샷: siphon_labels 테이블을 채우기 위해 현재 데이터의 스냅샷을 만듭니다.

• 지속적 복제: 논리 복제를 통해 이후의 모든 레코드 변경사항을 실시간으로 캡처하고 siphon_labels 테이블에 적용합니다.

rails 콘솔에서 테이블을 검사할 수 있습니다:

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

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

SELECT * FROM siphon_labels;

계층 구조 비정규화 예시#

GitLab에서 가장 큰 도전 중 하나는 PostgreSQL의 네임스페이스/그룹 계층 구조에서 데이터를 조회하는 것입니다. 종종 이는 효율적으로 가능하지 않으며, 더 큰 데이터 범위를 조회할 때 데이터베이스 쿼리가 시간 초과될 수 있습니다.

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_replicated_at DateTime64(6, 'UTC') DEFAULT now(),
        _siphon_deleted Bool DEFAULT FALSE,
        PROJECTION pg_pkey_ordered (
          SELECT *
          ORDER BY id
        )
      )
      ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
      PRIMARY KEY (traversal_path, id)
      SETTINGS deduplicate_merge_projection_mode = 'rebuild'

테이블의 기본 키는 (traversal_path, id)입니다. 또한 생성기는 원래 PostgreSQL 기본 키를 통해 레코드를 빠르게 찾기 위한 추가 프로젝션(pg_pkey_ordered)을 추가합니다. 이는 Siphon이 DELETE 문을 복제할 때 기존 레코드를 빠르게 찾는 데 필요합니다.

deduplicate_merge_projection_mode 설정#

프로젝션을 사용하는 테이블의 경우 deduplicate_merge_projection_mode를 rebuild로 명시적으로 설정합니다. 생성기 스크립트에 의해 자동으로 주입되는 이 구성은 부모 테이블과 프로젝션 간의 엄격한 일관성 유지에 중요합니다. 병합 중, 특히 행이 중복 제거될 때 이 모드는 ClickHouse가 기존 프로젝션 데이터를 버리고 새로 중복 제거된 행을 사용하여 처음부터 다시 계산하도록 지시합니다. 이 설정이 없으면 프로젝션이 오래되거나 원본 데이터와 수학적으로 일치하지 않는 위험이 있습니다.

테이블 쿼리#

계층 최적화 테이블을 쿼리할 때 traversal_path 필터가 쿼리의 일부인지 확인하고 쿼리 시간에 중복이 필터링되도록 하는 것이 중요합니다.

정확한 traversal_path 매칭 쿼리: 그룹의 항목 수 세기

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

자신과 하위 항목 쿼리: 그룹 계층 구조의 항목 수 세기

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는 JOIN을 지원하지만 계산 비용이 높습니다. 경험적인 규칙으로, JOIN이 쿼리 I/O를 세 배 이상 증가시킨다면 수집 시간에 비정규화해야 합니다.

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

고수준 개요#

원시 데이터를 두 번 저장하는 비용을 피하기 위해 "패스스루" 파이프라인을 활용합니다:

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>

참고: 단순화를 위해 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_replicated_at DateTime64(6, 'UTC') DEFAULT now() 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_replicated_at DateTime64(6, 'UTC') DEFAULT now() CODEC(ZSTD(1)),
  _siphon_deleted Bool DEFAULT FALSE CODEC(ZSTD(1)),
  PROJECTION pg_pkey_ordered (
    SELECT *
    ORDER BY id
  )
)
ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
PRIMARY KEY (traversal_path, merge_request_id, id)
SETTINGS deduplicate_merge_projection_mode = 'rebuild', index_granularity = 1024;

적용된 수정 사항:

merge_request_id는 MR당 리뷰어 조회를 최적화하기 위해 기본 키에 포함됩니다.
MV 트리거 중 JOIN 성능을 향상시키기 위해 index_granularity가 1024로 감소합니다.

3. merge_requests 대상 테이블 생성#
이 테이블은 siphon_merge_requests의 모든 열과 추가 비정규화된 열(이 경우 리뷰어용)을 포함합니다. 리뷰어는 각 튜플에 user_id와 리뷰 state를 포함하는 Tuple 배열로 모델링됩니다.
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_replicated_at DateTime64(6, 'UTC') DEFAULT now() CODEC(ZSTD(1)),
  _siphon_deleted Bool DEFAULT FALSE CODEC(ZSTD(1)),
  PROJECTION pg_pkey_ordered (
    SELECT *
    ORDER BY id
  )
)
ENGINE = ReplacingMergeTree(_siphon_replicated_at, _siphon_deleted)
PRIMARY KEY (traversal_path, id)
SETTINGS deduplicate_merge_projection_mode = 'rebuild'

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를 다시 트리거하고 최신 리뷰어 상태를 가져옵니다.
이것은 개념적으로 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 = 73)가 "변경 사항 요청" 상태(state = 3)로 검토 중인 프로젝트(id = 12)의 머지 리퀘스트 수를 셉니다.
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);


참고: 배열 데이터 유형은 파싱 및 필터링 중에 추가 오버헤드를 유발할 수 있습니다. 비정규화된 데이터가 연관된 상태 없이 단순한 ID 목록인 경우, hasSubstr와 결합된 구분된 문자열 필드(예: '/user_id1/user_id2/')가 더 높은 성능을 제공할 수 있습니다.
일관성 보장#
모든 비정규화에는 트레이드오프가 있습니다. Siphon 기반 계층 비정규화는 정상 운영 시(namespaces 및 projects 테이블의 초기 데이터 스냅샷이 완료되었다고 가정) 다음과 같은 일관성 보장을 제공합니다:

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

참고: 계층 조회 열(namespace_id)이 샤딩 키인 경우 이 열은 설계상 PostgreSQL에서 변경되지 않습니다.


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

정기적인 일관성 검사 작업을 통해 최종 일관성이 강제됩니다. 시스템은 대형 테이블의 이러한 일관성 문제를 5분 내에 해결하는 것을 목표로 합니다(이 이슈 내에서 구현 예정).



개발 환경에서는 레코드 생성이 프로젝트나 그룹 생성과 매우 가까운 시간에 발생할 수 있어 일관성 문제가 더 자주 나타날 수 있습니다. 이러한 경우 최종 일관성 강제가 초 또는 분 내에 문제를 해결해야 합니다(구성 가능).
테이블 파티셔닝#
ClickHouse 파티셔닝은 쿼리 최적화의 주요 도구가 아닌 데이터 수명주기 작업(이전 데이터 삭제 등)을 위한 데이터 관리 도구입니다.
파티션 세분성#
지나치게 세분화된 파티셔닝 방식을 피하세요. 경험적인 규칙으로, 연간 파티셔닝(예: toYear(created_at))보다 더 세분화하지 않는 것이 좋습니다.
파티션이 너무 많으면 ClickHouse 성능이 심각하게 저하될 수 있습니다:

수집 속도: 각 INSERT는 INSERT 블록이 접촉하는 모든 파티션에 대해 별도의 데이터 "파트"를 생성합니다. 높은 파티션 수는 "파트 폭발"로 이어져 메타데이터 오버헤드와 병합 압력을 증가시킵니다.
쿼리 성능: 많은 파티션에 걸친 쿼리는 많은 수의 작은 파일에 대한 메타데이터를 열고 처리해야 하므로 실행이 크게 느려질 수 있습니다.

제약 사항과 Siphon 스냅샷#
단일 삽입 문에서 접촉할 수 있는 파티션 수에 대한 하드 한계가 있습니다: max_partitions_per_insert_block=100. 이 한계는 ClickHouse Cloud에서 강제되며 증가시킬 수 없습니다.
Siphon이 PostgreSQL 테이블의 초기 스냅샷을 수행할 때 데이터는 종종 ClickHouse 파티션 키와 일치하지 않는 순서로 읽힙니다. 스냅샷이 100개 이상의 다른 월에 걸친 데이터를 포함하는 경우 삽입이 실패합니다.
다음 조건이 충족되는 경우에만 더 세분화된 파티셔닝(예: 월별)을 고려해야 합니다:

PostgreSQL 소스 테이블이 동일한 파티셔닝 로직을 따릅니다. Siphon은 각 PostgreSQL 파티션을 개별적으로 스냅샷하여 데이터 블록을 ClickHouse 한계 내에 자연스럽게 유지합니다.
데이터 볼륨이 더 많은 파티션의 관리 오버헤드를 정당화할 만큼 예외적으로 높습니다.

Cells 환경에서 작업#
Siphon으로 복제된 데이터베이스 테이블은 설계상 Cells 아키텍처를 지원합니다. 모든 셀에 Siphon이 구성되어 있다고 가정하면 조직이나 그룹이 다른 셀로 이동할 때 ClickHouse에 대한 추가 데이터 마이그레이션이 필요하지 않습니다.
진실의 소스는 항상 PostgreSQL이므로 재배치 프로세스(조직 이동) 내의 모든 레코드 변경사항이 복제됩니다:

Cell A에서 Cell B로 데이터 삽입.
Cell A에서 이전/오래된 데이터 제거.

이러한 변경사항은 Siphon producer에 의해 논리 복제 스트림을 통해 감지되고 결국 Siphon consumer에 의해 ClickHouse에 적용됩니다.