천만 행 DELETE 한 번에 복제가 수십 분 밀렸다

오래된 로그를 치우려고 DELETE 를 한 번 돌렸다. 천만 행을 지우는 문장 하나였다.

주 서버는 버텼다. 무너진 건 복제본. 복제 지연이 몇 초에서 수십 분으로 뛰었고, 읽기 트래픽을 받던 복제본은 옛 데이터를 돌려주기 시작했다.

디스크는 오히려 늘었다. 지웠는데 공간이 늘다니 이해가 안 됐다. 알고 보니 로그 파일과 죽은 행이 같이 불어난 결과였다.

치우려던 작업이 치울 대상보다 큰 사고가 됐다. 이런 정리는 대개 밤에 혼자 돌린다. 문제를 발견하는 사람도 혼자고, 되돌릴 방법도 마땅치 않다.

지운다는 건 기록을 새로 쓰는 일

DELETE 는 데이터를 없애지 않는다. 새로운 변경 기록을 쓴다.

PostgreSQL 에서 DELETE 는 행에 삭제 표시를 하고 WAL 에 기록한다. 공간을 실제로 돌려받는 건 나중에 VACUUM 이 한다. 그래서 지우는 순간에는 공간이 줄지 않는다. 오히려 WAL 이 는다.

복제는 이 변경 기록을 복제본이 따라 실행하는 구조다. 주 서버에서 한 문장으로 천만 행을 지우면 복제본은 그만큼의 변경을 재생해야 한다. 그런데 재생은 대개 단일 스레드다. 주 서버는 여러 코어로 나눠 처리했는데 복제본은 하나로 따라가니, 구조적으로 뒤처질 수밖에 없다.

MySQL 의 행 기반 복제에선 더 직접적이다. 지운 행 하나하나가 이벤트로 기록되니 바이너리 로그가 천만 개의 삭제 이벤트로 부푼다. 그걸 복제본이 하나씩 적용한다.

여기에 락이 겹친다. 큰 DELETE 는 오래 도는 트랜잭션이고, 그동안 그 행들엔 락이 걸려 있다. 커밋 전까지는 청소도 못 한다. 그래서 "지우는 동안 느리고, 지우고 나서도 한동안 느린" 상태가 된다.

한 번에 말고 잘게

한 번에 지우지 말고 나눠서 지운다.

✕ DELETE FROM logs WHERE created_at < now() - interval '90 days';
✕ -- 천만 행을 한 트랜잭션에서
✕ -- WAL 폭증 · 복제 지연 · 긴 락

· 배치로 나눈다 — 각각이 짧은 트랜잭션
✓ DELETE FROM logs
✓ WHERE id IN (
✓ SELECT id FROM logs
✓ WHERE created_at < now() - interval '90 days'
✓ LIMIT 5000)
✓ ;
✓ -- 지운 행 수가 0 이 될 때까지 반복, 사이에 잠깐 쉰다

· 애초에 파티션이면 지우는 게 아니라 떼어낸다
✓ ALTER TABLE logs DETACH PARTITION logs_2026_04;
✓ DROP TABLE logs_2026_04;   -- 즉시, WAL 거의 없음

✕ 문제 지점 · ✓ 해법 · 그대로 돌리기 전에 자기 환경에 맞게 고친다.

배치 크기는 행 수 말고 시간으로

나눠 지우는 게 표준 처방이다. 그럼 한 번에 얼마씩 지워야 할까?

기준은 한 배치가 끝나는 데 걸리는 시간이다. 대략 1초 안쪽이면 안전하다. 그보다 길어지면 락을 쥐는 시간이 늘고, 복제도 뒤처지기 시작한다. 행 수로는 수천 개 정도에서 시작해서 실제 소요 시간을 보며 맞춰 가면 된다.

배치 사이엔 쉬어야 한다. 쉬지 않고 연달아 돌리면 결국 같은 부하를 만든다. 제일 안전한 건 복제 지연을 직접 보면서, 임계값을 넘으면 멈추는 방식이다. 지연이 5초를 넘으면 쉬고, 1초 아래로 내려오면 다시 도는 식이다.

지울 행을 찾는 것도 공짜가 아니다. WHERE created_at < ... 조건에 인덱스가 없으면 배치마다 풀스캔을 한다. 나눠 지우는 게 도리어 더 느려진다. 기준 컬럼에 인덱스가 있는지부터 봐야 한다.

정말 큰 정리라면 방향을 뒤집는 게 낫다. 남길 데이터가 지울 데이터보다 훨씬 적다면, 남길 것만 새 테이블에 복사하고 이름을 바꾸는 편이 압도적으로 빠르다. 천만 행 중 만 행만 남기는 상황이라면 이게 정답이다.

제일 좋은 건 처음부터 파티셔닝해 두는 것이다. 날짜로 파티션을 나눠 두면 오래된 데이터를 지우는 일이 파티션을 떼어내는 일로 바뀐다. 메타데이터만 건드리니 즉시 끝나고 WAL 도 거의 안 생긴다. 로그나 이벤트처럼 시간 기준으로 버리는 데이터라면 처음부터 파티션으로 설계하는 게 맞다.

다음 정리 작업 전에

1. 큰 DELETE 는 배치로 나눈다. 배치 하나가 1초 안에 끝나는 크기로 잡고, 사이사이 쉬는 시간을 둔다.
2. 고정된 속도로 도는 것보다, 복제 지연을 보면서 조절하는 쪽이 안전하다. 임계값을 넘으면 멈추고 회복되면 다시 도는 루프로 만들면 된다.
3. 기준 컬럼에 인덱스가 없으면 배치마다 풀스캔이라 나눈 의미가 없다. 이것부터 확인하자.
4. 지울 게 대부분이라면 방향을 뒤집는 게 낫다. 남길 것만 새 테이블에 복사하고 바꿔 끼우는 편이 훨씬 빠르다.
5. VACUUM 은 공간을 재사용할 수 있게 만들 뿐 OS 에 돌려주진 않는다. 파일 크기까지 줄이려면 pg_repack 같은 도구가 필요하다. 지운 뒤 공간이 실제로 회수됐는지 확인해 두자.
6. 정기적으로 버리는 데이터라면 파티셔닝이 근본 해결이다. 지우는 작업이 떼어내는 작업으로 바뀐다.
7. 정리 작업은 트래픽이 낮은 시간에 돌리고, 진행 상황은 로그로 남긴다. 중간에 멈춰도 이어서 할 수 있어야 한다.

지우는 동안 띄워 둘 쿼리

지우는 동안엔 이 쿼리들을 보면서 속도를 조절한다.

· 복제 지연 — 이걸 보면서 속도를 조절한다
SELECT client_addr,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       replay_lag
  FROM pg_stat_replication;

· WAL 이 얼마나 쌓이고 있나
SELECT pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS wal_pos;

· 죽은 행이 회수되고 있나
SELECT relname, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables
 WHERE relname = 'logs';

· 외래 키 참조 컬럼에 인덱스가 있는지 (지우기 전에 확인)
✓ 없으면 부모 한 행마다 자식 테이블 풀스캔이 일어난다

핵심 한 줄지우는 일도 쓰는 일이다. 한 번에 지우면 한 번에 무너진다.