jsonb 자료형 다루기

들어가며

인터넷에서 자료를 전달할 때 그 자료 형식을 무엇으로 할 것인가에 대한 수 많은 방법이 등장했고, 앞으로도 등장 할 것이지만, 이 글을 쓰고 있는 현재 대세는 json 형식입니다. 데이터베이스에서 이 형식 그대로 저장하고, 더 나아가 그 자료에 대한 인덱스를 사용할 수 있다면, 응용 프로그램 개발 비용을 현저하게 줄일 수 있을 것입니다. jsonb 자료형이 만들어진 이유는 바로 이 점 때문입니다.

PostgreSQL에서는 9.2 버전(2012년)부터 json 자료형을 기본 자료형으로 포함했습니다. 기본 자료형이라고 하는 것은 그 자료형을 사용하는 연산자들을 함께 제공함을 의미합니다.  예를 들어 숫자형을 사용할 수 있다함은 사칙연산을 데이터베이스에서 할 수 있음을 의미합니다. 이렇게 9.2 버전부터 json 형식의 자료에 대해서 한 칼럼에 저장하고, 그 자료에 대한 연산자를 제공함으로 응용프로그램 쪽에서 하던 일을 SQL 쿼리로도 처리 할 수 있게 함으로 응용프로그램 개발 비용을 줄일 수 있었습니다.

하지만, json 자료형은 인덱스를 사용할 수 없다는 치명적인 단점이 있었습니다. (PostgreSQL 부분 인덱스, 함수 기반 인덱스를 사용함으로 이 문제를 어느 정도는 피해 갈 수는 있었지만,  궁극적으로 json 형식의 key-value 방식의 임의의 자료형에 대한 인덱스 처리가 유연하지 못했던 것은 그 한계였습니다.) 즉, 응용프로그램에서 사용하는 json 자료를 그냥 text 자료형으로 저장하고, 그것을 응용프로그램에서 json 자료형으로 변환해서 사용하는 것과 크게 다르지 않다는 것입니다.

jsonb 자료형은 9.4 버전(2014년)에서 기본 자료형이 되었습니다. 이 글은 이 자료형에 대한 소개와 그 예제를 소개함으로 응용 프로그램을 개발 할 때 비용을 최소화 할 수 있는 방안을 소개합니다.

json 자료형과 jsonb 자료형

먼저 json 자료형과, jsonb 자료형의 다른 점은 그 저장방식입니다.

json 자료형은 입력된 그 자료 그대로 저장되지만, jsonb 자료형은 의미단위(key-value 기준)로 선처리를 해서 저장합니다. 

ioseph=# SELECT '{"b":"1",    "b":2, "a":"문자열"}'::json;
               json
-----------------------------------
 {"b":"1",    "b":1, "a":"문자열"}
(1 row)

ioseph=# SELECT '{"b":"1",    "b":2, "a":"문자열"}'::jsonb;
          jsonb
-------------------------
 {"a": "문자열", "b": 2}
(1 row)

윗 경우가 바로 json과 jsonb 자료형의 처리 방식에 대한 가장 기본적인 차이점을 보이는 경우입니다.  공백문자를 무시하며, value 값은 적당한 형변환을 하며, 그 key 이름이 중복될 경우는 마지막 것을 사용하며, key는 정렬됩니다.

즉, jsonb 자료형을 사용하는 경우라면, 입력에서 저장까지 json 자료 형태에 대한 선처리 작업을 진행함을 의미합니다. (그 만큼의 자료 입력에 대한 작업 비용이 듦을 의미합니다.)

jsonb 자료형 연산

jsonb 자료형을 사용할 수 연산자는 기존 json 자료형과 같습니다.
 다음 내용은 PostgreSQL에서 설명서에서 소개하고 있는 연산자들입니다.

표 9-40. json and jsonb Operators

일반적으로 이 연산는 where 절에서는 다시 =, <> 같은 일반 연산와 함께 사용됩니다. 예를 들어

select * from jsontable where column1 ->>'hostname' = 'myhost'

이런 형태로 사용됩니다. 또는 부분 인덱스를 만들 때도 사용됩니다.

create index jsontable_column1_i on jsontable (column1->>'hostname')

jsonb 자료형의 인덱스

한편, jsonb 자료형에서만 사용할 수 있는 부가 연산자들이 있는데, 다음과 같습니다.

이 연산자들은 해당 jsonb 자료에 대한 인덱스를 만들면, 이 연산자를 사용할 경우 인덱스를 사용할 수 있습니다.  즉 윗 쿼리를 다시 예로 든다면,

select * from jsontable where column1 ->>'hostname' = 'myhost' 

윗 쿼리에 대해서

column1 칼럼에 대해서,  인덱스를 다음과 같이 만든다면,

create index jsontable_column1_jsonb_i on jsontable using gin (column1)

아래와 같은 쿼리로 그 인덱스를 사용할 수 있습니다.

select * from jsontable where column1 @> '{"hostname":"myhost"}'::jsonb

하지만, 이런 방식에도 한계가 있는데, 일단, json 형식이 복잡한 경우나 그 형식이 비정형인 경우는 인덱스를 사용할 수 없습니다. 또한 json 형식을 테이블로 생각한다면, key에 해당하는 모든 모든 칼럼들에 대해서 인덱스의 key로 사용되기 때문에, json 자료의 내용이 큰 경우, 자료량이 많은 경우 해당 인덱스의 크기가 의도되지 않게 커지게 됩니다. @> 연산자만 인덱스를 사용하게끔 인덱스 연산 클래스를 지정하면 그나마 인덱스 크기를 좀 줄일 수 있습니다.

create index jsontable_column1_jsonb_i on jsontable using gin (column1 jsonb_path_ops)

jsonb 자료형의 실무 예제

@> 연산자는 오른쪽 jsonb 자료가 왼쪽 jsonb 자료 안에 포함되어 있는가를 살펴보는 연산자입니다. 즉 윗 쿼리 처럼 조건을 만족하는 방식이 정확해야 합니다. 이렇게 json 데이터를 저장하고, 사용하는 실무 환경이 그다지 많지 않습니다.

실무에 적용해서 이익을 얻을 수 있는 영역은 회원정보 테이블처럼 필수 항목과 선택 항목이 혼재하는 정보에 대해서 아직 사용자가 입력하지 않은 선택 항목 정보는 처음부터 DB에 저장하지 않음으로 테이블의 크기를 줄일 수 있으며, 필수 항목(로그인 ID)에 대해서는 인덱스를 사용할 수 있으며, 응용프로그램의 사정에 의해 회원 속성이 추가되거나 삭제되는 경우에 대해서도 유연하게 대처할 수 있습니다.

전통적인 관계형 데이터베이스 모델링 표기법으로 이 회원 테이블을 모델링 해보면서, 선택 입력 칼럼은 기울림체로 표시 하겠습니다.

회원테이블 (회원번호, 로그인ID, 비밀번호, 이름, 전화번호, 이메일, 생일, 성별, 결혼유무, 결혼기념일, 주소, 우편번호)

이정도 될 것 같네요.

이것을 다시, jsonb 자료형을 사용하는 테이블로 모델링 하면, 

회원테이블 (회원번호, 회원ID, 회원정보)

이런 극단적인 모습이 될 수도 있겠습니다. MongoDB와 같이 회원 컬랙션은 회원이라는 도규먼트들의 반 비정형 집합(json 형태의 자료)으로만 구성됩니다. 회원정보 json 자료안에 회원ID가 있음에도 불구하고, 회원ID 칼럼을 또 만들어 비정규화 한 이유는 이 칼럼에 대한 유니크 인덱스를 만들어야 하기 때문입니다.

실재 구현된 테이블은 아래와 같습니다.

ioseph=# \d members
                          Table "public.members"
 Column |  Type   |                       Modifiers
--------+---------+-------------------------------------------------------
 num    | integer | not null default nextval('members_num_seq'::regclass)
 id     | text    | not null
 d      | jsonb   | not null
Indexes:
    "members_pkey" PRIMARY KEY, btree (num)
    "members_id_key" UNIQUE CONSTRAINT, btree (id)
    "members_d_i" gin (d jsonb_path_ops)

members_d_i 인덱스는 위에서 설명한 방식 그대로 @> 연산자에 대해서만 인덱스를 사용하겠다고 만들었습니다.

여기서 주의해야 할 점은, jsonb 자료형을 쓰겠다 함은 응용 프로그램에서 회원 가입 처리 할 때 가입 정보를 데이터베이스로 보낼 때, 선택 항목 자료가 없는 경우 해당 key 값조차 넘기지 말아야 jsonb 자료형을 제대로 사용하는 것이 될 것 입니다.

자료 입력 쿼리

ioseph=# insert into members (num, id, d) values (default, 'ioseph',
('{"id":"ioseph",
"pw":"'
|| encode(digest(digest(currval('members_num_seq')
|| '.' || 'mypassword', 'sha512'),'sha512'),'hex') || '"}')::jsonb);
INSERT 0 1

회원 인증 쿼리

select *, d->>'pw' 
= encode(digest(digest(id || '.' || 'mypassword','sha512'),'sha512'),'hex') 
as granted from members 
where id = 'ioseph'

이 쿼리의 실행 결과가 로우가 하나 있고, granted 칼럼 값이 true 경우를 제외한 모든 경우는 해당 id에 대한 인증을 통과하지 못합니다.

여기까지는 여느 회원 정보 관리 기법과 크게 다르지 않습니다. 하지만,
이름, 이메일, 생일 같은 부가 정보를 인증 방식으로 삼아 로그인ID 찾기, 로그인 비밀번호 초기화 같은 작업을 할 때 jsonb 자료형은 아주 효율적입니다. 로그인ID 찾기 기능에서 이름과 이메일을 입력 받아 그 ID를 찾는다면,

select id 
from members 
where d @> '{"username": "홍길동", "email": "myname@hostname"}'::jsonb

이런 형태로 쿼리를 보내고 해당 쿼리 결과가 있다면, 그 결과를 응용프로그램에서 처리하면 될 것입니다.

마치며

지금까지 PostgreSQL 9.4 버전에서 새롭게 제공하는 jsonb 자료형에 대해 살펴 보았습니다.

실무에서 이 자료형을 사용한다면, 모델링부터 새롭게 접근해야 하며, 응용 프로그램쪽에서는 새로운 쿼리를 만들어야하는 부담이 생깁니다. 또한 막상 실문 예제를 찾으면서 알게된 사실은 이 자료형이 대용량 자료를 처리해야 하는 경우이면서 json 특정 key 기준 범위 검색을 해야하는 경우에 이 자료형으로 사용하겠다는 발상은 꽤 위험해 보입니다.

하지만, json 형태로 저장해서 얻는 이점도 꽤 많기 때문에, 잘 사용한다면, 개발, 운영 비용을 크게 줄일 수 있을 것 같습니다.

이 글에서 미처 언급하지 못한 json, jsonb 관련 함수들 가운데, 실무에서 아주 유용하게 사용할 수 있는 함수들도 많습니다. 틈 나는 대로 한 번은 쭉 살펴 본다면, 이 자료형을 다루는데 많은 도움이 될 것입니다.