낙장불입 인생막장

2000년인가.. mysql을 사용하는 서비스를 개발해서 오픈했다. DB서버는 3대였고 테이블은 700여개 였고 버전은 3.x.x 였다. 잘 기억이 안난다; 여튼 한창 데이터를 쌓아갈 무렵 장애가 발생했다. 어느날 부턴가 몇몇 테이블을 시작으로 100여개 테이블의 데이터 파일이 크기가 0으로 변해버렸다. 회사는 난리가 났고 백업된 데이터로 응급 복구 등 여튼 정신이 없었다. 일부 데이터는 유실됐다.

이때부터 난 PostgreSQL(이하 pgsql)로 갈아탔다. 그 후 개인적인 프로젝트까지 모두 pgsql이 메인 DBMS였다.
그때나 지금이나 한글로 된 관련 문서가 별로 없었기에 참으로 버벅였었다; 게다가 당시의 mysql은 네트웍이 되는 파일DB 수준이 아니었던가.. mysql에 길들여진 상태에서 진정한 RDBMS로의 이주는 쉽지 않았다.

PostgreSQL의 양면성

pgsql은 가장 오래된 오픈소스 DBMS이다. 70년대 생이고 오라클과는 두 살 차이다. BSD라이센스를 채택한 덕에 많은 DBMS들이 pgsql의 코드를 차용했고 많은 벤더들이 pgsql을 기반으로 한 DBMS를 내 놓았다. 연구적인 특성이 강해 DBMS와 관련된 논문 중에는 pgsql을 기반한 것이 많았다.
이런 외부의 실험들이 pgsql에는 contrib 형태든 코어의 형태든 많이 녹아들어있다. 그렇다고 pgsql 자체가 실험적인건 아니었다. pgsql의 개발진들은 굉장히 보수적이다. 이 보수적인 개발진들은 몇 년에 걸쳐 이 혁신적이지만 실험적인 모듈들을 테스트 하고 보완하여 코어에 포함시킨다. 다른 DBMS들은 이 실험을 지켜보다가 자신들의 제품에 포함하곤 했다.
실험적이고 혁신적이었지만 보수적인 성격 탓인지 대중화에 실패한 pgsql은 90년대의 오픈소스 DBMS의 슈퍼스타인 mysql에게 왕좌를 넘겨주고 만다.

성능에 대한 오래된 속설

pgsql은 mysql보다 느리다. 이것은 누구나 알고 있는 "잘못된" 사실이다.
실제로 테스트 해보니 pgsql이 느리다는 사람은 이런 경우가 많다.

1. 테이블을 만든다.
2. 데이터를 백만건 쯤 넣어본다.
3. select 해 본다.

이뭐병;; 실제로 개발할 때 이런 경우가 얼마나 된다고 =_=
http://tweakers.net/reviews/657/6
mysql4/5 와 pgsql 8.2와의 벤치마크다; 저 페이지의 아래쪽 링크를 타면 처음부터 볼 수도 있다.
참고로 곧 나올 pgsql 8.3은 8.2보다 훨씬 빠르다

벤치마크야 원래 믿거나 말거나 라고 치고.. DBMS의 성능에 대해 다른 관점에서 얘기 쫌 해보자
어느 DBMS에나 있는 query optimizer를 보자. rule base든 cost base든 이것도 얘기 꺼내면 시끌시끌 하지만 pgsql의 geqo(genetic query optimizer)는 어쨌든 뛰어난 옵티마이저이다.
단일 테이블 select 아무리 빠르면 뭐하나? 테이블 조인 서너개 하면 디스크 처음부터 끝까지 다 긁는다.

pgsql은 테이블들의 데이터 통계를 가지고 있다. 주기적으로 변화를 감지해서 갱신한다. 이 통계는 놀랍게 똘똘한 쿼리 플랜을 만드는데 사용된다. pgsql은 인덱스를 사용할 지, 사용한다면 어떤놈을 사용할 지, 조인은 어떤 방식을 사용할 지 이 통계들을 바탕으로 정확한 쿼리 플랜을 만들어 낸다.
실제로 pgsql을 쓰다 보면 인덱스를 타지 않는 경우가 있다. 이런 경우 강제로 인덱스를 타게 해보면 실제로 느리다. 조인 방식도 마찬가지다. pgsql이 merge join을 추천하면 강제로 nested loop 해봤자 느린 경우가 다반사다.
pgsql은 내가 아는 한 가장 똑똑한 옵티마이저를 가졌다. oracle 10g를 썼을 때 pgsql 만큼 똑똑하구나 싶었고 oracle 9i는 바보다 -_-; mysql은 oracle 9i보다도 바보다.

라이프팟에서 실제로 사용하는 쿼리의 쿼리 플랜이다. explain만 붙이면 보기 편하고 예측 cost, 실제 cost를 보여준다.

lifepod=# EXPLAIN ANALYZE 
SELECT * from view_calendar_notify ;
                                                                                                                QUERY PLAN                                                                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=76.39..309.68 rows=32 width=109) (actual time=0.152..0.152 rows=0 loops=1)
   ->  Nested Loop  (cost=76.39..298.85 rows=32 width=79) (actual time=0.150..0.150 rows=0 loops=1)
         ->  Merge Join  (cost=76.39..100.11 rows=32 width=67) (actual time=0.146..0.146 rows=0 loops=1)
               Merge Cond: (s.sq_user = n.sq_user)
               ->  Index Scan using tb_smsauth_idx1 on tb_smsauth s  (cost=0.00..22.37 rows=341 width=18) (actual time=0.069..0.069 rows=1 loops=1)
               ->  Sort  (cost=76.39..76.48 rows=36 width=53) (actual time=0.071..0.071 rows=0 loops=1)
                     Sort Key: n.sq_user
                     ->  Bitmap Heap Scan on tb_calendar_notify n  (cost=3.63..75.46 rows=36 width=53) (actual time=0.050..0.050 rows=0 loops=1)
                           Recheck Cond: ((func_calendar_notify(dt_start, no_notify_before) >= (now() - '00:10:00'::interval)) AND (func_calendar_notify(dt_start, no_notify_before) <= (now() + '00:10:00'::interval)) AND (NOT tg_sent))
                           ->  Bitmap Index Scan on tb_calendar_notify_idx3  (cost=0.00..3.63 rows=36 width=0) (actual time=0.046..0.046 rows=0 loops=1)
                                 Index Cond: ((func_calendar_notify(dt_start, no_notify_before) >= (now() - '00:10:00'::interval)) AND (func_calendar_notify(dt_start, no_notify_before) <= (now() + '00:10:00'::interval)))
         ->  Index Scan using tb_calendar_pkey on tb_calendar c  (cost=0.00..6.20 rows=1 width=20) (never executed)
               Index Cond: (n.sq_calendar = c.sq_calendar)
   ->  Index Scan using tb_calendar_data_pkey on tb_calendar_data d  (cost=0.00..0.32 rows=1 width=46) (never executed)
         Index Cond: (c.sq_calendar_data = d.sq_calendar_data)
 Total runtime: 0.245 ms
(16 rows)

위 쿼리는 테이블을 4개 조인하고 수십만건의 데이터가 들어 있다.

게다가 pgsql은 튜닝할 수 있는 여지가 많다. 자세한 설정 파일은 기본이고 실력되면 소스를 뜯어볼 수도 있다. 물론 쉽게 DBMS 차원에서 할 수 있는 것도 많다.
테이블, 인덱스들의 fill factor를 SQL로 변경할 수 있다. 인덱스를 범위별로 쪼갤 수도 있다. 1억건의 데이터가 있으면 백만건 단위로 인덱스를 잘라서 탐색 시간을 줄일 수 있는 것이다.

pgsql은 엔터프라이즈 DBMS이다. 안정성과 신뢰를 최우선으로 생각하고 그래왔다. 그래서 예전엔 느렸다. pgsql이 성능에 신경쓰기 시작한 것이 오래되지 않았다. 한 5년? 구조, 프로토콜 개선 등 이 사이 pgsql의 성능은 비약적으로 상승했다. mysql이 성능을 내세우고 기능 붙이느라 점점 느려지는 것과는 반대이다.

ORDBMS

pgsql은 ORDBMS를 표방한다.
테이블 상속, 인덱스 클러스터, 사용자 정의 데이터 타입/연산자, 연산자 오버로드 등의 특성을 가지고 있다.
중첩 트랜젝션, save point, 테이블 스페이스, 뷰, 트리거, 룰, 프로시져 등 기본적인(어느 DBMS에는 없을 수도 있는) 기능 역시 모두 가지고 있다.
DBMS의 최고봉 Oracle과 기술 격차가 거의 없는 DBMS가 얼마나 될까? pgsql은 그 중에 하나인 것이다. 아니, 부족하다. Oracle과 pgsql은 서로 장/단이 있다.

다른 강력한 기능들

하나의 row가 하나의 데이터가 된다면 어떨까? 여러개의 데이터들을 모아서 하나의 데이터로 만드는건 어떨까? pgsql에서는 가능하다.
composite type이라는 데이터 타입은 하나의 row처럼 여러 종류의 데이터 타입들을 하나의 필드에 넣을 수 있게 해준다. C의 구조체를 하나의 데이터 타입처럼 다루는 것이다.
배열 필드는 어떨까?
pgsql이 지원하는 모든 데이터 타입은 [] 만 붙여주면 배열 타입으로 변한다. int[], smallint[][], timestamp[][][] 처럼 [] 를 붙여주는 만큼의 depth를 가지는 배열 데이터가 된다. 이런것을 테이블에 자유롭게 넣고 뺄 수 있다. 쓸데없이 테이블을 2~3개 만들어야 할 때 pgsql에서는 테이블 하나로 처리할 수도 있다.

오라클을 하는 사람이라면 pl/sql을 잘 알 것이다. pgsql에도 있다. pl/pgsql.. pl/sql과 아주 유사하다. 근데 pl/pgsql에 익숙하지 않거나 더 많은 기능을 원한다면?
pgsql에서는 여러가지 언어로 프로시져를 만들 수 있다. pl/c, pl/java, pl/perl, pl/python, pl/php, pl/sh... 등등등 원하는 언어로 프로시져를 만들 수 있다. DBMS위에 프로그램을 할 수 있게 된다.

다양한 인덱스와 데이터 타입, contrib 모듈들

pgsql은 기본적으로 btree, hash, gist, rtree 네가지의 인덱스를 지원한다.
데이터 종류나 연산자에 따라 효율적인 인덱스를 선택할 수 있다. 게다가 contrib에 포함된 다양한 다른 인덱스를 사용할 수도 있다. ltree, btree-gist 등 다른 DBMS에서는 듣도 보도 못하던 것 들이다.
그리고 위에서 보여준 쿼리 플랜을 잘 보면 검색 값에 함수가 들어있다. 함수의 연산 결과를 인덱스 할 수도 있다. pgsql은 필드의 값 외에도 값들을 함수에 넣어 돌린 결과, 다른 데이터 타입으로 캐스팅한 상태로 인덱스 하는 것도 가능하다.

데이터 타입은 어떤가? 기본적인건 다 있다. int, text, timestamp 등등..
pgsql은 GIS 솔루션에 사용하기 적합하다. postgis는 GEOS등과 연동하여 공간과 관련된 데이터 타입과 연산자, 인덱스를 제공한다. 물론 postgis가 없어도 기본적으로 point, box, polygon 등 공간을 표현하는 데이터 타입이 포함되어 있다.

contrib중에 대표적으로 xml과 tsearch2가 있다. 이 두가지 모듈은 8.3에 드디어 코어로 들어왔다. 이전에 코어로 들어온 autovacuum의 경우도 원래는 contrib에 오래 머물렀었다.
8.3부터는 xml쿼리가 지원되고 tsearch2를 이용해서 full text index가 지원된다. mysql의 그런 full text index가 아니다 -_-;
stop word, dictionary 등을 포함하고 가중치를 조절할 수 있는 랭크 알고리즘도 포함하며 자체적인 vector-gist 기반의 인덱스와 데이터 타입을 가지고 있다. pgsql+tsearch2 기반의 OpenFTS라는 오픈소스 검색엔진도 있다.

리플리케이션

pgsql에는 아직 코어에 포함된 리플리케이션 기능이 없다.
근데 오픈소스 pgsql 리플리케이터가 많다. 그것도 상황과 종류별로 -_-;

1. pgpool: DB 세션 풀러다. 최대 2개의 pgsql을 놓고 리플리케이션도 해주고 SELECT 쿼리는 분산도 시켜준다. 세팅이 간편하다.
2. pgpool-II: pgpool의 차세대(?) 버전으로 최대 128개의 pgsql을 리플리케이션 해주고 그리드형태의 분산 처리가 된다. select * from table idx between 0 and 100; 이런 쿼리가 들어오면 10대의 pgsql에 0..10, 11..20, 21..30 처럼 쿼리를 쪼개서 병렬 처리하게 한다. 보통의 클러스터보다 훨씬 좋은거다.
3. slony-I: 멀티 슬레이브 리플리케이터다. 1:n 방식으로 원거리 백업도 되고 쓸만하다. 설정이 좀 복잡하다. 8.4에 코어로 포함될 가능성이 있다.
4. pgcluster: pgsql기반의 클러스터다. 로드밸런서-복수의 노드-리플리케이터로 구성되어 있고 분산처리, 백업, 실시간 노드 확장 등 강력한 기능들을 가지고 있다. 설정이 매우 복잡하다; 이거 테스트 한 번 하려면 =_=;;
   

이 중 pgpool-II가 주목할 만 하다. 리플리케이션 하는 방식이 좀 구리긴 하지만? 보통의 클러스터는 select쿼리가 들어오면 RR로 하나의 노드에 할당해서 처리하게 하는데 이놈은 pgsql의 쿼리 옵티마이저를 내장하고 쿼리를 분석해서 복수의 노드들에게 분산해서 처리한다. 캬~



얼마전 PostgreSQL 8.3 beta 1이 발표되었다. 개발진은 이번해 안으로 8.3을 내놓겠다 했지만.. 될까? 어쨌건 8.3이 기다려진다.

나는 pgsql 빠다;