Ken
"Postgres에 다 있는데 왜 ClickHouse가 필요한가요?"와 "ClickHouse로 다 옮기면 안 되나요?"는 같은 질문의 양면입니다. 답은 워크로드의 성격에 있습니다. 좌표·폴리곤 같은 지리 연산은 PostGIS를 가진 Postgres의 영역이고, 수천만 행을 훑는 집계는 ClickHouse의 영역입니다. 이 원칙을 말로만 하지 않으려고 실제 공개 데이터로 처음부터 끝까지 만들어봤습니다. 서울 공공자전거(따릉이) 대여이력을 Managed Postgres(PostGIS)에 적재하고, CDC(ClickPipes)로 ClickHouse에 복제하고, FDW(pg_clickhouse)로 다시 이어서 Postgres에서 쿼리하면 집계는 ClickHouse에서 돌고 결과만 돌아오는 구조까지 배선했습니다.
이 글의 후반부는 특히 **푸시다운(pushdown)**에 지면을 씁니다. FDW 아키텍처의 성패가 거기서 갈리는데, 잘못되면 에러가 아니라 "테이블을 안 옮기느니만 못한 성능"으로 조용히 나타나기 때문입니다.
왜 이 데이터인가
쪽 | 담는 것 | 규모 | 성격 |
PostGIS | 대여소 마스터 — 좌표, 자치구, 주소 | 2,789행 (25개 자치구) | 사실상 고정 (디멘전) |
팩트 | 대여이력 — 대여 1건당 1행 | 실측+생성 합계 2,400만 행 / 228일 / 7.1GB | 계속 쌓임, 집계만 함 |
핵심은 조인 키가 대여소번호, 즉 정수라는 점입니다. 각 대여가 출발·도착 대여소를 정수 ID로 갖는 OD(출발-도착) 데이터라서, 지오메트리가 집계하는 쪽으로 건너갈 일이 없습니다. 아키텍처가 데이터 모델에서 그대로 떨어진 셈입니다. (데이터는 서울 열린데이터광장 공공누리 제1유형 — 출처표시 하에 상업적 이용·변경 가능, API 키·계정 불필요.)
전체 구조
주목할 점: ClickPipes는 trips만이 아니라 stations도 복제합니다. 이게 뒤에 나올 조인 푸시다운의 전제가 됩니다. 반면 지오메트리 컬럼(geom)은 ClickHouse에 대응 타입이 없어 경계를 넘지 못하고, 그래서 Voronoi·최근접·군집 같은 공간 연산은 구조적으로 Postgres에 남습니다.
플랫폼 조건도 확인해둘 가치가 있습니다. Managed Postgres에서 wal_level = logical이 기본값이라 CDC를 위한 재시작이 필요 없었고, PostGIS는 축소판이 아닌 정식 3.6.4(Voronoi·DBSCAN·geography 거리 모두 동작), pg_clickhouse 0.3은 기본 미설치이지만 사용 가능한 상태였습니다.
Postgres 쪽: CDC를 전제한 스키마 설계
하류에 CDC가 있다는 사실이 상류 스키마 설계를 바꿉니다. 세 가지 결정이 있었습니다.
① 대리키는 장식이 아니다. 원본에는 자연키가 없습니다 — 자전거 ID, 양쪽 타임스탬프, 양쪽 대여소번호를 전부 합쳐도 96행이 겹치는데, 이는 중복이 아니라 기록된 거리만 다른 실제 별개의 대여입니다. 그리고 논리 복제는 replica identity를 요구하므로, 기본키가 없으면 ClickPipes가 *"cannot be replicated because they don't have a valid replica identity"*로 테이블을 거부합니다. REPLICA IDENTITY FULL로도 통과는 되지만, bigint 대리키(GENERATED ALWAYS AS IDENTITY)는 ClickHouse 쪽 정렬·중복제거 기준까지 겸하므로 그쪽을 택했습니다. 164만 행에 키 추가는 7.7초.
② 외래키는 걸지 않는다. 이력에는 현재 마스터에 없는 대여소가 등장합니다(폐지된 대여소, 마스터는 스냅샷). 1월 이력만 해도 대여소번호 17개, 대여 26,346건이 마스터에 없었습니다. FK를 걸면 실제 데이터가 거부됩니다.
③ 시간대는 물려받는 게 아니라 결정하는 것이다. 원본은 한국 현지 시각으로 발행되고 컬럼은 timestamp without time zone입니다. Postgres 안에서는 견딜 만하지만 ClickHouse가 끼는 순간 위험해집니다 — ClickHouse는 DateTime에 시간대를 붙이므로 KST 벽시계 값이 그대로 넘어가면 조용히 9시간 이른 시각이 됩니다. 그래서 적재 시점 UTC 확정을 규칙으로 정했습니다. 이 결정을 미루면 어떻게 되는지도 직접 겪었습니다 — 생성기가 테이블이 아직 KST일 때 AT TIME ZONE 'Asia/Seoul'로 읽어 오후 4시 45분에 오전 7시의 수요 패턴을 만들었는데, 나온 행은 전혀 이상해 보이지 않았습니다. 시간대 버그는 에러가 아니라 그럴듯한 오답으로 나타납니다.
기존 데이터 2,387만 행의 KST→UTC 변환도 교훈이 하나 있습니다. 단일 UPDATE는 모든 튜플을 한 트랜잭션에서 재작성해 테이블이 약 두 배로 붇고 커밋 전까지 WAL 20GB가 묶입니다. PK를 25만 행씩 걸어가면 죽은 튜플이 계속 회수되고 WAL도 흘러갑니다 — 689초에 완료. 진행 상황은 타임스탬프 추론이 아니라 북마크 테이블에 기록했는데, 변환된 행과 변환이 필요 없던 행이 구별되지 않아 타임스탬프로 재개하면 일부를 두 번 밀게 되기 때문입니다.
CDC 구간: 계단식 전진은 정상, 비활성 슬롯이 사고
운영 관점의 핵심 지표는 Postgres 쪽 복제 슬롯입니다.
SELECT slot_name, plugin, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS unconsumed
FROM pg_replication_slots;confirmed_flush_lsn은 연속이 아니라 계단식으로 전진합니다. 소비자가 배치 하나를 다운스트림에 완전히 적재한 뒤에야 확인하기 때문입니다. 5초 지켜보고 "멈췄다"고 판단하면 틀립니다. 실측: 1,492행 삽입 시 미소비 WAL이 36MB까지 올랐다가 30초 안에 0으로 배수.
진짜 사고는 비활성 슬롯에서 났습니다. 2,200만 행 백필이 단일 트랜잭션으로 도착하자 소비자가 떨어져 나갔고, 그 뒤 슬롯이 WAL 13GB를 쥐고 있었습니다. 비활성 슬롯은 WAL을 무한정 붙들어 결국 디스크를 채우므로 SELECT count(*) FROM pg_replication_slots WHERE NOT active는 반드시 모니터링에 들어가야 하고, 대량 적재는 작은 트랜잭션으로 나누면 애초에 이 상황이 생기지 않습니다.
또 하나 — publication은 테이블을 명시적으로 지정해야 합니다. 생성기의 작업 테이블(표본 풀, 가중치, 계절 계수)은 별도 스키마(bikegen)에 격리해서 FOR ALL TABLES였더라도 쓸려가지 않게 했습니다. 복제 대상 스키마에는 trips와 stations만 존재합니다.
ClickHouse 미러 테이블: 평소의 설계 원칙 그대로
팩트가 ClickHouse에 도착하면 MergeTree 설계 3부작의 원칙이 그대로 적용됩니다. 파티션은 수명 관리 단위이므로 toYYYYMM(started_at) 월 단위, ORDER BY는 지배적 쿼리 패턴("대여소별 기간 집계")에 맞춰 저카디널리티 디멘전 → 시간 순인 (start_station_id, started_at, trip_id), 그리고 CDC의 update/delete 반영을 위해 ReplacingMergeTree — Postgres에서 replica identity 때문에 만든 trip_id가 여기서 중복 제거 기준으로 두 번째 일을 합니다. 인입 배칭과 재시도는 ClickPipes가 관리형으로 처리하므로 Part 생성 속도를 직접 설계할 필요가 없습니다.
푸시다운: 이 아키텍처의 성패가 갈리는 곳
여기까지는 데이터를 "옮기는" 이야기였고, 지금부터가 "일을 옮기는" 이야기입니다. pg_clickhouse FDW로 ClickHouse의 미러 테이블을 Postgres의 외래 테이블로 노출하면, Postgres에서 실행한 쿼리의 일부가 ClickHouse로 넘어가 실행됩니다. 문제는 얼마나 넘어가느냐입니다.
쿼리를 두 무더기로 나누다
경계가 눈에 보이도록 쿼리 10개를 두 파일로 나눴습니다.
옮길 수 없는 5개 (Postgres 잔류) — 대여소별 Voronoi 세력권, GiST 인덱스와 <-> 연산자를 통한 최근접 이웃, 행정경계를 가로지르는 DBSCAN 군집, 기록 거리와 측지선을 비교한 우회율, 자치구별 순유출입과 평균 방위. 전부 지오메트리 타입과 공간 함수에 의존하므로 원격 대응물이 없습니다.
옮겨야 하는 5개 (ClickHouse 실행) — 출퇴근형 vs 여가형 대여소 분류, 최다 통행 회랑, 재배치가 필요한 대여소, 자치구별 이용 양상, 자치구별 기상 시각. 수천만 행에 대한 WHERE·GROUP BY·HAVING과 기본 집계뿐입니다.
직관 하나가 틀렸다: 조인도 넘어간다
처음의 직관은 "대여소 조인은 Postgres에 남기고 집계(숫자 세기)만 보내자"였습니다. 이 직관이 틀렸습니다. pg_clickhouse 문서가 명시하는 두 문장이 이 아키텍처의 규칙입니다:
pg_clickhouse also pushes down JOINs to tables that are from the same remote server.Joining with a local table will generate less efficient queries without careful tuning.
즉 같은 원격 서버에 있는 테이블끼리의 조인은 통째로 푸시다운되고, 로컬 테이블을 섞는 순간 깨집니다. ClickPipes가 stations도 복제해뒀기 때문에, 대여소 이름을 붙이거나 자치구로 GROUP BY하는 것도 ClickHouse 쪽 원격 작업입니다. 조인 자체가 푸시다운의 적이 아니라, 로컬-원격 혼합이 적입니다.
이 규칙이 스키마 설계로 소급됩니다 — "조인에 필요한 디멘전은 팩트와 함께 복제한다"가 FDW 아키텍처의 설계 원칙이 되는 것입니다. 10개 중 로컬 혼합이 불가피한 쿼리는 정확히 하나였습니다: 회랑 쿼리가 ST_Distance를 쓰는데 원격 대응물이 없어, 이 쿼리만 경계 사례로 표시하고 나머지는 전부 원격으로 보냈습니다.
실패는 조용하다: EXPLAIN (VERBOSE)로 증명하기
푸시다운의 가장 위험한 성질은 실패해도 에러가 없다는 것입니다. 푸시다운이 깨지면 쿼리는 여전히 정답을 내놓습니다 — 모든 행이 네트워크를 건너와 Postgres가 세었을 뿐입니다. 2,400만 행이 매 쿼리마다 경계를 넘는다면 테이블을 옮기지 않느니만 못합니다. 아무도 경고해주지 않으므로 직접 봐야 합니다.
판정 도구는 EXPLAIN (VERBOSE)입니다. 래퍼가 원격으로 보낼 SQL(Remote SQL)이 플랜에 그대로 출력되고, 그 텍스트가 답입니다.
-- 푸시다운 성공: Remote SQL이 GROUP BY를 싣고 있다
Foreign Scan
Remote SQL: SELECT start_station_id, count(*) FROM trips GROUP BY start_station_id
-- 푸시다운 실패: 원격은 컬럼만 뽑고, 집계는 로컬 Aggregate 노드가 한다
Aggregate
-> Foreign Scan
Remote SQL: SELECT start_station_id FROM trips첫 번째 형태면 ClickHouse가 일했고 돌아오는 것은 GROUP BY가 끝난 수백 행입니다. 두 번째 형태면 수천만 행이 전부 건너왔습니다. 겉보기 결과는 동일하므로, 이 확인을 사람이 매번 하는 대신 스크립트(explain-pushdown.sh)가 플랜을 읽어 쿼리별로 판정하게 했습니다. FDW 기반 아키텍처를 운영한다면 이런 푸시다운 회귀 검사를 CI처럼 상시화하는 것을 권합니다 — 쿼리 하나에 함수 하나 추가되는 순간 조용히 깨질 수 있는 것이 푸시다운이기 때문입니다.
같은 파일을 양쪽에 겨누기
로컬과 원격의 비교를 공정하게 하려고, 쿼리 파일은 search_path로 스키마만 갈아끼우면 같은 SQL이 로컬 테이블에도 외래 테이블에도 실행되게 했습니다.
./scripts/psql.sh -f /sql/20-aggregate-pushdown.sql # 로컬 실행
./scripts/psql.sh -v target=ch_bike -f /sql/20-aggregate-pushdown.sql # 원격(푸시다운) 실행같은 질문에 대한 두 플랜을 나란히 놓고 보는 것 — 이것이 "집계는 ClickHouse가 잘한다"를 주장이 아니라 측정으로 만드는 방법입니다.
푸시다운 관점의 운영 수칙 정리
- 디멘전도 복제하라. 조인 상대가 원격에 없으면 그 조인은 로컬 혼합이 되고 푸시다운이 깨진다.
- 원격 대응물이 없는 함수를 파악하라. 이 랩에서는 공간 함수 전부. 해당 쿼리는 처음부터 Postgres 몫으로 분류한다.
- 네트워크 방향을 기억하라. FDW는 Postgres 서버에서 바깥으로 연결한다. 관리형 Postgres에서 노트북의 ClickHouse는 닿지 않는다 — 서비스가 접속 가능한 위치여야 한다.
- 믿지 말고 판정하라.
EXPLAIN (VERBOSE)의 Remote SQL이 유일한 증거다. 결과가 맞다는 것은 푸시다운의 증거가 아니다.
덤: 데모를 살아있게 만드는 합성 데이터
공개 데이터는 월 단위로 발행되는데 CDC 데모는 지금 흐르는 데이터가 필요해서 생성기를 붙였습니다. 방식이 하나의 교훈입니다 — 컬럼별로 각자의 분포에서 뽑으면 히스토그램은 전부 맞지만 결합 구조가 사라집니다(아침 대여는 지하철역으로 8분, 일요일 오후는 강변으로 40분이라는 상관관계가 무너져 OD 행렬이 균등해짐). 대신 "같은 요일 유형·같은 시각에 시작된 실제 대여"를 뽑아 타임스탬프만 바꾸고 도착률은 포아송에서 뽑았습니다. 생성한 한 주는 실제 1월과 아침 피크 비중 28.16% vs 28.20%, 평균 소요시간 17.1분 vs 17.0분 수준으로 일치합니다. pg_cron이 프리로드된 플랫폼이라 생성기가 서버 사이드에서 돌고, 노트북을 닫아도 데모가 계속 삽니다.
정리
- 경계는 가장 단순한 타입에 긋는다 — 지오메트리가 아니라 정수 키가 시스템을 잇는다
- 하류의 CDC가 상류의 스키마를 결정한다 — replica identity 때문에라도 대리키를 먼저
- 시간대는 적재 시점에 확정한다 — 시간대 버그는 에러가 아니라 그럴듯한 오답이다
- 비활성 슬롯이 진짜 위험이다 — 계단식 LSN 전진은 정상, WAL 13GB를 쥔 죽은 슬롯이 사고
- 조인도 푸시다운된다, 단 원격끼리만 — 디멘전을 함께 복제하는 것이 FDW 설계의 일부다
- 푸시다운은 신뢰가 아니라 검증의 대상이다 — Remote SQL에 GROUP BY가 실렸는지가 유일한 증거이고, 실패는 조용하다
전체 스크립트와 쿼리 세트는 저장소에 공개되어 있습니다. Managed Postgres 서비스만 있으면 psql 컨테이너로 그대로 재현됩니다.
