이 문서는 PostgreSQL에서 ClickHouse로 마이그레이션하는 가이드의 2부입니다. 실제 예제를 사용하여 실시간 복제(CDC) 접근 방식을 통해 마이그레이션을 효율적으로 수행하는 방법을 보여 줍니다. 여기에서 다루는 많은 개념은 PostgreSQL에서 ClickHouse로의 수동 대량 데이터 전송에도 적용할 수 있습니다.
기존 PostgreSQL 환경에서 사용하던 대부분의 SQL 쿼리는 별도 수정 없이 ClickHouse에서 실행할 수 있으며, 더 빠르게 실행되는 경우가 많습니다.
CDC를 사용한 중복 제거
CDC를 사용하는 실시간 복제 시에는 업데이트와 삭제 작업으로 인해 중복된 행이 발생할 수 있습니다. 이를 관리하기 위해 VIEW와 갱신 가능 구체화 뷰(Refreshable Materialized View)를 활용하는 기법을 사용할 수 있습니다.
이 가이드를 참고하여, CDC 기반 실시간 복제를 사용할 때 애플리케이션을 PostgreSQL에서 ClickHouse로 최소한의 변경만으로 이전하는 방법을 학습하십시오.
ClickHouse에서 쿼리 최적화
최소한의 쿼리 수정만으로 마이그레이션하는 것도 가능하지만, 쿼리를 크게 단순화하고 쿼리 성능을 더욱 향상시키기 위해서는 ClickHouse 기능을 활용하는 것이 좋습니다.
여기 있는 예시는 일반적인 쿼리 패턴을 다루며, 이를 ClickHouse에서 어떻게 최적화할 수 있는지 보여 줍니다. 이 예시들은 PostgreSQL과 ClickHouse에서 동일한 리소스(코어 8개, RAM 32GiB)로 전체 Stack Overflow 데이터셋 (2024년 4월까지)을 사용합니다.
단순화를 위해 아래 쿼리에서는 데이터 중복 제거 기법 사용을 생략했습니다.
여기에서의 카운트 값은 약간씩 다를 수 있습니다. Postgres 데이터에는 외래 키의 참조 무결성을 만족하는 행만 포함되어 있습니다. ClickHouse에는 이러한 제약 조건이 없으므로 익명 사용자를 포함한 전체 데이터셋이 포함됩니다.
질문을 10개 초과로 작성한 사용자 중에서 가장 많은 조회 수를 받은 사용자:
-- ClickHouse
SELECT OwnerDisplayName, sum(ViewCount) AS total_views
FROM stackoverflow.posts
WHERE (PostTypeId = 'Question') AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING count() > 10
ORDER BY total_views DESC
LIMIT 5
┌─OwnerDisplayName────────┬─total_views─┐
│ Joan Venge │ 25520387 │
│ Ray Vega │ 21576470 │
│ anon │ 19814224 │
│ Tim │ 19028260 │
│ John │ 17638812 │
└─────────────────────────┴─────────────┘
5 rows in set. Elapsed: 0.360 sec. Processed 24.37 million rows, 140.45 MB (67.73 million rows/s., 390.38 MB/s.)
Peak memory usage: 510.71 MiB.
--Postgres
SELECT OwnerDisplayName, SUM(ViewCount) AS total_views
FROM public.posts
WHERE (PostTypeId = 1) AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING COUNT(*) > 10
ORDER BY total_views DESC
LIMIT 5;
ownerdisplayname | total_views
-------------------------+-------------
Joan Venge | 25520387
Ray Vega | 21576470
Tim | 18283579
J. Pablo Fernández | 12446818
Matt | 12298764
Time: 107620.508 ms (01:47.621)
가장 많은 views를 받은 tags:
--ClickHouse
SELECT arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tags,
sum(ViewCount) AS views
FROM posts
GROUP BY tags
ORDER BY views DESC
LIMIT 5
┌─tags───────┬──────views─┐
│ javascript │ 8190916894 │
│ python │ 8175132834 │
│ java │ 7258379211 │
│ c# │ 5476932513 │
│ android │ 4258320338 │
└────────────┴────────────┘
5 rows in set. Elapsed: 0.908 sec. Processed 59.82 million rows, 1.45 GB (65.87 million rows/s., 1.59 GB/s.)
--Postgres
WITH tags_exploded AS (
SELECT
unnest(string_to_array(Tags, '|')) AS tag,
ViewCount
FROM public.posts
),
filtered_tags AS (
SELECT
tag,
ViewCount
FROM tags_exploded
WHERE tag <> ''
)
SELECT tag AS tags,
SUM(ViewCount) AS views
FROM filtered_tags
GROUP BY tag
ORDER BY views DESC
LIMIT 5;
tags | views
------------+------------
javascript | 7974880378
python | 7972340763
java | 7064073461
c# | 5308656277
android | 4186216900
(5 rows)
Time: 112508.083 ms (01:52.508)
집계 함수
가능한 경우 ClickHouse 집계 함수를 활용해야 합니다. 아래에서는 매년 가장 많이 조회된 질문을 구하기 위해 argMax 집계 함수를 사용하는 방법을 보여 줍니다.
--ClickHouse
SELECT toYear(CreationDate) AS Year,
argMax(Title, ViewCount) AS MostViewedQuestionTitle,
max(ViewCount) AS MaxViewCount
FROM stackoverflow.posts
WHERE PostTypeId = 'Question'
GROUP BY Year
ORDER BY Year ASC
FORMAT Vertical
Row 1:
──────
Year: 2008
MostViewedQuestionTitle: How to find the index for a given item in a list?
MaxViewCount: 6316987
Row 2:
──────
Year: 2009
MostViewedQuestionTitle: How do I undo the most recent local commits in Git?
MaxViewCount: 13962748
...
Row 16:
───────
Year: 2023
MostViewedQuestionTitle: How do I solve "error: externally-managed-environment" every time I use pip 3?
MaxViewCount: 506822
Row 17:
───────
Year: 2024
MostViewedQuestionTitle: Warning "Third-party cookie will be blocked. Learn more in the Issues tab"
MaxViewCount: 66975
17 rows in set. Elapsed: 0.677 sec. Processed 24.37 million rows, 1.86 GB (36.01 million rows/s., 2.75 GB/s.)
Peak memory usage: 554.31 MiB.
이는 동일한 Postgres 쿼리보다 훨씬 더 단순하고(또한 더 빠릅니다).
--Postgres
WITH yearly_views AS (
SELECT
EXTRACT(YEAR FROM CreationDate) AS Year,
Title,
ViewCount,
ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM CreationDate) ORDER BY ViewCount DESC) AS rn
FROM public.posts
WHERE PostTypeId = 1
)
SELECT
Year,
Title AS MostViewedQuestionTitle,
ViewCount AS MaxViewCount
FROM yearly_views
WHERE rn = 1
ORDER BY Year;
year | mostviewedquestiontitle | maxviewcount
------+-----------------------------------------------------------------------------------------------------------------------+--------------
2008 | How to find the index for a given item in a list? | 6316987
2009 | How do I undo the most recent local commits in Git? | 13962748
...
2023 | How do I solve "error: externally-managed-environment" every time I use pip 3? | 506822
2024 | Warning "Third-party cookie will be blocked. Learn more in the Issues tab" | 66975
(17 rows)
Time: 125822.015 ms (02:05.822)
조건문과 배열
조건문과 배열 함수는 쿼리를 크게 단순화합니다. 다음 쿼리는 2022년 대비 2023년에 발생 횟수가 10,000번을 초과하는 태그들 가운데 발생 횟수의 백분율 증가가 가장 큰 태그를 계산합니다. 다음 ClickHouse 쿼리가 조건문과 배열 함수, 그리고 HAVING 및 SELECT 절에서 별칭을 재사용하는 기능 덕분에 얼마나 간결한지에 주목하십시오.
--ClickHouse
SELECT arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag,
countIf(toYear(CreationDate) = 2023) AS count_2023,
countIf(toYear(CreationDate) = 2022) AS count_2022,
((count_2023 - count_2022) / count_2022) * 100 AS percent_change
FROM stackoverflow.posts
WHERE toYear(CreationDate) IN (2022, 2023)
GROUP BY tag
HAVING (count_2022 > 10000) AND (count_2023 > 10000)
ORDER BY percent_change DESC
LIMIT 5
┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐
│ next.js │ 13788 │ 10520 │ 31.06463878326996 │
│ spring-boot │ 16573 │ 17721 │ -6.478189718413183 │
│ .net │ 11458 │ 12968 │ -11.644046884639112 │
│ azure │ 11996 │ 14049 │ -14.613139725247349 │
│ docker │ 13885 │ 16877 │ -17.72826924216389 │
└─────────────┴────────────┴────────────┴─────────────────────┘
5 rows in set. Elapsed: 0.247 sec. Processed 5.08 million rows, 155.73 MB (20.58 million rows/s., 630.61 MB/s.)
Peak memory usage: 403.04 MiB.
--Postgres
SELECT
tag,
SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) AS count_2023,
SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) AS count_2022,
((SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) - SUM(CASE WHEN year = 2022 THEN count ELSE 0 END))
/ SUM(CASE WHEN year = 2022 THEN count ELSE 0 END)::float) * 100 AS percent_change
FROM (
SELECT
unnest(string_to_array(Tags, '|')) AS tag,
EXTRACT(YEAR FROM CreationDate) AS year,
COUNT(*) AS count
FROM public.posts
WHERE EXTRACT(YEAR FROM CreationDate) IN (2022, 2023)
AND Tags <> ''
GROUP BY tag, year
) AS yearly_counts
GROUP BY tag
HAVING SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) > 10000
AND SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) > 10000
ORDER BY percent_change DESC
LIMIT 5;
tag | count_2023 | count_2022 | percent_change
-------------+------------+------------+---------------------
next.js | 13712 | 10370 | 32.22757955641273
spring-boot | 16482 | 17474 | -5.677005837243905
.net | 11376 | 12750 | -10.776470588235295
azure | 11938 | 13966 | -14.520979521695546
docker | 13832 | 16701 | -17.178612059158134
(5 rows)
Time: 116750.131 ms (01:56.750)
3부는 여기에서 확인하십시오
