300만 건의 데이터에서 대량 DELETE를 지양해야 하는 이유

들어가며

최근 팀장님과 데이터베이스 최적화에 대해 이야기를 나누었다. 당시 대화의 주제는 “300만 건이 넘는 대규모 로그 테이블에서 불필요한 과거 데이터를 어떻게 정리할 것인가?” 였다.

나는 이 질문을 듣고 바로 조건문을 달아 DELETE 쿼리를 날리면 된다고 생각했지만, 깊게 생각해보지 않은 1차원적인 답변이었다. 내 답변을 듣고 난 후의 팀장님의 대답은 “대용량 환경에서는 DELETE 쿼리를 사용하는 것이 능사가 아니라는 것” 이다.

이 내용을 주제로 해결책과 근본적인 아키텍처까지 정리해 보고자 한다.

300만 건의 로그 정리 방법

팀장님이 내게 다음과 같은 질문을 던지셨다.

Q: “현재 로그 테이블에 300만 건 정도의 데이터가 쌓여있는데, 조회 성능을 높이려면 어떻게 해야 할까? 올해 데이터를 제외하고 과거 데이터는 필요 없다.”

A: “일단 올해 데이터를 제외한 과거 데이터들을 Dump 떠서 SQL 파일로 백업해 두고, 기존 테이블에서 WHERE 절로 올해 데이터를 제외한 나머지 행들을 전부 DELETE 한다.”

여기서 문제점은, RDBMS의 DML중 가장 무겁고 느린 작업이 바로 DELETE인 것이다.

대규모 DELETE가 문제인 이유

트리 자료구조에서 중간 데이터를 제거할 경우 트리가 재구성 되는 것은 알고는 있었는데, 팀장님의 질문에 대답할 당시에 데이터베이스의 PK는 인덱스 컬럼이고 그 인덱스는 b-tree로 구현되어 있다는 것까지의 깊이까지는 도달하지 못한 것 같다. 그리고 그 첫 번째 이유가 바로 해당 내용이다.

1. 인덱스 B-Tree 재구성 (Reorganization)의 늪

테이블의 각 row에는 pk가 필연적으로 걸려있고, 이는 b-tree 기반의 인덱스로 관리된다. 대량의 행을 삭제하면 단순히 디스크에서 데이터만 지워지는 것이 아니다. 얽혀있는 인덱스 트리에서 해당 노드를 삭제하고, 트리의 균형과 깊이를 유지하기 위해 노드를 merge하거나 split하는 재조정 작업이 동반된다. 이 과정에서 발생하는 CPU 연산과 디스크 I/O는 막대하며 인덱스가 많을수록 그 부하는 배수로 증가한다.

2. 트랜잭션 보장과 Undo Log 기록 (MVCC)

관계형 데이터베이스는 ACID 특성을 보장하기 위해 트랜잭션을 관리한다. 그중에서도 다중 버전 동시성 제어(MVCC)와 롤백 상황에 대비하기 위해, 삭제되는 모든 데이터의 원본 상태를 undo 로그 또는 트랜잭션 로그에 기록한다. 300만 건 중 200만 건을 지운다고 가정하면, 200만 건의 데이터를 일일이 undo 스페이스에 써야 하므로 메모리 버퍼 풀과 디스크 사이에 엄청난 병목이 발생할 것이다.

3. High Water Mark와 데이터 단편화

DELETE로 데이터를 지워도 하드디스크 상의 물리적인 용량은 즉시 반환되지 않는다. 데이터가 있던 위치만 ‘비어있음’으로 논리적 표시가 될 뿐이다. 풀 테이블 스캔을 수행할 때, 데이터가 한 번이라도 채워졌던 최고 수위선인 HWM(High Water Mark)까지 블록을 모두 읽어 들인다. 즉, 200만 건을 지워도 HWM은 그대로 유지되기 때문에 이후 SELECT 풀 스캔 시 성능 저하는 전혀 개선되지 않는다. 또한 빈 공간들이 흩어지면서 발생하는 데이터 단편화는 디스크 I/O 효율을 극도로 떨어뜨린다.

해결책

팀장님이 알려주신 해결방법은 기존 테이블에서 불필요한 것을 지우는 방식이 아니라, 필요한 데이터만 새로운 테이블로 옮기는 방식이었다. 생각보다 정말 간단하다.

기존 테이블과 완전히 동일한 구조의 빈 테이블을 생성하고, 기존 테이블에서 올해 데이터 혹은 필요한 데이터만 조회하여 새로 만든 테이블에 INSERT 한다. 그리고 마지막으로 기존 테이블과 새로운 테이블의 이름을 rename하여 교체한다.

쿼리로 보자면 아래와 같다.

새 테이블 생성

CREATE TABLE log_new LIKE log;

필요한 데이터만 이관

INSERT INTO log_new 
SELECT * FROM log WHERE created_at >= '2026-01-01';

테이블 스왑

RENAME TABLE log TO log_history, log_new TO log;

해당 방식의 기술적 이점은 아래와 같다.

대규모 DELETE의 Undo Log 오버헤드를 피하고, 상대적으로 소량의 INSERT만 수행하므로 DB 부하가 최소화된다.
새 테이블에 데이터가 순차적으로 적재되므로 단편화가 전혀 없는 깔끔한 상태가 된다. HWM 역시 실제 데이터 크기에 맞게 초기화되어 SELECT 스캔 성능이 대폭 향상된다.
MySQL 등 대부분의 RDBMS에서 RENAME은 데이터 딕셔너리(메타데이터)만 수정하는 DDL 명령어이므로, 테이블 락이 걸리는 시간이 매우 짧아 서비스 중단 없이 순식간에 교체가 완료된다.

고려해야 할 엣지 케이스

위에서 언급한 방법은 훌륭하지만 무중단 라이브 서비스에 적용할 때는 반드시 해결해야 할 정합성 문제가 있다.

만약 2번 단계인 INSERT ... SELECT 를 수행하는 데 10분이 소요되었다고 가정해 보자. 그 10분 동안 실제 라이브 서비스에서 발생한 신규 로그들은 기존 log 테이블에 계속 적재되고 있다. 이 상태에서 RENAME를 실행하면 그 10분간 유입된 신규 데이터는 log_history 테이블로 넘어가 버리고 현재 서비스되는 테이블에서는 data loss가 발생하는 치명적인 문제가 있다.

이를 방지하기 위한 현실적인 대안은 다음과 같다.

유지보수 시간 활용 - 트래픽이 가장 적은 새벽 시간에 짧은 점검을 걸고 애플리케이션의 쓰기 요청을 차단한 뒤 작업을 진행한다.
이중 동기화 - 테이블 스왑 직전에 짧게 락을 걸고, 1차 마이그레이션 이후 누락된 시간(10분) 동안 추가로 발생한 델타 데이터를 한 번 더 INSERT 한 뒤 스왑을 진행한다.

궁극적인 해결책: 테이블 파티셔닝

적어도 1년 뒤의 얘기이긴 한데, 앞으로 로그 데이터가 계속 쌓일 텐데 주기적으로 이런 마이그레이션 작업을 수동으로 반복해야 하나라는 의문이 들었다.

로그 테이블처럼 데이터가 시간에 따라 순차적으로 쌓이고 과거 데이터는 주기적으로 만료되는 특성을 가진 경우 애초에 설계 단계부터 테이블 파티셔닝, 그중에서도 Range 파티셔닝을 도입하는 것이 권장된다.

논리적으로는 하나의 테이블처럼 보이지만, 물리적으로는 연도별 혹은 월별로 파일이 분리되어 저장되도록 구성하는 것이다. 이렇게 설계되어 있다면 불필요해진 과거 데이터를 지울 때 다음과 같이 명령어 한 줄로 처리할 수 있다.

ALTER TABLE log DROP PARTITION p_2025;

DROP PARTITION은 RDBMS의 트랜잭션 시스템을 거치지 않고, 물리적인 데이터 파일(.ibd 등) 자체를 OS 레벨에서 삭제해 버린다. 따라서 b-tree 재조정이나 undo log 기록이 전혀 발생하지 않으며 즉각적으로 디스크 용량을 반환할 수 있다.

마치며

단순히 원하는 결과값을 반환하는 쿼리를 작성하는 것을 넘어서 데이터베이스 엔진의 내부 메커니즘을 이해하는 것이 성능 최적화에 얼마나 중요한지 깨달았다.

지금까지는 ‘어떻게 데이터를 잘 저장하고, 빠르게 조회할 것인가’에만 주로 집중해 왔던 것 같다. 하지만 서비스가 실제로 운영되고 데이터가 거대해질수록, ‘어떻게 잘 버릴 것인가’ 역시 시스템의 안정성을 좌우하는 핵심 과제라는 것을 이번 기회에 배우게 되었다.

훗날 프로덕션 레벨에서 테이블을 직접 설계하게 된다면 당장의 비즈니스 요구사항을 충족하는 것을 넘어 데이터의 생명주기와 폐기 전략까지 선제적으로 고려해야겠다는 생각이든다.