PostgreSQLの2種類のパーティショニングについて、AWSのRDSで作成し、ツールで接続してみて対応状況を調べました。

2種類のパーティショニング

• テーブル継承によるパーティショニング
• 宣言的パーティショニング

試したツール

• Tableau
• DBeaver
• AWS Glue Crawler
• AWS Redshift Federated Query

PostgreSQLのバージョンは11.4です。最新でない11.4を選んだのは、試した時点でRedshift Federated Queryが対応していた最新バージョンがそれだったためです。

2種類のパーティショニング

テーブル継承によるパーティショニングは、バージョン8.1からのもので、文字通りテーブルの継承を使って無理やりパーティショニングを実現するイメージです。レコード挿入時のパーティション振り分けは、トリガ関数を手動で定義することで実現します。

宣言的パーティショニングは、バージョン10からのもので、パーティションの振り分けルールを宣言的に書けるので、テーブル継承よりは運用が楽です。

継承による柔軟性を活用したい場合はテーブル継承しかありませんが、そうでない限りは宣言的なパーティショニングのほうがよさそうです。

テーブルのパーティショニング - PostgreSQL 11.5文書

まずはパーティショニングされたテーブルを作成します。

宣言的パーティショニング

テーブル作成手順

1. 親テーブルをCREATE TABLEにPARTITION BY句を付与して作成
2. 子テーブルをCREATE TABLEにPARTITION OF句を付与して作成

create table sample3 (
    timestamp timestamp,
    data text
) partition by range(timestamp);
create index on sample3 (timestamp);

create table sample3_201911 partition of sample3 for values from ('2019-11-01') to ('2019-12-01');
create table sample3_201912 partition of sample3 for values from ('2019-12-01') to ('2020-01-01');
insert into sample3 (timestamp, data) values ('2019-11-16 00:00:00', 'aaa');
insert into sample3 (timestamp, data) values ('2019-12-16 00:00:00', 'bbb');

継承によりパーティショニング

テーブル作成手順（宣言的パーティショニングより手順が多いです）

1. 親テーブルを作成
2. 子テーブルをCREATE TABLEにINHERITS句を付与して作成
3. レコードをパーティションに振り分けるトリガー関数をCREATE FUNCTIONで定義
4. 親テーブルに上記トリガー関数をトリガーとしてCREATE TRIGGERで設定

create table sample4 (
    timestamp timestamp,
    data text
);

create table sample4_201911 (
    check (timestamp >= '2019-11-01' and timestamp < '2019-12-01')
) inherits(sample4);
create index on sample4_201911 (timestamp);
create table sample4_201912 (
    check (timestamp >= '2019-12-01' and timestamp < '2020-01-01')
) inherits(sample4);
create index on sample4_201912 (timestamp);

CREATE OR REPLACE FUNCTION sample4_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timestamp >= DATE '2019-11-01' AND
         NEW.timestamp < DATE '2019-12-01' ) THEN
        INSERT INTO sample4_201911 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-12-01' AND
            NEW.timestamp < DATE '2020-01-01' ) THEN
        INSERT INTO sample4_201912 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the sample4_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_sample4_trigger
    BEFORE INSERT ON sample4
    FOR EACH ROW EXECUTE FUNCTION sample4_insert_trigger();

insert into sample4 (timestamp, data) values ('2019-11-16 00:00:00', 'aaa');
insert into sample4 (timestamp, data) values ('2019-12-16 00:00:00', 'bbb');

Tableau

TableauをPostgreSQLに接続してみます。

Tableauの画面では、Table一覧には宣言的パーティショニング(sample3)の親テーブルが見えず、子テーブルが見えます。継承によるパーティショニング(sample4)は親も子も見えています。

宣言的パーティショニング(sample3)もCustom SQLを書けば親テーブルをデータソースとして指定することは可能でした。

バージョンは
Tableau Desktop Professional Edition 2019.4.0
です。

DBeaver

DBeaverはデータベースに接続してクエリを投げれるデスクトップアプリです。DBeaverをPostgreSQLに接続してみます。

DBeaverでは宣言的パーティショニング(sample3)は親テーブルのみが見えます。継承によるパーティショニング(sample4)は親も子も見えています。

継承はパーティショニング以外の目的でも使われる可能性があると考えると、親と子の両方が出てくるのは合理的で、一方宣言的パーティショニングはパーティショニングだけが目的なので、親が一覧に出てこれば十分なケースが多く、DBeaverのパーティショニングの対応状況はよさそうに見えます。

バージョンは
DBeaver 6.3.0
です。

AWS Glue Crawler

AWSのGlue CrawlerでPostgreSQLに接続してData Catalogを作成してみました。

作成されたData Catalogのテーブルはこちらです。Tableauと同じ状況で、宣言的パーティショニングの親テーブルはクロールできませんでした。

2019/12/21時点です。

Amazon Redshift Federated Query

AWSのRedshiftのFederated QueryはRedshiftに外部スキーマを定義することでPostgreSQLに存在するデータに対してもRedshiftにクエリを投げれるようになる機能です。

Federated QueryとPostgreSQLのパーティショニングの組み合わせを試しましたが、Federated QueryはまだAWSのプレビュー機能でブログ等には書いちゃいけないらしいので、伏せておきます。早く使いたいですね。