변경 동기화와 정합성 검증: watermark, reconcile, SCN

← 이전 · 다음 →

개요

초기 적재는 시작일 뿐이다. 소스 Oracle은 이관 중에도 계속 변한다. 신규·수정·삭제가 발생하므로, 대상 PostgreSQL을 소스와 계속 맞추는 증분 동기화(CDC, Change Data Capture) 와 그 결과가 정말 맞는지 확인하는 정합성 검증(reconcile) 이 필요하다.

이 문서의 핵심 메시지는 두 가지다. 첫째, watermark(변경 타임스탬프) 방식은 편하지만 함정이 많다. 둘째, 어떤 증분 방식도 완벽하지 않으므로 주기적 대사(reconcile)로 진실을 재확인해야 한다. 특히 DELETE는 타임스탬프로 잡히지 않는다는 점이 반복해서 사람을 무너뜨린다.

핵심 개념·원리

watermark 방식과 그 함정

가장 흔한 증분 방식은 변경 타임스탬프 컬럼(예: updated_at)을 watermark로 삼는 것이다. “마지막 동기화 시각 이후 변경된 행만” 가져온다.

SELECT * FROM records
WHERE updated_at > :last_watermark
ORDER BY updated_at;

편리하지만 세 가지 함정이 있다.

함정	원인	결과
벌크 타임스탬프 공유	대량 적재/배치 갱신이 같은 updated_at을 찍음	watermark 경계에서 누락 또는 무한 재처리
타임스탬프 미갱신	애플리케이션이 특정 경로에서 updated_at을 안 건드림	변경이 있는데도 안 잡힘
DELETE 미탐지	삭제된 행은 조회 자체가 안 됨	대상에 유령 행이 남음

특히 벌크 적재된 수만 행이 동일한 타임스탬프를 공유하면, > 경계에서 일부만 가져오거나(누락) 매번 같은 행을 다시 처리(무한 루프)하게 된다. watermark를 >=로 하면 중복, >로 하면 누락 위험이 생기는 진퇴양난이다. 그래서 “가져온 행이 실제로 갱신됐는지”를 따로 검증해야 한다.

DELETE는 왜 안 잡히나

watermark는 “변경된 행을 SELECT”하는 방식이다. 삭제된 행은 소스에 존재하지 않으니 SELECT 결과에 나타날 수 없다. 결과적으로 소스에서 지워진 데이터가 대상에는 계속 남아, 시간이 지날수록 대상 행 수가 소스보다 많아진다. DELETE를 반영하려면 타임스탬프가 아니라 키 집합 비교(대사) 가 필요하다.

SCN 기반 대안

Oracle의 ora_rowscn 의사 컬럼은 행(또는 블록)의 마지막 변경 SCN(System Change Number)을 노출한다. 애플리케이션이 타임스탬프를 성실히 갱신하지 않을 때, SCN은 DB가 자동으로 매기는 신뢰도 높은 변경 신호가 될 수 있다.

-- 행 수준 SCN 신호(테이블이 ROWDEPENDENCIES면 행 단위 정밀도)
SELECT id, ora_rowscn, SCN_TO_TIMESTAMP(ora_rowscn) AS chg_ts
FROM records
WHERE ora_rowscn > :last_scn;

단, 기본 테이블은 블록 단위 SCN이라 같은 블록의 무관한 행까지 변경으로 잡힐 수 있고, SCN_TO_TIMESTAMP는 조회 가능한 과거 범위에 한계가 있다. 보조 신호로 쓰되 맹신하지 않는다.

실전

증분 루프와 검증

가져온 행이 실제로 대상과 다른지 확인하고 나서 반영하면, 벌크 타임스탬프로 인한 불필요·무한 재처리를 줄인다.

def sync_incremental(src, dst, last_wm):
    rows = fetch_changed(src, since=last_wm)      # updated_at > last_wm
    changed = 0
    for r in rows:
        if differs_from_target(dst, r):           # 실제 갱신 여부 검증
            upsert(dst, r)                        # 05의 멱등 upsert 재사용
            changed += 1
    new_wm = max((r["updated_at"] for r in rows), default=last_wm)
    return new_wm, changed

DELETE 반영: 키 집합 대사

주기적으로 소스와 대상의 키 집합을 비교해, 소스에 없는 키를 대상에서 정리한다.

-- 대상에만 있는 키(소스에서 삭제된 것으로 추정) 탐지
SELECT d.record_no
FROM documents d
LEFT JOIN src_keys s ON s.record_no = d.record_no
WHERE s.record_no IS NULL;

여기서 src_keys는 소스의 현재 키 전체를 임시로 적재한 테이블이다. 삭제 반영은 파괴적이므로, 곧바로 지우기보다 소프트 삭제 플래그를 먼저 세우고 검토 후 확정하는 편이 안전하다.

정합성 대사(reconcile)

가장 확실한 검증은 count·키·해시 세 층위로 맞춰 보는 것이다.

-- 1) 행 수 대사
SELECT (SELECT count(*) FROM documents) AS dst_cnt;  -- 소스 count와 비교
 
-- 2) 키 차집합 (양방향)
SELECT record_no FROM src_keys
EXCEPT
SELECT record_no FROM documents;   -- 소스에만 있음 = 미적재
 
-- 3) 내용 해시 대사(변조/부분 갱신 탐지)
SELECT record_no, md5(coalesce(title,'') || coalesce(body,'')) AS h
FROM documents;

행 수만 맞아도 내용이 어긋날 수 있으므로, 표본이나 전량에 대해 해시까지 비교하면 신뢰도가 크게 오른다.

참조 무결성 사전 점검

제약(UNIQUE/FK)을 대상에 걸기 전에, 소스 데이터가 그 제약을 이미 위반하고 있지 않은지 미리 확인한다. 위반 데이터가 있으면 제약 추가 자체가 실패한다.

-- 중복 키 사전 탐지: 위반이 있으면 UNIQUE 제약 추가가 실패
SELECT code, COUNT(*) AS c
FROM ref_codes
GROUP BY code
HAVING COUNT(*) > 1;

함정·트러블슈팅

증상	원인	해법
매 사이클 같은 행 재처리	벌크 타임스탬프 공유 + >= 경계	갱신 여부 검증 후 반영, 처리한 키 기록
변경이 반영 안 됨	애플리케이션이 updated_at 미갱신	ora_rowscn 보조 신호 병행
대상 행 수가 소스보다 많음	DELETE 미반영	키 집합 대사로 유령 행 정리
could not create unique index ... duplicate key	소스에 중복 키 존재	GROUP BY ... HAVING count>1 사전 점검
count는 같은데 검색 결과가 다름	부분 갱신/내용 불일치	해시 대사로 내용까지 검증

정리

증분 동기화는 편한 watermark 방식의 함정(벌크 타임스탬프 공유, 미갱신, DELETE 미탐지)을 인지하는 데서 출발한다. 타임스탬프는 신호일 뿐이고, 진실은 주기적 count·키·해시 대사로 재확인한다. DELETE는 키 집합 비교로만 잡히며, 타임스탬프가 못 미더우면 ora_rowscn을 보조로 쓴다. 제약을 걸기 전에는 반드시 위반 데이터를 사전 점검한다. 마지막 남은 축은 이 모든 과정을 관통하는 보안·프라이버시다.

• 다음: 07.보안·프라이버시 (마스킹·DLP)
• 관련: Database MOC