PostgreSQL 부가 확장 모듈 소개

0. 소개

PostgreSQL에서는 서버 기능을 확장하는 수 많은 부가 확장 모듈을 제공하고 있습니다. 데이터베이스 서버가 이런 일을 할 수 있을까? 생각한다면, 먼저 

http://postgresql.kr/docs/current/contrib.html

페이지를 살펴보길 권장합니다. 해당 페이지에서 각 부가 확장 모듈에 대한 자세한 정보를 담고 있습니다. 

이 글에서는 그 페이지에서 소개하고 있는 모듈들 가운데 실무에서 자주 쓰는, 사용하면 편한 모듈들을 예제와 함께 소개하고자 합니다.

1. adminpack

2. auto_explain

shared_preload_libraries = '$libdir/auto_explain'

-- 이 모듈을 쓰겠다
postgres=# set auto_explain.log_analyze = on;
SET
-- 모든 쿼리를 대상으로
postgres=# set auto_explain.log_min_duration = 0; 
SET
-- 최대한 자세히
postgres=# set auto_explain.log_verbose = on; 
SET
-- 버퍼 사용량도 함께
postgres=# set auto_explain.log_buffers = on; 
SET
-- 내부적으로 실행되는 쿼리도 함께
postgres=# set auto_explain.log_nested_statements = on; 
SET

3. dblink, postgres_fdw

4. earthdistance

postgres=# select
earth_distance(ll_to_earth(37.5668689,126.9777054), ll_to_earth(35.179555,129.075641));
earth_distance
-----------------
 325531.79010764
(1 row)

postgres=# create table libraries (
GU_NM text,FLY_GBN text,LON float,LAT float);
CREATE
-- 데이터 입력
postgres=# \copy libraries from '/tmp/wifizone.csv' (format 'csv', HEADER true);
-- gist 인덱스를 위한 위치 정보 칼럼 추가
postgres=# alter table libraries add p earth;
ALTER TABLE
-- 인덱스 만들기
postgres=# create index libraries_p_i on libraries using gist (p);
CREATE INDEX
-- 해당 칼럼 자료 입력 (이제 위, 경도 칼럼은 없어도 되겠죠)
postgres=# update libraries set p = ll_to_earth(lat, lon);
UPDATE 123
-- 시퀀스 스캔 안함
postgres=# set enable_seqscan = off;
SET
postgres=# explain SELECT * FROM libraries 
postgres-# WHERE p <@ earth_box(ll_to_earth(37.6796272,127.0456284) , 1000);
                                QUERY PLAN                                         
----------------------------------------------------------------
 Index Scan using libraries_p_i on libraries  (cost=0.14..8.15 rows=1 width=98)
   Index Cond: ((p)::cube <@ '(-3042137.60175663, 4028046.09801202, 3897627.5358785),
(-3040137.60175868, 4030046.09800997, 3899627.53587645)'::cube)
(2 rows)

postgres=# SELECT gu_nm, fly_gbn FROM libraries
WHERE p <@ earth_box(ll_to_earth(37.6796272,127.0456284) , 1000);
 gu_nm  |  fly_gbn
--------+------------
 도봉구 | 구립도서관
(1 row)

5. file_fdw, postgres_fdw

6. pageinspect, pg_buffercache, pg_freespacemap, pgstattuple

7. pg_stat_statements

8. xml2

9. 마무리