[PostgreSQL] 대량 적재 체크리스트 — COPY + 인덱스 후생성 + maintenance_work_mem + 단일 트랜잭션 + 후행 ANALYZE

설치·접속: PostgreSQL 설치와 접속

부제: 수백만 행 시드 데이터를 넣는데 INSERT 루프로 몇 시간 걸린다 — COPY로 바꾸고, 인덱스는 적재가 끝난 뒤에 만들어 시간을 확 줄일 때

핵심은 하나다. 인덱스를 켜둔 채 적재하면 행마다 인덱스를 갱신해야 한다. 초기 적재라면 인덱스 없이 통째로 부어 넣고 나중에 한 번에 만드는 게 훨씬 싸다. 200만 행으로 두 방식을 실측 비교했다.

나쁜 순서 — 인덱스를 켠 채 COPY

CREATE TABLE bulk (id bigint, uname text, amount numeric, payload text);
CREATE INDEX bulk_a_id     ON bulk(id);
CREATE INDEX bulk_a_uname  ON bulk(uname);
CREATE INDEX bulk_a_amount ON bulk(amount);
COPY bulk FROM '/tmp/bulk.csv' WITH (FORMAT csv);

CREATE TABLE     Time: 10.611 ms
CREATE INDEX     Time: 6.882 ms
CREATE INDEX     Time: 7.075 ms
CREATE INDEX     Time: 7.881 ms
COPY 2000000     Time: 8475.195 ms (00:08.475)

COPY 하나에 8475 ms. 세 인덱스를 매 행마다 갱신하는 비용이 여기 다 들어 있다.

좋은 순서 — 빈 테이블에 COPY, 인덱스는 나중에

CREATE TABLE bulk (id bigint, uname text, amount numeric, payload text);
COPY bulk FROM '/tmp/bulk.csv' WITH (FORMAT csv);

SET maintenance_work_mem = '512MB';   -- 인덱스 빌드용 작업 메모리 상향
CREATE INDEX bulk_b_id     ON bulk(id);
CREATE INDEX bulk_b_uname  ON bulk(uname);
CREATE INDEX bulk_b_amount ON bulk(amount);
ANALYZE bulk;                       -- 후행 통계 갱신
RESET maintenance_work_mem;

COPY 2000000     Time: 857.968 ms
SET              Time: 0.094 ms
CREATE INDEX     Time: 336.927 ms
CREATE INDEX     Time: 791.877 ms
CREATE INDEX     Time: 755.938 ms
ANALYZE          Time: 95.118 ms

빈 테이블 COPY는 857 ms로 끝난다(앞의 8475 ms 대비 약 10배). 인덱스 세 개를 뒤에 몰아서 빌드해도 337+792+756 ≈ 1885 ms, ANALYZE 95 ms를 더해 전체가 약 2.8초다. 인덱스를 켠 채 적재한 8.5초의 3분의 1이다. 인덱스를 나중에 만들면 정렬된 데이터를 한 번에 처리하고 maintenance_work_mem 덕에 더 큰 메모리로 빌드하므로, 행마다 B-tree를 조금씩 갱신하는 것보다 근본적으로 유리하다. 마지막 ANALYZE를 빼먹으면 방금 부은 데이터에 대한 통계가 없어 플래너가 헛발질하니 반드시 후행으로 돌린다.

체크리스트

1. INSERT 대신 COPY: 행 단위 파싱·왕복 오버헤드를 없앤다. psql 클라이언트라면 \copy.
2. 인덱스·FK는 적재 후 생성: 초기 적재라면 드롭 → 로드 → 재빌드.
3. maintenance_work_mem 상향: 인덱스 빌드가 더 큰 메모리로 빨라진다(세션 한정 SET).
4. 단일 트랜잭션: autocommit을 끄고 한 트랜잭션에 묶으면 커밋 오버헤드와 WAL 플러시가 준다.
5. 후행 ANALYZE: 적재 직후 통계를 갱신해 이후 쿼리가 올바른 플랜을 타게 한다.

이렇게도 쓴다

클라이언트 로컬 파일은 \copy — 서버 파일 접근 권한 없이 psql이 스트리밍한다.

\copy bulk FROM 'local.csv' WITH (FORMAT csv)

 

한 트랜잭션으로 묶어 커밋 오버헤드를 줄인다. (조합: 단일 트랜잭션)

BEGIN;
COPY bulk FROM '/tmp/bulk.csv' WITH (FORMAT csv);
CREATE INDEX ...;
ANALYZE bulk;
COMMIT;

 

파이프로 흘려 넣는다 — 압축 해제/변환을 중간에 끼운다. (조합: PROGRAM)

COPY bulk FROM PROGRAM 'zcat /data/dump.csv.gz' WITH (FORMAT csv);

 

기존 대용량 테이블에 적재할 땐 인덱스를 잠깐 드롭했다 재생성한다.

DROP INDEX bulk_a_uname;
COPY bulk FROM '/tmp/more.csv' WITH (FORMAT csv);
CREATE INDEX bulk_a_uname ON bulk(uname);

 

WAL 부담을 줄인다 — 같은 트랜잭션에서 만든 테이블에 COPY하면 WAL을 건너뛴다(wal_level=minimal 시).

BEGIN;
CREATE TABLE bulk (...);
COPY bulk FROM '/tmp/bulk.csv' WITH (FORMAT csv);  -- WAL 최소화
COMMIT;

언제 이 순서를 안 쓰나: 이미 데이터가 많이 든 운영 테이블에 소량을 자주 추가하는 경우엔 인덱스 드롭/재빌드 비용이 오히려 크다. "인덱스 후생성"은 테이블이 비었거나 적재량이 기존 대비 압도적으로 클 때의 전략이다.