2012年12月24日

テーブルパーティショニングツール「pg_part」を使ってみる

PostgreSQL Advent Calendar 2012（全部俺）のDay 24です。

前回のエントリでは、PostgreSQLのテーブルパーティショニングの基本的なしくみとその使い方を解説してきました。

前回解説した通り、PostgreSQLのパーティショニングの機能は「理屈としては」確かに動くのですが、実際にはそのためにいろいろなコマンドを実行したりしなければならず、なかなか手間がかかるのも事実です。

■テーブルパーティションを操作するpg_partパッケージ


テーブルパーティショニングは、特に分析系の処理をしている時には便利なのですが、PostgreSQLの場合、作成や管理に結構手間がかかるため、なかなか手を出せない、という方もいるのではないでしょうか。（私も以前はそうでした）

そのため、パーティション操作のための処理を一括して実施してくれる関数群を提供するpg_partパッケージを作成しました。

uptimejp/pg_part
https://github.com/uptimejp/pg_part

今回は、このpg_partパッケージに含まれるSQL関数の使い方を紹介します。

pg_partを使うと、
  • パーティションの作成（＋データの移行）
  • パーティションの解除（＋データの移行）
  • パーティションの追加（アタッチ）
  • パーティションの切り離し（デタッチ）
が簡単に行えるようになります。

なお、pg_partパッケージはSQL関数の定義の集まり（SQLスクリプト）ですので、psqlコマンドを使ってデータベースに登録して使用します。 
[snaga@devsv03 ~]$ psql -f pg_part.sql dbt3
BEGIN
CREATE FUNCTION
CREATE FUNCTION
(...snip...)
CREATE FUNCTION
CREATE FUNCTION
COMMIT
[snaga@devsv03 ~]$

■パーティションの作成


パーティションの作成には、pgpart.add_partition()関数を使います。 
SELECT pgpart.add_partition(
  schema_name,
  table_name,
  partition_name,
  check_condition,
  temp_file);
schema_nameはテーブルの存在するスキーマ名です。通常は 'public' などでしょう。

table_nameは、パーティションを作成する元となる親テーブルのテーブル名です。

partition_nameは、作成するパーティションの名前です。

check_conditionは、作成するパーティションが保持しているレコードの範囲を表すCHECK制約の条件です。特定のカラムについて範囲指定などの形で記述します。

temp_fileは、親テーブルからパーティションにデータを移行させる時に利用する一時ファイルのファイル名です。

これらのパラメータを指定して実行すると、成功するとtrue、失敗するとfalseを返します。 
dbt3=# SELECT pgpart.add_partition(
dbt3(#   'public',
dbt3(#   'orders',
dbt3(#   'orders_1992',
dbt3(#   ' ''1992-01-01'' <= o_orderdate AND o_orderdate < ''1993-01-01'' ',
dbt3(#   '/tmp/orders.tmp');
 add_partition
---------------
 t
(1 row)

dbt3=#
pgpart.add_partition()関数は、
  • 親テーブルを継承して子テーブル（パーティション）を作成
  • 親テーブルからパーティションに移動させるレコードをエクスポート
  • 親テーブルからパーティションに移動させるレコードを削除
  • エクスポートしておいたレコードをパーティションにインポート
  • パーティションに親テーブルと同等の主キー制約を追加
  • パーティションに親テーブルと同等のインデックスを追加
という処理を行います。

なお、pgpart.add_partition()関数を実行すると、以下のようにいくつかNOTICEメッセージが出力されます。これは、パーティションを作成するために内部的に実行しているDDL文です。 
psql:add_part.sql:8: NOTICE:  add_partition: CREATE TABLE public.orders_1992( CONSTRAINT __orders_1992_check CHECK( '1992-01-01' <= o_orderdate AND o_orderdate < '1993-01-01' )) INHERITS (public.orders);
psql:add_part.sql:8: NOTICE:  add_partition: COPY ( SELECT * FROM public.orders WHERE  '1992-01-01' <= o_orderdate AND o_orderdate < '1993-01-01'  ) to '/tmp/orders.tmp';
psql:add_part.sql:8: NOTICE:  add_partition: DELETE FROM public.orders WHERE  '1992-01-01' <= o_orderdate AND o_orderdate < '1993-01-01' ;
psql:add_part.sql:8: NOTICE:  add_partition: COPY public.orders_1992 FROM '/tmp/orders.tmp';
psql:add_part.sql:8: NOTICE:  add_partition: ALTER TABLE public.orders_1992 ADD PRIMARY KEY (o_orderkey);
psql:add_part.sql:8: NOTICE:  add_partition: CREATE INDEX orders_1992_o_orderdate_idx ON public.orders_1992 USING btree (o_orderdate);
psql:add_part.sql:8: NOTICE:  add_partition: CREATE INDEX orders_1992_o_custkey_idx ON public.orders_1992 USING btree (o_custkey);
pgpart.add_partition()関数だけではなく、pg_partパッケージの提供する関数群ではこのように内部的に呼び出されるDDL文をNOTICEメッセージとして出力しますが、すべて記載すると冗長になるためこのエントリでは省いています。

■パーティション一覧の取得


指定したテーブルが、どのようなパーティションから構成されているかを取得するための関数が pgpart.show_partition()関数です。 
SELECT pgpart.show_partition(schema_name, table_name);
schema_nameはテーブルの存在するスキーマ名で、table_nameは親テーブルのテーブル名です。

例えば、ordersテーブルをパーティション化している場合、以下のように実行することで、ordersテーブルを構成するパーティションの一覧を取得することができます。 
dbt3=# SELECT pgpart.show_partition('public', 'orders');
 show_partition
----------------
 orders_1992
 orders_1993
 orders_1994
 orders_1995
 orders_1996
 orders_1997
(6 rows)

dbt3=#

■パーティションのマージ（解除）


作成したパーティションを、もとのテーブルに戻すには pgpart.merge_partition()関数を使います。 
SELECT pgpart.merge_partition(
  schema_name,
  table_name,
  partition_name,
  check_constraint,
  temp_file);
引数は、pgpart.add_partition()関数とほとんど同じです。

schema_nameはテーブルの存在するスキーマ名です。

table_nameはパーティションのデータを戻す先となる親テーブルのテーブル名です。

partition_nameは親テーブルにマージするパーティションの名前です。

check_conditionは、作成するパーティションが保持しているレコードの範囲を表すCHECK制約の条件です。特定のカラムについて範囲指定などの形で記述します（但し、現時点では未使用です）。

temp_fileは、パーティションから親テーブルにデータを移行させる時に利用する一時ファイルのファイル名です。 
dbt3=# SELECT pgpart.merge_partition('public', 'orders', 'orders_1992', null, '/tmp/orders.tmp');
 merge_partition
-----------------
 t
(1 row)

dbt3=#

■パーティションのアタッチ（追加）


親テーブルとまったく同じスキーマ構造を持つテーブルを作成すると、そのテーブルをパーティションとして追加することができます。 
SELECT pgpart.attach_partition (
  schema_name,
  table_name,
  partition_name,
  check_condition);
schema_nameはテーブルの存在するスキーマ名です。

table_nameはアタッチするパーティションの親テーブルのテーブル名です。

partition_nameはアタッチするパーティションの名前です。

check_conditionは、アタッチするパーティションが保持しているレコードの範囲を表すCHECK制約の条件です。

以下は、パーティション化されているordersテーブルにorders_1998パーティションを追加している例です。 
dbt3=# SELECT pgpart.show_partition('public', 'orders');
 show_partition
----------------
 orders_1992
 orders_1993
 orders_1994
 orders_1995
 orders_1996
 orders_1997
(6 rows)

dbt3=# SELECT pgpart.attach_partition('public', 'orders', 'orders_1998', ' ''1998-01-01'' <= o_orderdate AND o_orderdate < ''1999-01-01'' ');
 attach_partition
------------------
 t
(1 row)

dbt3=# SELECT pgpart.show_partition('public', 'orders');
 show_partition
----------------
 orders_1992
 orders_1993
 orders_1994
 orders_1995
 orders_1996
 orders_1997
 orders_1998
(7 rows)

dbt3=#
パーティションをアタッチする前には1997年のパーティションまでしか無かったものの、pgpart.attach_partition()関数で1998年のパーティションを追加した結果、以下のEXPLAINを見ると、SELECTクエリでordersテーブルに検索を行う際に1998年のパーティションも検索対象になっていることが分かります。 
dbt3=# EXPLAIN SELECT count(*) FROM orders WHERE o_orderdate = '1998-01-01';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=6044.09..6044.10 rows=1 width=0)
   ->  Append  (cost=0.00..6027.42 rows=6667 width=0)
         ->  Seq Scan on orders  (cost=0.00..0.00 rows=1 width=0)
               Filter: (o_orderdate = '1998-01-01'::date)
         ->  Bitmap Heap Scan on orders_1998 orders  (cost=72.27..6027.42 rows=6666 width=0)
               Recheck Cond: (o_orderdate = '1998-01-01'::date)
               ->  Bitmap Index Scan on orders_1998_o_orderdate_idx  (cost=0.00..70.61 rows=6666 width=0)
                     Index Cond: (o_orderdate = '1998-01-01'::date)
(8 rows)

dbt3=#

■パーティションのデタッチ（削除）


逆に、特定のパーティションをテーブルから切り離す（デタッチ）することも可能です。 
SELECT pgpart.detach_partition (
  schema_name,
  table_name,
  partition_name);
schema_nameはテーブルの存在するスキーマ名です。

table_nameはデタッチするパーティションの親テーブルのテーブル名です。

partition_nameはデタッチするパーティションの名前です。

以下は、ordersテーブルを構成するパーティションのひとつであるorders_1992パーティションをデタッチしている例です。 
dbt3=# SELECT pgpart.show_partition('public', 'orders');
 show_partition
----------------
 orders_1992
 orders_1993
 orders_1994
 orders_1995
 orders_1996
 orders_1997
 orders_1998
(7 rows)

dbt3=# SELECT pgpart.detach_partition('public', 'orders', 'orders_1992');
 detach_partition
------------------
 t
(1 row)

dbt3=# SELECT pgpart.show_partition('public', 'orders');
 show_partition
----------------
 orders_1993
 orders_1994
 orders_1995
 orders_1996
 orders_1997
 orders_1998
(6 rows)

dbt3=#

■パーティション作成時の手順


テーブルをパーティション化するためには、pgpart.add_partition()関数を使って必要な全パーティションを作成していきます。

全パーティションの作成が終わって、親テーブルに残っているデータが無くなったら（親テーブルが論理的に空になったら）、親テーブルに対してVACUUMないしCLUSTERを実行してください。

パーティション作成の際には、パーティションに移動したレコードを親テーブルからDELETEしていますが、親テーブルにはまだ削除済みレコードが残っているため、明示的に親テーブルを切り詰める（作り直す）必要があります。

以下はordersテーブルをパーティション化している例ですが、VACUUMによってordersテーブルのサイズがゼロになっていることを確認してください。 
dbt3=# \d+
                        List of relations
 Schema |      Name       | Type  | Owner |  Size   | Description
--------+-----------------+-------+-------+---------+-------------
 public | orders          | table | snaga | 1964 MB |
 public | orders_1992     | table | snaga | 299 MB  |
 public | orders_1993     | table | snaga | 298 MB  |
(...snip...)

dbt3=# VACUUM orders;
VACUUM
dbt3=# \d+
                        List of relations
 Schema |      Name       | Type  | Owner |  Size   | Description
--------+-----------------+-------+-------+---------+-------------
 public | orders          | table | snaga | 0 bytes |
 public | orders_1992     | table | snaga | 299 MB  |
 public | orders_1993     | table | snaga | 298 MB  |
(...snip...)

dbt3=#

■まとめ


今回は、PostgreSQLのテーブルパーティションの機能をより簡単に使うためのユーティリティを紹介しました。

まだ機能としては必ずしも完璧ではないですが、PostgreSQLのパーティショニングを気軽に試していただくための機能としては一通り揃えてみたつもりです。

世間（の一部？）的にはRDBMSは古いテクノロジーだと思われているようですが、データ分析のプラットフォームとしては、まだまだ可能性を持っていると思います。むしろ、今までのスキルや業務データとの整合性を考えると、PostgreSQLはもっと活用できるはず、と言えるでしょう。

これからは、PostgreSQLをデータ分析のプラットフォームとして今まで以上に活用するノウハウを探っていきたいと思います。

では、また。
Posted by at
Labels: , ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)