Oracle vs PostgreSQL

1. User vs Schema

PostgreSQL 은 Schema 를 지원하고, Oracle 은 User 가 Schema 를 포함하는 개념이다.

PostgreSQL 에서는 Schema 의 사용을 권한다.

PostgreSQL 에서 Schema를 만들고, 테이블을 만들 때 Schema를 지정하는 따위의 예제는 ddl_table_postgresql.sql 를 참조하고, 테이블 등의 Schema를 변경하는 예제는 change_schema.sql 를 참조한다.

2. VARCHAR2 vs Varchar

Oracle 은 VARCHAR2 인데 반해, PostgreSQL 은 Varchar 이다.

Varchar 는 Variable-length character 약자이다.

대부분의 문자열은 Varchar 가 사용된다.

길이가 정해져 있는, 예를 들어 코드와 같은 것들은 char가 사용되는 경우도 있다.

3. null

Oracle 에서 '' 은 null 이지만, PostgreSQL 에서는 '' 는 '' 이고, null 은 null이다.

Oracle 에서 다음과 같이 사용되는 구문이

where title is null

PostgreSQL 에서는 다음과 같이 다시 작성되어야 한다.

where (title is null or title = '')

PostgreSQL 에서는 '' 대신에 명시적으로 null 을 입력하는 것을 추천한다.

PostgreSQL 에서 공백과 이와 비슷한 문자로만 구성된 것도 포함시키려면 다음과 같다.

where (title is null or REGEXP_REPLACE(title, '^\s+|\s+$', '', 'g') = '')

trim 함수 대신에 REGEXP_REPLACE 함수를 사용한 것에 대해서는 Postgresql 의 trim 함수는 chr(9), chr(10), chr(13) 따위를 없애지 않는다. 를 참조하라.

4. NVL vs COALESCE

첫 번째 파라미터 값이 null 인 경우, 두 번째 파라미터 값을 return 하고, 그렇지 않은 경우 첫 번째 파라미터 값을 return 한다.

PostgreSQL 에서 '' 은 null 이 아니라는 것에 주의해야 한다. PostgreSQL 에서 nvl 과 완벽하게 일치하는 결과를 얻기 위해서는 case when 구문을 사용해야 한다. Oracle 과 PostgreSQL 에서 null 의 차이는 이 글의 앞부분을 참고한다.

Oracle 에서 사용법은 다음과 같다.

select nvl(null, sysdate) from dual

PostgreSQL 에서 사용법은 다음과 같다.

select COALESCE(null, now())

5. decode vs case when

PostgreSQL 은 decode 함수를 지원하지 않기 때문에 case when 구문을 사용해야 한다.

Oracle 에서도 case when 구문을 지원하지만, 8i 정도로 오래된 버전들은 그렇지 않다.

6. dual

PostgreSQL 은 dual 이 없지만, from 절을 생략할 수 있다.

7. text 자료형

Oracle 은 text 자료형을 지원하지 않는다.

12c 부터는 MAX_STRING_SIZE 파라미터를 EXTENDED 로 설정하면, VARCHAR2 의 길이를 32767 까지 늘릴수 있다.

12c 이전은 clob 이나 blob 을 사용하고, 더 오래된 버전은 long을 사용하거나 (검색을 위해서) 여러 개의 VARCHAR2 컬럼으로 나누기도 한다.

8. index 와 like 검색

PostgreSQL 에서 index 를 생성할 때 text_pattern_ops 옵션을 추가해야 하는 경우도 있다.

Oracle 에서 title 컬럼에 index 가 걸려있다면, 다음과 같은 sql 을 실행할 때 index 스캔을 한다.

select title from memo where title like 'aaa%'

PostgreSQL 에서는 다음과 같이 해야 한다.

CREATE INDEX "memo$title" ON memo.memo (title text_pattern_ops);

PostgreSQL 에서는 like 검색의 효율을 높이는 다른 방법도 있다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX "memo$title" ON memo.memo USING gin (title gin_trgm_ops);

btree_gin 는 결합인덱스를 위한 것이다.

위와 같이 pg_trgm 확장기능을 이용하면, 양쪽 like 검색에서도 index scan 을 한다.

pg_trgm은 3글자 이상인데, pg_bigm 은 2글자 이상이다.

PostgreSQL 은 다음과 같이 index를 구성하는 컬럼을 함수로 가공할 수도 있다.

CREATE INDEX "memo$parent_id$title$contents$insert_date" 
ON memo.memo USING gin (
    parent_id,
    (coalesce(title, '') || ' ' || coalesce(contents, '')) gin_trgm_ops,
    insert_date
);

위 index 는 다음과 같이 사용한다.

select title from memo.memo
where parent_id > 0
    and (coalesce(title, '') || ' ' || coalesce(contents, '')) like '%' || '검색어' || '%'
order by insert_date desc

9. Oracle Spatial vs PostGIS

GIS 데이타를 저장하는 확장기능이다.

GIS 데이타는 전통적으로 RBDMS 에 저장되던 데이타와 차이가 있고, 검색을 위해 RTree 나 Quadtree 와 같은 index 를 사용한다.

대한민국에서 GIS 를 하는 사람들 대부분은 PostGIS 을 알고 있는 것으로 생각된다. 반대의 경우는 호기심이 많거나 주변에 호기심이 많은 사람이 있지 않는 한 PostgreSQL 을 아는 사람이 흔치는 않는 것 같다.

10. Oracle 과 PostgreSQL 은 Optimizer 가 다르다.

Oracle 에서 Index Scan 으로 실행되었던 sql 이 PostgreSQL 에서는 Seq Scan (Full Table Scan) 이 될 수 있다.

Oracle 에서 PostgreSQL 로 응용프로그램을 변경한다면, 해야 할 일이 생각외로 많을수도 있다는 의미이다.

이건 반대의 경우도 마찬가지이고, 데이타베이스를 변경하는 것은 상당한 위험을 감수해야 한다.

응용프로그램을 새롭게 개발한다고 하더라도, 한동안은 실행계획을 확인하면서 Optimizer 의 동작방식에 익숙해져야 한다.

PostgreSQL 에서 실행계획을 확인하기 위해서는 sql 구문 앞에 explain 을 붙여서 실행하면 된다.

explain
select title from memo where title like 'aaa%'
;

11. 잡담

모든 데이타베이스는 나름대로의 쓰임이 있다.