[PostgreSQL] DELETE 했는데 용량이 안 줄고 오히려 느려질 때 — VACUUM과 bloat

설치·접속: PostgreSQL 설치와 접속

부제: 50만 행을 넣고 90%를 지웠는데 테이블 크기가 그대로다 — MVCC가 남긴 죽은 튜플과 VACUUM의 역할

CREATE TABLE m8_events (id serial primary key, payload text);
INSERT INTO m8_events(payload) SELECT repeat('x',200) FROM generate_series(1,500000);
SELECT pg_size_pretty(pg_total_relation_size('m8_events'));   -- 넣은 직후
DELETE FROM m8_events WHERE id % 10 <> 0;                      -- 90% 삭제
SELECT pg_size_pretty(pg_total_relation_size('m8_events'));   -- 지운 직후

 size_after_insert
-------------------
 126 MB
DELETE 450000
 size_after_delete | live_rows
-------------------+-----------
 126 MB            |     50000

행의 90%를 지웠는데 용량은 126MB 그대로다. PostgreSQL은 MVCC라서 DELETE가 데이터를 바로 지우지 않고 "죽은 튜플(dead tuple)"로 표시만 해 둔다. 다른 트랜잭션이 아직 그 버전을 볼 수도 있기 때문이다. 그래서 디스크는 그대로고, 스캔할 때는 죽은 튜플까지 훑느라 오히려 더 느려진다. 이게 bloat다. pg_stat_user_tables로 죽은 튜플이 얼마나 쌓였는지 보고, VACUUM으로 회수한다.

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='m8_events';
VACUUM m8_events;                                              -- 죽은 튜플 회수
SELECT pg_size_pretty(pg_total_relation_size('m8_events')) AS after_vacuum,
       n_dead_tup FROM pg_stat_user_tables WHERE relname='m8_events';
VACUUM FULL m8_events;                                         -- 디스크를 OS에 반납
SELECT pg_size_pretty(pg_total_relation_size('m8_events')) AS after_vacuum_full;

 n_live_tup | n_dead_tup
------------+------------
      50000 |     450000        -- 45만 개가 죽은 채로 쌓여 있다
 after_vacuum | n_dead_tup
--------------+------------
 126 MB       |          0        -- 죽은 튜플은 0인데 크기는 그대로!
 after_vacuum_full
-------------------
 13 MB                            -- FULL은 126MB → 13MB로 반납

핵심 함정은 여기다. 일반 VACUUM은 죽은 튜플을 치워 그 공간을 재사용 가능하게 만들 뿐, 디스크를 OS에 돌려주지 않는다. 그래서 n_dead_tup은 0이 됐지만 크기는 126MB 그대로다(이후 INSERT가 이 빈 공간을 채운다). 진짜로 용량을 줄이려면 VACUUM FULL인데, 이건 테이블을 통째로 다시 써서 13MB로 줄이는 대신 ACCESS EXCLUSIVE 락을 잡아 그동안 조회·쓰기가 전부 막힌다. 운영 중에는 함부로 못 돌린다. 그래서 실무는 autovacuum이 죽은 튜플을 꾸준히 청소해 bloat가 쌓이지 않게 두는 쪽으로 간다.

이렇게도 쓴다

autovacuum이 언제 도는지 그 임계값을 확인한다. (조합: pg_settings)

SELECT name, setting FROM pg_settings
WHERE name IN ('autovacuum_vacuum_scale_factor','autovacuum_vacuum_threshold');
-- 기본: 죽은 튜플이 (전체행*0.2 + 50) 넘으면 자동 실행

 

특정 테이블만 autovacuum을 더 자주 돌게 조인다. (조합: 테이블 스토리지 파라미터)

ALTER TABLE m8_events SET (autovacuum_vacuum_scale_factor = 0.05);

 

마지막으로 청소된 시각과 autovacuum 횟수를 본다. (조합: 통계 뷰)

SELECT relname, last_autovacuum, autovacuum_count, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

 

락 없이 인덱스 bloat만 줄인다. (조합: REINDEX CONCURRENTLY)

REINDEX INDEX CONCURRENTLY idx_orders_user;

 

VACUUM FULL 대신 락을 짧게 잡는 온라인 재편성을 쓴다. (조합: pg_repack 확장)

pg_repack -d shop -t m8_events   # VACUUM FULL과 결과는 같고 락은 순간만

 

통계 갱신과 함께 청소한다. (조합: VACUUM ANALYZE)

VACUUM (VERBOSE, ANALYZE) m8_events;