[PostgreSQL] 디스크가 차오를 때 어디가 부었는지 찾기 — 테이블·인덱스 크기와 bloat

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

부제: "DB가 몇 GB"만으로는 모른다 — 본체(heap)·인덱스·TOAST를 갈라 보고, 죽은 튜플 비율로 bloat를 가늠한다

-- 한 테이블의 크기를 세 갈래로 분해
SELECT pg_size_pretty(pg_relation_size('m8_sized'))       AS heap,     -- 본체
       pg_size_pretty(pg_indexes_size('m8_sized'))        AS indexes,  -- 인덱스 합
       pg_size_pretty(pg_total_relation_size('m8_sized')) AS total;    -- 전부

  heap  | indexes | total
--------+---------+--------
 106 MB | 22 MB   | 129 MB

크기를 볼 때 함수를 구분해야 한다. pg_relation_size는 테이블 본체(heap)만, pg_indexes_size는 그 테이블에 딸린 모든 인덱스, pg_total_relation_size는 인덱스와 TOAST(큰 값 별도 저장분)까지 더한 전체다. 인덱스가 22MB나 붙어 있다는 걸 total만 봐서는 놓친다. 어느 테이블이 디스크를 먹는지는 아래처럼 랭킹으로 뽑는다.

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS heap,
       pg_size_pretty(pg_total_relation_size(relid)
                      - pg_relation_size(relid)
                      - pg_indexes_size(relid))       AS toast
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 5;

  relname  | total  |  heap  | toast
-----------+--------+--------+--------
 m5_orders | 132 MB | 118 MB | 64 kB
 m8_sized  | 129 MB | 106 MB | 56 kB
 m5_fresh  | 50 MB  | 42 MB  | 40 kB
 m5_geo    | 43 MB  | 43 MB  | 0 bytes
 m10_bulk  | 21 MB  | 21 MB  | 32 kB

그런데 "크다"와 "부었다(bloat)"는 다르다. 진짜 데이터가 많아 큰 건지, 죽은 튜플이 안 치워져 부풀어 있는 건지는 죽은 튜플 비율로 가늠한다. 아래는 300만 행 중 240만을 지운 뒤(ANALYZE로 통계 갱신) 잰 결과인데, 죽은 튜플이 80%다 — 이러면 VACUUM 대상이다.

SELECT relname, n_live_tup, n_dead_tup,
       round(100*n_dead_tup::numeric/nullif(n_live_tup+n_dead_tup,0),1) AS dead_pct
FROM pg_stat_user_tables WHERE relname='m8_sized';

 relname  | n_live_tup | n_dead_tup | dead_pct
----------+------------+------------+----------
 m8_sized |      60000 |     240000 |     80.0

이렇게도 쓴다

DB 전체 크기를 한눈에 본다. (조합: pg_database_size)

SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database
ORDER BY pg_database_size(datname) DESC;

 

인덱스별로 개별 크기를 뽑아 안 쓰는 큰 인덱스를 찾는다. (조합: pg_stat_user_indexes)

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;

 

psql 메타명령으로 크기 붙여 테이블 목록을 본다. (조합: \dt+)

\dt+

 

스키마 단위로 합산해 어느 영역이 큰지 본다. (조합: SUM + GROUP BY)

SELECT schemaname, pg_size_pretty(sum(pg_total_relation_size(relid))) AS total
FROM pg_statio_user_tables GROUP BY schemaname ORDER BY sum(pg_total_relation_size(relid)) DESC;

 

bloat 정밀 추정은 확장을 설치해 본다. (조합: pgstattuple)

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('m8_sized');   -- dead_tuple_percent 등 상세