테이블 크기를 heap / index / TOAST로 나눠 조회 및 가장 큰 인덱스 목록

명령어

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid) - pg_indexes_size(c.oid)) AS toast_fsm_vm,
       round(100.0 * pg_total_relation_size(c.oid) / NULLIF(pg_database_size(current_database()), 0), 1) AS pct_of_db
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

-- Largest indexes and their usage counts
SELECT s.schemaname,
       s.relname AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan,
       i.indisunique,
       i.indisprimary
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

하는 일

가장 큰 테이블을 전체, heap, 인덱스, TOAST(+FSM/VM)로 나누어 데이터베이스 내 비중과 함께 보여 주고, 가장 큰 인덱스를 스캔 횟수와 unique/primary 플래그와 함께 보여 준다.

사용 시점

디스크 사용량이 증가할 때 증가가 어디서(데이터, 인덱스, TOAST) 오는지 찾고, 사용되지 않는 대형 인덱스를 발견하기 위해.

주의

크기 함수는 relation마다 파일 시스템에 접근하므로 테이블이 수만 개인 데이터베이스에서는 느릴 수 있다. 파티션 부모(relkind 'p')는 자체 크기가 0 바이트이므로 제외된다. 계층 전체 합계: SELECT pg_size_pretty(sum(pg_total_relation_size(relid))) FROM pg_partition_tree('sales'); idx_scan = 0인 대형 인덱스는 삭제 후보지만, 카운터는 마지막 통계 reset 이후의 기간과 이 서버만 포함한다(replica의 사용량은 보이지 않음). unique/primary 인덱스는 제약 조건을 강제하므로 삭제하지 않는다.