수억 건 공공데이터 실시간 분석을 위한 ClickHouse MergeTree와 희소 인덱스 설계

1. 대용량 공공데이터 실시간 집계 시 RDBMS B-Tree의 한계

공공데이터포털(data.go.kr)에서 제공하는 건축물대장, 교통량 로그, 부동산 실거래가와 같은 공공데이터는 누적 레코드가 수억 건에서 수십억 건에 달합니다. 이러한 초대용량 시계열 데이터를 전통적인 RDBMS(MySQL, PostgreSQL)에 적재하고 실시간 필터링 및 그룹핑(GROUP BY) 집계를 수행하면 행 기반(Row-oriented) 저장 구조의 물리적 I/O 한계에 직면합니다.

B-Tree 인덱스는 단일 레코드 탐색(Point Query)에는 최적화되어 있으나, 수억 개 행의 특정 컬럼만 추출하여 평균이나 백분위수(Quantile)를 계산할 때 불필요한 전체 행 데이터를 디스크에서 메모리로 퍼올려야 하므로 버퍼 풀 고갈과 수십 초 이상의 쿼리 지연이 발생합니다.

2. ClickHouse MergeTree 엔진의 물리적 저장 구조와 백그라운드 병합

ClickHouse MergeTree 공식 문서에 따르면, MergeTree는 데이터를 컬럼 단위 파일(Columnar Format)로 분리 저장하고, 정렬 키(ORDER BY)를 기준으로 물리 디스크에 연속 배치하는 고성능 OLAP 엔진입니다.

• 불변 데이터 파트(Data Part): 대량의 데이터가 인서트될 때마다 정렬된 새로운 파트(Part) 디렉터리가 생성되며, 백그라운드 병합(Merge) 프로세스가 비동기적으로 여러 파트를 더 큰 파트로 컴팩션(Compaction)합니다.
• 극단적인 컬럼 압축률: 동일 컬럼의 연속된 데이터를 LZ4 또는 ZSTD 알고리즘으로 압축하여 디스크 공간을 일반 RDBMS 대비 80%~90% 이상 절감하고 디스크 읽기 I/O를 최소화합니다.

3. 희소 인덱스(Sparse Index)와 인덱스 그래뉼(Granule)의 원리

ClickHouse 희소 인덱스 가이드에 명시된 바와 같이, ClickHouse는 모든 행을 인덱싱하는 밀집 인덱스(Dense Index) 대신 희소 인덱스(Sparse Primary Index)를 채택합니다.

• 인덱스 그래뉼(Index Granule, 기본 8,192행): 테이블 데이터를 8,192개 행 단위의 그래뉼(Mark)로 분할하고, 각 그래뉼의 첫 번째 행에 해당하는 프라이머리 키 값만을 인덱스 파일(primary.idx)에 저장합니다.
• RAM 완전 상주 설계: 1억 건의 데이터가 존재하더라도 인덱스 항목은 약 12,200개에 불과하므로, 수 기가바이트의 인덱스 용량을 차지하는 B-Tree와 달리 단 수 메가바이트(MB) 수준으로 전체 인덱스를 RAM에 영구 상주시킵니다.
• 이진 탐색 및 마크 건너뛰기: 쿼리 조건절 평가 시 RAM 상의 희소 인덱스를 이진 탐색(Binary Search)하여 조건에 부합하지 않는 그래뉼을 디스크 I/O 없이 통째로 건너뜁니다.

4. 프로덕션 ClickHouse DDL 및 파이썬 적재·집계 파이프라인 구현

다음은 수억 건 공공데이터 처리를 위해 LowCardinality 최적화와 MergeTree 희소 인덱스를 적용한 DDL 및 네이티브 파이썬 연동 파이프라인 구현체입니다.

import clickhouse_connect
import time

# 1. ClickHouse 고성능 컬럼형 클라이언트 초기화
client = clickhouse_connect.get_client(
    host='localhost',
    port=8123,
    username='default',
    password=''
)

# 2. 수억 건 공공데이터 부동산 실거래 테이블 DDL
DDL_PUBLIC_REAL_ESTATE = '''
CREATE TABLE IF NOT EXISTS default.public_real_estate_mergetree (
    deal_date Date,
    region_code LowCardinality(String),
    building_type LowCardinality(String),
    deal_amount UInt64,
    exclusive_area Float32,
    floor Int8,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(deal_date)
ORDER BY (region_code, deal_date, building_type)
SETTINGS index_granularity = 8192;
'''

client.command(DDL_PUBLIC_REAL_ESTATE)

# 3. 고속 네이티브 배치 인서트 파이프라인
def ingest_public_data_batch(data_rows: list):
    column_names = ['deal_date', 'region_code', 'building_type', 'deal_amount', 'exclusive_area', 'floor']
    # ClickHouse 네이티브 컬럼형 직렬화 (단 1회 네트워크 전송)
    client.insert('public_real_estate_mergetree', data_rows, column_names=column_names)

# 4. 희소 인덱스 기반 프라이머리 키 범위 스캔 집계 질의
def query_regional_aggregates(target_region: str, start_date: str, end_date: str):
    sql = '''
    SELECT 
        building_type,
        count(*) AS total_tx_count,
        avg(deal_amount) AS avg_amount,
        quantile(0.95)(deal_amount) AS p95_amount
    FROM default.public_real_estate_mergetree
    WHERE region_code = %(region)s 
      AND deal_date BETWEEN %(start)s AND %(end)s
    GROUP BY building_type
    ORDER BY total_tx_count DESC
    '''
    start_ts = time.time()
    result = client.query(sql, parameters={'region': target_region, 'start': start_date, 'end': end_date})
    elapsed_ms = (time.time() - start_ts) * 1000
    return result.result_rows, elapsed_ms

5. 1억 건 공공데이터 쿼리 벤치마크 및 운영 가이드

1억 건의 공공 부동산 실거래 데이터셋을 대상으로 다중 조건 필터링 및 집계 쿼리를 수행했을 때의 실측 벤치마크 결과는 다음과 같습니다.

프로덕션 공공데이터 분석 환경에 본 아키텍처를 적용할 때는 다음 사항을 반드시 설계에 반영해야 합니다.

• ORDER BY 정렬 키 순서 설계: 필터링 조건절에서 가장 빈번하게 사용되고 카디널리티가 낮은 컬럼부터 높은 순서(예: region_code → deal_date → building_type)로 배치해야 그래뉼 건너뛰기 효율을 극대화할 수 있습니다.
• 과도한 파티셔닝(Partitioning) 지양: 일자별(toYYYYMMDD) 파티셔닝은 너무 많은 작은 파트(Too many parts) 에러를 유발하므로 반드시 월 단위(toYYYYMM)로 묶어 파트 수를 관리해야 합니다.
• 최소 인서트 배치 크기 유지: 단건 INSERT는 파트 폭증을 초래하므로 최소 10,000건 이상의 배치 또는 Buffer 테이블 엔진을 경유하여 적재해야 합니다.