데이터 비정규화

데이터 비정규화 (Denormalization)

데이터 비정규화(denormalization)는 조인을 피해 쿼리 지연 시간을 최소화하기 위해 평탄화된 테이블을 사용하는 ClickHouse의 기법이에요.

출처: 문서

본문

정규화 vs 비정규화 스키마 비교

데이터 비정규화는 특정 쿼리 패턴에 대해 데이터베이스 성능을 최적화하기 위해 정규화 과정을 의도적으로 역전시키는 작업이에요. 정규화된 데이터베이스에서 데이터는 중복을 최소화하고 데이터 무결성을 보장하기 위해 여러 관련 테이블로 분리돼요. 비정규화는 테이블을 결합하고, 데이터를 중복하며, 계산된 필드를 단일 테이블이나 더 적은 테이블에 통합함으로써 중복을 재도입해요. 즉 조인을 쿼리 시점에서 삽입 시점으로 옮기는 거예요. 이 과정은 쿼리 시점의 복잡한 조인 필요성을 줄이고 읽기 작업을 크게 빠르게 할 수 있어, 읽기 요구가 많고 쿼리가 복잡한 애플리케이션에 이상적이에요. 그러나 중복된 데이터의 어떤 변경도 일관성을 유지하기 위해 모든 인스턴스에 전파되어야 하므로, 쓰기 작업과 유지보수의 복잡성이 증가할 수 있어요. NoSQL 솔루션들이 대중화한 일반적인 기법은 JOIN 지원이 없는 상황에서 데이터를 비정규화하는 거예요. 즉 모든 통계나 관련 행을 부모 행에 컬럼과 중첩 객체로 저장하는 거예요. 예를 들어 블로그용 예시 스키마에서 모든 Comments를 각 게시물의 객체 Array로 저장할 수 있어요.

비정규화를 언제 사용할까

일반적으로 다음 경우에 비정규화를 권장해요.

  • 자주 변경되지 않는 테이블이나, 데이터가 분석 쿼리에 사용 가능해지기까지 지연이 허용되는 경우(즉 데이터를 배치로 완전히 다시 적재할 수 있는 경우)에 비정규화하세요.
  • 다대다(many-to-many) 관계의 비정규화는 피하세요. 단일 소스 행이 변경되면 많은 행을 업데이트해야 할 수 있어요.
  • 고카디널리티(high cardinality) 관계의 비정규화는 피하세요. 한 테이블의 각 행이 다른 테이블에 수천 개의 관련 항목이 있다면, 이것들을 Array — 기본 타입 또는 튜플 — 로 표현해야 해요. 일반적으로 1000개 이상의 튜플을 가진 배열은 권장되지 않아요.
  • 모든 컬럼을 중첩 객체로 비정규화하기보다, 매터리얼라이즈드 뷰로 통계 하나만 비정규화하는 것을 고려해 보세요 (아래 참조).

모든 정보를 비정규화할 필요는 없어요. 자주 접근해야 하는 핵심 정보만 비정규화하면 돼요. 비정규화 작업은 ClickHouse나 Apache Flink 같은 업스트림에서 처리할 수 있어요.

자주 업데이트되는 데이터의 비정규화 피하기

ClickHouse에서 비정규화는 쿼리 성능을 최적화하기 위해 사용할 수 있는 여러 옵션 중 하나지만 신중하게 사용해야 해요. 데이터가 자주 업데이트되고 거의 실시간으로 업데이트되어야 한다면 이 접근은 피해야 해요. 주 테이블이 대부분 append-only이거나 주기적으로(예: 매일) 배치로 다시 적재할 수 있을 때 사용하세요. 이 접근은 한 가지 주요한 도전 — 쓰기 성능과 데이터 업데이트 — 으로 고통을 받아요. 더 구체적으로 비정규화는 데이터 조인의 책임을 쿼리 시점에서 수집(ingestion) 시점으로 효과적으로 옮겨요. 이것은 쿼리 성능을 크게 개선할 수 있지만 수집을 복잡하게 만들고, 데이터 파이프라인이 행을 구성하는 데 사용된 행 중 하나라도 변경되면 ClickHouse에 행을 다시 삽입해야 함을 의미해요. 이것은 하나의 소스 행 변경이 잠재적으로 ClickHouse의 많은 행 업데이트를 의미할 수 있어요. 복잡한 조인으로 행이 구성된 복잡한 스키마에서, 조인의 중첩 구성 요소의 단일 행 변경이 잠재적으로 수백만 행의 업데이트를 의미할 수 있어요. 이것을 실시간으로 달성하는 것은 종종 비현실적이며, 두 가지 도전 때문에 상당한 엔지니어링이 필요해요.

  1. 테이블 행이 변경될 때 올바른 조인 문을 트리거하는 것. 이상적으로는 조인의 모든 객체를 업데이트하지 않고, 영향을 받은 것만 업데이트해야 해요. 높은 처리량에서 효율적으로 올바른 행으로 필터링하도록 조인을 수정하는 것은 외부 도구나 엔지니어링이 필요해요.
  2. ClickHouse의 행 업데이트를 신중하게 관리해야 하므로 추가적인 복잡성이 도입돼요.

따라서 모든 비정규화 객체를 주기적으로 다시 적재하는 배치 업데이트 과정이 더 일반적이에요.

비정규화의 실용적인 사례

비정규화가 타당한 몇 가지 실용적인 예시와, 대안 접근이 더 바람직한 다른 예시를 살펴볼게요. AnswerCount와 CommentCount 같은 통계로 이미 비정규화된 Posts 테이블을 고려해 봐요 — 소스 데이터가 이 형태로 제공돼요. 실제로는 이 정보가 자주 변경되기 쉬우므로 정규화하는 것이 좋을 수 있어요. 이 컬럼들 중 다수는 다른 테이블에서도 얻을 수 있어요. 예를 들어 게시물의 댓글은 PostId 컬럼과 Comments 테이블로 얻을 수 있어요. 예시를 위해 게시물이 배치 과정으로 다시 적재된다고 가정할게요. 또한 Posts를 분석의 주 테이블로 고려하므로 다른 테이블을 Posts에 비정규화하는 것만 고려해요. 반대 방향으로의 비정규화도 일부 쿼리에 적절하며, 같은 위 고려사항이 적용돼요. 다음 각 예시에서 두 테이블을 조인에 사용해야 하는 쿼리가 존재한다고 가정하세요.

Posts와 Votes

게시물에 대한 투표(Votes)는 별도 테이블로 표현돼요. 이에 대한 최적화된 스키마와 데이터를 적재하는 insert 명령이 아래에 나와 있어요.

CREATE TABLE votes
(
        `Id` UInt32,
        `PostId` Int32,
        `VoteTypeId` UInt8,
        `CreationDate` DateTime64(3, 'UTC'),
        `UserId` Int32,
        `BountyAmount` UInt8
)
ENGINE = MergeTree
ORDER BY (VoteTypeId, CreationDate, PostId)

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

0 rows in set. Elapsed: 26.272 sec. Processed 238.98 million rows, 2.13 GB (9.10 million rows/s., 80.97 MB/s.)

언뜻 보면 이것들은 posts 테이블에 비정규화하기 위한 후보로 보일 수 있어요. 이 접근에는 몇 가지 도전이 있어요. 투표는 게시물에 자주 추가돼요. 시간이 지나면서 게시물당 줄어들 수 있지만, 다음 쿼리는 3만 개 게시물에 걸쳐 시간당 약 4만 건의 투표가 있음을 보여줘요.

SELECT round(avg(c)) AS avg_votes_per_hr, round(avg(posts)) AS avg_posts_per_hr
FROM
(
        SELECT
        toStartOfHour(CreationDate) AS hr,
        count() AS c,
        uniq(PostId) AS posts
        FROM votes
        GROUP BY hr
)

┌─avg_votes_per_hr─┬─avg_posts_per_hr─┐
│               41759 │         33322 │
└──────────────────┴──────────────────┘

지연이 허용된다면 이것은 배치로 해결할 수 있지만, 모든 게시물을 주기적으로 다시 적재하지 않는 한(바람직하지 않을 가능성이 높음) 여전히 업데이트를 처리해야 해요. 더 골치 아픈 것은 일부 게시물은 극도로 많은 투표 수를 가진다는 점이에요.

SELECT PostId, concat('https://stackoverflow.com/questions/', PostId) AS url, count() AS c
FROM votes
GROUP BY PostId
ORDER BY c DESC
LIMIT 5

┌───PostId─┬─url──────────────────────────────────────────┬─────c─┐
│ 11227902 │ https://stackoverflow.com/questions/11227902 │ 35123 │
│   927386 │ https://stackoverflow.com/questions/927386   │ 29090 │
│ 11227809 │ https://stackoverflow.com/questions/11227809 │ 27475 │
│   927358 │ https://stackoverflow.com/questions/927358   │ 26409 │
│  2003515 │ https://stackoverflow.com/questions/2003515  │ 25899 │
└──────────┴──────────────────────────────────────────────┴───────┘

여기서 핵심 관찰은 각 게시물에 대한 집계된 투표 통계만 대부분의 분석에 충분하다는 거예요. 모든 투표 정보를 비정규화할 필요는 없어요. 예를 들어 현재 Score 컬럼은 그런 통계 — 총 찬성 투표에서 반대 투표를 뺀 값 — 를 나타내요. 이상적으로는 쿼리 시점에 단순 조회로 이 통계만 가져올 수 있으면 좋겠어요 (dictionaries 참조).

Users와 Badges

이제 Users와 Badges를 살펴볼게요. 먼저 다음 명령으로 데이터를 삽입해요.

CREATE TABLE users
(
    `Id` Int32,
    `Reputation` LowCardinality(String),
    `CreationDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
    `DisplayName` String,
    `LastAccessDate` DateTime64(3, 'UTC'),
    `AboutMe` String,
    `Views` UInt32,
    `UpVotes` UInt32,
    `DownVotes` UInt32,
    `WebsiteUrl` String,
    `Location` LowCardinality(String),
    `AccountId` Int32
)
ENGINE = MergeTree
ORDER BY (Id, CreationDate)

CREATE TABLE badges
(
    `Id` UInt32,
    `UserId` Int32,
    `Name` LowCardinality(String),
    `Date` DateTime64(3, 'UTC'),
    `Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
    `TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId

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

0 rows in set. Elapsed: 26.229 sec. Processed 22.48 million rows, 1.36 GB (857.21 thousand rows/s., 51.99 MB/s.)

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

0 rows in set. Elapsed: 18.126 sec. Processed 51.29 million rows, 797.05 MB (2.83 million rows/s., 43.97 MB/s.)

사용자가 배지를 자주 획득할 수 있지만, 매일보다 더 자주 업데이트할 필요가 있을 것 같지 않은 데이터셋이에요. 배지와 사용자 사이의 관계는 일대다예요. 어쩌면 배지를 튜플 목록으로 사용자에게 간단히 비정규화할 수 있을까요? 가능하긴 하지만, 사용자당 최대 배지 수를 확인하는 빠른 확인은 이것이 이상적이지 않음을 시사해요.

SELECT UserId, count() AS c FROM badges GROUP BY UserId ORDER BY c DESC LIMIT 5

┌─UserId─┬─────c─┐
│  22656 │ 19334 │
│   6309 │ 10516 │
│ 100297 │  7848 │
│ 157882 │  7574 │
│  29407 │  6512 │
└────────┴───────┘

단일 행에 19k 객체를 비정규화하는 것은 아마 현실적이지 않아요. 이 관계는 별도 테이블로 두거나 통계를 추가하는 것이 가장 좋을 수 있어요.

배지의 통계(예: 배지 수)를 사용자에게 비정규화하고 싶을 수 있어요. 이 데이터셋에 대해 insert 시점에 딕셔너리를 사용할 때 그런 예시를 고려해 봐요.

PostLinks는 사용자들이 관련 또는 중복이라고 간주하는 Posts를 연결해요. 다음 쿼리는 스키마와 적재 명령을 보여줘요.

CREATE TABLE postlinks
(
  `Id` UInt64,
  `CreationDate` DateTime64(3, 'UTC'),
  `PostId` Int32,
  `RelatedPostId` Int32,
  `LinkTypeId` Enum('Linked' = 1, 'Duplicate' = 3)
)
ENGINE = MergeTree
ORDER BY (PostId, RelatedPostId)

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

0 rows in set. Elapsed: 4.726 sec. Processed 6.55 million rows, 129.70 MB (1.39 million rows/s., 27.44 MB/s.)

비정규화를 막을 정도로 과도한 수의 링크를 가진 게시물이 없다는 것을 확인할 수 있어요.

SELECT PostId, count() AS c
FROM postlinks
GROUP BY PostId
ORDER BY c DESC LIMIT 5

┌───PostId─┬───c─┐
│ 22937618 │ 125 │
│  9549780 │ 120 │
│  3737139 │ 109 │
│ 18050071 │ 103 │
│ 25889234 │  82 │
└──────────┴─────┘

마찬가지로 이 링크들은 지나치게 자주 발생하는 이벤트가 아니에요.

SELECT
  round(avg(c)) AS avg_votes_per_hr,
  round(avg(posts)) AS avg_posts_per_hr
FROM
(
  SELECT
  toStartOfHour(CreationDate) AS hr,
  count() AS c,
  uniq(PostId) AS posts
  FROM postlinks
  GROUP BY hr
)

┌─avg_votes_per_hr─┬─avg_posts_per_hr─┐
│                54 │                    44     │
└──────────────────┴──────────────────┘

이것을 아래 비정규화 예시로 사용할게요.

단순 통계 예시

대부분의 경우 비정규화는 부모 행에 단일 컬럼이나 통계를 추가하는 것을 요구해요. 예를 들어 게시물에 중복 게시물 수만 보강하고 싶다면 컬럼을 추가하기만 하면 돼요.

CREATE TABLE posts_with_duplicate_count
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
   ... -other columns
   `DuplicatePosts` UInt16
) ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)

이 테이블을 채우려면 중복 통계를 게시물과 조인하는 INSERT INTO SELECT를 활용해요.

INSERT INTO posts_with_duplicate_count SELECT
    posts.*,
    DuplicatePosts
FROM posts AS posts
LEFT JOIN
(
    SELECT PostId, countIf(LinkTypeId = 'Duplicate') AS DuplicatePosts
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId

일대다 관계를 위한 복합 타입 활용

비정규화를 수행하려면 종종 복합 타입을 활용해야 해요. 일대일 관계를 비정규화할 때 컬럼 수가 적으면 위에서처럼 원래 타입을 가진 행으로 추가하면 돼요. 그러나 큰 객체에는 불필요하고 일대다 관계에는 불가능해요. 복잡한 객체나 일대다 관계에서는 다음을 사용할 수 있어요.

  • 명명된 튜플(Named Tuples) — 관련 구조를 컬럼 집합으로 나타낼 수 있어요.
  • Array(Tuple) 또는 Nested — 명명된 튜플의 배열(일명 Nested)로, 각 항목이 객체를 나타내요. 일대다 관계에 적용 가능해요.

예시로 아래에서 PostLinks를 Posts에 비정규화하는 것을 보여드릴게요. 각 게시물은 앞서 PostLinks 스키마에서 보았듯 다른 게시물로의 여러 링크를 포함할 수 있어요. Nested 타입으로 이 링크된·중복된 게시물을 다음과 같이 표현할 수 있어요.

SET flatten_nested=0
CREATE TABLE posts_with_links
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
   ... -other columns
   `LinkedPosts` Nested(CreationDate DateTime64(3, 'UTC'), PostId Int32),
   `DuplicatePosts` Nested(CreationDate DateTime64(3, 'UTC'), PostId Int32),
) ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)

flatten_nested=0 설정을 사용하는 것에 주목하세요. 중첩 데이터의 평탄화를 끄는 것을 권장해요. 이 비정규화는 OUTER JOIN 쿼리와 함께 INSERT INTO SELECT로 수행할 수 있어요.

INSERT INTO posts_with_links
SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
         PostId,
         groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId

0 rows in set. Elapsed: 155.372 sec. Processed 66.37 million rows, 76.33 GB (427.18 thousand rows/s., 491.25 MB/s.)
Peak memory usage: 6.98 GiB.

여기서 타이밍에 주목하세요. 약 2분 만에 6600만 행을 비정규화했어요. 나중에 보게 되겠지만 이것은 스케줄링할 수 있는 작업이에요. 조인하기 전에 groupArray 함수를 사용해 PostLinks를 각 PostId에 대한 배열로 축소한 것에 주목하세요. 그런 다음 이 배열은 LinkedPosts와 DuplicatePosts라는 두 하위 목록으로 필터링되며, outer join에서 빈 결과도 제외해요. 몇 개 행을 선택해 새 비정규화 구조를 볼 수 있어요.

SELECT LinkedPosts, DuplicatePosts
FROM posts_with_links
WHERE (length(LinkedPosts) > 2) AND (length(DuplicatePosts) > 0)
LIMIT 1
FORMAT Vertical

Row 1:
──────
LinkedPosts:    [('2017-04-11 11:53:09.583',3404508),('2017-04-11 11:49:07.680',3922739),('2017-04-11 11:48:33.353',33058004)]
DuplicatePosts: [('2017-04-11 12:18:37.260',3922739),('2017-04-11 12:18:37.260',33058004)]

비정규화 오케스트레이션과 스케줄링

배치 (Batch)

비정규화를 활용하려면 그것을 수행하고 오케스트레이션할 수 있는 변환 과정이 필요해요. 데이터가 INSERT INTO SELECT로 적재된 후 ClickHouse로 이 변환을 수행하는 방법을 위에서 보여드렸어요. 이것은 주기적 배치 변환에 적합해요. 주기적 배치 적재 과정이 허용된다면 ClickHouse에서 이를 오케스트레이션할 여러 옵션이 있어요.

  • Refresherable Materialized Views — Refreshable 매터리얼라이즈드 뷰로 쿼리를 주기적으로 스케줄링하고 결과를 대상 테이블로 보낼 수 있어요. 쿼리 실행 시 뷰는 대상 테이블이 원자적으로 업데이트되도록 보장해요. 이것은 이 작업을 스케줄링하는 ClickHouse 네이티브 수단을 제공해요.
  • 외부 도구 — dbt와 Airflow 같은 도구를 사용해 변환을 주기적으로 스케줄링해요. dbt용 ClickHouse 통합은 대상 테이블의 새 버전이 만들어지고 쿼리를 받는 버전과 원자적으로 교체되는(EXCHANGE 명령으로) 방식으로 이것이 원자적으로 수행되도록 보장해요.

스트리밍 (Streaming)

대안으로 이를 ClickHouse 밖, 삽입 전에 Apache Flink 같은 스트리밍 기술로 수행할 수도 있어요. 또는 증분 매터리얼라이즈드 뷰로 데이터가 삽입될 때 이 과정을 수행할 수 있어요.

더 알아보기 (Learn more)