DELETE 매커니즘과 성능 비교
🗑️

DELETE 매커니즘과 성능 비교

ClickHouse 분류
Core Architecture
Type
Lab
작성자

Ken

ClickHouse는 OLAP에서 시작하였지만, 지속적으로 업데이트 기능을 개선해오면서 데이터 삭제 기능을 개선해왔습니다.

ClickHouse Upsert 메커니즘의 발전 과정

본 실습에서는 1,000,000 rows를 생성하고 100,000 rows (10%, user_id % 10 = 0 조건) 삭제시 삭제 방법에 따른 구현 및 성능 차이를 확인해 보겠습니다.

  • 실습 개요
  • 리소스 사용 비교 (직관적 시각화)
  • 핵심 결과
  • 주요 인사이트
  • 테스트 메서드 상세
  • 1. ALTER TABLE DELETE (SharedMergeTree)
  • 2. ReplacingMergeTree with is_deleted Flag
  • 3. CollapsingMergeTree with Sign Column
  • 성능 벤치마크 결과
  • 1. 스토리지 효율성
  • 2. DELETE 작업 성능
  • 3. SELECT 쿼리 성능 (Aggregation)
  • 4. 시계열 집계 쿼리 성능
  • 5. 데이터 정확성 비교
  • 실제 워크로드 시뮬레이션
  • 시나리오 1: 단순 COUNT 쿼리
  • 시나리오 2: Event Type별 집계
  • 시나리오 3: 시계열 분석
  • 장단점 종합 분석
  • ALTER TABLE DELETE
  • ReplacingMergeTree
  • CollapsingMergeTree
  • 성능 비교 요약 테이블
  • SELECT 쿼리 성능
  • 스토리지 효율성
  • 운영 특성
  • 시나리오별 권장사항
  • 1. 대규모 배치 삭제 (GDPR, Data Retention)
  • 2. 실시간 사용자 데이터 삭제 (User Account Deletion)
  • 3. 감사 추적이 필요한 삭제 (Financial, Healthcare)
  • 4. 고빈도 Counter/Metrics 업데이트
  • 5. 낮은 삭제 빈도 + 높은 쿼리 빈도
  • 6. 높은 삭제 빈도 + 높은 쿼리 빈도
  • 실전 성능 비교 치트시트
  • 삭제 작업 시간
  • 복잡한 집계 쿼리
  • 메모리 사용량
  • 스토리지 사용량
  • 결론 및 최종 권장사항
  • 종합 평가
  • 최종 권장사항
  • 성능 수치 요약

실습 개요

ClickHouse에서 데이터 삭제를 처리하는 세 가지 주요 메커니즘의 성능을 실제 워크로드를 통해 비교 분석했습니다.

  • ALTER Table DELETE
  • CollapsingMergeTree
  • ReplacingMergeTree

리소스 사용 비교 (직관적 시각화)

주요 인사이트:

  • 쿼리 성능: ALTER DELETE가 3.8배 빠름 (ReplacingMergeTree 대비)
  • 스토리지: ALTER DELETE가 26% 더 효율적
  • 메모리: ReplacingMergeTree가 3배 더 많이 사용
  • 삭제 속도: CollapsingMergeTree가 가장 빠름 (실시간 반영)
  • ALTER DELETE는 비동기 처리 (초기 응답 시간만 측정)

핵심 결과

Metric
ALTER DELETE
ReplacingMergeTree
CollapsingMergeTree
삭제 후 Visible Rows
810,000
900,000
900,000
실제 저장 Rows
810,000
1,100,000
1,100,000
압축 데이터 크기
7.11 MiB
9.56 MiB
9.32 MiB
평균 쿼리 시간
23.5 ms
90.0 ms (FINAL)
27.5 ms
평균 메모리 사용
24.94 MiB
75.66 MiB
32.54 MiB
Parts 수
13
26
26

주요 인사이트

ALTER DELETE는 물리적 삭제로 가장 적은 스토리지 사용과 가장 빠른 쿼리 성능을 제공합니다.

ReplacingMergeTree는 FINAL 사용 시 쿼리 성능이 3.8배 느리며 메모리 사용량이 3배 더 높습니다.

CollapsingMergeTree는 ALTER DELETE와 유사한 쿼리 성능을 보이면서 실시간 삭제가 가능합니다.

테스트 메서드 상세

1. ALTER TABLE DELETE (SharedMergeTree)

물리적 삭제 방식으로, mutation을 통해 데이터를 완전히 제거합니다.

구현 방식:

ALTER TABLE alter_delete_table
DELETE WHERE user_id % 10 = 0

내부 구현 특징:

Mutation 프로세스: ALTER DELETE는 mutation 작업으로 처리됩니다. 이는 파티션의 데이터를 읽어서 조건에 맞지 않는 row만 새로운 파트에 작성하고, 기존 파트를 교체하는 방식입니다.

파트 재작성: 삭제 조건이 포함된 모든 파트를 읽어서 필터링 후 재작성합니다. 예를 들어, 100만 row 파트에서 10만 row를 삭제하면, 90만 row의 새 파트를 생성합니다.

비동기 실행: Mutation은 백그라운드 스레드 풀에서 실행됩니다. mutations_sync 설정으로 제어 가능하며 (0=비동기, 2=동기), 기본값은 비동기입니다.

파티션 병렬 처리: 여러 파티션에 걸친 삭제는 병렬로 처리될 수 있습니다. max_alter_threads 설정으로 병렬도 조정 가능합니다.

Mutation 큐: 각 mutation은 system.mutations 테이블에 기록되며, 순차적으로 실행됩니다. is_done=1이 되면 완료된 것입니다.

메모리 사용: 파트를 읽고 쓰는 과정에서 메모리를 사용하지만, 한 번에 하나의 파트만 처리하므로 메모리 사용이 제한적입니다.

특징: Mutation을 통한 비동기 처리, 파티션 전체를 재작성, 삭제된 데이터는 완전히 제거, 스토리지 공간 즉시 회수

2. ReplacingMergeTree with is_deleted Flag

논리적 삭제 방식으로, is_deleted 플래그를 사용합니다.

구현 방식:

내부 구현 특징:

Version 기반 중복 제거: ReplacingMergeTree는 ORDER BY 키가 동일한 row들 중에서 version 컬럼 값이 가장 큰 row만 유지합니다. 삭제는 더 큰 version으로 is_deleted=1인 row를 INSERT합니다.

머지 타이밍: 중복 제거는 백그라운드 머지 시에만 발생합니다. 머지 전까지는 모든 버전의 row가 디스크에 존재하므로, 동일한 데이터가 여러 번 저장됩니다.

FINAL Modifier: FINAL을 사용하면 쿼리 실행 시점에 중복 제거를 수행합니다. 이는 모든 파트를 읽어서 ORDER BY 키로 정렬하고, 각 키 그룹의 최신 버전만 반환합니다.

FINAL 성능 오버헤드: FINAL 사용 시 모든 파트를 스캔하고 정렬해야 하므로, 파트 수가 많을수록 성능이 크게 저하됩니다. 이 벤치마크에서는 26개 파트로 인해 3.8배 느려졌습니다.

머지 후 스토리지: 백그라운드 머지가 완료되면 중복된 row는 제거되지만, is_deleted=1인 최신 버전은 남아있습니다. 완전히 제거하려면 추가 작업이 필요합니다.

Optimize Table Final: OPTIMIZE TABLE ... FINAL을 실행하면 강제로 머지를 수행하여 중복을 제거할 수 있지만, 이 역시 리소스 집약적입니다.

특징: 빠른 INSERT 작업으로 삭제 마킹, FINAL modifier 필요 (정확한 결과), 삭제 히스토리 유지, 롤백 가능

3. CollapsingMergeTree with Sign Column

sign 컬럼을 사용한 논리적 삭제 방식입니다.

구현 방식:

내부 구현 특징:

Collapsing 메커니즘: ORDER BY 키가 동일하고 모든 컬럼 값이 같은 row 쌍 중에서 sign=1과 sign=-1이 있으면, 백그라운드 머지 시 두 row를 모두 제거합니다 (collapsing).

순서 의존성: sign=-1 row가 sign=1 row보다 나중에 INSERT되어야 합니다. 순서가 바뀌면 머지 후에도 row가 남을 수 있습니다. 이는 CollapsingMergeTree의 가장 큰 제약사항입니다.

즉시 집계 가능: 머지를 기다리지 않고 sum(sign)을 사용하면 즉시 정확한 카운트를 얻을 수 있습니다. sign=1과 sign=-1이 서로 상쇄되기 때문입니다.

값 집계: sum(value * sign)을 사용하면 삭제된 row의 값을 제외한 집계가 가능합니다. 이는 counter나 metrics 업데이트에 매우 효율적입니다.

머지 후 스토리지: 백그라운드 머지가 완료되면 상쇄된 row 쌍은 완전히 제거되어 스토리지 공간이 회수됩니다. 하지만 쌍이 안 맞는 row는 남습니다.

VersionedCollapsingMergeTree: 순서 문제를 해결하기 위해 version 컬럼을 추가한 엔진입니다. version이 높은 row를 우선 적용하므로 INSERT 순서와 무관하게 동작합니다.

쿼리 패턴: 모든 쿼리에서 sum(sign)을 사용해야 하므로, GROUP BY 쿼리가 복잡해집니다. HAVING sum(sign) > 0 조건도 필요합니다.

특징: sign=-1 row 추가로 삭제, sum(sign)으로 net count 계산, Incremental update 지원, 복잡한 쿼리 로직

성능 벤치마크 결과

1. 스토리지 효율성

Method
Stored Rows
Compressed Size
Uncompressed Size
Compression Ratio
Parts
ALTER DELETE
810,000
7.11 MiB
27.02 MiB
3.80x
13
ReplacingMergeTree
1,100,000
9.56 MiB
45.14 MiB
4.72x
26
CollapsingMergeTree
1,100,000
9.32 MiB
37.70 MiB
4.05x
26

분석: ALTER DELETE는 26% 더 적은 스토리지 사용 (7.11 MiB vs 9.32-9.56 MiB). ReplacingMergeTree가 가장 많은 공간 사용 (is_deleted 컬럼 추가). ALTER DELETE는 50% 더 적은 Parts 보유 (13 vs 26).

2. DELETE 작업 성능

Method
Operation Type
Avg Duration
Read Rows
Written Bytes
Mechanism
ALTER DELETE
Mutation
34.8 ms*
0
0
Async rewrite
ReplacingMergeTree
INSERT
530.0 ms
95,578
4.46 MiB
Sync insert
CollapsingMergeTree
INSERT
435.0 ms
95,578
4.46 MiB
Sync insert
  • 초기 응답 시간, 실제 mutation은 백그라운드 실행

분석: ALTER DELETE는 즉각 응답하지만 백그라운드에서 처리. ReplacingMergeTree INSERT가 가장 느림 (530ms). CollapsingMergeTree가 18% 더 빠른 INSERT (435ms vs 530ms).

3. SELECT 쿼리 성능 (Aggregation)

Method
Execution Count
Avg Duration
Min
Max
Avg Read Rows
Avg Memory
ALTER DELETE
2
23.5 ms
13 ms
34 ms
483,291
24.94 MiB
COLLAPSING
2
27.5 ms
15 ms
40 ms
500,154
32.54 MiB
REPLACING (FINAL)
3
90.0 ms
35 ms
138 ms
767,465
75.66 MiB

분석: ALTER DELETE가 가장 빠른 쿼리 성능 (23.5ms). ReplacingMergeTree FINAL은 3.8배 느림 (90.0ms vs 23.5ms). ReplacingMergeTree는 3배 더 많은 메모리 사용 (75.66 MiB vs 24.94 MiB). CollapsingMergeTree는 ALTER DELETE와 17% 차이만 (27.5ms vs 23.5ms).

4. 시계열 집계 쿼리 성능

복잡한 GROUP BY 쿼리 (toStartOfHour + event_type 집계):

Method
Duration
Read Rows
Read Bytes
Memory Usage
ALTER DELETE
13 ms
66,582
1.63 MiB
14.98 MiB
CollapsingMergeTree
15 ms
90,420
2.29 MiB
17.31 MiB
ReplacingMergeTree (FINAL)
35 ms
90,420
2.59 MiB
15.21 MiB

분석: ALTER DELETE가 가장 적은 row 스캔 (66,582 rows). ReplacingMergeTree FINAL은 2.7배 느림 (35ms vs 13ms). CollapsingMergeTree는 15% 오버헤드만 존재 (15ms vs 13ms).

5. 데이터 정확성 비교

Method
Visible Rows
Stored Rows
Overhead
정확성
ALTER DELETE
810,000
810,000
0%
✅ 정확
REPLACING (No FINAL)
1,000,000
1,100,000
35.8%
❌ 부정확
REPLACING (FINAL)
900,000
1,000,000
11.1%
✅ 정확
COLLAPSING (Raw count)
1,000,000
1,100,000
22.2%
❌ 부정확
COLLAPSING (sum(sign))
900,000
1,100,000
22.2%
✅ 정확

실제 워크로드 시뮬레이션

시나리오 1: 단순 COUNT 쿼리

시나리오 2: Event Type별 집계

Method
Query Pattern
Result Correctness
Performance
ALTER DELETE
GROUP BY event_type
✅ 정확
🟢 빠름 (23.5ms)
REPLACING (No FINAL)
WHERE is_deleted=0 GROUP BY
❌ 부정확
🟡 중간 (40ms)
REPLACING (FINAL)
FINAL WHERE is_deleted=0 GROUP BY
✅ 정확
🔴 느림 (90ms)
COLLAPSING
GROUP BY HAVING sum(sign) > 0
✅ 정확
🟢 빠름 (27.5ms)

시나리오 3: 시계열 분석

쿼리: 시간대별 event 집계 (1개월 데이터)

ALTER DELETE: 13ms, COLLAPSING: 15ms, REPLACING FINAL: 35ms

성능 차이: REPLACING FINAL은 ALTER DELETE 대비 2.7배 느림

장단점 종합 분석

ALTER TABLE DELETE

✅ 장점: 최고의 쿼리 성능 (23.5ms), 최소 스토리지 (7.11 MiB, 26% 절약), 단순한 쿼리 (추가 로직 불필요), 정확한 결과 (FINAL 불필요), 최소 메모리 (24.94 MiB), 적은 Parts (13개)

❌ 단점: 비동기 처리 (Mutation 백그라운드 실행), 리소스 집약적 (대량 삭제 시 부하), 파티션 재작성 (전체 파티션 영향), 롤백 불가, 히스토리 없음

💡 최적 사용 사례: 배치 삭제 작업 (야간), GDPR 준수 (완전한 데이터 제거), 아카이빙, 스토리지 최적화 우선, 삭제 빈도가 낮은 경우

ReplacingMergeTree

✅ 장점: 빠른 삭제 작업 (530ms INSERT), 히스토리 추적 (version으로 변경 이력), 롤백 가능 (삭제 마커 제거), 실시간 삭제 (즉시 반영), 감사 추적 (삭제 기록 유지)

❌ 단점: 매우 느린 쿼리 (90ms, 3.8배 느림), 높은 메모리 (75.66 MiB, 3배 더 많음), 많은 스토리지 (9.56 MiB, 34% 더 많음), FINAL 필수 (없으면 부정확), 복잡한 쿼리 (FINAL 처리 오버헤드), 더 많은 Parts (26개)

💡 최적 사용 사례: Audit trail 필요, 삭제 롤백 요구사항, 규제 준수 (변경 이력), 쓰기 > 읽기 워크로드, 실시간 soft delete

CollapsingMergeTree

✅ 장점: 빠른 쿼리 (27.5ms, ALTER DELETE와 유사), 실시간 삭제 (435ms INSERT), 적절한 메모리 (32.54 MiB), Incremental update (버전 관리 가능), 빠른 집계 (sum(sign) 효율적)

❌ 단점: 복잡한 쿼리 (sum(sign), HAVING 필요), 더 많은 스토리지 (9.32 MiB, 31% 더 많음), 순서 의존성 (잘못된 INSERT 순서 문제), 학습 곡선 (개발자 교육 필요), 디버깅 어려움

💡 최적 사용 사례: 빈번한 update/delete, Counter/Metrics 데이터, 실시간 집계 필요, 성능과 스토리지 균형, Versioning 필요

성능 비교 요약 테이블

SELECT 쿼리 성능

Metric
ALTER DELETE
ReplacingMergeTree
CollapsingMergeTree
Winner
평균 Duration
23.5 ms
90.0 ms
27.5 ms
⭐ ALTER
읽은 Rows
483,291
767,465
500,154
⭐ ALTER
메모리 사용
24.94 MiB
75.66 MiB
32.54 MiB
⭐ ALTER
상대 성능
1.0x
3.8x
1.2x
⭐ ALTER

스토리지 효율성

Metric
ALTER DELETE
ReplacingMergeTree
CollapsingMergeTree
Winner
압축 크기
7.11 MiB
9.56 MiB
9.32 MiB
⭐ ALTER
저장 Rows
810,000
1,100,000
1,100,000
⭐ ALTER
Parts 수
13
26
26
⭐ ALTER
오버헤드
0%
35.8%
22.2%
⭐ ALTER
상대 크기
1.0x
1.34x
1.31x
⭐ ALTER

운영 특성

Aspect
ALTER DELETE
ReplacingMergeTree
CollapsingMergeTree
Winner
실시간 삭제
❌ 비동기
✅ 즉시
✅ 즉시
⭐ Replacing/Collapsing
롤백 가능
❌ 불가
✅ 가능
❌ 어려움
⭐ Replacing
감사 추적
❌ 없음
✅ 가능
❌ 제한적
⭐ Replacing
개발 복잡도
🟢 단순
🟡 중간
🔴 복잡
⭐ ALTER
운영 복잡도
🔴 높음
🟢 낮음
🟡 중간
⭐ Replacing

시나리오별 권장사항

1. 대규모 배치 삭제 (GDPR, Data Retention)

추천: ⭐ ALTER TABLE DELETE

이유: 가장 적은 스토리지 사용 (26% 절약), 가장 빠른 후속 쿼리 (23.5ms), 완전한 데이터 제거 (규제 준수)

주의사항: Off-peak 시간에 실행, 파티션 단위로 삭제 권장, mutation_sync=2로 동기 실행 가능

2. 실시간 사용자 데이터 삭제 (User Account Deletion)

추천: ⭐ CollapsingMergeTree

이유: 빠른 삭제 반영 (435ms INSERT), 우수한 쿼리 성능 (27.5ms, 17% 오버헤드만), 적절한 리소스 사용

대안: ReplacingMergeTree (히스토리 추적 필요 시)

3. 감사 추적이 필요한 삭제 (Financial, Healthcare)

추천: ⭐ ReplacingMergeTree

이유: 완전한 변경 이력 추적, 롤백 가능, 규제 준수 용이

주의사항: 읽기 쿼리에 항상 FINAL 사용, Materialized View로 최적화 고려

4. 고빈도 Counter/Metrics 업데이트

추천: ⭐ CollapsingMergeTree

이유: Incremental update 최적화, 빠른 집계 성능, 효율적인 versioning

5. 낮은 삭제 빈도 + 높은 쿼리 빈도

추천: ⭐ ALTER TABLE DELETE

이유: 최고의 쿼리 성능 (23.5ms), 최소 스토리지, 단순한 쿼리 로직

시나리오: 월별 아카이빙, 분기별 데이터 정리, 연간 retention policy

6. 높은 삭제 빈도 + 높은 쿼리 빈도

추천: ⭐ CollapsingMergeTree

이유: 실시간 삭제 + 우수한 쿼리 성능 균형, ALTER DELETE보다 17%만 느림, ReplacingMergeTree보다 3배 빠름

시나리오: 실시간 대시보드, 스트리밍 데이터 처리, IoT 센서 데이터

실전 성능 비교 치트시트

삭제 작업 시간

ALTER DELETE: 34.8 ms (응답) + 백그라운드 처리

ReplacingMerge: 530 ms (완료)

CollapsingMerge: 435 ms (완료)

Winner: ALTER DELETE (응답), ReplacingMerge/CollapsingMerge (실제 완료)

복잡한 집계 쿼리

ALTER DELETE: 23.5 ms (기준)

ReplacingMerge (FINAL): 90.0 ms (+283%)

CollapsingMerge: 27.5 ms (+17%)

Winner: ALTER DELETE > CollapsingMerge > ReplacingMerge

메모리 사용량

ALTER DELETE: 24.94 MiB (기준)

ReplacingMerge (FINAL): 75.66 MiB (+203%)

CollapsingMerge: 32.54 MiB (+30%)

Winner: ALTER DELETE > CollapsingMerge > ReplacingMerge

스토리지 사용량

ALTER DELETE: 7.11 MiB (기준)

CollapsingMerge: 9.32 MiB (+31%)

ReplacingMerge: 9.56 MiB (+34%)

Winner: ALTER DELETE > CollapsingMerge > ReplacingMerge

결론 및 최종 권장사항

종합 평가

Criterion
ALTER DELETE
ReplacingMergeTree
CollapsingMergeTree
쿼리 성능
⭐⭐⭐⭐⭐
⭐⭐
⭐⭐⭐⭐
삭제 속도
⭐⭐⭐
⭐⭐⭐⭐⭐
⭐⭐⭐⭐⭐
스토리지 효율
⭐⭐⭐⭐⭐
⭐⭐
⭐⭐⭐
메모리 효율
⭐⭐⭐⭐⭐
⭐⭐
⭐⭐⭐⭐
개발 단순성
⭐⭐⭐⭐⭐
⭐⭐⭐
⭐⭐
운영 단순성
⭐⭐
⭐⭐⭐⭐⭐
⭐⭐⭐
감사 추적
⭐
⭐⭐⭐⭐⭐
⭐⭐
실시간성
⭐⭐
⭐⭐⭐⭐⭐
⭐⭐⭐⭐⭐

최종 권장사항

기본 선택: CollapsingMergeTree - 대부분의 use case에서 좋은 균형. 실시간 삭제 + 우수한 쿼리 성능. 학습 곡선 있지만 투자 가치 있음.

성능 최우선: ALTER DELETE - 삭제 빈도가 낮은 경우. 쿼리 성능이 critical한 경우. 스토리지 비용 최소화.

감사/규제 준수: ReplacingMergeTree - 완전한 히스토리 필요. 롤백 요구사항. 쿼리 성능 희생 가능.

성능 수치 요약

쿼리 성능 (상대 비교): ALTER DELETE 1.0x (baseline), CollapsingMergeTree 1.2x, ReplacingMergeTree 3.8x

스토리지 효율 (상대 비교): ALTER DELETE 1.0x (baseline), CollapsingMergeTree 1.31x, ReplacingMergeTree 1.34x

메모리 사용 (상대 비교): ALTER DELETE 1.0x (baseline), CollapsingMergeTree 1.3x, ReplacingMergeTree 3.0x

이는 특정 기능 선택에 따른 다른 영향도를 고려하지 않고 단순한 삭제를 염두한 것으로, 실제 운영환경에 적용을 위한 상황에서는 충분한 검증을 통한 기능 선택이 필요합니다.

활용 코드:

clickhouse-hols/workload/delete-benchmark at main · litkhai/clickhouse-hols

ClickHouse Hands-on Labs . Contribute to litkhai/clickhouse-hols development by creating an account on GitHub.

github.com

clickhouse-hols/workload/delete-benchmark at main · litkhai/clickhouse-hols