도형준 지식 볼트

멱등 적재: ON CONFLICT upsert, COPY 고속 적재, 자연키 하드닝

8 min read

멱등 적재: ON CONFLICT upsert, COPY 고속 적재, 자연키 하드닝

← 이전 · 다음 →

개요

정제된 데이터를 대상 PostgreSQL에 넣는 단계다. 적재의 유일한 절대 요구사항은 멱등성이다. 이관은 반드시 여러 번 중단되고 재시작되므로, 같은 데이터를 두 번 적재해도 중복이나 유실이 없어야 한다. 여기에 처리량(수천만 행을 현실적 시간 안에)과 자연키 무결성(레거시 키의 지저분함 흡수)이 더해진다.

이 문서는 네 가지를 다룬다. (1) 공백·NULL 자연키를 다루는 하드닝, (2) ON CONFLICT를 이용한 멱등 upsert, (3) COPY/executemany를 통한 처리량 확보, (4) 적재량 자체를 줄이는 스코핑이다.

핵심 개념·원리

자연키 하드닝

레거시 소스의 자연키(예: record_no)는 신뢰할 수 없다. 앞뒤 공백이 붙거나, 빈 문자열이거나, NULL인 행이 섞여 있다. 대상 테이블의 키 컬럼에 NOT NULL이나 UNIQUE를 걸어 두면 이런 행에서 적재가 멈춘다. 그래서 키를 먼저 정규화(trim)하고, 그래도 비어 있으면 건너뛰거나 격리한다.

멱등 upsert의 원리

PostgreSQL의 INSERT ... ON CONFLICT (key) DO UPDATE는 “있으면 갱신, 없으면 삽입”을 원자적으로 처리한다. 자연키에 유니크 제약이 있으면, 같은 키로 재적재해도 새 행이 생기지 않고 기존 행이 갱신된다. 이것이 멱등성의 핵심 메커니즘이다. DO NOTHING은 재적재 시 갱신을 건너뛰므로, 소스가 바뀌었을 때 반영이 안 된다는 차이가 있다.

절의미언제
DO UPDATE SET ...충돌 시 지정 컬럼 갱신소스 변경을 반영해야 할 때 (기본)
DO NOTHING충돌 시 무시최초 삽입만 원하고 갱신 불필요할 때

처리량: per-row vs 배치

행마다 INSERT를 한 번씩 왕복하면 네트워크·파싱 오버헤드가 재구축 시간을 지배한다. 배치 방식과 처리량 차이는 크다.

방식특징상대 처리량
per-row INSERT행마다 왕복기준(느림)
executemany다중 바인딩 한 번에 전송수 배~수십 배
COPY (스테이징)바이너리 스트림 벌크 로드가장 빠름

실전

자연키 하드닝

def harden_key(row: dict) -> str | None:
    key = (row.get("record_no") or "").strip()
    return key or None  # 빈 키는 None
 
def loadable(row: dict) -> bool:
    key = harden_key(row)
    if not key:
        return False    # 빈 키 행은 건너뜀(또는 격리 테이블로)
    row["record_no"] = key
    return True

멱등 upsert (ON CONFLICT)

자연키에 유니크 제약이 있어야 ON CONFLICT가 동작한다.

-- 대상 테이블: 자연키에 UNIQUE
ALTER TABLE documents ADD CONSTRAINT uq_documents_record_no UNIQUE (record_no);
INSERT INTO documents (record_no, title, body, updated_at)
VALUES (%s, %s, %s, %s)
ON CONFLICT (record_no) DO UPDATE
SET title      = EXCLUDED.title,
    body       = EXCLUDED.body,
    updated_at = EXCLUDED.updated_at;

EXCLUDED는 삽입하려던 새 값을 가리키므로, 충돌 시 그 값으로 기존 행을 덮어쓴다.

재개(SKIP_EXISTING)

중단 후 재시작할 때, 이미 적재된 키를 다시 처리하지 않도록 플래그로 건너뛴다. upsert 자체가 멱등이라 재처리해도 정합성은 깨지지 않지만, 이미 끝난 수백만 건을 다시 쓰는 것은 시간 낭비다.

def load_chunk(cur, rows, skip_existing: bool):
    if skip_existing:
        existing = fetch_existing_keys(cur, [r["record_no"] for r in rows])
        rows = [r for r in rows if r["record_no"] not in existing]
    upsert_many(cur, rows)

고속 적재: COPY + 스테이징 후 upsert

초기 벌크 적재는 COPY로 스테이징 테이블에 밀어 넣고, 스테이징에서 본 테이블로 한 번에 upsert하는 패턴이 가장 빠르다.

# 1) 스테이징에 COPY (psycopg)
with cur.copy("COPY staging_documents (record_no, title, body, updated_at) FROM STDIN") as cp:
    for r in rows:
        cp.write_row((r["record_no"], r["title"], r["body"], r["updated_at"]))
-- 2) 스테이징 → 본 테이블 멱등 병합
INSERT INTO documents (record_no, title, body, updated_at)
SELECT record_no, title, body, updated_at FROM staging_documents
ON CONFLICT (record_no) DO UPDATE
SET title = EXCLUDED.title, body = EXCLUDED.body, updated_at = EXCLUDED.updated_at;
 
TRUNCATE staging_documents;

스코핑으로 적재량 줄이기

가장 효과 큰 최적화는 “안 옮기는 것”이다. 백업·이력·테스트 테이블을 대상에서 제외하면 적재량이 3~4배까지 줄기도 한다.

EXCLUDE_SUFFIX = ("_BAK", "_TR", "_HIS", "_TEST")
 
def in_scope(table: str) -> bool:
    return not table.upper().endswith(EXCLUDE_SUFFIX)
 
targets = [t for t in all_tables if in_scope(t)]

함정·트러블슈팅

증상원인해법
null value in column "record_no" violates not-null공백/NULL 자연키harden_key로 trim + 빈 키 격리
ON CONFLICT 사용 시 no unique or exclusion constraint matching대상 자연키에 UNIQUE 없음유니크 제약 먼저 생성
재적재 후 행 수 급증유니크 제약 없이 순수 INSERTupsert로 전환
적재가 비현실적으로 느림per-row INSERTCOPY/executemany 배치
임베딩 컬럼만 비어 있음대상에 pgvector 미설치CREATE EXTENSION vector 선행
디스크·시간 낭비백업/이력 테이블까지 적재접미어 스코핑으로 제외

pgvector 확장 확인

embeddings.vector 컬럼을 쓰려면 대상에 확장이 있어야 한다. 없으면 컬럼 생성부터 조용히 실패하거나 검색이 무력화된다(01.마이그레이션 개요와 전략).

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extname FROM pg_extension WHERE extname = 'vector';  -- 존재 확인

정리

적재는 멱등성을 최우선으로 설계한다. 자연키를 하드닝해 지저분한 키를 흡수하고, ON CONFLICT ... DO UPDATE로 재실행 안전성을 확보하며, SKIP_EXISTING으로 재개를 빠르게 한다. 처리량은 COPY + 스테이징 병합으로 끌어올리고, 백업·이력 테이블을 스코핑으로 제외해 적재량 자체를 줄인다. 초기 적재가 끝나면 이제 소스의 변경을 대상에 반영하는 증분 동기화로 넘어간다.

그래프 뷰

백링크