백엔드 데이터 인프라 27편 — DELETE 깊이 bloat·VACUUM·파티션 DROP

이 글은 백엔드 데이터 인프라 시리즈 70편 중 27편이에요. 14편 DELETE 에서 "기본 + Soft Delete" 를 다뤘으니, 이번 27편은 그 위에 — bloat·VACUUM·파티션 DROP 운영 깊이.

DELETE 내부 — 다시

24편 DML 개요 에서 봤듯 DELETE 는 행을 "표시만" 하고 실제 디스크 공간은 회수하지 않아요. 대량 DELETE 후 테이블이 부풀어 있는 상태가 되는데, 이걸 bloat(죽은 행이 쌓여 테이블이 부푼 상태) 라고 부릅니다.

-- 1억 행 테이블에서 9000만 행 DELETE
DELETE FROM logs WHERE created_at < '2025-01-01';

-- 디스크 사용량은 그대로! 9000만 행이 "표시"만 됐을 뿐

해결은 VACUUM(죽은 행 공간을 재사용 가능하게 마킹) 입니다.

VACUUM — 옛 행 회수

VACUUM logs;

VACUUM 은 "표시된 행" 의 공간을 "재사용 가능한 빈 공간" 으로 마킹해요. 디스크 크기는 줄어들지 않고, 새로 들어오는 INSERT 가 그 공간을 재사용합니다.

autovacuum — 자동

PG 는 기본으로 autovacuum 이 활성화돼 있어 백그라운드에서 주기적으로 VACUUM 을 돌립니다. 운영에서 거의 신경 안 써도 OK — 단 큰 테이블은 튜닝이 필요해요.

ALTER TABLE logs SET (
    autovacuum_vacuum_scale_factor = 0.1,    -- 10% 변경 시 트리거
    autovacuum_vacuum_threshold = 50000      -- 최소 5만 행 변경
);

기본값은 "20% 변경 + 50행" 이라 큰 테이블에선 트리거가 너무 늦게 걸립니다.

VACUUM FULL — 디스크 회수

VACUUM FULL logs;

테이블을 재작성 해요 — 빈 공간 없이 다시 작성하니까 디스크 크기가 실제로 줄어듭니다. 단 완전 락 이 걸려서 운영 중엔 위험해요.

대안은 pg_repack(락 없이 테이블을 재작성하는 PG 확장) 확장입니다. 락 없이 디스크 회수가 됩니다.

bloat 모니터링

SELECT
    schemaname, relname,
    n_dead_tup,                      -- 죽은 행
    n_live_tup,                       -- 살아있는 행
    ROUND(100.0 * n_dead_tup / NULLIF(n_dead_tup + n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

pg_stat_user_tables(테이블별 죽은 행·살아있는 행 통계 뷰) 에서 dead_pct > 20% 면 bloat 의심. autovacuum 튜닝 또는 수동 VACUUM 으로 대응합니다.

파티션 DROP — 대량 삭제의 우아한 방법

22편 PARTITION TABLE 의 진가가 여기서 나옵니다.

-- 1억 행 DELETE = 30분 + WAL 폭발
DELETE FROM logs WHERE created_at < '2025-01-01';

-- 파티션 DROP = 0.1초
DROP TABLE logs_2024_01;
DROP TABLE logs_2024_02;
-- ...

여기서 WAL(Write-Ahead Log, 변경 기록 로그) 이 폭발하는 게 1억 행 DELETE 의 진짜 비용이에요. 반면 파티션 단위로 "한 번에" 삭제하면 bloat 0, WAL 0, 락 0 — 시계열 데이터의 표준 패턴 입니다.

자동화 — pg_partman

SELECT partman.create_parent(
    p_parent_table => 'public.logs',
    p_control => 'created_at',
    p_interval => '1 month',
    p_premake => 12             -- 12개월 미리 생성
);

pg_partman(월별 파티션 자동 생성·DROP PG 확장) 확장은 월별 파티션을 자동 생성하고 옛 파티션도 자동 DROP 해줘요. 한국 회사 운영에서 자주 씁니다.

대량 DELETE chunk 패턴

파티션이 없는 경우엔 chunk 분할 + autocommit 으로 갑니다.

DO $$
DECLARE
    deleted INTEGER;
BEGIN
    LOOP
        DELETE FROM logs
        WHERE id IN (
            SELECT id FROM logs
            WHERE created_at < '2025-01-01'
            LIMIT 10000
        );
        GET DIAGNOSTICS deleted = ROW_COUNT;
        EXIT WHEN deleted = 0;
        COMMIT;
        PERFORM pg_sleep(0.1);   -- 시스템 부담 양보
    END LOOP;
END $$;

• chunk 10000 = 메모리·WAL 적정
• COMMIT 마다 디스크 동기화
• pg_sleep = 다른 작업 영향 최소
• 인덱스 SKIP LOCKED 가능

TRUNCATE 의 깊이

TRUNCATE TABLE logs;

DELETE 와의 차이는 14편 에서 다뤘고, 여기선 깊이 한 가지 — 외래 키 참조가 있으면 거부 합니다.

TRUNCATE TABLE users;
-- ERROR: cannot truncate a table referenced in a foreign key constraint

해결은 CASCADE 입니다.

TRUNCATE TABLE users CASCADE;
-- users 와 그 자식 테이블 모두 비움 (위험!)

TRUNCATE 트랜잭션

PG 의 TRUNCATE 는 트랜잭션 안에서 동작 해요. 다른 RDBMS(Oracle 등) 와 차이 나는 지점입니다.

BEGIN;
TRUNCATE TABLE logs;
SELECT COUNT(*) FROM logs;   -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;   -- 원상 복귀

PG 운영에서 큰 강점이에요.

Soft Delete 깊이

14편 의 Soft Delete 패턴을 운영 깊이로 확장합니다.

부분 인덱스 적극 활용

-- 활성 사용자에만 인덱스
CREATE INDEX idx_users_email_active ON users(email)
WHERE deleted_at IS NULL;

-- 활성 사용자만 UNIQUE
CREATE UNIQUE INDEX uniq_users_email_active ON users(email)
WHERE deleted_at IS NULL;

부분 인덱스는 크기가 작고 갱신이 빠릅니다. Soft Delete 의 핵심이에요.

진짜 삭제 정책

-- 90일 이상 지난 Soft Deleted = 진짜 삭제
DELETE FROM users
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL '90 days';

스케줄러로 매일 batch 를 돌리면 GDPR(EU 개인정보 보호 규정) 같은 법적 요건도 충족됩니다.

RETURNING 으로 아카이브 동시

WITH archived AS (
    DELETE FROM logs WHERE created_at < '2025-01-01'
    RETURNING *
)
INSERT INTO logs_archive SELECT * FROM archived;

CTE(Common Table Expression, WITH 임시 결과 집합) 와 RETURNING 을 묶으면 삭제와 아카이브를 한 SQL 로 끝낼 수 있어요.

ON DELETE CASCADE 운영 함정

CREATE TABLE orders (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    ...
);

DELETE FROM users WHERE id = 1;
-- → orders 도 자동 삭제 (수십~수만 건 가능)

CASCADE 가 박힌 채로 한 사용자를 삭제하면 수만 건 주문이 연쇄 삭제 — WAL 폭발에 락도 폭주합니다. 운영에선 NO ACTION 으로 두고 앱 코드에서 단계적으로 삭제하는 쪽이 안전해요.

함정 5가지

(1) 대량 DELETE 후 VACUUM 안 함

테이블 부풀어 — 쿼리 느려짐. ANALYZE + VACUUM 또는 autovacuum 대기.

(2) bloat 모니터링 X

pg_stat_user_tables 안 보면 — bloat 알아채는 데 늦음. 주기적 점검.

(3) 파티션 안 만들고 DELETE

시계열 = 파티션이 정답. 처음부터 PARTITION BY RANGE.

(4) VACUUM FULL 운영 중

완전 락. 운영 중 절대 X. 대안 = pg_repack.

(5) CASCADE 박힌 채 대량 부모 삭제

WAL·락 폭주. NO ACTION + 코드 단계적.

한 줄 정리 — DELETE 내부 = 표시만. bloat 누적. autovacuum + 큰 테이블은 튜닝. 시계열 = 파티션 DROP 표준. 대량 = chunk + autocommit. Soft Delete + 90일 batch + 부분 인덱스. CASCADE = NO ACTION + 코드.

시험 직전 한 번 더 — DELETE 깊이 입문자가 매번 헷갈리는 것

• DELETE 내부 = 옛 행 표시 (디스크 안 회수)
• bloat = 죽은 행 누적
• VACUUM = 빈 공간 마킹 (재사용)
• autovacuum = 자동 (기본 활성)
• 큰 테이블 = autovacuum_vacuum_scale_factor 튜닝
• VACUUM FULL = 디스크 실제 회수 (완전 락 — 운영 X)
• pg_repack = 락 없는 디스크 회수
• bloat 모니터링 = pg_stat_user_tables.n_dead_tup
• dead_pct > 20% = autovacuum 검토
• 파티션 DROP = 대량 삭제 우아한 방법
• 1억 행 DELETE 30분 vs 파티션 DROP 0.1초
• pg_partman = 파티션 자동 관리
• 시계열 = RANGE 파티션 + 자동 DROP
• 대량 DELETE = chunk + autocommit 루프
• chunk 10000 + pg_sleep
• TRUNCATE = 빠름 (FK 없을 때)
• TRUNCATE CASCADE = 위험
• PG TRUNCATE = 트랜잭션 안 (PG 강점)
• Soft Delete + 부분 인덱스 = 활성만 unique
• 90일 batch 진짜 삭제 (GDPR)
• RETURNING + CTE = 삭제 + 아카이브 한 SQL
• CASCADE 운영 위험 = NO ACTION + 코드 단계적
• ANALYZE 대량 DELETE 후
• 인덱스 = DELETE 비용 증가
• Hibernate @SQLDelete + @Where = JPA Soft Delete

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

• 22편 — CREATE TABLE 깊이 PARTITION·UNLOGGED·TEMPORARY
• 23편 — 제약 깊이 CHECK DEFERRABLE EXCLUDE
• 24편 — DML 개요 4가지 동사 + MVCC 통합
• 25편 — INSERT 깊이 Bulk·COPY·UPSERT 전략
• 26편 — UPDATE 깊이 HOT·fillfactor·FROM JOIN

다음 글:

• 28편 — 쿼리 개요 SELECT 전체 구조
• 29편 — SELECT 절과 표현식의 깊이
• 30편 — 테이블 표현식 FROM 절의 깊이
• 31편 — 쿼리 종합 WHERE GROUP BY HAVING ORDER BY
• 32편 — 데이터 타입 개요