4 年くらい前から PostgreSQL を使っている。
MySQL は、システム上必要な場合以外は使っていない。
MySQL が嫌いなわけではなく、
「何かみんな MySQL だなぁ…俺も MySQLer になろうかなぁ。」
と思ったことは数知れず。
でも、PostgreSQL にしかない機能の恩恵に、度々、与ってしまっているので、「乗り換え」には至らず。
それにしても、いろいろな RDBMS があるものだ
(
RDBMS比較資料[PDFです]
)。
各 RDBMS の特徴は、DB の設計や、チューニングの際に、顕著になるのではないだろうか?
「管理しやすくて、検索が速くて、更新もサクサクできる」
そんな DB はどうしたら実現できるだろうか…と考えていくと、自然と実装の話になっていく。
(もちろん DB を抽象化してポータブルに作っておく事が要求される場合は別。)
ということで、前置きが長くなったが、 PostgreSQL でいろいろ試したメモ。
データのセグメンテーション
PostgreSQL のデータは、
cluster ⊃ database ⊃ schema ⊃ table
という階層の最下層であるところの 'table' に保存されるが、
これらの各階層で権限の設定ができる。
「複数ユーザ(100 アカウント程度)を想定した場合、どの階層で分断するのが現実的か?」
というのを、改めてマジメに考えてみた。
以下、 アカウント [user] のデータ識別子を [user] とする。
-
table :
=> create table [user]_table(...);
=> grant all on [user]_table to [user];
却下
(テーブルの一覧がすごい事になるため)。
-
schema :
$ createuser -P [user];
=> /* public は PUBLIC に grant されてるので消す。*/
=> drop schema public;
=> create schema [user] authorization [user];
却下
(
テーブルの一覧には [user] schema に属するテーブルしか現れないが、
スーパーユーザにも見えにくくなるため。
)
-
database :
$ createdb [user];
$ createuser -P [user];
採用
(もっとも現実的)
-
cluster :
$ initdb -D /path/to/[user];
で、ポートを切替えて運用。
…却下でしょう。
ユーザ毎のデータは database のレベルで分断するのが現実的。
当たり前の結論に至り、なんか安心。
選択型データの保存形式
例えば、
「
好きな果物はなんですか?
A. りんご
B. みかん
C. いちご
...
P. いちじく
」
という問いに対する回答を保存したい場合、
- RAW: 'りんご'とか'いちご'という内容を保存すべきか?
- CODE: 'A'とか'C'というコードを保存すべきか?
◆RAW に対する CODE の strong / weak points :
[管理系]
Ｏ コードと内容との対応を外部で一元管理できるので修正が容易。
Ｘ コードと内容との対応表が無いとデータとして成立しない。
[検索系]
Ｏ データが簡潔に表せるのでちょっと速いかも。
Ｘ 特になし。
[更新系]
Ｏ 特になし。
Ｘ 特になし。
管理系の視点で CODE のほうが有利と判断する。
これも「正規化」に通じる当たり前の結論。
ただし、コードと内容との対応表(マスターテーブル) の死守が前提。
データ構造の保存
例えば、
members =
[
{
'name': '太郎',
'gender': '男'
'fruits': [1, 3]
},
{
'name': '花子',
'gender': '女'
'fruits': [2, 3, 11]
}
];
というデータ構造を保存したい場合、
-
NORMAL:
データの値 1 つをフィールド 1 つに( 1 対 1 に)保存するべきか?
上の例の場合、正規化後のテーブルは 3 つか 4 つになるはず。
-
BULK:
上の例(意図的に JSON で記述してある)のように、
「データのラベルや値を含む構造そのものを適切に表現する文字列」
として保存するべきか?
BULK の例としては、JSON の他に、
PHPコードそのもの( array('name'=>'太郎',...) )、
それを serialize(); したもの、あるいは、XML などを想定している。
また、少し毛色が違うが、PostgreSQL の配列型データも、BULK の一種として考える。
◆NORMAL に対する BULK の strong / weak points :
[管理系]
Ｏ データ構造とテーブル定義の結合度が低いので、より汎用性がある。
Ｘ データを直接操作しにくい ?
=> PostgreSQL の配列型では問題無し。
[検索系]
Ｏ 主テーブルを検索するだけで、該当するデータ構造を取得できる。
Ｘ 直積を作りにくい ?
=> PostgreSQL の配列型では問題無し。
[更新系]
Ｏ 主テーブルを更新するだけで、該当するデータ構造を更新できる。
Ｘ データ構造の部分的な更新がしにくい ?
=> PostgreSQL の配列型では問題無し。
総合的に視て、BULK というか「 PostgreSQL の配列型」のほうが有利と判断する。
以下、具体例
- NORMAL の場合:
create table fruit
(
fruit_id serial,
name text
);
create table member
(
member_id serial,
name text,
gender text
);
create table fruit_to_member
(
fruit_to_member_id serial,
member_id integer,
fruit_id integer
);
insert into fruit(name) values('りんご');
insert into fruit(name) values('みかん');
insert into fruit(name) values('いちご');
insert into member(name,gender) values('太郎','男');
insert into member(name,gender) values('花子','女');
-- make relations;
insert into fruit_to_member(member_id,fruit_id) values(1,1);
insert into fruit_to_member(member_id,fruit_id) values(1,3);
-- make another relations;
insert into fruit_to_member(member_id,fruit_id) values(2,2);
insert into fruit_to_member(member_id,fruit_id) values(2,3);
select * from member,fruit
where fruit.fruit_id in ( select fruit_id from fruit_to_member
where fruit_to_member.member_id = member.member_id );
member_id | name | gender | fruit_id | name
-----------+------+--------+----------+--------
1 | 太郎 | 男 | 1 | りんご
1 | 太郎 | 男 | 3 | いちご
2 | 花子 | 女 | 2 | みかん
2 | 花子 | 女 | 3 | いちご
(4 rows)
Time: 0.784 ms
- BULK の場合:
create table fruit
(
fruit_id serial,
name text
);
create table member
(
member_id serial,
name text,
gender text,
fruit smallint[]
);
insert into fruit(name) values('りんご');
insert into fruit(name) values('みかん');
insert into fruit(name) values('いちご');
-- make record, with ARRAY;
insert into member(name,gender,fruit) values('太郎','男','{1,3}');
-- make another record, with ARRAY;
insert into member(name,gender,fruit) values('花子','女','{2,3}');
/*
* where 2 = ANY(some_array)
* (古い記法: where some_array *= 2)
*/
select * from member,fruit
where fruit.fruit_id = ANY(member.fruit)
order by member_id;
member_id | name | gender | fruit | fruit_id | name
-----------+------+--------+-------+----------+--------
1 | 太郎 | 男 | {1,3} | 1 | りんご
1 | 太郎 | 男 | {1,3} | 3 | いちご
2 | 花子 | 女 | {2,3} | 2 | みかん
2 | 花子 | 女 | {2,3} | 3 | いちご
(4 rows)
Time: 0.506 ms
感想
たまにマニュアルを読むと幸せになれる…と信じよう。
