데이터 모델링 기법

데이터 모델링 기법 (3부)

이 문서는 PostgreSQL에서 ClickHouse로 마이그레이션하는 가이드의 3부예요. PostgreSQL에서 마이그레이션할 때 ClickHouse에서 데이터를 어떻게 모델링하는지 실용 예시로 보여줘요. 기본(정렬) 키, 파티션, 매터리얼라이즈드 뷰 vs projection, 비정규화를 다뤄요.

출처: Data modeling techniques

본문

이 문서는 PostgreSQL에서 ClickHouse로 마이그레이션하는 가이드의 3부예요. 실용 예시를 사용해서 PostgreSQL에서 마이그레이션할 때 ClickHouse에서 데이터를 모델링하는 방법을 보여줘요.

Postgres에서 마이그레이션하는 사용자들은 ClickHouse 데이터 모델링 가이드를 읽어 보길 권장해요. 이 가이드는 동일한 Stack Overflow 데이터셋을 사용하고 ClickHouse 기능을 사용한 여러 접근을 탐구해요.

ClickHouse의 기본(정렬) 키

OLTP 데이터베이스에서 오는 사용자들은 ClickHouse에서 동등한 개념을 찾곤 해요. ClickHouse가 PRIMARY KEY 문법을 지원한다는 것을 보고, 사용자들은 소스 OLTP 데이터베이스와 같은 키로 테이블 스키마를 정의하고 싶어 할 수 있어요. 이것은 적절하지 않아요.

ClickHouse 기본 키는 어떻게 다른가?

ClickHouse에서 OLTP 기본 키를 사용하는 것이 왜 부적절한지 이해하려면 ClickHouse 인덱싱의 기본을 이해해야 해요. 비교 예시로 Postgres를 사용하지만, 이 일반 개념은 다른 OLTP 데이터베이스에도 적용돼요.

  • Postgres 기본 키는 정의상 행마다 고유해요. B-tree 구조를 사용하면 이 키로 단일 행을 효율적으로 조회할 수 있어요. ClickHouse도 단일 행 값 조회에 최적화될 수 있지만, 분석 워크로드는 보통 몇 개 컬럼을 많은 행에 대해 읽어야 해요. 필터는 더 자주 집계가 수행될 행 부분집합을 식별해야 해요.
  • 메모리와 디스크 효율은 ClickHouse가 자주 사용되는 규모에 중요해요. 데이터는 파트(part)라고 하는 청크로 ClickHouse 테이블에 쓰이며, 백그라운드에서 파트를 병합하는 규칙이 적용돼요. ClickHouse에서 각 파트는 자체 기본 인덱스가 있어요. 파트가 병합되면 병합된 파트의 기본 인덱스도 병합돼요. Postgres와 달리 이 인덱스는 각 행마다 만들어지지 않아요. 대신 파트의 기본 인덱스는 행 그룹마다 인덱스 항목이 하나 있어요 — 이 기법을 스파스 인덱싱(sparse indexing) 이라고 해요.
  • 스파스 인덱싱이 가능한 이유는 ClickHouse가 파트의 행을 지정된 키로 정렬해 디스크에 저장하기 때문이에요. 단일 행을 직접 찾는(B-Tree 기반 인덱스처럼) 대신, 스파스 기본 인덱스는 (인덱스 항목에 대한 이진 검색으로) 쿼리와 일치할 수 있는 행 그룹을 빠르게 식별할 수 있게 해 줘요. 찾은 잠재적으로 일치하는 행 그룹들은 그다음 병렬로 ClickHouse 엔진으로 스트리밍되어 일치 항목을 찾아요. 이 인덱스 설계는 기본 인덱스가 작게 유지되면서(주 메모리에 완전히 들어감) 쿼리 실행 시간을 크게 단축할 수 있게 해 줘요. 특히 데이터 분석 사용 사례에서 일반적인 범위 쿼리에 그렇죠.

더 자세한 내용은 심층 가이드를 권장해요. ClickHouse에서 선택한 키는 인덱스뿐 아니라 데이터가 디스크에 쓰이는 순서도 결정해요. 그래서 압축 수준에 큰 영향을 줄 수 있고, 이것이 다시 쿼리 성능에 영향을 줄 수 있어요. 대부분의 컬럼 값이 연속된 순서로 쓰이게 하는 정렬 키는 선택된 압축 알고리즘(과 코덱)이 데이터를 더 효과적으로 압축하게 해 줘요.

테이블의 모든 컬럼은 지정된 정렬 키 값에 따라 정렬되며, 키에 포함되었는지와 무관해요. 예를 들어 CreationDate가 키로 사용되면 다른 모든 컬럼의 값 순서는 CreationDate 컬럼의 값 순서와 대응돼요. 여러 정렬 키를 지정할 수 있어요 — 이것은 SELECT 쿼리의 ORDER BY 절과 동일한 의미로 정렬해요.

정렬 키 선택하기

posts 테이블을 예시로 정렬 키 선택의 고려사항과 단계는 여기를 참고하세요. CDC와 실시간 복제를 사용할 때는 고려해야 할 추가 제약이 있어요. CDC로 정렬 키를 커스터마이즈하는 기법은 이 문서를 참고하세요.

파티션 (Partitions)

Postgres에서 왔다면, 테이블을 파티션이라는 더 작고 관리하기 쉬운 조각으로 나눠 대규모 데이터베이스의 성능과 관리성을 향상시키는 테이블 파티셔닝 개념에 익숙할 거예요. 이 파티셔닝은 지정된 컬럼(예: 날짜)의 범위, 정의된 목록, 또는 키에 대한 해시를 사용해 달성할 수 있어요. 관리자는 날짜 범위나 지리적 위치 같은 특정 기준으로 데이터를 구성할 수 있게 됩니다. 파티셔닝은 파티션 프루닝과 더 효율적인 인덱싱으로 더 빠른 데이터 접근을 가능하게 해 쿼리 성능을 개선해요. 또한 개별 파티션에 대한 작업을 전체 테이블 대신 허용함으로써 백업과 데이터 제거 같은 유지보수 작업도 도와줘요. 추가로 파티셔닝은 부하를 여러 파티션에 분산시켜 PostgreSQL 데이터베이스의 확장성을 크게 개선할 수 있어요.

ClickHouse에서 파티셔닝은 테이블이 처음 정의될 때 PARTITION BY 절로 지정돼요. 이 절은 어떤 컬럼에든 SQL 표현식을 포함할 수 있고, 그 결과가 행이 어느 파티션으로 보내질지 정의해요. 데이터 파트는 디스크에서 각 파티션과 논리적으로 연관되고 격리되어 쿼리될 수 있어요. 아래 예시에서는 toYear(CreationDate) 표현식을 사용해 posts 테이블을 연도별로 파티셔닝해요. 행이 ClickHouse에 삽입되면 이 표현식이 각 행에 대해 평가되어 해당 파티션이 있으면 그 결과 파티션으로 라우팅돼요 (행이 해당 연도 첫 번째면 파티션이 생성돼요).

 CREATE TABLE posts
(
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime64(3, 'UTC'),
...
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)

파티셔닝의 전체 설명은 "테이블 파티션"을 참고하세요.

파티션의 적용

ClickHouse의 파티셔닝은 Postgres와 유사한 적용이 있지만 미묘한 차이가 있어요. 구체적으로:

  • 데이터 관리 — ClickHouse에서 파티셔닝은 주로 쿼리 최적화 기법이 아니라 데이터 관리 기능으로 고려해야 해요. 키를 기준으로 데이터를 논리적으로 분리하면 각 파티션은 독립적으로 운영될 수 있어요 (예: 삭제). 이를 통해 파티션, 즉 부분집합을 스토리지 티어 사이에서 시간에 따라 효율적으로 이동하거나 데이터를 만료시키고/클러스터에서 효율적으로 삭제할 수 있어요. 예시에서 아래는 2008년의 posts를 제거해요:
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'

┌─partition─┐
│ 2008      │
│ 2009      │
│ 2010      │
│ 2011      │
│ 2012      │
│ 2013      │
│ 2014      │
│ 2015      │
│ 2016      │
│ 2017      │
│ 2018      │
│ 2019      │
│ 2020      │
│ 2021      │
│ 2022      │
│ 2023      │
│ 2024      │
└───────────┘

17 rows in set. Elapsed: 0.002 sec.

ALTER TABLE posts
(DROP PARTITION '2008')

Ok.

0 rows in set. Elapsed: 0.103 sec.
  • 쿼리 최적화 — 파티션이 쿼리 성능을 도울 수 있지만 이것은 접근 패턴에 크게 의존해요. 쿼리가 몇 개 파티션(이상적으로 하나)만 대상으로 하면 성능이 잠재적으로 개선될 수 있어요. 이것은 보통 파티셔닝 키가 기본 키에 없고 그것으로 필터링할 때만 유용해요. 그러나 많은 파티션을 다루어야 하는 쿼리는 파티셔닝을 사용하지 않을 때보다 더 나쁠 수 있어요 (파티셔닝 결과로 더 많은 파트가 생길 수 있으므로). 파티셔닝 키가 이미 기본 키의 앞쪽 항목이라면 단일 파티션을 대상으로 하는 이점은 훨씬 덜하거나 없을 거예요. 각 파티션의 값이 고유하다면 파티셔닝은 GROUP BY 쿼리 최적화에도 사용될 수 있어요. 하지만 일반적으로 기본 키가 최적화되어 있는지 확인하고, 접근 패턴이 하루의 특정 예측 가능한 부분집합을 접근하는 예외적인 경우에만 파티셔닝을 쿼리 최적화 기법으로 고려해야 해요 (예: 하루로 파티셔닝하고 대부분의 쿼리가 지난 하루에 있는 경우).

파티션 권장사항

파티셔닝을 데이터 관리 기법으로 고려해야 해요. 시계열 데이터를 운영할 때 데이터를 클러스터에서 만료시켜야 하는 경우에 이상적이에요 (예: 가장 오래된 파티션을 간단히 드롭할 수 있음). 중요: 파티셔닝 키 표현식이 높은 카디널리티 집합을 만들지 않도록 하세요 — 즉 100개 이상의 파티션을 만드는 것은 피해야 해요. 예를 들어 클라이언트 식별자나 이름 같은 고카디널리티 컬럼으로 데이터를 파티셔닝하지 마세요. 대신 클라이언트 식별자나 이름을 ORDER BY 표현식의 첫 번째 컬럼으로 만드세요.

내부적으로 ClickHouse는 삽입된 데이터에 대해 파트를 만듭니다. 더 많은 데이터가 삽입될수록 파트 수가 증가해요. 과도하게 많은 파트 수(쿼리 성능을 저하시키는, 읽을 파일이 더 많아짐)를 방지하기 위해 파트는 백그라운드 비동기 프로세스에서 병합돼요. 파트 수가 사전 구성된 한도를 초과하면 ClickHouse는 삽입 시 "too many parts" 오류로 예외를 던져요. 이것은 정상 운영에서는 발생하지 않아야 하며, ClickHouse가 잘못 구성되거나 잘못 사용된 경우에만 발생해요 (예: 많은 작은 삽입).

파트가 파티션마다 격리되어 생성되므로, 파티션 수를 늘리면 파트 수가 증가해요 — 즉 파티션 수의 배수가 되는 거죠. 고카디널리티 파티셔닝 키는 따라서 이 오류를 일으킬 수 있으므로 피해야 해요.

매터리얼라이즈드 뷰 vs projection

Postgres는 단일 테이블에 여러 인덱스를 만들어 다양한 접근 패턴에 대한 최적화를 허용해요. 이 유연성은 관리자와 개발자가 특정 쿼리와 운영 요구에 맞게 데이터베이스 성능을 조정할 수 있게 해 줘요. ClickHouse의 projection 개념은 이것과 완전히 동일하지는 않지만, 테이블에 여러 ORDER BY 절을 지정할 수 있게 해 줘요. ClickHouse 데이터 모델링 문서에서 매터리얼라이즈드 뷰를 사용해 집계를 사전 계산하고, 행을 변환하고, 다양한 접근 패턴에 쿼리를 최적화하는 방법을 탐구해요. 후자의 경우, 매터리얼라이즈드 뷰가 삽입을 받는 원래 테이블과 다른 정렬 키를 가진 대상 테이블로 행을 보내는 예시를 제공했어요.

예를 들어 다음 쿼리를 고려해 보세요:

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.040 sec. Processed 90.38 million rows, 361.59 MB (2.25 billion rows/s., 9.01 GB/s.)
Peak memory usage: 201.93 MiB.

이 쿼리는 UserId가 정렬 키가 아니므로 9천만 행 전부를 스캔해야 해요 (물론 빠르게). 앞서 우리는 PostId에 대한 룩업 역할을 하는 매터리얼라이즈드 뷰로 이 문제를 해결했어요. 같은 문제는 projection으로 해결할 수 있어요. 아래 명령은 ORDER BY user_id에 대한 projection을 추가해요:

ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)

ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id

먼저 projection을 만든 다음 materialize해야 한다는 점을 주목하세요. 후자 명령은 두 가지 다른 순서로 데이터가 디스크에 두 번 저장되게 해요. projection은 아래처럼 데이터가 생성될 때 정의될 수도 있으며, 데이터가 삽입됨에 따라 자동으로 유지돼요.

CREATE TABLE comments
(
        `Id` UInt32,
        `PostId` UInt32,
        `Score` UInt16,
        `Text` String,
        `CreationDate` DateTime64(3, 'UTC'),
        `UserId` Int32,
        `UserDisplayName` LowCardinality(String),
        PROJECTION comments_user_id
        (
        SELECT *
        ORDER BY UserId
        )
)
ENGINE = MergeTree
ORDER BY PostId

projection이 ALTER를 통해 생성되면 MATERIALIZE PROJECTION 명령이 발행될 때 생성이 비동기적이에요. 다음 쿼리로 이 작업의 진행 상황을 확인하고 is_done=1을 기다릴 수 있어요:

SELECT
        parts_to_do,
        is_done,
        latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')

   ┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │           1 │       0 │                    │
   └─────────────┴─────────┴────────────────────┘

1 row in set. Elapsed: 0.003 sec.

위 쿼리를 반복하면 추가 저장 공간을 대가로 성능이 크게 개선된 것을 볼 수 있어요:

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.008 sec. Processed 16.36 thousand rows, 98.17 KB (2.15 million rows/s., 12.92 MB/s.)
Peak memory usage: 4.06 MiB.

EXPLAIN 명령으로 이 쿼리를 서비스하는 데 projection이 사용됐는지도 확인할 수 있어요:

EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

    ┌─explain─────────────────────────────────────────────┐
1. │ Expression ((Projection + Before ORDER BY))         │
2. │   Aggregating                                       │
3. │   Filter                                            │
4. │           ReadFromMergeTree (comments_user_id)      │
5. │           Indexes:                                  │
6. │           PrimaryKey                                │
7. │           Keys:                                     │
8. │           UserId                                    │
9. │           Condition: (UserId in [8592047, 8592047]) │
10. │           Parts: 2/2                                │
11. │           Granules: 2/11360                         │
   └─────────────────────────────────────────────────────┘

11 rows in set. Elapsed: 0.004 sec.

projection을 언제 사용할까

Projection은 데이터가 삽입됨에 따라 자동으로 유지되므로 새 사용자에게 매력적인 기능이에요. 게다가 쿼리는 단일 테이블로 보내지면 가능한 곳에서 projection이 활용되어 응답 시간을 단축해요. 이것은 매터리얼라이즈드 뷰와 대조적이에요 — 매터리얼라이즈드 뷰에서는 사용자가 필터에 따라 적절한 최적화 대상 테이블을 선택하거나 쿼리를 다시 써야 하거든요. 이것은 사용자 애플리케이션에 더 큰 부담을 두고 클라이언트 측 복잡성을 증가시켜요.

이러한 장점에도 불구하고 projection에는 인지해야 할 고유한 한계가 있으며, 따라서 드물게 배포해야 해요. 다음과 같은 경우 projection 사용을 권장해요:

  • 데이터의 완전한 재정렬이 필요한 경우. projection의 표현식은 이론상 GROUP BY를 사용할 수 있지만, 집계 유지에는 매터리얼라이즈드 뷰가 더 효과적이에요. 쿼리 최적화기도 단순한 재정렬, 즉 SELECT * ORDER BY x를 사용하는 projection을 더 활용할 가능성이 높아요. 저장 공간을 줄이기 위해 이 표현식에서 컬럼 부분집합을 선택할 수 있어요.
  • 저장 공간 증가와 데이터를 두 번 쓰는 오버헤드에 익숙한 경우. 삽입 속도에 미치는 영향을 테스트하고 저장 오버헤드를 평가하세요.

버전 25.5부터 ClickHouse는 projection에서 가상 컬럼 _part_offset을 지원해요. 이것은 projection을 더 공간 효율적으로 저장하는 방법을 열어 줘요. 자세한 내용은 "Projections"를 참고하세요.

비정규화 (Denormalization)

Postgres는 관계형 데이터베이스이므로 데이터 모델이 많이 정규화(normalized)되어 있고, 종종 수백 개의 테이블이 관련돼요. ClickHouse에서 비정규화는 JOIN 성능을 최적화하는 데 때로 유익할 수 있어요. Stack Overflow 데이터셋을 ClickHouse에서 비정규화하는 이점을 보여 주는 가이드를 참조할 수 있어요. 이것으로 Postgres에서 ClickHouse로 마이그레이션하는 기본 가이드를 마칩니다. 더 고급 ClickHouse 기능을 배우려면 ClickHouse 데이터 모델링 가이드를 읽어 보길 권장해요.

더 알아보기 (Learn more)