프로젝션
프로젝션 (Projections)
ClickHouse는 대량의 데이터에 대한 실시간 시나리오에서 분석 쿼리를 가속화하는 다양한 메커니즘을 제공해요. 쿼리를 빠르게 만드는 그러한 메커니즘 중 하나가 *프로젝션(Projections)*입니다.
출처: 문서
본문
소개 (Introduction)
ClickHouse는 실시간 시나리오에서 대량의 데이터에 대한 분석 쿼리를 가속화하는 다양한 메커니즘을 제공합니다. 쿼리를 빠르게 만드는 그러한 메커니즘 중 하나가 프로젝션의 사용입니다. 프로젝션은 관심 있는 속성별로 데이터의 재정렬을 만들어 쿼리를 최적화하는 데 도움을 줍니다. 이는 다음과 같을 수 있습니다:
- 완전한 재정렬
- 다른 순서를 가진 원본 테이블의 부분집합
- 집계에 정렬된 미리 계산된 집계(머티리얼라이즈드 뷰와 유사)이지만 집계에 정렬이 맞춰진 것.
프로젝션은 어떻게 동작하나요? (How do Projections work?)
실질적으로 프로젝션은 원본 테이블에 대한 추가적이고 숨겨진 테이블로 생각할 수 있습니다. 프로젝션은 원본 테이블과 다른 행 순서, 따라서 다른 primary index를 가질 수 있고, 집계 값을 자동으로 그리고 증분적으로 미리 계산할 수 있습니다. 그 결과 프로젝션 사용은 쿼리 실행을 가속화하는 두 개의 "튜닝 노브(tuning knobs)"를 제공합니다:
- primary index를 올바르게 사용하기
- 집계 미리 계산하기
프로젝션은 어떤 면에서 머티리얼라이즈드 뷰와 유사합니다. 그것들도 여러 행 순서를 가질 수 있고 insert 시점에 집계를 미리 계산할 수 있기 때문입니다.
프로젝션은 원본 테이블과 자동으로 업데이트되고 동기화를 유지합니다. 명시적으로 업데이트되는 머티리얼라이즈드 뷰와는 다릅니다. 쿼리가 원본 테이블을 대상으로 할 때, ClickHouse는 자동으로 primary key를 샘플링하고, 동일한 올바른 결과를 생성할 수 있지만 읽어야 할 데이터 양이 가장 적은 테이블을 선택합니다.
_part_offset으로 더 스마트한 저장 (Smarter storage with _part_offset)
25.5 버전부터 ClickHouse는 프로젝션에서 가상 컬럼 _part_offset을 지원하며, 이는 프로젝션을 정의하는 새로운 방법을 제공합니다.
이제 프로젝션을 정의하는 두 가지 방법이 있습니다:
- 전체 컬럼 저장(원래 동작): 프로젝션이 전체 데이터를 포함하고 직접 읽을 수 있어, 필터가 프로젝션의 정렬 순서와 일치할 때 더 빠른 성능을 제공합니다.
- 정렬 키 +
_part_offset만 저장: 프로젝션은 인덱스처럼 동작합니다. ClickHouse는 프로젝션의 primary index를 사용해 일치하는 행을 찾지만, 실제 데이터는 기본 테이블에서 읽습니다. 이는 쿼리 시점에 약간 더 많은 I/O를 대가로 스토리지 오버헤드를 줄입니다.
위 접근 방식은 혼합될 수도 있습니다. 일부 컬럼은 프로젝션에 저장하고, 다른 컬럼은 _part_offset를 통해 간접적으로 저장하는 방식입니다.
언제 프로젝션을 사용할까? (When to use Projections?)
프로젝션은 데이터가 삽입될 때 자동으로 유지되므로 새 사용자에게 매력적인 기능입니다. 게다가 쿼리를 단일 테이블로 보내기만 하면, 가능한 곳에서 프로젝션이 활용되어 응답 시간을 단축합니다.
이는 머티리얼라이즈드 뷰와 대조적입니다. 머티리얼라이즈드 뷰에서는 필터에 따라 적절한 최적화된 대상 테이블을 선택하거나 쿼리를 재작성해야 합니다. 이는 사용자 애플리케이션에 더 큰 부담을 주고 클라이언트 측 복잡성을 증가시킵니다.
이러한 장점에도 불구하고 프로젝션은 몇 가지 내재된 제한이 있으므로, 이를 알고 있어야 하며 아껴서 배포해야 합니다.
- 프로젝션은 원본 테이블과 (숨겨진) 대상 테이블에 서로 다른 TTL을 허용하지 않습니다. 머티리얼라이즈드 뷰는 서로 다른 TTL을 허용합니다.
- 프로젝션이 있는 테이블에서는 경량 업데이트와 삭제가 지원되지 않습니다.
- 머티리얼라이즈드 뷰는 체이닝할 수 있습니다: 한 머티리얼라이즈드 뷰의 대상 테이블이 다른 머티리얼라이즈드 뷰의 원본 테이블이 될 수 있습니다. 프로젝션에서는 이것이 불가능합니다.
- 프로젝션 정의는 조인을 지원하지 않지만, 머티리얼라이즈드 뷰는 지원합니다. 다만 프로젝션이 있는 테이블에 대한 쿼리는 자유롭게 조인할 수 있습니다.
- 프로젝션 정의는 필터(
WHERE절)를 지원하지 않지만, 머티리얼라이즈드 뷰는 지원합니다. 다만 프로젝션이 있는 테이블에 대한 쿼리는 자유롭게 필터링할 수 있습니다.
다음과 같은 경우 프로젝션 사용을 권장합니다:
- 데이터의 완전한 재정렬이 필요한 경우. 이론적으로 프로젝션의 표현식은
GROUP BY를 사용할 수 있지만, 집계를 유지하는 데는 머티리얼라이즈드 뷰가 더 효과적입니다. 쿼리 최적화기도 단순 재정렬(즉,SELECT * ORDER BY x)을 사용하는 프로젝션을 활용할 가능성이 더 높습니다. 이 표현식에서 컬럼 부분집합을 선택해 저장 공간을 줄일 수 있습니다. - 사용자가 잠재적인 스토리지 증가와 데이터를 두 번 쓰는 오버헤드를 받아들일 수 있는 경우. 삽입 속도에 미치는 영향을 테스트하고 스토리지 오버헤드를 평가하세요.
예시 (Examples)
primary key에 없는 컬럼으로 필터링하기 (Filtering on columns which aren't in the primary key)
이 예시에서는 테이블에 프로젝션을 추가하는 방법을 보여드리겠습니다. 또한 프로젝션이 테이블의 primary key에 없는 컬럼으로 필터링하는 쿼리를 가속화하는 데 어떻게 사용될 수 있는지도 살펴볼 것입니다.
이 예시를 위해 sql.clickhouse.com에서 제공되는 New York Taxi Data 데이터셋을 사용할 것이며, 이는 pickup_datetime으로 정렬되어 있습니다.
승객이 운전자에게 $200 이상 팁을 준 모든 여행 ID를 찾는 간단한 쿼리를 작성해 봅시다.
ORDER BY에 없는 tip_amount로 필터링하기 때문에 ClickHouse가 전체 테이블 스캔을 해야 했다는 점을 주목하세요. 이 쿼리를 가속화해 봅시다.
원본 테이블과 결과를 보존하기 위해 새 테이블을 만들고 INSERT INTO SELECT로 데이터를 복사합니다:
CREATE TABLE nyc_taxi.trips_with_projection AS nyc_taxi.trips;
INSERT INTO nyc_taxi.trips_with_projection SELECT * FROM nyc_taxi.trips;
프로젝션을 추가하려면 ALTER TABLE 문과 함께 ADD PROJECTION 문을 사용합니다:
ALTER TABLE nyc_taxi.trips_with_projection
ADD PROJECTION prj_tip_amount
(
SELECT *
ORDER BY tip_amount, dateDiff('minutes', pickup_datetime, dropoff_datetime)
)
프로젝션을 추가한 후에는 MATERIALIZE PROJECTION 문을 사용해 그 안의 데이터가 위 지정 쿼리에 따라 물리적으로 정렬되고 재작성되도록 해야 합니다:
ALTER TABLE nyc.trips_with_projection MATERIALIZE PROJECTION prj_tip_amount
이제 프로젝션을 추가했으니 쿼리를 다시 실행해 봅시다:
쿼리 시간을 크게 줄였고, 더 적은 행을 스캔할 수 있었음을 주목하세요.
위 쿼리가 실제로 우리가 만든 프로젝션을 사용했는지 system.query_log 테이블을 쿼리해 확인할 수 있습니다:
SELECT query, projections
FROM system.query_log
WHERE query_id='<query_id>'
┌─query─────────────────────────────────────────────────────────────────────────┬─projections──────────────────────┐
│ SELECT ↴│ ['default.trips.prj_tip_amount'] │
│↳ tip_amount, ↴│ │
│↳ trip_id, ↴│ │
│↳ dateDiff('minutes', pickup_datetime, dropoff_datetime) AS trip_duration_min↴│ │
│↳FROM trips WHERE tip_amount > 200 AND trip_duration_min > 0 │ │
└───────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────┘
프로젝션으로 UK 주택 가격 지불 쿼리 가속화하기 (Using projections to speed up UK price paid queries)
프로젝션이 쿼리 성능을 어떻게 가속화할 수 있는지 보여주기 위해, 실제 데이터셋을 사용하는 예시를 살펴봅시다. 이 예시에서는 30.03백만 행이 있는 UK Property Price Paid 튜토리얼의 테이블을 사용할 것입니다. 이 데이터셋은 sql.clickhouse.com 환경에서도 사용할 수 있습니다.
테이블이 어떻게 만들어지고 데이터가 삽입되었는지 보려면 "The UK property prices dataset" 페이지를 참고하세요.
이 데이터셋에서 두 개의 간단한 쿼리를 실행할 수 있습니다. 첫 번째는 가장 높은 지불 가격을 가진 런던의 카운티를 나열하고, 두 번째는 카운티들의 평균 가격을 계산합니다:
테이블을 만들 때 ORDER BY 문에 town이나 price가 없었기 때문에, 두 쿼리 모두 매우 빠르지만 30.03백만 행 전체의 테이블 스캔이 발생했다는 점을 주목하세요:
CREATE TABLE uk.uk_price_paid
(
...
)
ENGINE = MergeTree
ORDER BY (postcode1, postcode2, addr1, addr2);
프로젝션을 사용해 이 쿼리를 가속화할 수 있는지 봅시다.
원본 테이블과 결과를 보존하기 위해 새 테이블을 만들고 INSERT INTO SELECT로 데이터를 복사합니다:
CREATE TABLE uk.uk_price_paid_with_projections AS uk_price_paid;
INSERT INTO uk.uk_price_paid_with_projections SELECT * FROM uk.uk_price_paid;
prj_oby_town_price 프로젝션을 만들고 채웁니다. 이는 특정 town에서 가장 높은 지불 가격을 가진 카운티를 나열하는 쿼리를 최적화하기 위해 town과 price로 정렬하는 primary index를 가진 추가(숨겨진) 테이블을 만듭니다:
ALTER TABLE uk.uk_price_paid_with_projections
(ADD PROJECTION prj_obj_town_price
(
SELECT *
ORDER BY
town,
price
))
ALTER TABLE uk.uk_price_paid_with_projections
(MATERIALIZE PROJECTION prj_obj_town_price)
SETTINGS mutations_sync = 1
mutations_sync 설정은 동기 실행을 강제하는 데 사용됩니다.
prj_gby_county 프로젝션을 만들고 채웁니다 — 기존의 130개 영국 카운티 모두에 대해 avg(price) 집계 값을 증분적으로 미리 계산하는 추가(숨겨진) 테이블입니다:
ALTER TABLE uk.uk_price_paid_with_projections
(ADD PROJECTION prj_gby_county
(
SELECT
county,
avg(price)
GROUP BY county
))
ALTER TABLE uk.uk_price_paid_with_projections
(MATERIALIZE PROJECTION prj_gby_county)
SETTINGS mutations_sync = 1
위 prj_gby_county 프로젝션처럼 프로젝션에 GROUP BY 절이 사용되면, (숨겨진) 테이블의 기본 스토리지 엔진은 AggregatingMergeTree가 되고, 모든 집계 함수는 AggregateFunction으로 변환됩니다. 이는 올바른 증분 데이터 집계를 보장합니다.
아래 그림은 메인 테이블 uk_price_paid_with_projections과 두 프로젝션의 시각화입니다:
이제 가장 높은 지불 가격 세 개의 런던 카운티를 나열하는 쿼리를 다시 실행하면 쿼리 성능이 개선된 것을 볼 수 있습니다:
마찬가지로, 평균 지불 가격이 가장 높은 세 개의 영국 카운티를 나열하는 쿼리도:
두 쿼리 모두 원본 테이블을 대상으로 하며, 두 프로젝션을 만들기 전에는 두 쿼리 모두 전체 테이블 스캔(30.03백만 행 전체가 디스크에서 스트리밍됨)이 발생했음을 주목하세요.
또한 가장 높은 지불 가격 세 개의 런던 카운티를 나열하는 쿼리는 2.17백만 행을 스트리밍하고 있습니다. 이 쿼리에 최적화된 두 번째 테이블을 직접 사용했을 때는 81.92천 행만 디스크에서 스트리밍되었습니다.
차이의 이유는 현재 위에서 언급한 optimize_read_in_order 최적화가 프로젝션에서 지원되지 않기 때문입니다.
system.query_log 테이블을 검사해 ClickHouse가 위 두 쿼리에 대해 두 프로젝션을 자동으로 사용했음을 확인합니다(아래 projections 컬럼 참고):
SELECT
tables,
query,
query_duration_ms::String || ' ms' AS query_duration,
formatReadableQuantity(read_rows) AS read_rows,
projections
FROM clusterAllReplicas(default, system.query_log)
WHERE (type = 'QueryFinish') AND (tables = ['default.uk_price_paid_with_projections'])
ORDER BY initial_query_start_time DESC
LIMIT 2
FORMAT Vertical
Row 1:
──────
tables: ['uk.uk_price_paid_with_projections']
query: SELECT
county,
avg(price)
FROM uk_price_paid_with_projections
GROUP BY county
ORDER BY avg(price) DESC
LIMIT 3
query_duration: 5 ms
read_rows: 132.00
projections: ['uk.uk_price_paid_with_projections.prj_gby_county']
Row 2:
──────
tables: ['uk.uk_price_paid_with_projections']
query: SELECT
county,
price
FROM uk_price_paid_with_projections
WHERE town = 'LONDON'
ORDER BY price DESC
LIMIT 3
SETTINGS log_queries=1
query_duration: 11 ms
read_rows: 2.29 million
projections: ['uk.uk_price_paid_with_projections.prj_obj_town_price']
2 rows in set. Elapsed: 0.006 sec.
추가 예시 (Further examples)
다음 예시들은 같은 UK 가격 데이터셋을 사용하며, 프로젝션이 있는 쿼리와 없는 쿼리를 대조합니다.
원본 테이블(과 성능)을 보존하기 위해 CREATE AS와 INSERT INTO SELECT로 테이블의 사본을 다시 만듭니다.
CREATE TABLE uk.uk_price_paid_with_projections_v2 AS uk.uk_price_paid;
INSERT INTO uk.uk_price_paid_with_projections_v2 SELECT * FROM uk.uk_price_paid;
프로젝션 만들기 (Build a Projection)
year, district, town 차원으로 집계 프로젝션을 만들어 봅시다.
이 프로젝션은 각 판매 연도별로 판매를 그룹화하므로, 필터에서 참조할 수 있도록 그 표현식을 컬럼으로 materialize합니다:
ALTER TABLE uk.uk_price_paid_with_projections_v2
ADD COLUMN year UInt16 MATERIALIZED toYear(date)
ALTER TABLE uk.uk_price_paid_with_projections_v2
ADD PROJECTION projection_by_year_district_town
(
SELECT
year,
district,
town,
avg(price),
sum(price),
count()
GROUP BY
year,
district,
town
)
기존 데이터에 대한 프로젝션을 채웁니다. (materialize하지 않으면 프로젝션은 새로 삽입된 데이터에 대해서만 생성됩니다):
ALTER TABLE uk.uk_price_paid_with_projections_v2
MATERIALIZE PROJECTION projection_by_year_district_town
SETTINGS mutations_sync = 1
다음 쿼리들은 프로젝션이 있는 경우와 없는 경우의 성능을 대조합니다. 프로젝션 사용을 비활성화하려면 기본 활성화된 optimize_use_projections 설정을 사용합니다.
쿼리 1. 연도별 평균 가격 (Query 1. Average price per year)
결과는 같아야 하지만, 후자 예시에서 성능이 더 좋습니다!
쿼리 2. 런던의 연도별 평균 가격 (Query 2. Average price per year in London)
쿼리 3. 가장 비싼 동네 (Query 3. The most expensive neighborhoods)
2020년 이후 행에 대한 필터는 저장된 프로젝션 차원 year를 직접 참조합니다:
다시 말하지만 결과는 같지만 2번째 쿼리의 쿼리 성능 개선을 주목하세요.
필터는 저장된 차원 year를 직접 참조해야 합니다. WHERE toYear(date) >= 2020을 작성하면 프로젝션을 사용하지 않습니다: 최적화기가 조건을 date에 대한 범위로 재작성하고, 프로젝션은 date 컬럼을 저장하지 않으므로 ClickHouse는 기본 테이블 스캔으로 대체합니다.
하나의 쿼리에서 프로젝션 결합하기 (Combining projections in one query)
25.6 버전부터, 이전 버전에서 도입된 _part_offset 지원을 기반으로, ClickHouse는 이제 여러 필터가 있는 단일 쿼리를 가속화하기 위해 여러 프로젝션을 사용할 수 있습니다.
중요하게도, ClickHouse는 여전히 하나의 프로젝션(또는 기본 테이블)에서만 데이터를 읽지만, 다른 프로젝션의 primary index를 사용해 읽기 전에 불필요한 파츠를 프루닝할 수 있습니다. 이는 각각 다른 프로젝션과 일치할 수 있는 여러 컬럼으로 필터링하는 쿼리에 특히 유용합니다.
현재 이 메커니즘은 전체 파츠만 프루닝합니다. Granule 수준 프루닝은 아직 지원되지 않습니다.
이를 설명하기 위해, 테이블을 정의하고(위 그림과 일치하는 _part_offset 컬럼을 사용하는 프로젝션 포함) 다섯 개의 예시 행을 삽입합니다.
CREATE TABLE page_views
(
id UInt64,
event_date Date,
user_id UInt32,
url String,
region String,
PROJECTION region_proj
(
SELECT _part_offset ORDER BY region
),
PROJECTION user_id_proj
(
SELECT _part_offset ORDER BY user_id
)
)
ENGINE = MergeTree
ORDER BY (event_date, id)
SETTINGS
index_granularity = 1, -- one row per granule
max_bytes_to_merge_at_max_space_in_pool = 1; -- disable merge
그런 다음 테이블에 데이터를 삽입합니다:
INSERT INTO page_views VALUES (
1, '2025-07-01', 101, 'https://example.com/page1', 'europe');
INSERT INTO page_views VALUES (
2, '2025-07-01', 102, 'https://example.com/page2', 'us_west');
INSERT INTO page_views VALUES (
3, '2025-07-02', 106, 'https://example.com/page3', 'us_west');
INSERT INTO page_views VALUES (
4, '2025-07-02', 107, 'https://example.com/page4', 'us_west');
INSERT INTO page_views VALUES (
5, '2025-07-03', 104, 'https://example.com/page5', 'asia');
참고: 이 테이블은 예시를 위해 한 행 granule과 파트 병합 비활성화 같은 커스텀 설정을 사용하며, 이는 프로덕션 사용에 권장되지 않습니다.
이 설정은 다음을 생성합니다:
- 다섯 개의 개별 파츠(삽입된 행당 하나)
- 행당 하나의 primary index 항목(기본 테이블과 각 프로젝션에서)
- 각 파츠는 정확히 하나의 행을 포함
이 설정으로 region과 user_id 둘 다로 필터링하는 쿼리를 실행합니다.
기본 테이블의 primary index가 event_date와 id로 만들어졌으므로 여기서는 도움이 되지 않습니다. 따라서 ClickHouse는 다음을 사용합니다:
region_proj로 region별 파츠 프루닝user_id_proj로user_id에 따라 추가 프루닝
이 동작은 ClickHouse가 프로젝션을 어떻게 선택하고 적용하는지 보여주는 EXPLAIN projections = 1로 확인할 수 있습니다.
EXPLAIN projections=1
SELECT * FROM page_views WHERE region = 'us_west' AND user_id = 107;
┌─explain────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Expression │
3. │ ReadFromMergeTree (default.page_views) │
4. │ Projections: │
5. │ Name: region_proj │
6. │ Description: Projection has been analyzed and is used for part-level filtering │
7. │ Condition: (region in ['us_west', 'us_west']) │
8. │ Search Algorithm: binary search │
9. │ Parts: 3 │
10. │ Marks: 3 │
11. │ Ranges: 3 │
12. │ Rows: 3 │
13. │ Filtered Parts: 2 │
14. │ Name: user_id_proj │
15. │ Description: Projection has been analyzed and is used for part-level filtering │
16. │ Condition: (user_id in [107, 107]) │
17. │ Search Algorithm: binary search │
18. │ Parts: 1 │
19. │ Marks: 1 │
20. │ Ranges: 1 │
21. │ Rows: 1 │
22. │ Filtered Parts: 2 │
└────────────────────────────────────────────────────────────────────────────────────────┘
위 EXPLAIN 출력은 위에서 아래로 논리적 쿼리 플랜을 보여줍니다:
| 행 번호 | 설명 |
|---|---|
| 3 | page_views 기본 테이블에서 읽을 계획 |
| 5-13 | region_proj를 사용해 region = 'us_west'인 3개 파츠를 식별하고, 5개 중 2개 파츠를 프루닝 |
| 14-22 | user_id_proj를 사용해 user_id = 107인 1개 파츠를 식별하고, 남은 3개 중 2개 파츠를 추가 프루닝 |
결국 기본 테이블에서 5개 중 단 1개의 파츠만 읽힙니다.
여러 프로젝션의 인덱스 분석을 결합함으로써 ClickHouse는 스캔되는 데이터 양을 크게 줄여, 스토리지 오버헤드를 낮게 유지하면서 성능을 개선합니다.