유용한 extension 정리

정리 📌
pg_stat_statements SQL 쿼리 성능 분석 (실행 횟수, 평균 실행 시간 등)
pg_store_plans SQL 실행 계획 저장 및 분석
pg_stat_kcache 쿼리별 CPU, 메모리, 디스크 I/O 사용량 확인
pg_buffercache 캐시에서 어떤 테이블이 자주 사용되는지 확인
pg_prewarm PostgreSQL 재시작 후 중요한 데이터를 미리 로드
pg_cron 자동화된 데이터 정리 및 백업 스케줄링
pg_bloat_check 테이블과 인덱스의 블로트(불필요한 공간) 확인
pg_repack 다운타임 없이 테이블 최적화
auto_explain 실행 시간이 긴 쿼리의 실행 계획 자동 로깅
pg_stat_io PostgreSQL 16+에서 디스크 I/O 분석

---

운영 환경에서 PostgreSQL을 효율적으로 모니터링하려면
✅ 쿼리 분석: pg_stat_statements, pg_store_plans, auto_explain
✅ 리소스 모니터링: pg_stat_kcache, pg_stat_io, pg_buffercache
✅ 공간 관리: pg_bloat_check, pg_repack
✅ 운영 자동화: pg_cron, pg_prewarm

---

1. pg_stat_statements (쿼리 성능 분석)

✅ 주요 기능:

실행된 SQL 쿼리의 통계를 저장하고 분석할 수 있음.
쿼리 실행 횟수, 평균 실행 시간, 버퍼 사용량 등을 확인할 수 있음.
느린 쿼리를 찾아 성능 튜닝할 때 필수적인 확장.

💡 설치 및 활성화:

CREATE EXTENSION pg_stat_statements;

💡 주요 조회 방법:

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

→ 실행 시간이 가장 긴 쿼리 10개를 조회

2. pg_store_plans (실제 실행 계획 저장)

✅ 주요 기능:

실행된 SQL의 실제 실행 계획(EXPLAIN ANALYZE) 을 저장함.
pg_stat_statements와 함께 사용하면 쿼리 튜닝에 강력한 도움이 됨.

💡 설치 및 활성화:

CREATE EXTENSION pg_store_plans;

💡 주요 조회 방법:

SELECT query, plan FROM pg_store_plans ORDER BY total_exec_time DESC LIMIT 5;

→ 실행 시간이 오래 걸린 쿼리들의 실행 계획을 확인.

3. pg_stat_kcache (쿼리별 OS 레벨 성능 모니터링)

✅ 주요 기능:

각 쿼리의 CPU 사용량, 디스크 I/O, 메모리 사용량을 측정.
PostgreSQL 내부 정보뿐만 아니라 OS 레벨에서 사용된 리소스까지 확인 가능.

💡 설치 및 활성화:

CREATE EXTENSION pg_stat_kcache;

💡 주요 조회 방법:

SELECT * FROM pg_stat_kcache ORDER BY calls DESC LIMIT 10;

→ 가장 많이 호출된 쿼리와 그에 따른 리소스 사용량을 확인.

4. pg_buffercache (버퍼 캐시 사용량 분석)

✅ 주요 기능:

PostgreSQL이 관리하는 버퍼 캐시의 현재 상태를 확인할 수 있음.
디스크 I/O를 줄이기 위한 캐시 최적화에 유용함.

💡 설치 및 활성화:

CREATE EXTENSION pg_buffercache;

💡 주요 조회 방법:

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC LIMIT 10;

→ 캐시에서 가장 많이 사용되는 테이블 확인.

5. pg_prewarm (데이터 미리 로드)

✅ 주요 기능:

PostgreSQL이 재시작될 때, 자주 사용되는 데이터를 미리 버퍼 캐시에 로드.
장기적으로 성능을 안정화하는 데 유용함.

💡 설치 및 활성화:

CREATE EXTENSION pg_prewarm;

💡 특정 테이블을 미리 로드하는 방법:

SELECT pg_prewarm('your_table');

6. pg_cron (PostgreSQL 내부에서 크론 작업 실행)

✅ 주요 기능:

PostgreSQL에서 정기적인 자동 백업, 데이터 정리, 통계 갱신 작업 등을 수행할 수 있음.
운영 작업을 자동화할 때 매우 유용.

💡 설치 및 활성화:

CREATE EXTENSION pg_cron;

💡 특정 작업을 예약 실행하는 방법:

SELECT cron.schedule('0 3 * * *', 'VACUUM ANALYZE');

→ 매일 새벽 3시에 VACUUM ANALYZE 실행.

7. pg_bloat_check (인덱스와 테이블 블로트 확인)

✅ 주요 기능:

PostgreSQL 테이블과 인덱스에 불필요한 블로트(Bloat, 쓸데없이 차지하는 공간)가 있는지 검사.
디스크 공간을 줄이고 성능을 향상시킬 수 있음.
💡 주요 조회 방법:

SELECT * FROM bloat_check();

→ 블로트가 발생한 테이블과 인덱스를 확인.

8. pg_repack (다운타임 없이 테이블 재구성)

✅ 주요 기능:

VACUUM FULL 없이 테이블과 인덱스를 재구성(리팩토링)하여 성능 최적화.
운영 중인 서비스에서도 테이블을 정리할 수 있어 매우 유용.

💡 설치 및 활성화:

pg_repack -h localhost -U postgres -d your_database -t your_table

→ your_table을 재구성하여 성능 최적화.

9. auto_explain (자동으로 실행 계획 로그 저장)

✅ 주요 기능:

특정 실행 시간이 초과하는 쿼리의 실행 계획을 자동으로 로깅.
느린 쿼리를 추적하는 데 매우 유용.
💡 설정 방법 (postgresql.conf 수정)

shared_preload_libraries = 'auto_explain'
log_min_duration_statement = 1000  # 1초 이상 걸리는 쿼리 로깅

💡 활성화 후 실행 계획 확인

SHOW auto_explain.log_min_duration;

10. pg_stat_io (PostgreSQL 16+ 버전에서 I/O 모니터링)

✅ 주요 기능:

PostgreSQL 16부터 추가된 확장으로 데이터베이스의 디스크 I/O 사용량을 추적.
디스크 성능 문제를 해결하는 데 유용.
💡 설치 및 활성화:

CREATE EXTENSION pg_stat_io;

💡 주요 조회 방법:

SELECT * FROM pg_stat_io ORDER BY total_read DESC LIMIT 10;

→ 가장 많이 읽힌 테이블 및 인덱스 확인.