久々の投稿です。最近PostgreSQLばっかりやっているので、ちょっとしたTipsでも書こうかなと思います。
PostgreSQLで大規模なDWHを構築しようとした場合に、データファイルとストレージの容量の問題に悩まされます。数億オーダーのレコードが想定されるならば、気軽にPostgreSQLを使うとはいいにくいのではないでしょうか。素直にOracleとかを使えばよいのですが、そうもなかなか言ってられません。そこでテーブルのパーティショニングでデータファイルを分散化し、分散されたデータファイルを別ストレージに追いやる方法についてまとめてみます。
（今手元に動くPostgreSQLがないので、下のコードにバグがあったら後日直します・・・。）
まずは、何を格納するかについてですが、アクセスログを格納するという感じでテーブル設計してみましょう。（実際の業務でも似たようなことを・・・ry)
まずはリクエストテーブル(request)
| 物理名 | 論理名 | 型 |
| request_id | リクエストID | serial8 |
| timestamp | リクエスト日時 | timestamp |
| uri | URI | text |
| user_agent | ユーザエージェント | text |
| remote_host | リモートホスト | text |
次にパラメータテーブル(parameter)
| 物理名 | 論理名 | 型 |
| parameter_id | パラメータID | serial8 |
| request_id | リクエストID | int8 |
| timestamp | リクエスト日時 | timestamp |
| name | パラメータ名 | text |
| value | 値 | text |
というような感じになりました。そしてDDL。
create table request(
request_id serial8 not null,
timestamp timestamp not null default now(),
uri text not null default '',
user_agent text not null default '',
remote_host text not null default '',
primary key(request_id)
);
create table parameter(
parameter_id serial8 not null
request_id int8 not null default 0,
timestamp timestamp not null default now(),
name text not null default '',
value text not null default '',
primary key(parameter_id)
);
この時点では、まだインデックスや外部キーは不要です。
そして次にパーティショニングの戦略を考える必要がありますが、年月単位でパーティショニングを行ってみましょう。PostgreSQLでのパーティショニングは自動化されないので、テーブル継承と振り分けルールによって行うようにしてみましょう。
まずは表領域を作成します。(事前にpostgresユーザでディレクトリを作っておきましょう)
create tablespace log_space_200807 location '/var/local/pgsql/log_spaces/log_space_200807';
この表領域を利用する、リクエストテーブルを継承したテーブルを作成します。check(条件)という構文が出てきましたが、これはこのテーブルにはこの条件のレコードしか入らないという宣言です。以下の場合、タイムスタンプが2008年7月の日付のレコードしか入りません。
create table request_200807(
check(
'2008-07-01' <= timestamp and timestamp < '2008-08-01'
)
)
inherits(request)
tablespace log_space_200807;
create index
ix_request_200807_1
on request_200807(request_id)
tablespace log_space_200807;
create index
ix_request_200807_2
on request_200807(timestamp, uri)
tablespace log_space_200807;
そしてパラメータテーブルを継承したテーブルを作成します。チェック制約は同様です。
create table parameter_200807(check(
'2008-07-01' <= timestamp and timestamp < '2008-08-01'
)
)
inherits(parameter)
tablespace log_space_200807;
create index
ix_parameter_200807_1
on parameter_200807(parameter_id)
tablespace log_space_200807;
create index
ix_parameter_200807_2
on parameter_200807(
request_id, name, timestamp)
tablespace log_space_200807;
ここでインデックスが出てきました。継承元にはデータを格納しない為インデックスは不要ですが、継承したテーブルはデータを格納するためインデックスが必要になります。そして外部キー制約を設定します。これも実際にデータを格納するテーブル同士で設定します。
alter table parameter_200807
add constraint fk_parameter_200807_1
foreign key(request_id) references
request_200807(request_id)
on delete cascade;
最後に振り分けルールを設定します。振り分けルールとは、あるテーブルにレコードが挿入されるとき、どのテーブルにレコードが挿入されるかというルールです。チェック制約と同じ条件が設定される事になりますが、PostgreSQLにはチェック制約と振り分けルールが同じであるかを検証するすべがありません。慎重に同じ条件を設定する必要があります。
create rule
rl_request_200807_1 as
on insert to
request
where
('2008-07-01' <= timestamp and timestamp < '2008-08-01')
do instead
insert into
request_200807
values(
new.request_id,
new.timestamp,
new.uri,
new.user_agent,
new.remote_host
);
create rule
rl_parameter_200807_1 as
on insert to
parameter
where
('2008-07-01' <= timestamp and timestamp < '2008-08-01')
do instead
insert into
parameter_200807
values(
new.parameter_id,
new.request_id,
new.timestamp,
new.name,
new.value
);
これでテーブルは以上です。次にサーバの設定で、constraint_exclusionをonにしておきましょう。レコードの検索条件にタイムスタンプが含まれる場合、関係のないテーブルは実行プランに含まれなくなります。
・・・と、非常に面倒なことになりますが、これはスクリプトを使って必要に応じて生成するのがオススメです。この構成を行った場合、通常のSQLはというと、request/parameterのテーブルに対して実行するだけでよくなります。年月のサフィックスが付いたテーブルを意識する必要はありません。
複数年月のテーブルを作成し、request/parameterに対してinsertを行ってから、以下のクエリを実行してみてください。キチンとテーブルがパーティショニングされていることが確認できると思います。
select * from request;
select * from only request;
select * from request_200807;
select * from only request_200807;
select * from request_200808;
select * from only request_200808;
で、最後に注意点になりますが、insertは年月付きの実テーブルへ行った方が早いです。ベンチマークしましたが、数倍は早いです。。。大量データのインサートは直接実テーブルを行うようにしましょう。