Selfkleptomaniac

PostgreSQL8.1以降の透過的なパーティショニング

PostgreSQLのConstraint Exclusionの使い方がよくわからなかったのでまとめた。

基本的にはマスタテーブルを作成して、そこで型の指定をする。で、Constraint Exclusionを利用することで透過的に選択、更新処理のSQLを扱うことができる。

たとえば、購入履歴だとかダウンロード履歴、閲覧履歴など、何らかのログを残したい場合、データ量が膨大になることが予想されるので期間やユーザでデータを分割しておきたくなる。でも、アプリケーション側から分割されたテーブルにアクセスするのは何かと面倒だ。

そんな場合、継承元と継承先を透過的に扱うにはこんな風にする手がある。期間別テーブルの例は以下の通り：

まずはマスタとなるテーブルで方を指定して、そいつを継承した子テーブルを作成する。

--マスタテーブル（SERIALにはあまり意味はない）
CREATE TABLE master_table (
    id SERIAL, name VARCHAR(20),
    created_at TIMESTAMP WITHOUT TIME ZONE,
    updated_at TIMESTAMP WITHOUT TIME ZONE
);

--子テーブル（2009年1月のデータ用）
CREATE TABLE child_table_2009_01
    (CHECK(created_at >= '2009-01-01 00:00:00'
     AND created_at < '2009-02-01 00:00:00'))
 INHERITS (master_table);

--子テーブル（2009年2月のデータ用）
CREATE TABLE child_table_2009_02
    (CHECK(created_at >= '2009-02-01 00:00:00'
    AND created_at < '2009-03-01 00:00:00'))
 INHERITS (master_table);

で、子テーブルに入るデータの制約を決める。

--2009年1月のデータしか入らない
CREATE RULE insert_child_table_2009_01
    AS ON INSERT TO master_table
    WHERE created_at >= '2009-01-01 00:00:00'
    AND created_at < '2009-02-01 00:00:00'
DO INSTEAD INSERT INTO child_table_2009_01
    (name, created_at, updated_at)
    VALUES
    (NEW.name, NEW.created_at, NEW.updated_at);

--2009年2月のデータしか入らない
CREATE RULE insert_child_table_2009_02
    AS ON INSERT TO master_table
    WHERE created_at >= '2009-02-01 00:00:00'
    AND created_at < '2009-03-01 00:00:00'
 DO INSTEAD INSERT INTO child_table_2009_02
    (name, created_at, updated_at)
    VALUES
    (NEW.name, NEW.created_at, NEW.updated_at);

とりあえず1月と2月のデータがあればいいので、こんな感じのテストデータを用意。

$ cat insert.sh
#!/bin/sh

COUNT=0
INTERVAL=1000
MAX=3000000
BASETIME=`date --date "2009-01-01 09:00:00" "+%s"`

while test $COUNT -lt $MAX
do
        TIME=`expr $BASETIME + $COUNT`
        DATE=`date --date "1970-01-01 $TIME sec" "+%Y-%m-%d %T"`
        SQL="INSERT INTO master_table (name, created_at, updated_at)
　　　　　　　　　　VALUES ('"$COUNT"', '"$DATE"', '"$DATE"');";
        echo $SQL
        COUNT=`expr $COUNT + $INTERVAL`
done

で、テスト用データベース（p_testにした）に流し込む。

$ ./insert.sh > $HOME/pgsql/test/insert.sql

$ head -1 $HOME/pgsql/test/insert.sql
INSERT INTO master_table (name, created_at, updated_at)
VALUES ('0', '2009-01-01 00:00:00', '2009-01-01 00:00:00');
$ tail -1 $HOME/pgsql/test/insert.sql
INSERT INTO master_table (name, created_at, updated_at)
VALUES ('2990000', '2009-02-04 14:33:20', '2009-02-04 14:33:
##テストだからまあいい。
$ psql -Upostgres -d p_test -f $HOME/pgsql/test/insert.sql

インデックスはそれぞれの子テーブルに作成しておく。

p_test=# CREATE INDEX idx_2009_01 ON child_table_2009_01(created_at);
p_test=# CREATE INDEX idx_2009_02 ON child_table_2009_02(created_at);
p_test=# CREATE INDEX idx_master_table ON master_table(created_at);

で、準備は完了。しかし、これだけだと透過的に扱えるといってもあまり効率はよくない。

p_test=# EXPLAIN ANALYZE
SELECT * FROM master_table
WHERE created_at BETWEEN '2009-01-01 00:00:00'
AND '2009-01-15 00:00:00';

こんなクエリを実行すると、なぜかchild_table_2009_02まで検索されてしまう。

そこで

p_test=# SET constraint_exclusion TO on;
SET
p_test=# EXPLAIN ANALYZE
SELECT * FROM master_table
WHERE created_at BETWEEN '2009-01-01 00:00:00'
AND '2009-01-15 00:00:00';

constraint_exclusionをセットすることでWHERE句から子テーブルの制約にひっかかるテーブルの検索がなくなる。

SELECTを発行するたびにconstraint_exclusionを設定するのはバカらしいので、postgresql.confを開き、

#constraint_exclusion = off

デフォルトでoffになっているconstraint_exclusionをonに変更する。

constraint_exclusion = on

これで本当にマスタテーブルと子テーブルを透過的に扱うことが出来るようになる。どれくらい透過的かというと、上のINSERTやSELECT文に加えて

p_test=# UPDATE master_table SET name = 'hoge'
WHERE created_at >= '2009-01-01 00:00:00'
AND created_at < '2009-02-01 00:00:00';

みたいなUPDATE文もみんなmaster_tableに向けて発行できる。

でも、まだまだ問題はある。たとえば、透過的に扱えるようになることでオーバーヘッドは増す。マニュアルによれば、

A rule has significantly more overhead than a trigger, but the overhead is paid once per query rather than once per row, so this method might be advantageous for bulk-insert situations. In most cases, however, the trigger method will offer better performance.

RULEによる処理ではクエリ毎に適用されるので一気に更新する場合はいいが、行毎の実行であれば都度トリガーを実行する方がオーバーヘッドは少ない。また、COPYによるデータ挿入はこのRULEを無視してしまう。また、RULEの記述ミスがあった場合、エラーにはならず、そのままマスターとなるテーブルにデータが追加されてしまう。

それから、CURRENT_DATEなどをWHERE句に使用するとクエリプランナはどのパーティションを使えばいいのか事前にわからないので避ける必要がある。またパーティションが多すぎる場合もクエリプランナによる最適化に時間がかかるためオーバーヘッドが大きくなるので、せいぜい100個程度にしておくべき、とのこと。

あと、ちょっと不思議なことに、relpagesからサイズを求めようとしてもmaster_tableのサイズが出ない。

$ vacuumdb -f -z -d p_test -U postgres
$ psql -p 5433 -Upostgres -d p_test
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

p_test=# select sum(relpages) * 8 * 1024 from pg_class
where relname = 'master_table';
 ?column?
----------
        0
(1 row)

でも子テーブルにはちゃんとデータがある。

p_test=# select sum(relpages) * 8 * 1024 from pg_class
where relname = 'child_table_2009_01';
 ?column?
----------
   155648
(1 row)

p_test=# select sum(relpages) * 8 * 1024 from pg_class
where relname = 'child_table_2009_02';
 ?column?
----------
    40960
(1 row)

このあたりはまだよくわかっていない…

Popularity: 2% [?]

PostgreSQLメモ

PostgreSQLのデータベース毎の容量調査方法を忘れていたのでメモ。統計情報が更新されていないと意味ないが。

psql -Upostgres -l -t -q | while read line
do
    DBNAME=`awk <<< $line '{print $1}'`
    if test -n "$DBNAME"&& test "$DBNAME" != "template0"
    then
        echo -n $DBNAME ":"
        psql -Upostgres -d $DBNAME -t -q -c "SELECT sum(relpages) * 8 * 1024 from pg_class"
    fi
done

Popularity: 1% [?]