필사 모드: 대용량 데이터 처리 완전 가이드: COPY, 청크 배치, 그리고 되돌릴 수 있는 작업

• 들어가며
• 1. 대량 작업이 실패하는 세 가지 방식
• 2. 적재 — COPY와 INSERT의 차이
• 3. 초기 적재 절차
• 4. 불량 행을 견디는 적재
• 5. 대량 UPDATE와 DELETE를 청크로 나누기
• 6. 삭제 이후의 뒷정리
• 7. 백필 작업을 안전하게 설계하기
• 8. 되돌릴 수 있게 만들기
• 퀴즈: 실력을 확인해 보세요
• 마치며
• 참고 자료
• 이어서 읽기

들어가며

대용량 데이터 작업은 개발 환경에서 잘 돌던 스크립트가 운영에서 서비스를 세우는 대표적인 영역입니다. 100만 행에서 30초 걸린 작업이 3억 행에서 그냥 300배 걸리지 않습니다. 어느 지점부터는 작동 방식 자체가 달라집니다. WAL이 폭증해 체크포인트가 몰아치고, 죽은 행이 쌓여 테이블이 부풀고, 한 트랜잭션이 너무 길어져 VACUUM이 멈추고, 복제 지연이 벌어집니다.

이 글은 그 지점들을 순서대로 다룹니다. 적재, 갱신, 삭제, 백필. 각 작업에서 무엇이 병목이 되고, PostgreSQL 문서가 권장하는 절차가 무엇이며, 실패했을 때 어떻게 되돌리는가.

기준 엔진은 PostgreSQL 18 이며, 인용한 옵션과 기본값은 모두 PostgreSQL 18 문서에서 확인했습니다.

1. 대량 작업이 실패하는 세 가지 방식

먼저 실패 유형을 알아 둡시다. 대응 전략이 여기서 갈립니다.

첫째, 한 트랜잭션이 너무 크다. 3억 행을 한 트랜잭션으로 갱신하면 트랜잭션이 몇 시간 열려 있게 됩니다. 그동안 데이터베이스 전체에서 죽은 행 회수가 막히고, 롤백이 발생하면 그 몇 시간이 통째로 버려집니다. 중간에 실패하면 처음부터 다시 시작해야 합니다.

둘째, 자원 소비가 순간적으로 폭발한다. 대량 쓰기는 WAL을 대량으로 만들고, WAL이 max_wal_size(기본값 1GB)를 넘길 때마다 체크포인트가 발생합니다. 체크포인트가 몰아치면 더티 페이지를 디스크로 밀어내느라 I/O가 포화되고, 서비스 쿼리의 지연이 튑니다.

셋째, 뒷정리가 남는다. 대량 삭제나 갱신은 죽은 행을 그만큼 만듭니다. 삭제 자체는 끝났는데 테이블 크기는 그대로이고, 순차 스캔은 여전히 옛날만큼 페이지를 읽습니다. 작업이 "끝났다"고 보고한 시점과 시스템이 정상으로 돌아온 시점 사이에 며칠의 간격이 생깁니다.

이 세 가지에 대한 대응이 각각 청크 분할, 자원 파라미터 조정과 속도 제한, VACUUM 계획입니다.

2. 적재 — COPY와 INSERT의 차이

PostgreSQL 문서의 "Populating a Database" 장은 대량 적재의 표준 절차를 담고 있습니다. 첫 두 항목이 가장 중요합니다.

자동 커밋을 끄고 하나의 트랜잭션으로 묶으라. 문서 설명대로 PostgreSQL은 개별적으로 커밋되는 행마다 상당한 작업을 수행하므로, 자동 커밋을 끄면 그 오버헤드가 줄어듭니다.

COPY를 쓰라. 문서 표현으로 COPY는 대량 적재에 최적화되어 있고 여러 INSERT보다 "훨씬 적은 오버헤드"를 발생시킵니다. 단일 COPY 명령을 쓰면 자동 커밋을 끌 필요조차 없습니다. COPY를 쓸 수 없다면 PREPARE로 준비된 INSERT를 만들고 EXECUTE를 반복해 파싱과 계획 수립 오버헤드를 피하라고 권합니다.

COPY의 주요 옵션과 문서 기준 기본값입니다.

# 파일에서 서버로: psql의 \copy는 클라이언트 파일을 읽는다
psql -d appdb -c "\copy orders_staging FROM 'orders.csv' WITH (FORMAT csv, HEADER true)"

-- 서버 파일 시스템에서 직접 읽기 (서버 권한 필요)
COPY orders_staging FROM '/var/lib/pgsql/import/orders.csv'
  WITH (FORMAT csv, HEADER true);

-- 프로그램 출력에서 읽기
COPY orders_staging FROM PROGRAM 'zcat /var/lib/pgsql/import/orders.csv.gz'
  WITH (FORMAT csv, HEADER true);

HEADER에는 MATCH 값을 줄 수 있습니다. 이 경우 헤더 이름이 테이블 컬럼과 정확히 일치해야 하므로, 컬럼 순서가 바뀐 CSV를 조용히 잘못 적재하는 사고를 막아 줍니다. 외부에서 받는 파일에는 항상 HEADER MATCH를 쓰는 것을 권합니다.

3. 초기 적재 절차

빈 테이블이나 새 테이블에 대량 적재할 때 문서가 권장하는 순서입니다.

1단계 — 인덱스를 나중에 만든다. 문서 표현으로 "테이블을 만들고, COPY로 대량 적재한 뒤, 인덱스를 만든다. 기존 데이터에 인덱스를 만드는 것이 행마다 점진적으로 갱신하는 것보다 빠르다." 이미 데이터가 있는 테이블에 대량 추가하는 경우에도 인덱스를 지우고 적재한 뒤 다시 만드는 편이 빠를 수 있지만, 그동안 다른 사용자의 성능이 나빠진다는 단서가 붙습니다.

2단계 — 외래 키 제약을 나중에 만든다. 문서는 이것이 단순한 최적화가 아닐 수 있다고 경고합니다. 외래 키는 "대량으로 검사하는 편이 행마다 검사하는 것보다 효율적"이며, 수백만 행을 적재할 때는 "트리거 이벤트 큐가 가용 메모리를 넘겨 견딜 수 없는 스와핑이나 완전한 실패를 일으킬 수 있다"고 합니다.

3단계 — maintenance_work_mem을 임시로 올린다. 기본값은 64MB입니다. 문서에 따르면 이 값은 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 같은 유지보수 작업이 쓰며, 대량 적재 중 임시로 올리면 인덱스 생성과 외래 키 추가가 빨라집니다. COPY 자체는 빨라지지 않습니다.

4단계 — max_wal_size를 임시로 올린다. 기본값은 1GB입니다. 문서 설명대로 대량 적재는 체크포인트를 평소보다 자주 발생시키고, 이 값을 올리면 필요한 체크포인트 횟수가 줄어듭니다.

5단계 — 끝나면 ANALYZE를 돌린다. 문서는 "대량 적재 후에는 ANALYZE(또는 VACUUM ANALYZE)를 실행해 플래너가 최신 통계를 갖도록 하라"고 하며, 통계가 없으면 플래너가 나쁜 계획을 세울 수 있다고 경고합니다. autovacuum이 켜져 있으면 자동으로 돌 수도 있지만, 적재 직후 곧바로 쿼리를 받아야 한다면 직접 실행하는 편이 확실합니다.

-- 초기 적재 템플릿
SET maintenance_work_mem = '2GB';   -- 세션 한정

BEGIN;
CREATE TABLE orders_new (LIKE orders INCLUDING DEFAULTS);
COPY orders_new FROM '/import/orders.csv' WITH (FORMAT csv, HEADER match);
COMMIT;

CREATE INDEX idx_orders_new_tenant ON orders_new (tenant_id, created_at DESC);
ALTER TABLE orders_new ADD PRIMARY KEY (id);
ANALYZE orders_new;

RESET maintenance_work_mem;

경고: 문서는 wal_level을 minimal로, archive_mode를 off로, max_wal_senders를 0으로 내려 WAL 아카이브와 스트리밍 복제를 끄는 방법도 소개합니다. 하지만 같은 문서가 단서를 답니다. 이 변경은 서버 재시작이 필요하고, 이전 베이스 백업을 아카이브 복구와 스탠바이 서버에 사용할 수 없게 만들어 데이터 손실로 이어질 수 있습니다. 운영 데이터베이스에서는 이 항목을 건너뛰세요. 초기 구축 중인 새 클러스터에만 해당하는 조언입니다.

COPY FREEZE도 초기 적재 전용 옵션입니다. 문서에 따르면 현재 서브트랜잭션에서 테이블이 생성되거나 잘렸어야 하고, 열린 커서가 없어야 하며, 트랜잭션이 더 오래된 스냅샷을 들고 있지 않아야 하고, 파티션 테이블과 외부 테이블에는 쓸 수 없습니다. 그리고 문서가 명시하듯 적재가 성공하는 즉시 다른 모든 세션이 데이터를 볼 수 있게 되어 통상적인 MVCC 가시성 규칙을 위반합니다.

4. 불량 행을 견디는 적재

외부에서 받은 파일에는 반드시 깨진 행이 있습니다. 기본 동작은 첫 오류에서 전체가 실패하는 것입니다. ON_ERROR의 기본값이 stop이기 때문입니다.

ignore로 바꾸면 오류가 난 행을 버리고 계속 진행합니다. 문서에 따르면 이 값은 COPY FROM에서 text 또는 csv 형식일 때만 적용됩니다.

COPY orders_staging FROM '/import/orders.csv'
  WITH (FORMAT csv, HEADER match,
        ON_ERROR ignore,
        LOG_VERBOSITY verbose,
        REJECT_LIMIT 1000);

LOG_VERBOSITY는 default, verbose, silent 중 하나이며, ON_ERROR가 ignore일 때 어느 정도로 로그를 남길지 정합니다. REJECT_LIMIT은 허용할 최대 오류 수로, 문서에 따르면 ON_ERROR=ignore와 함께 써야 하고 양의 정수여야 합니다. 이 절을 생략하면 오류 수에 제한이 없어 잘못된 데이터를 전부 조용히 건너뜁니다.

REJECT_LIMIT을 반드시 지정하세요. 제한이 없으면 파일 인코딩이 통째로 잘못된 경우에도 "적재 성공, 0행"이라는 결과를 받게 됩니다.

더 안전한 패턴은 모든 컬럼을 text로 받는 스테이징 테이블을 두는 것입니다.

CREATE UNLOGGED TABLE orders_raw (
  id_txt text, tenant_txt text, amount_txt text, created_txt text
);

COPY orders_raw FROM '/import/orders.csv' WITH (FORMAT csv, HEADER match);

-- 검증과 변환을 SQL로 수행하고, 불량 행은 남겨서 조사한다
INSERT INTO orders (id, tenant_id, total_amount, created_at)
SELECT id_txt::bigint, tenant_txt::bigint,
       amount_txt::numeric, created_txt::timestamptz
FROM orders_raw
WHERE id_txt ~ '^[0-9]+$'
  AND amount_txt ~ '^[0-9]+(\.[0-9]+)?$';

UNLOGGED 테이블은 WAL을 거의 쓰지 않으므로 스테이징에 적합합니다. 다만 크래시 시 내용이 사라지고 복제본으로 전파되지 않습니다. 버리고 다시 만들 수 있는 데이터에만 쓰세요.

5. 대량 UPDATE와 DELETE를 청크로 나누기

3억 행을 한 문장으로 갱신하면 안 되는 이유는 1절에서 봤습니다. 청크로 나누는 것이 표준 대응입니다.

청크 분할 설계에는 네 가지 요건이 있습니다.

요건 1 — 각 청크가 독립된 트랜잭션이어야 합니다. 그래야 중간에 실패해도 이미 처리한 부분이 남습니다.

요건 2 — 재개 가능해야 합니다. 어디까지 처리했는지 기록해 두고, 다시 시작하면 그 지점부터 이어가야 합니다.

요건 3 — 진행률을 알 수 있어야 합니다. 몇 시간짜리 작업에서 "얼마나 남았는가"에 답할 수 없으면 운영이 불가능합니다.

요건 4 — 속도를 조절할 수 있어야 합니다. 서비스 지연이 튀면 청크 사이의 대기 시간을 늘려 부하를 낮출 수 있어야 합니다.

키 범위로 나누는 방식이 가장 견고합니다. OFFSET을 쓰면 뒤로 갈수록 느려지므로 쓰지 마세요.

-- 진행 상태를 테이블에 남긴다
CREATE TABLE backfill_progress (
  job_name   text PRIMARY KEY,
  last_id    bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO backfill_progress (job_name) VALUES ('orders_channel_backfill')
ON CONFLICT DO NOTHING;

-- 한 청크: 애플리케이션이나 스케줄러가 이 블록을 반복 호출한다
BEGIN;
SET LOCAL statement_timeout = '60s';
SET LOCAL lock_timeout = '3s';

WITH bounds AS (
  SELECT last_id FROM backfill_progress
  WHERE job_name = 'orders_channel_backfill'
  FOR UPDATE
),
target AS (
  SELECT o.id
  FROM orders o, bounds b
  WHERE o.id > b.last_id
    AND o.channel IS NULL
  ORDER BY o.id
  LIMIT 5000
),
updated AS (
  UPDATE orders o
     SET channel = 'WEB'
   WHERE o.id IN (SELECT id FROM target)
  RETURNING o.id
)
UPDATE backfill_progress
   SET last_id = COALESCE((SELECT max(id) FROM updated), last_id),
       updated_at = now()
 WHERE job_name = 'orders_channel_backfill'
RETURNING last_id;

COMMIT;

청크 크기를 정하는 기준은 한 청크가 1초 이내에 끝나는가입니다. 그보다 길면 잠금 보유 시간이 길어져 서비스에 영향을 줍니다. 실측해서 조정하세요. SET LOCAL lock_timeout을 걸어 두는 것도 중요합니다. 잠금 대기로 청크가 오래 걸리면 그 자체가 서비스 지연이 됩니다.

대량 삭제도 같은 구조입니다. 다만 삭제에는 더 나은 선택지가 있는 경우가 많습니다. 파티션 테이블이라면 DROP TABLE이나 DETACH PARTITION이 압도적으로 낫습니다. 그리고 테이블의 대부분을 지우는 것이라면 남길 행만 새 테이블로 복사하고 이름을 바꾸는 방법이 더 빠릅니다.

6. 삭제 이후의 뒷정리

DELETE는 행을 지우지 않습니다. 죽었다고 표시할 뿐입니다. 공간 회수는 VACUUM의 몫입니다.

autovacuum이 언제 도는지는 임계값 공식이 결정합니다. PostgreSQL 18 문서 기준으로 autovacuum_vacuum_threshold의 기본값은 50 튜플, autovacuum_vacuum_scale_factor의 기본값은 0.2, 즉 테이블의 20%입니다. PostgreSQL 18에는 autovacuum_vacuum_max_threshold가 추가되어 기본값이 1억 튜플이며, 이 상한 덕분에 아주 큰 테이블에서도 vacuum이 무한정 미뤄지지 않습니다.

대량 삭제 직후에는 직접 실행하는 편이 낫습니다.

-- 병렬로 인덱스까지 정리하고 통계도 갱신
VACUUM (ANALYZE, VERBOSE, PARALLEL 4) orders;

문제는 공간이 운영체제로 돌아오지 않는다는 점입니다. 일반 VACUUM은 죽은 행이 차지하던 공간을 재사용 가능하게 표시할 뿐, 테이블 파일 크기는 그대로입니다(테이블 끝부분의 빈 페이지는 예외적으로 반환됩니다).

경고: 공간을 실제로 돌려받으려면 VACUUM FULL이 필요하지만, 이 명령은 대상 테이블에 ACCESS EXCLUSIVE 잠금을 겁니다. 읽기까지 전부 차단되며, 테이블 전체를 새로 쓰므로 원본 크기만큼의 여유 디스크가 추가로 필요합니다. PostgreSQL 문서 자체가 "따라서 일반적으로 관리자는 표준 VACUUM을 쓰도록 노력하고 VACUUM FULL은 피해야 한다"라고 명시합니다. CLUSTER도 같은 잠금을 잡습니다. 서비스 중에 필요하다면 pg_repack 같은 외부 도구를 검토하되, 도구의 동작과 제약은 해당 도구 문서에서 확인하세요.

실무적인 결론은 이렇습니다. 정기적으로 대량 삭제가 발생하는 테이블은 파티셔닝하세요. 파티션을 통째로 떼어 내면 죽은 행도, VACUUM도, bloat도 발생하지 않습니다. 이것이 파티셔닝을 도입하는 가장 실용적인 이유 중 하나입니다.

7. 백필 작업을 안전하게 설계하기

백필은 "이미 있는 데이터에 새 규칙을 적용하는" 작업입니다. 신규 컬럼 채우기, 잘못 저장된 값 교정, 새 인덱스용 정규화 컬럼 생성 등이 여기 속합니다.

설계 원칙 다섯 가지입니다.

원칙 1 — 신규 데이터부터 막는다. 백필을 시작하기 전에 애플리케이션이 새 규칙으로 쓰도록 먼저 배포합니다. 그러지 않으면 백필이 진행되는 동안 새 데이터가 계속 옛 규칙으로 들어와 끝나지 않습니다.

원칙 2 — 멱등하게 만든다. 같은 청크를 두 번 실행해도 결과가 같아야 합니다. WHERE channel IS NULL 같은 조건을 넣어 이미 처리된 행을 건드리지 않게 하면 자연히 멱등해집니다.

원칙 3 — 부하를 관측하며 조절한다. 청크 사이 대기 시간을 설정으로 빼 두고, 복제 지연과 서비스 지연을 보며 실시간으로 조절할 수 있게 합니다. 특히 복제 지연을 감시하세요.

-- 스탠바이의 복제 지연 확인 (프라이머리에서)
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

원칙 4 — 진행률과 예상 완료 시각을 남긴다. backfill_progress 테이블에 처리 건수와 시각을 기록하면 남은 시간을 계산할 수 있습니다.

원칙 5 — 검증을 작업의 일부로 포함한다. "끝났다"가 아니라 "끝났고 검증했다"가 완료 조건입니다.

-- 백필 완료 검증: 미처리 행이 0인가
SELECT count(*) AS remaining FROM orders WHERE channel IS NULL;

-- 값 분포가 예상과 맞는가
SELECT channel, count(*) FROM orders GROUP BY 1 ORDER BY 2 DESC;

8. 되돌릴 수 있게 만들기

대량 작업의 롤백은 "역방향 문장"이 아닙니다. 이미 커밋된 청크들은 트랜잭션 롤백으로 되돌아가지 않습니다.

실무에서 쓰는 안전망은 세 가지입니다.

첫째, 원본 값을 남긴다. 값을 덮어쓰는 백필이라면 옛 값을 별도 테이블이나 새 컬럼에 보관합니다. 저장 비용을 내고 되돌릴 능력을 사는 것입니다. 작업이 끝나고 검증까지 마친 뒤 정리합니다.

-- 되돌리기용 스냅샷: 바꿀 행의 옛 값만 저장
CREATE UNLOGGED TABLE orders_channel_backup AS
SELECT id, channel FROM orders WHERE channel IS NOT NULL;

둘째, 드라이런을 먼저 돌린다. 실제 갱신 없이 영향받는 행 수와 샘플을 확인합니다. EXPLAIN ANALYZE로 확인하려면 문서가 권하는 대로 트랜잭션으로 감싸고 롤백하세요.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET channel = 'WEB' WHERE channel IS NULL AND id BETWEEN 1 AND 5000;
ROLLBACK;

문서는 이 패턴을 명시적으로 안내합니다. "ANALYZE 옵션을 쓰면 문장이 실제로 실행된다는 점을 기억하라. EXPLAIN은 SELECT가 반환할 출력은 버리지만 그 밖의 부수 효과는 평소대로 일어난다."

셋째, 스테이징에서 같은 규모로 리허설한다. 100만 행에서 잰 시간에 300을 곱하는 것은 대개 틀립니다. 인덱스 갱신 비용과 캐시 적중률이 규모에 따라 비선형으로 변하기 때문입니다. 최소한 같은 자릿수의 데이터로 측정하세요.

작업 시간대 선택도 안전망의 일부입니다. 트래픽이 낮은 시간이 좋지만, 야간에는 대응 인력도 적다는 점을 함께 고려해야 합니다. 되돌릴 수 있게 설계했다면 오히려 사람이 많은 시간에 하는 편이 나을 수 있습니다.

퀴즈: 실력을 확인해 보세요

CREATE TABLE archive_new (LIKE archive INCLUDING ALL);
INSERT INTO archive_new SELECT * FROM archive WHERE created_at >= '2026-01-01';
-- 짧은 잠금 구간에서 교체
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE archive RENAME TO archive_old;
ALTER TABLE archive_new RENAME TO archive;
COMMIT;

SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

마치며

대용량 데이터 작업의 원칙은 세 문장으로 요약됩니다. 한 번에 다 하지 않는다. 되돌릴 수 있게 한다. 끝난 뒤의 뒷정리까지 작업에 포함한다.

특히 세 번째를 강조합니다. 삭제가 끝난 시점과 시스템이 정상으로 돌아온 시점은 다릅니다. 죽은 행 회수, 통계 갱신, 인덱스 정리까지가 작업의 일부입니다. "DELETE 3시간, VACUUM 1일"이라고 계획에 적어 두면 나중에 놀라지 않습니다.

그리고 반복되는 대량 삭제가 있다면 그것은 파티셔닝을 검토하라는 신호입니다. 파티션을 떼어 내는 작업에는 죽은 행도 VACUUM도 없습니다.

CSV 변환이 필요하면 CSV/JSON 변환기를, 대용량 테스트 데이터가 필요하면 목업 데이터 생성기를 활용하세요.

참고 자료

• PostgreSQL 18, Populating a Database: https://www.postgresql.org/docs/18/populate.html (2026-08-15 확인)
• PostgreSQL 18, COPY: https://www.postgresql.org/docs/18/sql-copy.html (2026-08-15 확인)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (2026-08-15 확인)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (2026-08-15 확인)
• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (2026-08-15 확인)
• PostgreSQL 18, Write Ahead Log: https://www.postgresql.org/docs/18/runtime-config-wal.html (2026-08-15 확인)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (2026-08-15 확인)

이어서 읽기

• 이전 편: 데이터베이스 캐싱 전략 완전 가이드 — 무효화가 전부다
• 다음 편: 데이터 모델링 완전 가이드 — 논리 모델에서 물리 모델까지
• 파티셔닝과 샤딩 완전 가이드 — 대량 삭제를 DROP으로 바꾸기
• PostgreSQL VACUUM과 MVCC 내부 구조 — 죽은 행이 쌓이는 원리
• CSV/JSON 변환기 — 적재용 파일 변환
• 목업 데이터 생성기 — 대용량 테스트 데이터 만들기