テーブルパーティショニングを使って実現するパフォーマンス向上

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

今回は、PostgreSQLにおけるテーブルパーティショニングの機能を取り上げます。

BigDataやデータ分析といったキーワードが多く聞かれるようになってきましたが、PostgreSQLでも大きなデータに対する集計処理のパフォーマンスを向上させるための機能が提供されています。

■「テーブルパーティショニング」

RDBMSにおける「テーブルパーティショニング」と呼ばれる機能には、ディスクへのアクセスを「局所化させる」、または「分散させる」ために提供されている機能です。これらの機能を使うことによって、大規模なデータを対象とした集計系の処理のパフォーマンスを向上させることができます。

RDBMSにおけるパーティションの種類には大きく分けて3つの種類があります。（正確には、2つとそのコンビネーションですが）

・レンジパーティショニング

レンジパーティショニングというのは、連続的な値（と連続的な意味）を持つカラム（日付など）をパーティションキー（分割のキー）として指定することで、テーブルスキャンなどのアクセスを「局所化」させることを目的としたパーティショニングです。「垂直方向のパーティショニング」と呼ばれることもあります。

・ハッシュパーティショニング

ハッシュパーティショニングは、パーティションキーにするカラム（多くは主キー）のハッシュ値によって分割する方法です。こちらは、アクセスを「分散」させることを目的としたパーティショニングです。「水平方向のパーティショニング」と呼ばれることもあります。

・コンポジットパーティショニング

レンジパーティションとハッシュパーティショニングの組合せです。データへのアクセスを分散させつつ、局所化することができます。

なお、PostgreSQL（単体）で利用できるのは、上記のうち「レンジパーティショニング」になります。

■PostgreSQLのテーブルパーティショニング（Constraint Exclusion）

PostgreSQLにおけるテーブルパーティショニングは、「Constraint Exclusion」という機能を使って実現されます。

PostgreSQLは、「オブジェクトリレーショナルデータベース」と呼ばれる通り、PostgreSQL内部で使われるオブジェクトの高い拡張性と柔軟性を提供していますが、その「オブジェクトリレーショナルデータベース」としてのPostgreSQLの特徴のひとつに「テーブルの継承」という機能があります。

PostgreSQL 9.0.4文書：継承
http://www.postgresql.jp/document/9.0/html/ddl-inherit.html

これは、似たようなテーブルを定義する際、親テーブルの定義をベースにして、新たなカラムを追加するなどして「子テーブル（派生テーブル）」を作成できるというもので、オブジェクト指向プログラミングにおける「クラスの継承」の概念に似たものです。

この「派生テーブル」の機能を使って、派生テーブルを複数作成、その中にデータを分割して配置し、クエリを実行する際には必要な派生テーブル（これがパーティションとなる）だけを対象にクエリを実行する、というのがPostgreSQLにおけるテーブルパーティショニングです。

この時、対象となる派生テーブルだけをクエリの処理対象とする必要がありますが、これが「Constraint Exclusion」、日本語で「制約による排他」と呼ばれる機能です。

制約（通常はCHECK制約）を定義することによって、「どのパーティションにどのようなデータ（どのようなCHECK制約を満たすデータ）が入っているか」をオプティマイザが判断することが可能になるのです。

■パーティションの作成

それでは、実際にテーブルのパーティション化を行ってみましょう。

ここでは、以下のような「注文テーブル」を対象として、注文日（o_orderdate）をパーティションキーとしてパーティション化してみます。

CREATE TABLE orders (
        o_orderkey INTEGER PRIMARY KEY,
        o_custkey INTEGER,
        o_orderstatus CHAR(1),
        o_totalprice REAL,
        o_orderdate DATE,
        o_orderpriority CHAR(15),
        o_clerk CHAR(15),
        o_shippriority INTEGER,
        o_comment VARCHAR(79)
);

まず、通常のテーブル orders を作成し、インデックスも作成します。

testdb=# CREATE TABLE orders (
testdb(#         o_orderkey INTEGER PRIMARY KEY,
testdb(#         o_custkey INTEGER,
testdb(#         o_orderstatus CHAR(1),
testdb(#         o_totalprice REAL,
testdb(#         o_orderDATE DATE,
testdb(#         o_orderpriority CHAR(15),
testdb(#         o_clerk CHAR(15),
testdb(#         o_shippriority INTEGER,
testdb(#         o_comment VARCHAR(79)
testdb(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "orders_pkey" for table "orders"
CREATE TABLE
testdb=# CREATE INDEX orders_o_orderdate_idx ON orders(o_orderdate);
CREATE INDEX
testdb=# \d orders
                Table "public.orders"
     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 o_orderkey      | integer               | not null
 o_custkey       | integer               |
 o_orderstatus   | character(1)          |
 o_totalprice    | real                  |
 o_orderdate     | date                  |
 o_orderpriority | character(15)         |
 o_clerk         | character(15)         |
 o_shippriority  | integer               |
 o_comment       | character varying(79) |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
    "orders_o_orderdate_idx" btree (o_orderdate)

testdb=#

次に、このordersテーブルを親テーブルとして、これを継承して子テーブルを作成します。

ここでは、注文日（o_orderdate）をパーティションキーとして分割しますので、1992年の注文情報を保持するパーティション orders_1992 を作成してみます。

派生してパーティションを作成するには、CREATE TABLE文でINHERITSオプションを使います。

testdb=# CREATE TABLE orders_1992 () INHERITS (orders);
CREATE TABLE
testdb=# \d
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | orders      | table | postgres
 public | orders_1992 | table | postgres
(2 rows)

testdb=# \d orders_1992
             Table "public.orders_1992"
     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 o_orderkey      | integer               | not null
 o_custkey       | integer               |
 o_orderstatus   | character(1)          |
 o_totalprice    | real                  |
 o_orderdate     | date                  |
 o_orderpriority | character(15)         |
 o_clerk         | character(15)         |
 o_shippriority  | integer               |
 o_comment       | character varying(79) |
Inherits: orders

testdb=#

子テーブル（orders_1992）が作成され、「Inherits: orders」とあるようにordersテーブルからの派生テーブルであることも確認できましたが、テーブルを派生して作成しただけでは、主キー制約やインデックスなどは作成されませんので、必要に応じてこれらを追加で作成します。

testdb=# ALTER TABLE orders_1992 ADD PRIMARY KEY (o_orderkey);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_1992_pkey" for table "orders_1992"
ALTER TABLE
testdb=# 

派生テーブルを作成し、主キー制約や必要なインデックスを作成したら、最後にCHECK制約を作成します。

testdb=# ALTER TABLE orders_1992 ADD CHECK(o_orderdate >= '1992-01-01' AND o_orderdate < '1993-01-01');
ALTER TABLE
testdb=# \d orders_1992
             Table "public.orders_1992"
     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 o_orderkey      | integer               | not null
 o_custkey       | integer               |
 o_orderstatus   | character(1)          |
 o_totalprice    | real                  |
 o_orderdate     | date                  |
 o_orderpriority | character(15)         |
 o_clerk         | character(15)         |
 o_shippriority  | integer               |
 o_comment       | character varying(79) |
Indexes:
    "orders_1992_pkey" PRIMARY KEY, btree (o_orderkey)
Check constraints:
    "orders_1992_o_orderdate_check" CHECK (o_orderdate >= '1992-01-01'::date AND o_orderdate < '1993-01-01'::date)
Inherits: orders2

testdb=#

このCHECK制約を作成することで、このorder_1992テーブル（パーティション）には、注文日が1992年のデータのみが含まれているということをPostgreSQLのオプティマイザが知ることができるようになります。

上記の処理を他の年についても繰り返し、必要なパーティションすべてを作成したのが以下の状態です。

testdb=# \d
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | orders      | table | postgres
 public | orders_1992 | table | postgres
 public | orders_1993 | table | postgres
 public | orders_1994 | table | postgres
 public | orders_1995 | table | postgres
 public | orders_1996 | table | postgres
 public | orders_1997 | table | postgres
 public | orders_1998 | table | postgres
(8 rows)

testdb=#

■パーティションへのデータのローディング

パーティションへのデータのローディング方法は、大きく2種類あります。

• 親テーブルにINSERTトリガを設定し、親テーブルへのINSERTを適切なパーティション（子テーブル）に自動的に振り分ける方法
• パーティションを直接指定してデータをローディングする方法

です。

UPDATEやDELETEについても同じことが言えますので、状況に応じて選択すると良いでしょう。ある程度事前にデータを分割できるのであれば、パーティションに直接ロードしても良いかもしれません。（簡単ですし）

■パーティションテーブルに対する実行プラン

それでは、実際に通常のテーブルとパーティションテーブルで、処理やパフォーマンスがどのように異なってくるのかを見てみます。

まずは、条件無しでテーブルフルスキャンを実施し、すべての注文金額を合計する処理の実行プランを見てみます。クエリとしては以下のようになります。

SELECT sum(o_totalprice) FROM orders;

以下は通常のテーブルに対するクエリの実行プランです。

testdb=# EXPLAIN
SELECT sum(o_totalprice) FROM orders;
                              QUERY PLAN
----------------------------------------------------------------------
 Aggregate  (cost=44076.00..44076.01 rows=1 width=4)
   ->  Seq Scan on orders  (cost=0.00..40326.00 rows=1500000 width=4)
(2 rows)

testdb=#

次にパーティションテーブルに対して同じクエリを実行してみます。

testdb=# EXPLAIN SELECT sum(o_totalprice) FROM orders2;
                                      QUERY PLAN

--------------------------------------------------------------------------------
-------
 Aggregate  (cost=44079.01..44079.02 rows=1 width=4)
   ->  Append  (cost=0.00..40329.00 rows=1500001 width=4)
         ->  Seq Scan on orders2  (cost=0.00..0.00 rows=1 width=4)
         ->  Seq Scan on orders_1992 orders2  (cost=0.00..6104.89 rows=227089 width=4)
         ->  Seq Scan on orders_1993 orders2  (cost=0.00..6093.45 rows=226645 width=4)
         ->  Seq Scan on orders_1994 orders2  (cost=0.00..6117.97 rows=227597 width=4)
         ->  Seq Scan on orders_1995 orders2  (cost=0.00..6146.37 rows=228637 width=4)
         ->  Seq Scan on orders_1996 orders2  (cost=0.00..6148.26 rows=228626 width=4)
         ->  Seq Scan on orders_1997 orders2  (cost=0.00..6124.83 rows=227783 width=4)
         ->  Seq Scan on orders_1998 orders2  (cost=0.00..3593.23 rows=133623 width=4)
(10 rows)

testdb=# 

パーティションテーブルに対するスキャンでは、orders_1992からorders_1998まで、すべてのパーティションをスキャンしているため、結果として通常のテーブルをスキャンするコスト（44076.01）とほとんど同じ実行コスト（44079.02）になっていることが分かります。

これは、パーティション化されたテーブルであっても、一部のパーティションだけではなくすべてのパーティションをスキャンすれば、総実行コストは同じになる、ということを意味しています。

■テーブルパーティショニングによるパフォーマンス向上

では、次に条件を追加して集計処理をしてみます。例えば、1993年9月の注文金額だけを合計してみます。クエリとしては以下のようになります。

SELECT sum(o_totalprice) FROM orders
 WHERE o_orderdate >= '1993-09-01'
   AND o_orderdate <= '1993-09-30';

上記のクエリを通常のテーブルに対してEXPLAINしたものが以下です。

testdb=# EXPLAIN SELECT sum(o_totalprice) FROM orders
 WHERE o_orderdate >= '1993-09-01'
   AND o_orderdate <= '1993-09-30';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=33690.55..33690.56 rows=1 width=4)
   ->  Seq Scan on orders  (cost=0.00..33683.58 rows=2786 width=4)
         Filter: ((o_orderdate >= '1993-09-01'::date) AND (o_orderdate <= '1993-09-30'::date))
(3 rows)

testdb=# 

実行プランとしては、ordersテーブルに対するフルスキャンであり、実行コストは「33690.56」と見積もられています。

一方で、パーティションテーブルに対してEXPLAINした結果が以下です。

testdb=# EXPLAIN SELECT sum(o_totalprice) FROM orders
 WHERE o_orderdate >= '1993-09-01'
   AND o_orderdate <= '1993-09-30';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Aggregate  (cost=7270.49..7270.50 rows=1 width=4)
   ->  Append  (cost=0.00..7226.67 rows=17525 width=4)
         ->  Seq Scan on orders  (cost=0.00..0.00 rows=1 width=4)
               Filter: ((o_orderdate >= '1993-09-01'::date) AND (o_orderdate <= '1993-09-30'::date))
         ->  Seq Scan on orders_1993 orders  (cost=0.00..7226.67 rows=17524 width=4)
               Filter: ((o_orderdate >= '1993-09-01'::date) AND (o_orderdate <= '1993-09-30'::date))
(6 rows)

testdb=#

実行プランとしては、orders_1993パーティションに対するスキャンであり、実行コストは「7270.50」と見積もられており、通常のテーブルに比べて、実行コストが1/5程度に低減していることが分かります。

これは、スキャンをする対象がテーブル全体（全パーティション）ではなく、一部のパーティションに局所化することができたため、実行コストが小さくなっていることを意味しています。

このクエリを、通常のテーブルとパーティションテーブルそれぞれに実行した結果が以下になります。

以下は通常のテーブルに対する実行結果です。

testdb=# SELECT sum(o_totalprice) FROM orders
 WHERE o_orderdate >= '1993-09-01'
   AND o_orderdate <= '1993-09-30';
    sum
------------
 2.8482e+09
(1 row)

Time: 405.432 ms
testdb=# 

以下はパーティションテーブルに対する実行結果です。

testdb=# SELECT sum(o_totalprice) FROM orders
 WHERE o_orderdate >= '1993-09-01'
   AND o_orderdate <= '1993-09-30';
    sum
------------
 2.8482e+09
(1 row)

Time: 72.615 ms
testdb=#

通常のテーブルに対して実行すると405ミリ秒、パーティションテーブルに対して実行すると72ミリ秒となっており、実際の実行時間の観点からも集約処理のパフォーマンスを大幅に向上できたことが分かります。

■まとめ

今回は、PostgreSQLで利用できるテーブルパーティショニングの機能について簡単に紹介しました。

まだ少し手間がかかるのが難点ではありますが、PostgreSQLでもテーブルパーティショニングの機能を使うことはできますし、BigDataやデータ分析が大きなトレンドとなりつつある今、こういった機能をうまく使いこなすことも大事になってくるのではないかと思います。

ぜひ、機会を見つけて試してみていただければと思います。

では、また。