설치·접속: PostgreSQL 설치와 접속
부제: autovacuum이 도는데도 테이블이 계속 커질 때, VACUUM FULL/REINDEX를 진짜 숫자로 결정하기
pg_stat_user_tables.n_dead_tup은 통계 추정치라, 통계가 리셋됐거나 갱신이 안 되면 dead tuple을 0으로 보고한다. 실제로 페이지를 스캔해 정확한 dead_tuple_percent를 재고, 빈 공간이 페이지별로 어떻게 흩어졌는지 보고, 특정 페이지의 튜플 헤더까지 부검하는 3단 진단을 붙여본다.
문제 상황
대량 INSERT 후 절반을 DELETE하고 나머지 일부를 UPDATE한 테이블. autovacuum을 끈 상태(autovacuum_enabled=false)라 dead tuple이 그대로 쌓여 있다.
CREATE TABLE bloat (id int primary key, payload text)
WITH (autovacuum_enabled = false);
INSERT INTO bloat SELECT g, repeat('x', 200) FROM generate_series(1, 50000) g;
DELETE FROM bloat WHERE id % 2 = 0; -- 절반 삭제
UPDATE bloat SET payload = repeat('y', 200) WHERE id % 4 = 1; -- 1/4 갱신1단계 — 추정치는 못 믿는다: 먼저 통계를 본다
ANALYZE bloat;
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 'bloat'; relname | n_live_tup | n_dead_tup
----------+------------+------------
bloat | 25000 | 0DELETE 2.5만 건 + UPDATE 1.25만 건으로 dead tuple이 분명히 있는데도 n_dead_tup은 0이다. 이 카운터는 autovacuum/통계 수집기가 갱신하는 누적값이라, autovacuum을 껐거나 통계가 리셋되면 현실과 어긋난다. 이 숫자만 보고 "블로트 없음"이라 판단하면 오진이다.
2단계 — pgstattuple로 진짜 dead_tuple_percent 실측
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, tuple_count, tuple_percent,
dead_tuple_count, dead_tuple_percent, free_percent
FROM pgstattuple('bloat'); table_len | tuple_count | tuple_percent | dead_tuple_count | dead_tuple_percent | free_percent
-----------+-------------+---------------+------------------+--------------------+--------------
12050432 | 25000 | 48.13 | 12510 | 24.08 | 25.37pgstattuple은 통계 추정이 아니라 실제 페이지를 전부 스캔해 튜플 단위로 센다. dead tuple이 12510개, 물리 공간의 24.08%를 죽은 튜플이 차지하고 있다. 추정치 n_dead_tup=0과 정면으로 어긋나는 진짜 숫자다. table_len은 약 12MB, live 튜플은 48%뿐이다.
3단계 — pg_freespacemap으로 빈 공간 분포 확인
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
SELECT avail, count(*) AS pages
FROM pg_freespace('bloat')
GROUP BY avail ORDER BY avail; avail | pages
-------+-------
0 | 1
128 | 1470여기가 핵심이다. dead tuple이 24%나 되는데 FSM이 보고하는 재사용 가능 공간은 페이지당 128 바이트뿐이다(200바이트짜리 payload 한 줄도 못 들어간다). VACUUM을 아직 안 돌렸으니 죽은 튜플이 차지한 공간이 FSM에 반영되지 않은 것이다. "dead는 많은데 쓸 수 있는 빈자리는 없다" = 새 INSERT는 계속 새 페이지를 만들며 테이블이 커진다.
4단계 — pageinspect로 특정 페이지 튜플 부검
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, lp_off, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('bloat', 0))
ORDER BY lp LIMIT 12; lp | lp_off | t_xmin | t_xmax | t_ctid
----+--------+--------+--------+--------
1 | 7960 | 1210 | 1212 | (0,35)
2 | 0 | | |
3 | 7728 | 1210 | 0 | (0,3)
4 | 0 | | |
5 | 7496 | 1210 | 1212 | (0,36)
6 | 0 | | |
7 | 7264 | 1210 | 0 | (0,7)
8 | 0 | | |
9 | 7032 | 1210 | 1212 | (0,37)
10 | 0 | | |
11 | 6800 | 1210 | 0 | (0,11)
12 | 0 | | |페이지 0을 바이트 단위로 열어 보면 세 종류의 라인 포인터가 보인다. lp_off=0인 짝수 lp(2, 4, 6…)는 이미 정리된/죽은 라인 포인터 — DELETE된 짝수 id 행들이다. t_xmax=1212가 찍힌 튜플(lp 1, 5, 9)은 트랜잭션 1212에 의해 삭제 표시된 것으로, t_ctid가 자기 자신이 아니라 (0,35) 같은 새 위치를 가리킨다 = UPDATE된 헌 버전이 새 튜플로 이어진 흔적이다. t_xmax=0인 튜플(lp 3, 7, 11)만 살아있는 현재 버전이다. dead_tuple_percent 24%가 실제로 어떤 모습인지 눈으로 확인한 셈이다.
5단계 — VACUUM 후 변화, 그리고 결정
VACUUM bloat;
SELECT dead_tuple_count, dead_tuple_percent, free_percent, table_len
FROM pgstattuple('bloat');
SELECT avail, count(*) AS pages
FROM pg_freespace('bloat') GROUP BY avail ORDER BY avail; dead_tuple_count | dead_tuple_percent | free_percent | table_len
------------------+--------------------+--------------+-----------
0 | 0 | 49.45 | 12050432
avail | pages
-------+-------
4032 | 1470
5728 | 1VACUUM은 dead tuple을 0으로 만들고 FSM을 갱신했다. 이제 페이지당 재사용 가능 공간이 128 → 4032 바이트로 뛰었다(새 INSERT가 빈자리를 재사용할 수 있다). 하지만 table_len은 12MB 그대로다 — VACUUM은 공간을 반납하지 않고 재사용 가능 상태로만 표시한다. free_percent가 49%라는 건 이 테이블이 반은 텅 빈 채로 디스크를 물고 있다는 뜻이다.
여기서 결정이 갈린다. 이 빈 공간을 곧 새 INSERT가 채운다면 VACUUM으로 충분하다. 하지만 워크로드가 더는 INSERT하지 않아 이 49%가 영원히 낭비될 거라면, 디스크를 실제로 반납하는 VACUUM FULL이 답이다.
VACUUM FULL bloat;
SELECT table_len, tuple_count, dead_tuple_count, free_percent
FROM pgstattuple('bloat'); table_len | tuple_count | dead_tuple_count | free_percent
-----------+-------------+------------------+--------------
6029312 | 25000 | 0 | 1.8VACUUM FULL이 테이블을 통째로 재작성해 table_len이 12MB → 6MB로 반토막 났고 free_percent는 1.8%로 떨어졌다. 대신 이 명령은 ACCESS EXCLUSIVE 락으로 테이블 전체를 잠그므로(운영 중 조회·쓰기 모두 차단) 유지보수 창에서만 돌려야 한다.
결론 — 언제 이 조합을 쓰나
"테이블이 계속 커진다"는 막연한 느낌을 세 도구로 숫자화한다. pgstattuple로 진짜 dead_tuple_percent와 free_percent를 재고(추정치 n_dead_tup에 속지 않는다), pg_freespace로 그 빈 공간이 재사용 가능한 상태인지 페이지별로 확인하고, pageinspect로 의심 페이지의 튜플이 실제로 죽었는지·UPDATE 체인인지 부검한다. 그 위에서 "VACUUM이면 충분(빈자리를 곧 재사용)" vs "VACUUM FULL 필요(공간 반납)"를 추측이 아니라 free_percent·table_len 변화로 결정한다.
이렇게도 쓴다
전체 스캔이 부담스러운 대형 테이블은 근사판으로 빠르게 판단한다. (조합: pgstattuple_approx)
-- visibility map을 활용해 전체 스캔을 건너뛰는 근사치
SELECT approx_free_percent, approx_tuple_count, dead_tuple_percent
FROM pgstattuple_approx('bloat');
인덱스 블로트는 pgstatindex의 leaf_fragmentation으로 본다. (조합: pgstatindex → REINDEX)
-- leaf_fragmentation이 높으면 REINDEX 후보
SELECT index_size, leaf_pages, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('bloat_pkey');
특정 페이지 하나의 여유만 콕 집어 확인한다. (조합: pg_freespace 단일 페이지)
SELECT pg_freespace('bloat', 0); -- blkno 0의 avail 바이트
페이지 헤더 레벨에서 pruning/all-visible 상태를 본다. (조합: pageinspect page_header)
SELECT lower, upper, special, pagesize, flags
FROM page_header(get_raw_page('bloat', 0));
infomask 플래그를 사람이 읽는 이름으로 해독한다. (조합: heap_tuple_infomask_flags)
SELECT t_ctid, flags
FROM heap_page_items(get_raw_page('bloat', 0)) p,
heap_tuple_infomask_flags(p.t_infomask, p.t_infomask2)
WHERE p.t_xmax <> 0 LIMIT 5;
전체 테이블·인덱스 블로트를 한 번에 훑어 우선순위를 매긴다. (조합: 대상 릴레이션 순회)
SELECT c.relname,
(pgstattuple(c.oid)).dead_tuple_percent AS dead_pct,
(pgstattuple(c.oid)).free_percent AS free_pct
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY dead_pct DESC LIMIT 10;