운영 DB 대용량 로그 테이블 정리기 — 백업부터 DROP까지

1. 배경

운영 환경에서 장기간 누적된 영상 재생 로그용 테이블의 용량이 약 800GB에 달해 DB 성능 저하 및 디스크 용량 부족 이슈가 발생했다.

• 단순 SELECT COUNT(*) 도 수 분 이상 걸림
• pg_dump 전체 백업 불가 (EC2 디스크 용량 한계)
• 실제 서비스 로직에서는 더 이상 사용하지 않음 (API 주석 처리 완료)

이에 따라 “백업 + 데이터 정리 + DROP” 단계로 접근한 실무 고민 및 절차를 정리해본다.

본 글에서는 프로젝트명을 ProjectA, 스키마명을 main, 테이블명을 ad_log_records로 가정한다.

---

2. 현황 요약

구분	항목	현황
운영 DB	main.ad_log_records	799GB (약 6.9억 행)
개발 S3	사용량	3GB
운영 S3	사용량	3.3GB
개발 EC2	디스크 용량	30GB (사용 12GB / 잔여 19GB)
운영 EC2	디스크 용량	20GB (사용 3GB / 잔여 18GB)

📌 문제 요약

• EC2 로컬 디스크로 전체 백업 불가
• S3 용량은 비어 있지만, 한 번에 업로드하기엔 네트워크 부하 우려
• 테이블이 너무 커서 단순 통계 조회도 어려움

---

3. 전략 목표

운영 DB 디스크 확보

• 오래된 데이터(2024년 등 과거분)부터 백업 후 삭제

운영 서비스 무중단

• 실제 API는 이미 DB 접근 비활성화 상태로 안전하게 작업 가능

S3 중심의 백업 파이프라인 구축

• EC2 디스크를 거의 사용하지 않고 pg_dump → gzip → S3 스트리밍 방식 적용

데이터 복구 가능성 보장

• 커스텀 포맷(-Fc) 백업으로 재복원 시 효율적

---

4. 백업 및 이관 전략

A. 조건부  pg_dump  + S3 스트리밍 백업

pg_dump -Fc -t main.ad_log_records \
  --where "created_at >= '2024-01-01' AND created_at < '2025-01-01'" <DB명> \
  | gzip | aws s3 cp - s3://projecta-backup/ad_log_records_2024.dump.gz

• -Fc: PostgreSQL 커스텀 포맷 (압축 효율 ↑, 복원 시 유연성 ↑)
• gzip: 추가 압축으로 S3 저장 효율 향상
• 연도/월 단위 분할 백업: 중간 실패 시에도 재시작 용이

B. 백업 완료 후 데이터 삭제

백업 확인 후, 일정 단위로 배치 삭제 수행:

DELETE FROM main.ad_log_records
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
LIMIT 100000;

• ⚠️ 대용량 DELETE 시 한 번에 지우지 말고, 배치 단위로 반복 실행하여 트랜잭션 부하를 줄이는 게 핵심

C. 올해(2025년) 데이터 백업

• 전체 예상 용량 약 480GB
• S3 용량 상황에 따라 월별 / 분기별 분할 백업
• S3 부족 시 AWS Glacier 장기 보관소 활용

D. 월별 데이터 용량/행수 추정

COUNT(*) 대신 샘플 기반 추정:

WITH sample AS (
  SELECT created_at
  FROM main.ad_log_records
  WHERE created_at >= '2025-01-01'
  ORDER BY id DESC
  LIMIT 100000
)
SELECT EXTRACT(MONTH FROM created_at) AS month,
       COUNT(*) AS sample_count
FROM sample
GROUP BY EXTRACT(MONTH FROM created_at)
ORDER BY month;

• 샘플 비율 × 전체 reltuples(pg_class) 로 월별 용량을 추정

---

5. 2차 분석 — DROP 검토

• 기존 API(영상 재생 로그 전송)는 AWS Lambda → DB INSERT 구조
• 하지만 현재 Lambda 함수에서 호출 막은 상태
• 따라서 테이블 DROP 시 서비스 영향 없음

---

6. DROP 전략 순서 (운영용 안전 절차)

1단계: 테이블명 변경 (rename)

ALTER TABLE main.ad_log_records RENAME TO ad_log_records_to_drop;

• 💬 빠른 이유: 실제 데이터 파일을 옮기지 않고, pg_class의 테이블 이름만 변경 (수 ms 수준)
• 단, 누군가 이 테이블을 SELECT 중이면 락 대기 가능하므로 pg_stat_activity 로 확인 후 실행 권장

2단계: 모니터링 (1~2일)

• pg_stat_activity 에서 관련 쿼리 없음 확인
• 앱·서버 로그에 "relation does not exist" 에러 없는지 점검

3단계: 완전 삭제

DROP TABLE main.ad_log_records_to_drop;

• ⚠️ 오프피크 시간대(새벽)에 실행 권장
• 800GB라도 메타데이터 삭제만 하므로 논리적 DROP은 수 초 이내 완료됨
• 단, 파일 삭제는 OS 레벨에서 백그라운드로 진행되어 디스크 I/O 일시 상승 가능

4단계: 디스크 회수 확인

SELECT pg_size_pretty(pg_database_size(current_database()));

or

SELECT pg_size_pretty(pg_total_relation_size('main.ad_log_records_to_drop'));

---

7. 주요 개념 정리

약어 / 명령어	의미
DDL	Data Definition Language (CREATE / DROP / ALTER 등 스키마 정의 명령어)
WAL	Write-Ahead Log — PostgreSQL의 트랜잭션 복구용 로그 파일
pg_stat_activity	현재 DB 세션 상태 모니터링 뷰
pg_dump -Fc	PostgreSQL 백업 명령 (커스텀 포맷, 압축 지원)
S3 / Glacier	AWS 스토리지 서비스 (S3 = 일반 보관, Glacier = 장기 보관)
autovacuum	PostgreSQL의 자동 정리 프로세스 (DROP 시 충돌 가능)

---

8. 최종 권장 절차 요약

단계	작업	예상 시간	위험도
1단계	RENAME (백업용 이름 변경)	수 ms	매우 낮음
2단계	1~2일 모니터링	-	없음
3단계	DROP (off-peak 실행)	수 초	중간 (I/O 주의)
4단계	디스크 회수 확인	-	없음

---

9. 결론

• 주석 처리된 API 덕분에 서비스 레벨에서의 의존성은 제거됨
• RENAME → 모니터링 → DROP 순서로 진행 시 운영 중에도 안전하게 정리 가능
• EC2 디스크를 거의 쓰지 않고 S3로 백업 스트리밍 가능
• 약 800GB 대용량 테이블도 무중단 백업 및 삭제 구현 성공

---

후기

운영 중 대용량 테이블을 정리하는 작업은

“단순히 DROP 하면 된다” 수준이 아니라

백업 경로, 스토리지 용량, 서비스 의존성, 락 관리까지 고려해야 한다.

 

이번 케이스에서는

• Lambda → DB 로깅 로직 비활성화
• S3 스트리밍 백업 방식 도입
• 단계적 DROP 전략
• 을 통해 안전하게 800GB급 테이블을 제거할 수 있었다.