|
Facebook Joinc 그룹
Joinc QA 사이트
joinc는 Firefox와 chrome에서 테스트 했습니다. IE에서는 테이블이 깨지거나 이미지가 보이지 않을 수 있습니다. 특히 구글 Docs이미지의 경우 엑박처리될 수 있습니다.
배열컬럼을 이용한 일반적인 테이블 설계
작성자: 신기배(소타) nonun@nate.com
Contents
- 1 배열컬럼
- 1.1 PostgreSQL의 Data Types
- 1.2 배열컬럼으로 전환
- 1.3 배열 컬럼이 왜 필요한가?
- 1.3.1 테이블 설계 측면
- 1.3.2 테이블 관계/의존성 단순화
- 2 연산자
- 2.1 연산자 목록
- 3 함수
- 3.1 함수 목록
- 3.2 비교 함수
- 4 사용예
- 4.1 기본적인 사용법
- 4.2 데이터 입력
- 4.3 데이터 조회
- 5 generic 테이블 설계
- 5.1 설계측면의 generic 한 테이블 설계 (예제: 투표)
- 5.1.1 일반적인 정규화에 따른 설계
- 5.1.2 PostgreSQL의 배열컬럼을 사용한 설계
- 5.2 필자의 테이블 사용예
|
1 배열컬럼
1.1 PostgreSQL의 Data Types
| Numeric | smallint (int2), integer (int4), bigint (int8), decimal, numeric, real, double precision, serial, bigserial |
| Monetary | money (곧 사라지는 데이터 타입이며 to_char() 함수 사용을 권장한다.) |
| Character | character varying (varchar), character (char), text |
| Binary Data | bytea |
| Date/Time | timestamp [ (p) ] [ without time zone ], timestamp [ (p) ] with time zone, interval [ (p) ], date, time [ (p) ] [ without time zone ], time [ (p) ] with time zone (pgsql의 시간 저장 정밀도는 꽤 높은 편이다.) |
| Boolean | boolean (bool) |
| Geometric | point, line, lseg, box, path, polygon, circle |
| Network Address | cidr (IPV6 지원), inet (IPV6 지원), macaddr |
| Bit String | BIT (zerofill), BIT VARYING |
| Pseudo | any, anyarray, anyelement, cstring, void 등 (직접 정의 할 수는 없다.) |
이 외에도 사용자가 직접 데이터 타입을 만드는 것이 가능하다!! (SQL: CREATE TYPE)
1.2 배열컬럼으로 전환
| 원 데이터 타입 | 배열 데이터 타입 | 설명 |
| text | text[] | text 1차원 배열 |
| text | text[][] | text 2차원 배열 |
| text | text[][2] | 2개의 요소를 가지는 text배열의 배열 |
| integer | integer[] | integer 1차원 배열 |
| integer | integer[][] | integer 2차원 배열 |
........
위의 표를 보시느라 수고하셨다. 그냥 뒤에 [] 를 붙여주는 깊이 만큼의 배열이 생성된다. 그것도 모든 데이터 타입에 대해서!
1.3 배열 컬럼이 왜 필요한가?
아무리 설계가 중요하고 데이터 설계가 중요하다지만 누구나 실수는 있는법. 데이터 설계하고 테이블을 다 만들어 놨는데, 요구사항이 바뀌거나 빼먹은 컬럼이 있다고 가정을 하자.
보통 이런 경우는 PK나 FK 또는 인덱스가 잡혀야 할 만큼 크리티컬한 경우는 아닐것이다. (이런 경우라면 큰! 실수이며 테이블을 새로 만들어야 하겠다.)
그렇다면 빼먹은 데이터가 단순히 저장하고 보여줄 수만 있다면 된다고 치자. 또는 시스템이나 기능이 업그레이드 되면서 크리티컬하지 않은 저장해야 할 데이터가 새로 생겼다고 치자! 일반적인 경우라면 alter table 등의 SQL을 이용하여 컬럼을 추가한다...... (어떤 DBA는 임시 컬럼을 몇개 미리 추가해서 설계하는 경우도 있다. 그다음에 view만 잘 만들면 된다나...)
이런 경우 배열컬럼이 위력을 발휘한다. 몇천만의 row가 들어있는 테이블에 alter table 을 사용한다면 오랜 시간의 테이블 락이 걸릴것이고 서비스는 중지된다. 새로 테이블을 만들고 dump데이터나 insert into ... select 로 한다고 해도 꽤 많은 시간이 걸린다. 배열컬럼은 배열 요소 하나를 추가 저장하는 것만으로 모든 작업이 끝난다.
1.3.2 테이블 관계/의존성 단순화
- 1:n의 관계에 있는 테이블이 2개 있다. 자식 테이블은 어미테이블의 pk를 기준으로 n개의 레코드가 쌓인다. 두 테이블에서 데이터를 가져올 때 select ... from .. join 등을 써서 가져오거나 쿼리를 2번 해야 한다.
- 배열 컬럼은 이런 조인작업을 없애준다. 한개의 테이블에서 한개의 배열컬럼으로 해결 되는 것이다. 1:n:n 테이블의 경우라면 [][] 으로 2차원 배열을 만들면 되는 것이다.
- 물론 배열 요소의 검색이 없다면 말이다. (integer형이라면 검색할 수도 있다. gist 인덱스를 써서..)
2 연산자
2.1 연산자 목록
| 연산자 | 설명 | 예제 | 결과 |
| = | equal (배열 비교) | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t |
| <> | not equal (배열 비교) | ARRAY[1,2,3] <> ARRAY[1,2,4] | t |
| < | less than (배열 비교) | ARRAY[1,2,3] < ARRAY[1,2,4] | t |
| > | greater than (배열 비교) | ARRAY[1,4,3] > ARRAY[1,2,4] | t |
| <= | less than or equal (배열 비교) | ARRAY[1,2,3] <= ARRAY[1,2,3] | t |
| >= | greater than or equal (배열 비교) | ARRAY[1,4,3] >= ARRAY[1,4,3] | t |
| || | array-to-array concatenation (배열과 배열 합치기) | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
| || | array-to-array concatenation (배열과 배열 합치기) | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
| || | element-to-array concatenation (요소와 배열 합치기) | 3 || ARRAY[4,5,6] | {3,4,5,6} |
| || | array-to-element concatenation (요소와 배열 합치기, array_push) | ARRAY[4,5,6] || 7 | {4,5,6,7} |
3 함수
| 함수 | 리턴타입 | 설명 | 예제 | 결과 |
| array_cat (anyarray, anyarray) | anyarray | 앞의 배열($1)뒤에 뒤의 배열($2)을 붙인다. NULL이 들어오면 NULL을 리턴한다. | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
| array_append (anyarray, anyelement) | anyarray | 앞의 배열($1) 뒤에 요소($2)를 추가한다. NULL이 들어오면 NULL을 리턴한다. | array_append(ARRAY[1,2], 3) | {1,2,3} |
| array_prepend (anyelement, anyarray) | anyarray | 뒤의 배열($2) 앞에 요소($1)를 추가한다. NULL이 들어오면 NULL을 리턴한다. | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
| array_dims (anyarray) | text | 배열($1)의 크기를 리턴한다. NULL이 들어오면 에러를 리턴한다. | array_dims(array[[1,2,3], [4,5,6]]) | [1:2][1:3] |
| array_lower (anyarray, integer) | integer | returns lower bound of the requested array dimension, returning NULL for NULL inputs | array_lower(array_prepend(0, ARRAY[1,2,3]), 1) | 0 |
| array_upper (anyarray, integer) | integer | returns upper bound of the requested array dimension, returning NULL for NULL inputs | array_upper(ARRAY[1,2,3,4], 1) | 4 |
| array_to_string (anyarray, text) | text | 배열요소($1)를 구분자($2)로 합친다. implode, join() 과 유사. NULL이 들어오면 NULL을 리턴한다. | array_to_string(array[1, 2, 3], '~^~') | 1~^~2~^~3 |
| string_to_array (text, text) | text[] | 문자열($1)을 구분자($2)로 나눠서 배열로 리턴한다. explode, split() 과 유사. NULL이 들어오면 NULL을 리턴한다. | string_to_array( 'xx~^~yy~^~zz', '~^~') | {xx,yy,zz} |
| 함수 | 예제 | 결과 |
| expression operator ALL (array expression) | 3 = ALL(ARRAY[1,2,3]) | f |
| expression operator ANY/SOME (array expression) | 3 = ANY(ARRAY[1,2,3]) | t |
ALL, ANY/SOME 은 아주 빠른 비교 검색은 아니다.(그렇다고 느린건 아니고..) integer형이라면 contrib/intarray 를 참고하면 gist 인덱스를 사용하게 할 수 있다. 대략 빠르다.. -_-;
4 사용예
4.1 기본적인 사용법
nonun=# SELECT ARRAY[1,2,3]; //anyarray가 리턴된다.
array
---------
{1,2,3}
(1 row)
nonun=# SELECT '{1,2,3}'::integer[]; //integer array가 리턴된다
int4
---------
{1,2,3}
(1 row)
4.2 데이터 입력
nonun=# CREATE TABLE test(
nonun(# a text[],
nonun(# b integer[],
nonun(# c boolean[]
nonun(# );
CREATE TABLE
nonun=# INSERT INTO test VALUES ('{text1, text2, "text3~ ^^"}','{1, 3, 4, 6, 8}','{true, false, true, true}');
INSERT 222122 1
nonun=# INSERT INTO test VALUES (ARRAY['text1', 'text2', 'text3~ ^^'],ARRAY[1, 3, 4, 6, 8],ARRAY[true, false, true, true]);
INSERT 222123 1
" 과 ' 의 쓰임을 주의하세요..
4.3 데이터 조회
nonun=# SELECT * from test;
a | b | c
---------------------------+-------------+-----------
{text1,text2,"text3~ ^^"} | {1,3,4,6,8} | {t,f,t,t}
{text1,text2,"text3~ ^^"} | {1,3,4,6,8} | {t,f,t,t}
(2 rows)
nonun=# SELECT a[1] from test;
a
-------
text1
text1
(2 rows)
nonun=# SELECT a[1:2] from test;
a
---------------
{text1,text2}
{text1,text2}
(2 rows)
nonun=# SELECT a[1:3] from test;
a
---------------------------
{text1,text2,"text3~ ^^"}
{text1,text2,"text3~ ^^"}
(2 rows)
nonun=# SELECT a[3] from test;
a
-----------
text3~ ^^
text3~ ^^
(2 rows)
5 generic 테이블 설계
5.1 설계측면의 generic 한 테이블 설계 (예제: 투표)
투표의 특성은 질문이 몇개가 입력될지 알 수 없다는 단점이 있다.
일반적인 멀티한(?) 투표 테이블은 이렇다.
┌────┐ ┌────┐
│ 투표 ├───▶ ┤ 투표 │ 투표 질문 row 하나에 답변 row가 n개 이다.
│ 질문 │ │ 답변 │ 투표 답변 테이블에는 질문의 점수가 같이 저장된다.
└────┘ └────┘
위 그림과 같이 2개의 테이블이 필요하다.
투표질문
| 컬럼 | PK | FK | 인덱스 | 설명 |
| 질문의 ID | √ | | √ | |
| 질문 | | | | |
투표답변
| 컬럼 | PK | FK | 인덱스 | 설명 |
| 답변의 ID | √ | | √ | |
| 질문의 ID | | √ | √ | 투표질문의 PK |
| 답변내용 | | | | |
| 점수 | | | | |
..........
5.1.2 PostgreSQL의 배열컬럼을 사용한 설계
이제 PostgreSQL의 배열컬럼을 써서 바꿔보자..
| 컬럼 | PK | FK | 인덱스 | 설명 |
| 질문의 ID | √ | | √ | |
| 질문 | | | | |
| 답변 | | | | text[]로 text 1차원 배열로 저장한다. |
| 점수 | | | | integer[]로 정수형 1차원 배열로 저장한다. |
저장되는 크기나 인덱스의 크기가 훨씬 줄어들었다..........
5.2 필자의 테이블 사용예
| 컬럼 | 테이터 타입 |
| id | bigserial |
| text_array | text[] |
| integer_array | integer[] |
| boolean_array | bool[] |
이 테이블 하나로 대부분의 데이터 타입을 처리할 수 있다. join과 view 조합으로 여러가지 모양의 결과를 가져갈 수 있다.
속도가 느려질 꺼라구요? ORDBMS라고 자처하는 pgsql에서 row의 수는 중요하지 않답니다!!
|
|
