스키마 설계

스키마 설계 (Schema Design)

효과적인 스키마 설계를 이해하는 것은 ClickHouse 성능 최적화의 핵심이에요. 설계 선택은 종종 트레이드오프를 수반하며, 최적의 접근은 서빙되는 쿼리뿐 아니라 데이터 업데이트 빈도, 지연 시간 요구 사항, 데이터 볼륨 같은 요소에 따라 달라져요. 이 가이드는 ClickHouse 성능 최적화를 위한 스키마 설계 모범 사례와 데이터 모델링 기법의 개요를 제공해요.

출처: 문서

본문

Stack Overflow 데이터셋

이 가이드의 예시에는 Stack Overflow 데이터셋의 부분집합을 사용해요. 이것은 2008년부터 2024년 4월까지 Stack Overflow에서 발생한 모든 게시물, 투표, 사용자, 댓글, 배지를 포함해요. 이 데이터는 S3 버킷 s3://datasets-documentation/stackoverflow/parquet/ 아래의 아래 스키마들을 사용해 Parquet으로 제공돼요.

표시된 기본 키와 관계는 제약(constraint)으로 강제되지 않아요 (Parquet은 테이블 포맷이 아니라 파일 포맷이니까요). 즉 데이터가 어떻게 관련되어 있고 어떤 고유 키를 가지는지 순수하게 나타낼 뿐이에요.

Stack Overflow 데이터셋은 여러 관련 테이블을 포함해요. 어떤 데이터 모델링 작업에서든 주 테이블을 먼저 적재하는 데 집중할 것을 권장해요. 꼭 가장 큰 테이블이 아니라 가장 많은 분석 쿼리를 받을 것으로 예상되는 테이블이어야 해요. 이것으로 ClickHouse의 주요 개념과 타입에 익숙해질 수 있어요. 특히 OLTP 배경이 주로였다면 더 중요해요. 이 테이블은 추가 테이블이 더해지면서 ClickHouse 기능을 완전히 활용하고 최적의 성능을 얻기 위해 재모델링이 필요할 수 있어요. 위 스키마는 이 가이드의 목적상 의도적으로 최적이 아니에요.

초기 스키마 구축

posts 테이블이 대부분의 분석 쿼리의 대상이 될 것이므로, 이 테이블의 스키마를 구축하는 데 집중할게요. 이 데이터는 연도별 파일로 s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet 공개 S3 버킷에서 사용 가능해요.

S3에서 Parquet 포맷으로 데이터를 적재하는 것은 ClickHouse로 데이터를 적재하는 가장 일반적이고 선호되는 방법이에요. ClickHouse는 Parquet 처리에 최적화되어 있어 S3에서 초당 수천만 행을 읽고 삽입할 수 있어요. ClickHouse는 데이터셋의 타입을 자동으로 식별하는 스키마 추론 기능을 제공해요. 이것은 Parquet을 포함한 모든 데이터 포맷에서 지원돼요. s3 테이블 함수와 DESCRIBE 명령으로 데이터의 ClickHouse 타입을 식별하기 위해 이 기능을 활용할 수 있어요. 아래에서 stackoverflow/parquet/posts 폴더의 모든 파일을 읽기 위해 glob 패턴 *.parquet을 사용하는 것에 주목하세요.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet', NOSIGN)
SETTINGS describe_compact_output = 1

┌─name──────────────────┬─type───────────────────────────┐
│ Id                    │ Nullable(Int64)                │
│ PostTypeId            │ Nullable(Int64)                │
│ AcceptedAnswerId      │ Nullable(Int64)                │
│ CreationDate          │ Nullable(DateTime64(3, 'UTC')) │
│ Score                 │ Nullable(Int64)                │
│ ViewCount             │ Nullable(Int64)                │
│ Body                  │ Nullable(String)               │
│ OwnerUserId           │ Nullable(Int64)                │
│ OwnerDisplayName      │ Nullable(String)               │
│ LastEditorUserId      │ Nullable(Int64)                │
│ LastEditorDisplayName │ Nullable(String)               │
│ LastEditDate          │ Nullable(DateTime64(3, 'UTC')) │
│ LastActivityDate      │ Nullable(DateTime64(3, 'UTC')) │
│ Title                 │ Nullable(String)               │
│ Tags                  │ Nullable(String)               │
│ AnswerCount           │ Nullable(Int64)                │
│ CommentCount          │ Nullable(Int64)                │
│ FavoriteCount         │ Nullable(Int64)                │
│ ContentLicense        │ Nullable(String)               │
│ ParentId              │ Nullable(String)               │
│ CommunityOwnedDate    │ Nullable(DateTime64(3, 'UTC')) │
│ ClosedDate            │ Nullable(DateTime64(3, 'UTC')) │
└───────────────────────┴────────────────────────────────┘

s3 table function은 ClickHouse에서 S3의 데이터를 제자리에서 쿼리할 수 있게 해줘요. 이 함수는 ClickHouse가 지원하는 모든 파일 포맷과 호환돼요. 이것은 우리에게 초기의 비최적화 스키마를 제공해요. 기본적으로 ClickHouse는 이것들을 동등한 Nullable 타입에 매핑해요. CREATE EMPTY AS SELECT 명령으로 이 타입들을 사용해 ClickHouse 테이블을 만들 수 있어요.

CREATE TABLE posts
ENGINE = MergeTree
ORDER BY () EMPTY AS
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet', NOSIGN)

몇 가지 중요한 점이 있어요. 이 명령을 실행한 후 posts 테이블은 비어 있어요. 데이터는 적재되지 않았어요. 테이블 엔진으로 MergeTree를 지정했어요. MergeTree는 여러분이 사용할 가능성이 가장 높은 ClickHouse 테이블 엔진이에요. ClickHouse 도구 상자의 다목적 도구로, PB 단위의 데이터를 처리할 수 있고 대부분의 분석 사용 사례를 제공해요. CDC처럼 효율적인 업데이트를 지원해야 하는 사용 사례를 위한 다른 테이블 엔진도 있어요. ORDER BY () 절은 인덱스가 없고, 더 구체적으로 데이터에 순서가 없다는 뜻이에요. 이것에 대해서는 나중에 더 설명할게요. 지금은 모든 쿼리가 선형 스캔을 요구한다는 것만 알면 돼요. 테이블이 생성됐는지 확인하려면:

SHOW CREATE TABLE posts

CREATE TABLE posts
(
        `Id` Nullable(Int64),
        `PostTypeId` Nullable(Int64),
        `AcceptedAnswerId` Nullable(Int64),
        `CreationDate` Nullable(DateTime64(3, 'UTC')),
        `Score` Nullable(Int64),
        `ViewCount` Nullable(Int64),
        `Body` Nullable(String),
        `OwnerUserId` Nullable(Int64),
        `OwnerDisplayName` Nullable(String),
        `LastEditorUserId` Nullable(Int64),
        `LastEditorDisplayName` Nullable(String),
        `LastEditDate` Nullable(DateTime64(3, 'UTC')),
        `LastActivityDate` Nullable(DateTime64(3, 'UTC')),
        `Title` Nullable(String),
        `Tags` Nullable(String),
        `AnswerCount` Nullable(Int64),
        `CommentCount` Nullable(Int64),
        `FavoriteCount` Nullable(Int64),
        `ContentLicense` Nullable(String),
        `ParentId` Nullable(String),
        `CommunityOwnedDate` Nullable(DateTime64(3, 'UTC')),
        `ClosedDate` Nullable(DateTime64(3, 'UTC'))
)
ENGINE = MergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY tuple()

초기 스키마가 정의되었으니, s3 테이블 함수로 데이터를 읽는 INSERT INTO SELECT로 데이터를 채울 수 있어요. 다음은 8-코어 ClickHouse Cloud 인스턴스에서 약 2분 만에 posts 데이터를 적재해요.

INSERT INTO posts SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet', NOSIGN)

0 rows in set. Elapsed: 148.140 sec. Processed 59.82 million rows, 38.07 GB (403.80 thousand rows/s., 257.00 MB/s.)

위 쿼리는 6000만 행을 적재해요. ClickHouse에는 작지만, 인터넷 연결이 느린 사용자는 데이터 부분집합을 적재하고 싶을 수 있어요. glob 패턴으로 적재할 연도를 지정하면 됩니다. 예: https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2008.parquet 또는 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/{2008, 2009}.parquet. glob 패턴으로 파일 부분집합을 겨냥하는 방법은 여기를 참고하세요.

타입 최적화

ClickHouse 쿼리 성능의 비결 중 하나는 압축이에요. 디스크에 데이터가 적으면 I/O가 줄고 쿼리와 삽입이 빨라져요. CPU 관점에서 어떤 압축 알고리즘의 오버헤드도 대부분의 경우 I/O 감소가 상쇄해요. ClickHouse 쿼리를 빠르게 만들기 위해 노력할 때 데이터 압축 개선이 첫 번째 초점이 되어야 해요.

ClickHouse가 데이터를 그렇게 잘 압축하는 이유는 이 문서를 권장해요. 요약하면, 컬럼 지향 데이터베이스로서 값들이 컬럼 순서로 쓰여집니다. 이 값들이 정렬되면 같은 값들이 서로 인접하게 됩니다. 압축 알고리즘은 데이터의 연속 패턴을 활용해요. 게다가 ClickHouse에는 압축 기법을 더 튜닝할 수 있는 코덱과 세분화된 데이터 타입이 있어요. ClickHouse의 압축은 3가지 주요 요소 — 정렬 키, 데이터 타입, 사용되는 코덱 — 의 영향을 받아요. 이 모든 것은 스키마를 통해 구성돼요. 압축과 쿼리 성능의 가장 큰 초기 개선은 단순한 타입 최적화 과정으로 얻을 수 있어요. 스키마를 최적화하는 몇 가지 간단한 규칙이 적용될 수 있어요.

엄격한 타입 사용 — 초기 스키마는 명백히 숫자인 많은 컬럼에 String을 사용했어요. 올바른 타입을 사용하면 필터링과 집계 시 기대되는 의미가 보장돼요. Parquet 파일에서 올바르게 제공된 날짜 타입에도 마찬가지가 적용돼요.
Nullable 컬럼 피하기 — 기본적으로 위 컬럼들은 Null이라고 가정돼요. Nullable 타입은 쿼리가 빈 값과 Null 값의 차이를 결정할 수 있게 해줘요. 이것은 UInt8 타입의 별도 컬럼을 만들어요. 이 추가 컬럼은 사용자가 nullable 컬럼으로 작업할 때마다 처리되어야 해요. 이것은 추가 저장 공간을 사용하고 거의 항상 쿼리 성능에 부정적인 영향을 줘요. 타입의 기본 빈 값과 Null 사이에 차이가 있을 때만 Nullable을 사용하세요. 예를 들어 ViewCount 컬럼의 빈 값에 대한 0 값은 대부분의 쿼리에 충분하고 결과에 영향을 주지 않을 거예요. 빈 값이 다르게 취급되어야 한다면 필터로 쿼리에서 제외할 수도 있어요. 숫자 타입에는 최소 정밀도를 사용하세요. ClickHouse에는 서로 다른 숫자 범위와 정밀도를 위해 설계된 여러 숫자 타입이 있어요. 항상 컬럼을 표현하는 데 사용되는 비트 수를 최소화하는 것을 목표로 하세요. Int16 같은 서로 다른 크기의 정수뿐 아니라 ClickHouse는 최소값이 0인 unsigned 변형을 제공해요. 이것들은 컬럼에 더 적은 비트를 사용할 수 있게 해줘요. 예: UInt16은 최대값 65535로 Int16의 두 배예요. 가능하면 더 큰 signed 변형보다 이 타입들을 선호하세요.
날짜 타입의 최소 정밀도 — ClickHouse는 여러 날짜·날짜시간 타입을 지원해요. Date와 Date32는 순수 날짜 저장에 쓰일 수 있는데, 후자는 더 많은 비트를 대가로 더 큰 날짜 범위를 지원해요. DateTime과 DateTime64는 날짜 시간을 지원해요. DateTime은 초 단위로 제한되고 32비트를 사용해요. 이름이 암시하듯 DateTime64는 64비트를 사용하지만 나노초 단위까지 지원해요. 언제나처럼 쿼리에 허용되는 더 거친 버전을 선택해 필요한 비트 수를 최소화하세요.
LowCardinality 사용 — 고유 값이 적은 숫자, 문자열, Date 또는 DateTime 컬럼은 LowCardinality 타입으로 인코딩될 수 있어요. 이것은 값을 사전 인코딩해서 디스크 크기를 줄여요. 10k 미만의 고유 값이 있는 컬럼에 고려하세요. 특수한 경우 FixedString — 고정 길이인 문자열(예: 언어·통화 코드)은 FixedString 타입으로 인코딩될 수 있어요. 이것은 데이터의 길이가 정확히 N 바이트일 때 효율적이에요. 다른 모든 경우에는 효율성을 낮출 가능성이 있고 LowCardinality가 선호돼요.
데이터 검증을 위한 Enum — Enum 타입은 열거형을 효율적으로 인코딩하는 데 쓸 수 있어요. Enum은 저장해야 하는 고유 값 수에 따라 8비트 또는 16비트일 수 있어요. insert 시점에 관련 검증(선언되지 않은 값은 거부됨)이 필요하거나, Enum 값의 자연스러운 순서를 활용하는 쿼리를 수행하고 싶은 경우 사용을 고려하세요. 예: 사용자 응답을 포함하는 Enum(':(' = 1, ':|' = 2, ':)' = 3) 피드백 컬럼을 상상해 보세요.

팁: 모든 컬럼의 범위와 고유 값 수를 찾으려면 간단한 쿼리 SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical을 사용할 수 있어요. 비용이 들 수 있으므로 더 작은 데이터 부분집합에 대해 수행할 것을 권장해요. 정확한 결과를 위해서는 이 쿼리가 적어도 숫자를 숫자로 정의해야 해요. 즉 String이 아니어야 해요. 이 간단한 규칙들을 posts 테이블에 적용하면 각 컬럼의 최적 타입을 식별할 수 있어요.

Column	Is Numeric	Min, Max	Unique Values	Nulls	Comment	Optimized Type
`PostTypeId`	Yes	1, 8	8	No		`Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8)`
`AcceptedAnswerId`	Yes	0, 78285170	12282094	Yes	Differentiate Null with 0 value	UInt32
`CreationDate`	No	2008-07-31 21:42:52.667000000, 2024-03-31 23:59:17.697000000	-	No	Millisecond granularity isn't required, use DateTime	DateTime
`Score`	Yes	-217, 34970	3236	No		Int32
`ViewCount`	Yes	2, 13962748	170867	No		UInt32
`Body`	No	-	-	No		String
`OwnerUserId`	Yes	-1, 4056915	6256237	Yes		Int32
`OwnerDisplayName`	No	-	181251	Yes	Consider Null to be empty string	String
`LastEditorUserId`	Yes	-1, 9999993	1104694	Yes	0 is an unused value can be used for Nulls	Int32
`LastEditorDisplayName`	No	-	70952	Yes	Consider Null to be an empty string. Tested LowCardinality and no benefit	String
`LastEditDate`	No	2008-08-01 13:24:35.051000000, 2024-04-06 21:01:22.697000000	-	No	Millisecond granularity isn't required, use DateTime	DateTime
`LastActivityDate`	No	2008-08-01 12:19:17.417000000, 2024-04-06 21:01:22.697000000	-	No	Millisecond granularity isn't required, use DateTime	DateTime
`Title`	No	-	-	No	Consider Null to be an empty string	String
`Tags`	No	-	-	No	Consider Null to be an empty string	String
`AnswerCount`	Yes	0, 518	216	No	Consider Null and 0 to same	UInt16
`CommentCount`	Yes	0, 135	100	No	Consider Null and 0 to same	UInt8
`FavoriteCount`	Yes	0, 225	6	Yes	Consider Null and 0 to same	UInt8
`ContentLicense`	No	-	3	No	LowCardinality outperforms FixedString	LowCardinality(String)
`ParentId`	No	-	20696028	Yes	Consider Null to be an empty string	String
`CommunityOwnedDate`	No	2008-08-12 04:59:35.017000000, 2024-04-01 05:36:41.380000000	-	Yes	Consider default 1970-01-01 for Nulls. Millisecond granularity isn't required, use DateTime	DateTime
`ClosedDate`	No	2008-09-04 20:56:44, 2024-04-06 18:49:25.393000000	-	Yes	Consider default 1970-01-01 for Nulls. Millisecond granularity isn't required, use DateTime	DateTime

위 결과는 다음 스키마를 만들어 줘요.

CREATE TABLE posts_v2
(
   `Id` Int32,
   `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   `AcceptedAnswerId` UInt32,
   `CreationDate` DateTime,
   `Score` Int32,
   `ViewCount` UInt32,
   `Body` String,
   `OwnerUserId` Int32,
   `OwnerDisplayName` String,
   `LastEditorUserId` Int32,
   `LastEditorDisplayName` String,
   `LastEditDate` DateTime,
   `LastActivityDate` DateTime,
   `Title` String,
   `Tags` String,
   `AnswerCount` UInt16,
   `CommentCount` UInt8,
   `FavoriteCount` UInt8,
   `ContentLicense`LowCardinality(String),
   `ParentId` String,
   `CommunityOwnedDate` DateTime,
   `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'

간단한 INSERT INTO SELECT로 이전 테이블에서 데이터를 읽어 이 테이블에 채울 수 있어요.

INSERT INTO posts_v2 SELECT * FROM posts

0 rows in set. Elapsed: 146.471 sec. Processed 59.82 million rows, 83.82 GB (408.40 thousand rows/s., 572.25 MB/s.)

우리의 새 스키마에는 어떤 null도 유지하지 않아요. 위 insert는 이것들을 각 타입의 기본값 — 정수는 0, 문자열은 빈 값 — 으로 암시적으로 변환해요. ClickHouse는 또한 숫자들을 목표 정밀도로 자동 변환해요. ClickHouse의 기본(정렬) 키 — OLTP 데이터베이스 출신 사용자들은 ClickHouse에서의 동등한 개념을 자주 찾아요.

정렬 키 선택

ClickHouse가 자주 사용되는 규모에서는 메모리와 디스크 효율이 무엇보다 중요해요. 데이터는 parts로 알려진 청크로 ClickHouse 테이블에 기록되며, 백그라운드에서 파트를 병합하는 규칙이 적용돼요. ClickHouse에서 각 파트는 자체 기본 인덱스를 가져요. 파트가 병합되면 병합된 파트의 기본 인덱스도 병합돼요. 파트의 기본 인덱스는 행 그룹당 하나의 인덱스 항목을 가져요. 이 기법을 희소 인덱싱(sparse indexing)이라고 해요. ClickHouse에서 선택된 키는 인덱스뿐 아니라 데이터가 디스크에 기록되는 순서도 결정해요. 그 때문에 압축 수준에 극적인 영향을 줄 수 있고, 이것은 결국 쿼리 성능에 영향을 줄 수 있어요. 대부분의 컬럼 값들이 연속 순서로 기록되게 만드는 정렬 키는 선택된 압축 알고리즘(및 코덱)이 데이터를 더 효과적으로 압축하게 해줘요.

테이블의 모든 컬럼은 키에 포함 여부와 관계없이 지정된 정렬 키 값에 따라 정렬돼요. 예를 들어 CreationDate가 키로 사용되면 다른 모든 컬럼의 값 순서가 CreationDate 컬럼의 값 순서와 대응돼요. 여러 정렬 키를 지정할 수 있는데, 이것은 SELECT 쿼리의 ORDER BY 절과 같은 의미로 정렬돼요. 정렬 키 선택을 돕기 위해 몇 가지 간단한 규칙을 적용할 수 있어요. 다음은 때로 충돌할 수 있으므로 순서대로 고려하세요. 이 과정에서 여러 키를 식별할 수 있는데, 4-5개가 일반적으로 충분해요.

일반적인 필터와 맞는 컬럼을 선택하세요. 컬럼이 WHERE 절에서 자주 사용된다면, 덜 자주 사용되는 컬럼보다 키에 포함하는 것을 우선시하세요. 필터링될 때 전체 행의 큰 비율을 제외하는 데 도움이 되는 컬럼을 선호해서, 읽어야 하는 데이터 양을 줄이세요.
테이블의 다른 컬럼들과 높은 상관관계가 있을 것으로 보이는 컬럼을 선호하세요. 이것은 이 값들도 연속적으로 저장되게 해 압축을 개선하는 데 도움이 돼요. 정렬 키의 컬럼에 대한 GROUP BY와 ORDER BY 연산은 더 메모리 효율적으로 만들어질 수 있어요.

정렬 키의 컬럼 부분집합을 식별할 때는 특정 순서로 컬럼을 선언하세요. 이 순서는 쿼리에서 보조 키 컬럼 필터링의 효율과 테이블 데이터 파일의 압축 비율 모두에 상당한 영향을 줄 수 있어요. 일반적으로 카디널리티 오름차순으로 키를 정렬하는 것이 가장 좋아요. 이것은 정렬 키에서 더 나중에 나타나는 컬럼에 대한 필터링이 튜플에서 더 일찍 나타나는 것보다 덜 효율적이라는 사실과 균형을 맞춰야 해요. 이 동작들의 균형을 맞추고 접근 패턴을 고려하세요 (그리고 무엇보다 변형을 테스트하세요).

예시

위 지침을 posts 테이블에 적용해 볼게요. 사용자가 날짜와 게시물 타입으로 필터링하는 분석을 수행하고 싶다고 가정해요. 예: "지난 3개월 동안 댓글이 가장 많은 질문은 무엇인가?". 최적화된 타입이지만 정렬 키가 없는 앞선 posts_v2 테이블을 사용한 이 질문에 대한 쿼리:

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v2
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

┌───────Id─┬─Title─────────────────────────────────────────────────────────────┬─CommentCount─┐
│ 78203063 │ How to avoid default initialization of objects in std::vector?    │           74 │
│ 78183948 │ About memory barrier                                              │           52 │
│ 77900279 │ Speed Test for Buffer Alignment: IBM's PowerPC results vs. my CPU │           49 │
└──────────┴───────────────────────────────────────────────────────────────────┴──────────────┘

10 rows in set. Elapsed: 0.070 sec. Processed 59.82 million rows, 569.21 MB (852.55 million rows/s., 8.11 GB/s.)
Peak memory usage: 429.38 MiB.

여기서 쿼리는 모든 6000만 행이 선형 스캔되었음에도 매우 빠릅니다. ClickHouse는 그냥 빨라요 :) TB·PB 규모에서는 정렬 키가 가치 있다는 것을 믿으셔야 해요! 컬럼 PostTypeId와 CreationDate를 정렬 키로 선택해 볼게요. 우리의 경우 사용자가 항상 PostTypeId로 필터링할 것이라고 기대해요. 이것은 카디널리티 8을 가지며 정렬 키의 첫 항목으로 논리적 선택을 나타내요. 날짜 세분화 필터링이 충분할 것임을 인식해서(datetime 필터에도 여전히 혜택이 있어요) toDate(CreationDate)를 키의 2번째 구성 요소로 사용해요. 이것은 또한 날짜가 16으로 표현되므로 더 작은 인덱스를 만들어 필터링을 빠르게 해요. 마지막 키 항목은 댓글 수가 가장 많은 게시물(최종 정렬)을 찾는 데 도움이 되도록 CommentCount예요.

CREATE TABLE posts_v3
(
        `Id` Int32,
        `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime,
        `Score` Int32,
        `ViewCount` UInt32,
        `Body` String,
        `OwnerUserId` Int32,
        `OwnerDisplayName` String,
        `LastEditorUserId` Int32,
        `LastEditorDisplayName` String,
        `LastEditDate` DateTime,
        `LastActivityDate` DateTime,
        `Title` String,
        `Tags` String,
        `AnswerCount` UInt16,
        `CommentCount` UInt8,
        `FavoriteCount` UInt8,
        `ContentLicense` LowCardinality(String),
        `ParentId` String,
        `CommunityOwnedDate` DateTime,
        `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)
COMMENT 'Ordering Key'

--populate table from existing table

INSERT INTO posts_v3 SELECT * FROM posts_v2

0 rows in set. Elapsed: 158.074 sec. Processed 59.82 million rows, 76.21 GB (378.42 thousand rows/s., 482.14 MB/s.)
Peak memory usage: 6.41 GiB.

이전 쿼리가 3배 이상 응답 시간을 개선해요.

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v3
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

10 rows in set. Elapsed: 0.020 sec. Processed 290.09 thousand rows, 21.03 MB (14.65 million rows/s., 1.06 GB/s.)

특정 타입과 적절한 정렬 키를 사용함으로써 얻은 압축 개선에 관심이 있다면 ClickHouse의 압축을 참고하세요. 압축을 더 개선해야 한다면 올바른 컬럼 압축 코덱 선택 섹션도 권장해요.

다음 단계: 데이터 모델링 기법

지금까지 단일 테이블만 마이그레이션했어요. 이것으로 몇 가지 핵심 ClickHouse 개념을 소개할 수 있었지만, 대부분의 스키마는 안타깝게도 이렇게 단순하지 않아요. 아래에 나열된 다른 가이드들에서 최적의 ClickHouse 쿼리를 위해 더 넓은 스키마를 재구성하는 여러 기법을 탐구할 거예요. 이 과정 전반에 걸쳐 Posts가 대부분의 분석 쿼리가 수행되는 중심 테이블로 유지되도록 목표로 해요. 다른 테이블도 격리되어 쿼리될 수 있지만, 대부분의 분석은 posts의 맥락에서 수행되기를 가정해요.

이 섹션을 통해 우리는 다른 테이블들의 최적화된 변형을 사용해요. 이것들의 스키마를 제공하지만 간결함을 위해 내린 결정은 생략해요. 이것들은 앞서 설명한 규칙에 기반하며, 결정 추론은 독자에게 맡겨요. 다음 접근들은 모두 읽기를 최적화하고 쿼리 성능을 개선하기 위해 JOIN 사용을 최소화하는 것을 목표로 해요. ClickHouse에서 JOIN이 완전히 지원되지만, 최적의 성능을 위해 드물게(JOIN 쿼리에 2~3개 테이블이면 충분해요) 사용하는 것을 권장해요. ClickHouse에는 외래 키 개념이 없어요. 이것이 조인을 금지하지는 않지만, 참조 무결성은 애플리케이션 수준에서 사용자가 관리해야 함을 의미해요. ClickHouse 같은 OLAP 시스템에서 데이터 무결성은 데이터베이스 자체가 상당한 오버헤드를 발생시키며 강제하기보다, 종종 애플리케이션 수준이나 데이터 수집 과정 중에 관리돼요. 이 접근은 더 많은 유연성과 더 빠른 데이터 삽입을 허용해요. 이것은 매우 큰 데이터셋으로 읽기·삽입 쿼리의 속도와 확장성에 대한 ClickHouse의 초점과 일치해요. 쿼리 시점의 JOIN 사용을 최소화하기 위해 사용자에게 여러 도구/접근 방식이 있어요.

데이터 비정규화 — 테이블을 결합하고 비 1:1 관계에 복합 타입을 사용해 데이터를 비정규화해요. 여기에는 종종 조인을 쿼리 시점에서 삽입 시점으로 옮기는 것이 포함돼요.
딕셔너리 — 직접 조인과 키-값 조회를 처리하기 위한 ClickHouse 고유 기능이에요.
증분 매터리얼라이즈드 뷰 — 계산 비용을 쿼리 시점에서 삽입 시점으로 옮기는 ClickHouse 기능으로, 집계 값을 증분 계산하는 능력을 포함해요.
Refreshable 매터리얼라이즈드 뷰 — 다른 데이터베이스 제품에서 사용되는 매터리얼라이즈드 뷰와 비슷하게, 쿼리 결과를 주기적으로 계산하고 결과를 캐시할 수 있게 해줘요.

각 가이드에서 이 접근들을 각각 탐구하며, 언제 적절한지 강조하고 Stack Overflow 데이터셋의 질문 해결에 어떻게 적용할 수 있는지 보여주는 예시를 제공할 거예요.

스키마 설계