[ postgresql ] 많이 쓰는 extension

postgresql 에서는 기본기능외의 것을 사용하려면 extension (확장기능) 이라는 것을 추가로 설치해야한다. 

기본 인스턴스 상태에서 아래처럼 pg_freespace 를 사용하려고 하면 에러가 난다. 

아래와 같이 현재 설치된 extension 가 설치가능한 extension 을 확인한 후 필요한 extension 을 설치해준다.

select * from pg_extension; -- 현재 설치된 extension 확인
select * from pg_available_extensions;  -- 설치 가능한 extension 확인

create extension pg_freespacemap;

select * from pg_freespace('t1');

-- drop extension pg_freespacemap; 설치한 extension 삭제 

 

# Extension upgrade

엔진 버전이 upgrade 되었을때 extension 까지 자동 upgrade 되지 않는다. 

수동으로 아래와 같이 upgrade 해줘야 한다. 

ALTER EXTENSION extension_name UPDATE TO 'new_version';

 

아래는 많이 사용하는 extension 에 대해서 정리해 보았다.

Extension	설명	예시
pg_stat_statements	전체 쿼리 실행 통계 집계. 성능 튜닝 필수.	- 느린 쿼리 찾기 SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;  - 많이 호출되는 쿼리 보기 SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 10;
auto_explain	느린 쿼리 자동 실행계획 기록. 프로덕션 장애조치 및 튜닝 정보 확보.	- 느린 쿼리 자동으로 플랜 로깅 SET auto_explain.log_min_duration = 500; - 로그에서 실행계획 확인 (서버 로그 파일에서 auto_explain 문구 확인)
pgaudit	DML, DDL, 인증 등 세분화된 감사로그 기록 (컴플라이언스 대응).	감사 로그 확인은 PostgreSQL 로그 파일에서 이벤트별로 pgaudit 메시지로 확인 가능
pgcrypto	암호화, 해시함수, 서명검증 등 데이터 보안 기능	- SHA 해시: SELECT digest('text', 'sha256');  - 암호화: SELECT crypt('password', gen_salt('bf'));
postgres_fdw	외부 PostgreSQL DB 연동. 데이터 통합·분산 쿼리 지원.	- 외부 DB 연결: CREATE SERVER remotesrv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx', dbname 'test'); CREATE USER MAPPING FOR current_user SERVER remotesrv OPTIONS (user 'dbuser', password 'xxx'); - 외부 테이블 import: IMPORT FOREIGN SCHEMA public FROM SERVER remotesrv INTO local_schema;
pg_cron	DB 내 스케줄 기반 작업(백업·리포팅 등) 실행 지원.	- 주기적 작업 등록: SELECT cron.schedule('nightly-backup', '0 1 * * *', $$VACUUM$$);
pg_freespacemap	Free Space Map (FSM)을 조회 테이블 페이지별 여유 공간 확인. 테이블 단위 저장공간 활용도와 단편화 진단에 도움.	- 전체 페이지 여유 공간 조회: SELECT * FROM pg_freespace('테이블명'); - 특정 페이지 조회: SELECT * FROM pg_freespace('테이블명', 페이지번호);
pg_repack	테이블을 온라인 상태에서 락을 최소화하며 재구성	- 특정 테이블 재구성: pg_repack -t 테이블명 -d 데이터베이스명 - 재구성 효과로 디스크 사용량 감소 및 성능 향상
pgstattuple	테이블, 인덱스 내 실제 live tuple, dead tuple, free space 비율 통계 제공. 저장 공간 효율성과 bloat 상태 점검용.	- 테이블 통계 조회: SELECT * FROM pgstattuple('테이블명'); - 결과는 live_tuple_count, dead_tuple_count, free_space, approx_bloat(%) 등 포함