[PostgreSQL] COPY 수백만 행을 INSERT보다 빠르게 밀어 넣는다

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

부제: 초기 데이터 이관이나 배치 적재에서 행을 한 건씩 INSERT하면 하세월일 때, 파일이나 스트림을 통째로 받아 한 번에 적재하고 싶을 때

-- 같은 10만 행을, 한 건씩 INSERT vs COPY 로 적재해 비교
\timing on
CREATE TABLE m10_ins (id bigint, cat int, note text);

-- (1) 한 행씩 INSERT 10만 번
DO $$ BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO m10_ins VALUES (i, i%100, 'x');
  END LOOP;
END $$;
-- Time: 139.075 ms

-- (2) 같은 10만 행을 CSV로 COPY
TRUNCATE m10_ins;
COPY m10_ins FROM '/tmp/m10_100k.csv' WITH (FORMAT csv);
-- Time: 19.171 ms

DO
Time: 139.075 ms       -- 한 행씩 INSERT
...
COPY 100000
Time: 19.171 ms        -- COPY (약 7배 빠름)

COPY는 행을 하나씩 파싱·계획·실행하는 INSERT와 달리, 데이터 스트림을 통째로 받아 파서와 실행 계획을 재사용하며 대량으로 밀어 넣는다. 같은 10만 행을 넣는데 한 건씩 INSERT는 139ms, COPY는 19ms로 약 7배 차이가 났고, 이 격차는 행 수가 늘수록, 특히 INSERT를 문장마다 개별 커밋·네트워크 왕복으로 날릴수록 훨씬 벌어진다. 서버 측 COPY는 서버 디스크의 파일을 읽으므로 슈퍼유저 권한이 필요하고, 클라이언트 파일이면 psql의 \copy를 쓴다. 대량 이관·로그 적재·DB 간 이사에서 표준으로 쓰는 경로다.

이렇게도 쓴다

WAL을 쓰지 않는 UNLOGGED 테이블로 스테이징을 더 빠르게 한다. (조합: UNLOGGED)

CREATE UNLOGGED TABLE m10_stage (id bigint, cat int, note text);
COPY m10_stage FROM '/tmp/m10_100k.csv' WITH (FORMAT csv);   -- 크래시 복구 대상 아님, 대신 빠름

 

일부 컬럼만 적재하고 나머지는 기본값으로 채운다. (조합: 컬럼 목록)

CREATE TABLE m10_cols (id bigint, cat int DEFAULT -1, note text DEFAULT 'n/a');
\copy m10_cols(id) FROM PROGRAM 'seq 1 3'
--  id | cat | note   →  1,2,3 행의 cat/note 는 기본값

 

파일 없이 인라인 데이터를 바로 흘려 넣는다. (조합: FROM STDIN)

COPY m10_inline FROM STDIN WITH (FORMAT csv);
10,hello
20,world
\.

 

헤더 있는 CSV를 첫 줄 건너뛰고 읽는다. (조합: HEADER)

COPY m10_ins FROM '/tmp/data.csv' WITH (FORMAT csv, HEADER true);

 

압축 파일을 풀면서 곧장 적재한다. (조합: FROM PROGRAM)

COPY m10_ins FROM PROGRAM 'gunzip -c /tmp/big.csv.gz' WITH (FORMAT csv);

 

대량 적재는 인덱스를 지운 뒤 넣고 나중에 다시 만든다(적재 중 인덱스 유지 비용 회피).

DROP INDEX m10_idx;         -- 적재 전
COPY m10_ins FROM '...';
CREATE INDEX m10_idx ON m10_ins(id);   -- 적재 후 한 번에