bytea 자료형이 이진 자료를 저장하는 용도입니다.

문제는 입력인데,

세가지 방법이 있습니다.

1.

서버측에서 할 수 있는 것은 encode, decode 함수를 사용해서 클라이언트의 입출력을 편하게 하는 것인데,

데이터베이스 입장에서는 단지 입출력 때문에 encode, decode 비용을 지불하는 것이 부담이 되기는 하죠.

2.

클라언트측에서 encode, decode를 담당하는 방식으로 데이터베이스는 bytea 자료로 담는 것이 아니라,

base64 같은 클라이언트 측에서 미리 인코딩이 끝난 text 자료형으로 입출력합니다.

장점은 zlib 같은 압축도 기능도 클라이언트에서 직접구현해서 쓸 수 있으니 좀더 자유롭고 encode,decode 비용을 데이터베이스가 맡지 않으니까 그만큼 데이터베이스는 가벼워지겠죠.

3.

jdbc 드라이버나, python 드라이버처럼 응용프로그램에서 사용하는 데이터베이스 조작 라이브러리가 자체적으로 그 기능을 제공하고 있어, 그 API를 이용해서 이진자료 그대로 입력하고, 그대로 가져오는 방식도 있습니다. php에서는 어떻게 하는지는 저도 잘 모릅니다.  아마 "php postgresql bytea insert" 이렇게 검색하면 자료가 있지 않을까싶네요.

하지만!

이 모든 것을 떠나서 해당 칼럼에 담길 자료가 일반적으로 생각하는 '자료'라는 기준에서는 크기가 큰 멀티미디어 파일을 데이터베이스에 담겠다면 많은 고민이 필요합니다. 왜냐하면, 데이터베이스 서버는 멀티미디어 서버는 아니거든요.

참고로 PostgreSQL에서 자료를 처리하는 최소 단위는 8킬로바이트입니다. 이 크기 안에 여러 레코드(로우)를 순차적으로 담아놓고 사용합니다.

 가르쳐주셔서 감사합니다 ^^

저희 데이터베이스 자체가 모델링 파일 자체를 관리하기위해서  

보안이나 이런측면 때문에 데이터베이스에 넣어서 관리하는데 

이 방식이 아닌것 같나요?

모델링크기가 조금 최대 100mb? 하지만 압축하거나하면은 조금은 괜찮아 질것같은데

제가 데이터베이스가 전문가가 아니라서 질문드립니다..

가르쳐주시2번 방법을 해볼려고

지금 byte[] 이진 배열을 text문에 넣을려고하니깐 안넣어져서 찾아봣는데

byte[] 타입을 string으로 바꾸는거 자체가 데이터 적으로 크기가 더 커지지않나요?

byte[] 형이 byte 자료형의 배열인거죠?

그 배열 그 자체가 PostgreSQL에서는 bytea 형입니다.

SQL 구문으로 이 칼럼 자료형에 자료를 넣으려면,  쿼리문 자체가 문자열이기 때문에, 이진 자료를 문자열로 변환이 필요합니다. 가장 대표적인게 base64겠죠. 응용프로그램에서 그 자료가 base64로 이미 처리되고 있다면, 그 문자열을 (byte[] 자료, 또는 string 자료) insert into 구문에 decode와 함께 사용합니다.

ioseph=> \d binary_table
            "public.binary_table" 테이블
  필드명   |  종류   | Collation | NULL허용 | 초기값
-----------+---------+-----------+----------+--------
 file_name | text    |           |          |
 file_type | text    |           |          |
 file_size | integer |           |          |
 file_data | bytea   |           |          |

ioseph=> insert into binary_table values ('file1.txt', 'txt', 13, decode('aGVsbG8gd29ybGQhCg==', 'base64'));
INSERT 0 1
ioseph=> select * from binary_table ;
 file_name | file_type | file_size |          file_data
-----------+-----------+-----------+------------------------------
 file1.txt | txt       |        13 | \x68656c6c6f20776f726c64210a
(1개 행)
ioseph=> select convert_from(file_data,'utf8') from binary_table ;
 convert_from
--------------
 hello world!+

(1개 행)

전형적인 이진자료입니다. byte(8bit 자료) 배열인거죠.

여기서 더 나아가면, 이런 생각을 해 볼 수 있어요. 어차피 encode, decode를 사용해서 데이터베이스에 넣을거면, 그냥 base64 문자열을 그대로 집어 넣어도 되겠네.

ioseph=>  create table base64_table (
  file_name text,
  file_type text,
  file_size int,
  file_data text);
CREATE TABLE
ioseph=> insert into base64_table values ('file1.txt', 'txt', 13, 'aGVsbG8gd29ybGQhCg==');
INSERT 0 1
ioseph=> select * from base64_table;
 file_name | file_type | file_size |      file_data
-----------+-----------+-----------+----------------------
 file1.txt | txt       |        13 | aGVsbG8gd29ybGQhCg==
(1개 행)
ioseph=> select convert_from(decode(file_data, 'base64'),'utf8') from base64_table;
 convert_from
--------------
 hello world!+

(1개 행)

여기서는 base64로 미리 인코딩 된 문자열이 text형으로 저장되기 때문에 꺼내쓸 때는 decode() 함수로 이진자료로 다시 바꿔야합니다. 물론이 이 작업을 클라이언트가 맡아도 됩니다.

여기까지가 순수 텍스트 기반 SQL 구문으로 이 문제를 푸는거고,

클라이언트 api를 사용한다면, 각 언어마다 사용법이 약간씩 다릅니다. php는 잠깐 짜보니, 아무 선작업 없이  그냥 쓰면 되네요.

$db = new PDO("pgsql:host=127.0.0.1;dbname=ioseph","ioseph","test");

$base64_str = "aGVsbG8gd29ybGQhCg==";
$binary_str = base64_decode($base64_str);

// bytea
$stmt = $db->prepare("insert into binary_table (file_data) values (?)");
$stmt->execute(array($binary_str));

// text
$stmt = $db->prepare("insert into base64_table (file_data) values (?)");
$stmt->execute(array($base64_str));

그럼에도 불구하고,

이 처리 방식은 100mb 단위의 자료를 처히는데는 권장하지 않는 방식입니다.

물론 이 PostgreSQL을 혼자 쓰는 그저 모델링 파일을 보관하는 개인용 저장소 역할만 하겠다면,

말리지는 않습니다. 하지만, 그런 용도라면, PostgreSQL 보다 훨씬 가벼운 sqlite 도 있습니다.

PostgreSQL로 구현하고 다중 사용자 환경이고, 분비는 서비스라면,

그 100mb를 처리하는 동안 그 세션은 활성 세션으로 처리될 것이고, 관계형 데이터베이스에서 활성 세션 관리는 꽤 중요한 항목 가운데 하나인데, 이 세션 관리가 엉망이 될 것임이 분명하기 때문입니다.

다시 앞에서 했던 말, 꼭 기억해야할 말

데이터베이스 서버는 멀티미디어 서버는 아니거든요.

참고로 PostgreSQL에서 자료를 처리하는 최소 단위는 8킬로바이트입니다. 이 크기 안에 여러 레코드(로우)를 순차적으로 담아놓고 사용합니다.