백엔드 데이터 인프라 25편 — INSERT 깊이 Bulk·COPY·UPSERT 전략

이 글은 백엔드 데이터 인프라 시리즈 70편 중 25편이에요. 10편 INSERT 에서 "5가지 패턴" 을 다뤘으니, 이번 25편은 그 위에 — 성능·UPSERT 전략·운영 함정.

INSERT 의 세 가지 속도

같은 100,000건을 넣을 때 방법에 따라 속도가 이렇게 갈린다.

20배 차이가 난다. 큰 데이터를 넣을 때는 "어떤 방법을 고르느냐" 가 결정적이다.

단건 INSERT 의 비용

INSERT INTO logs (level, msg) VALUES ('INFO', 'hello');

각 INSERT 마다 네트워크 왕복 1번, 파싱·계획 1번, 트랜잭션 시작·커밋 1번, WAL(Write-Ahead Log, 변경 사항 선기록) fsync(디스크 동기화) 1번, 인덱스 갱신, 트리거 발동이 따라붙는다.

100,000번을 호출하면 위 모든 비용이 100,000번 반복된다. 느림의 근본 원인.

Bulk INSERT — N 배 빠름

INSERT INTO logs (level, msg) VALUES
    ('INFO', 'msg 1'),
    ('INFO', 'msg 2'),
    ...
    ('INFO', 'msg 1000');

한 SQL 로 묶으면 파싱·계획·트랜잭션이 한 번에 끝난다. WAL 도 묶여서 fsync 횟수가 줄어든다.

chunk 사이즈 — 1000~5000 권장

너무 크면 메모리 부담이 커지고 계획자 시간이 폭주한다. 너무 작으면 단건과 다를 게 없다.

한국 회사 표준은 1000~5000건/chunk 다.

JPA + Bulk

JPA(Java Persistence API, 자바 ORM 표준) saveAll() 도 Hibernate 설정에 따라 Bulk INSERT 가 박힌다.

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 1000
        order_inserts: true
        order_updates: true

batch_size 를 박지 않으면 saveAll() 도 단건을 N번 부른다. 한국 회사에서 흔한 함정.

COPY — 가장 빠른 입력

-- 클라이언트 파일
\copy logs (level, msg) FROM 'logs.csv' WITH (FORMAT csv, HEADER true)

-- 또는 STDIN
\copy logs (level, msg) FROM STDIN WITH (FORMAT csv)
INFO,hello
INFO,world
\.

COPY 가 Bulk 보다 빠른 이유는 SQL 파싱을 안 하고(별도 프로토콜), 인덱스·제약 검사를 일괄로 처리하고, WAL 을 페이지 단위로 기록하기 때문이다.

Spring 에서 COPY — PgCopy 또는 PostgreSQL JDBC

import org.postgresql.copy.CopyManager;
import org.postgresql.PGConnection;

@Transactional
public void bulkInsert(List<Log> logs) throws SQLException, IOException {
    PGConnection pgCon = connection.unwrap(PGConnection.class);
    CopyManager copyManager = pgCon.getCopyAPI();

    String csv = logs.stream()
        .map(l -> l.level() + "," + l.msg())
        .collect(Collectors.joining("\n"));

    copyManager.copyIn("COPY logs (level, msg) FROM STDIN WITH (FORMAT csv)",
                       new StringReader(csv));
}

JPA 표준은 아니고, PostgreSQL JDBC(Java Database Connectivity, 자바 DB 접속 표준) 드라이버를 직접 쓴다. 100만건 입력 시나리오에서 표준으로 자리 잡았다.

UPSERT 전략 4가지

10편 에서 ON CONFLICT 를 소개했다. 여기서 더 들어간다.

전략 1 — ON CONFLICT DO UPDATE

INSERT INTO user_stats (user_id, visit_count)
VALUES (1, 1)
ON CONFLICT (user_id)
DO UPDATE SET visit_count = user_stats.visit_count + 1;

PG 9.5+ 부터 들어간 표준이고 가장 많이 쓴다.

전략 2 — ON CONFLICT DO NOTHING

INSERT INTO unique_emails (email, source)
VALUES ('alice@example.com', 'signup')
ON CONFLICT (email) DO NOTHING;

중복이면 그냥 패스한다. 멱등 입력에 표준.

전략 3 — MERGE (PG 15+)

MERGE INTO users target
USING (VALUES (1, 'Alice')) AS source(id, name)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

복잡한 조건 분기나 DELETE 까지 묶어야 하는 시나리오에 쓴다.

전략 4 — 트랜잭션 + SELECT FOR UPDATE + 분기

BEGIN;
SELECT id FROM users WHERE email = ? FOR UPDATE;
-- 결과 있으면 UPDATE, 없으면 INSERT
COMMIT;

성능은 떨어진다. ON CONFLICT 가 안 될 때 쓰는 대체안이다.

UPSERT 인덱스 요구

ON CONFLICT (col)   -- col 에 UNIQUE 또는 unique 인덱스 필수
ON CONFLICT (col) WHERE 조건   -- 부분 unique 인덱스
ON CONFLICT ON CONSTRAINT name   -- 명명된 제약

인덱스가 없으면 ERROR 가 난다.

EXCLUDED — INSERT 하려던 값

INSERT INTO products (id, name, price, updated_at)
VALUES (1, 'Item', 1000, NOW())
ON CONFLICT (id)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = EXCLUDED.updated_at;

EXCLUDED 는 "INSERT 하려던 그 값" 이다. UPDATE 안 박는 컬럼은 옛 값을 그대로 유지한다.

조건부 UPDATE

ON CONFLICT (id)
DO UPDATE SET price = EXCLUDED.price
WHERE products.price <> EXCLUDED.price;

가격이 다를 때만 UPDATE 가 일어난다. 불필요한 변경을 피하고 WAL 도 절약된다.

트리거 영향

INSERT 가 일어나면 트리거가 발동한다. BEFORE INSERT 에서는 값을 수정·검증하고, AFTER INSERT 에서는 감사 로그를 남기거나 캐시를 갱신한다.

CREATE TRIGGER log_insert
AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION log_user_creation();

대량 INSERT 시 트리거가 "각 행마다" 발동하면 부담이 커진다. chunk 처리 + 임시 비활성화 가 표준이다.

ALTER TABLE users DISABLE TRIGGER log_insert;

COPY users FROM 'data.csv' ...;

ALTER TABLE users ENABLE TRIGGER log_insert;

시퀀스와 INSERT

CREATE TABLE users (id BIGSERIAL PRIMARY KEY, ...);
INSERT INTO users (...) VALUES (...);
-- 자동으로 nextval('users_id_seq')

시퀀스 동작 — 트랜잭션 무관

BEGIN;
INSERT INTO users VALUES (...) RETURNING id;   -- id = 100
ROLLBACK;

INSERT INTO users VALUES (...) RETURNING id;   -- id = 101 (100 안 재사용)

ROLLBACK 해도 시퀀스 값은 소비된다. ID 가 "건너뛰는" 게 정상.

currval·lastval

SELECT currval('users_id_seq');     -- 현 세션 마지막
SELECT lastval();                    -- 세션 마지막 (어느 시퀀스든)

대체로 INSERT RETURNING 이 더 깔끔하다.

동시성 함정

동시 INSERT + UNIQUE

-- 트랜잭션 A
SELECT 1 FROM users WHERE email = 'alice@x.com';   -- 없음
INSERT INTO users (email, ...) VALUES ('alice@x.com', ...);

-- 트랜잭션 B (동시)
SELECT 1 FROM users WHERE email = 'alice@x.com';   -- 없음
INSERT INTO users (email, ...) VALUES ('alice@x.com', ...);   -- UNIQUE 위반!

해결책은 세 가지다. ON CONFLICT DO NOTHING 으로 UPSERT 처리, SELECT FOR UPDATE 로 락을 잡기, 예외를 잡고 재시도.

시퀀스 경합 — 거의 없음

PG 시퀀스는 락이 거의 없다. 100스레드가 동시에 INSERT 해도 빠르다.

함정 5가지

(1) 단건 N번 호출

JPA 에서 for (User u : list) repo.save(u) 로 돌리면 단건이 N번 나간다. saveAll(list) 로 묶고 batch_size 를 박는다.

(2) chunk 너무 큼

100,000건을 한 INSERT 로 넣으면 메모리가 폭발한다. 1000~5000 권장.

(3) UPSERT 인덱스 누락

ON CONFLICT (col) 을 쓰려면 col 에 UNIQUE 가 있어야 한다. ERROR 가 나면 인덱스부터 확인한다.

(4) EXCLUDED 안 박음

ON CONFLICT (id) DO UPDATE SET name = 'fixed'   -- ❌ 항상 'fixed'

SET name = EXCLUDED.name 가 정상.

(5) 트리거 무시 대량 INSERT

각 행마다 트리거가 발동하면 대량 입력 시 폭발한다. chunk 처리 + 임시 비활성화로 풀어야 한다.

한 줄 정리 — INSERT 속도 = 단건 < Bulk < COPY (20배 차이). ON CONFLICT 4전략 (DO UPDATE·DO NOTHING·MERGE·트랜잭션). EXCLUDED 로 INSERT 값 참조. 시퀀스는 ROLLBACK 무관 — ID 건너뜀 정상. 대량은 트리거 일시 비활성화 + ANALYZE.

시험 직전 한 번 더 — INSERT 깊이 입문자가 매번 헷갈리는 것

• 속도 = 단건 < Bulk < COPY (수십 배 차이)
• 단건 비용 = 네트워크·파싱·트랜잭션·WAL 모두 N번
• Bulk = 한 SQL 여러 VALUES (chunk 1000~5000)
• COPY = 가장 빠름 (별도 프로토콜)
• COPY STDIN = 스트림 입력
• Spring CopyManager = PostgreSQL JDBC API
• JPA batch_size 박아야 saveAll Bulk
• order_inserts·order_updates 옵션
• UPSERT 4전략 = DO UPDATE·DO NOTHING·MERGE·SELECT FOR UPDATE
• EXCLUDED = INSERT 하려던 값
• 조건부 UPDATE = WHERE 기존 <> EXCLUDED
• UPSERT 대상 컬럼 = UNIQUE 인덱스 필수
• 부분 UNIQUE 인덱스 = WHERE 조건
• ON CONFLICT ON CONSTRAINT name = 명명 제약
• 트리거 = 각 행마다 발동 (대량 시 부담)
• 대량 INSERT = 트리거 일시 비활성화
• ALTER TABLE DISABLE TRIGGER name·ENABLE
• 시퀀스 = ROLLBACK 무관 (ID 건너뜀)
• currval·lastval = 세션 마지막 값
• RETURNING id 가 더 깔끔
• 동시 INSERT 경합 = ON CONFLICT DO NOTHING 으로 안전
• SELECT-INSERT 분리 = race condition 위험
• 100K+ = COPY + 트리거 OFF + ANALYZE
• 운영 = Bulk 또는 COPY 무조건

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

• 20편 — PostgreSQL SQL 문법 전반
• 21편 — DDL 개요 데이터 정의 언어 전체 그림
• 22편 — CREATE TABLE 깊이 PARTITION·UNLOGGED·TEMPORARY
• 23편 — 제약 깊이 CHECK DEFERRABLE EXCLUDE
• 24편 — DML 개요 4가지 동사 + MVCC 통합

다음 글:

• 26편 — UPDATE 깊이 HOT·fillfactor·FROM JOIN
• 27편 — DELETE 깊이 bloat·VACUUM·파티션 DROP
• 28편 — 쿼리 개요 SELECT 전체 구조
• 29편 — SELECT 절과 표현식의 깊이
• 30편 — 테이블 표현식 FROM 절의 깊이