PostgreSQL에서 데이터베이스 내 모든 테이블의 디스크 사용량 확인

PostgreSQL에서는 데이터베이스 내 모든 테이블의 디스크 사용량(테이블, 인덱스, TOAST 포함)을 한 번에 조회할 수 있습니다.

---

1. 전체 테이블 디스크 사용량 확인 쿼리

다음 쿼리는 현재 데이터베이스의 모든 테이블에 대해 테이블 본체, 인덱스, TOAST 데이터 크기, 그리고 전체 디스크 사용량을 조회합니다.

SELECT 
    schemaname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM 
    pg_stat_user_tables
ORDER BY 
    pg_total_relation_size(relid) DESC;

---

2. 쿼리 설명

• pg_stat_user_tables: 사용자 테이블 목록을 제공하는 시스템 뷰입니다.
• schemaname: 테이블이 속한 스키마 이름.
• relname: 테이블 이름.
• pg_relation_size(relid): 테이블 본체(데이터)의 디스크 사용량.
• pg_indexes_size(relid): 해당 테이블과 연결된 인덱스의 디스크 사용량.
• pg_total_relation_size(relid): 테이블 본체, 인덱스, TOAST 데이터를 포함한 전체 디스크 사용량.
• pg_size_pretty: 바이트 단위의 크기를 사람이 읽기 쉬운 형식(예: KB, MB, GB)으로 변환.
• ORDER BY pg_total_relation_size(relid) DESC: 디스크 사용량 기준으로 결과를 내림차순 정렬.

---

3. 실행 결과

schema_name	table_name	table_size	index_size	total_size
public	employees	10 MB	2 MB	12 MB
public	sales_data	8 MB	1 MB	9 MB
public	orders	5 MB	1 MB	6 MB

결과 해석

1. employees:
   • 테이블 본체 크기: 10 MB
   • 인덱스 크기: 2 MB
   • 전체 디스크 사용량: 12 MB
2. sales_data:
   • 전체 크기: 9 MB
   • 인덱스 비율이 낮아 데이터 접근 효율이 높을 가능성이 있음.
3. orders:
   • 크기가 작아 상대적으로 적은 스토리지를 차지.

---

4. 활용 방안

1. 디스크 사용량 모니터링:
   • pg_total_relation_size를 활용해 스토리지 부족 가능성을 사전에 예측.
2. 인덱스 최적화:
   • 인덱스 크기가 지나치게 큰 경우, 사용되지 않는 인덱스를 확인하고 제거.
3. 테이블 관리:
   • 자주 사용되지 않는 대규모 테이블은 파티셔닝하거나 보관용 데이터베이스로 이동.

---

5. 추가 팁: 특정 스키마만 조회

특정 스키마의 테이블만 조회하고 싶다면 WHERE 조건을 추가합니다.

SELECT 
    schemaname AS schema_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM 
    pg_stat_user_tables
WHERE 
    schemaname = 'public'
ORDER BY 
    pg_total_relation_size(relid) DESC;