テーブルパーティション - PolarDB - Alibaba Cloud ドキュメントセンター

PostgreSQLは、基本的なテーブル分割をサポートします。このセクションでは、パーティション分割をデータベース設計の一部として実装する理由と方法について説明します。

概要

パーティション化とは、論理的に1つの大きなテーブルをより小さな物理的部分に分割することを指します。パーティション分割には、いくつかの点があります。

クエリのパフォーマンスは、特定の状況で、特にテーブルのアクセス頻度の高い行のほとんどが単一のパーティションまたは少数のパーティションにある場合に劇的に向上します。パーティショニングは、インデックスの上位ツリーレベルを効果的に置き換え、インデックスの頻繁に使用される部分がメモリに収まる可能性を高めます。
クエリまたは更新が1つのパーティションの大部分にアクセスする場合、インデックスを使用する代わりに、そのパーティションのシーケンシャルスキャンを使用することでパフォーマンスを向上させることができます。
パーティションの設計で使用パターンが考慮されている場合、パーティションを追加または削除することで、一括ロードと削除を行うことができます。 DROP TABLEを使用して個々のパーティションを削除するか、ALTER TABLE DETACH partitionを実行すると、バルク操作よりもはるかに高速です。これらのコマンドは、バルクDELETEによって引き起こされるVACUUMオーバーヘッドも完全に回避します。
めったに使用されないデータは、より安価で低速のストレージメディアに移行できます。

これらの利点は通常、テーブルが非常に大きい場合にのみ価値があります。テーブルのサイズがデータベースサーバーの物理メモリを超える必要があるという経験則はありますが、テーブルがパーティショニングの恩恵を受ける正確なポイントはアプリケーションによって異なります。

PostgreSQLは、次の形式のパーティション分割の組み込みサポートを提供します。

範囲分割
テーブルは、キー列または列のセットによって定義される「範囲」に分割され、異なるパーティションに割り当てられた値の範囲間で重複はありません。例えば、日付範囲によって、または特定のビジネスオブジェクトの識別子の範囲によって分割することができる。各範囲の境界は、下端では包括的であり、上端では排他的であると理解される。たとえば、1つのパーティションの範囲が1から10であり、次のパーティションの範囲が10から20である場合、値10は最初のパーティションではなく2番目のパーティションに属します。
リスト分割
テーブルは、各パーティションに表示されるキー値を明示的にリストすることによって分割されます。
ハッシュ分割
テーブルは、各パーティションのモジュラスおよび剰余を指定することによってパーティション化される。各パーティションは、指定されたモジュラスで除算されたパーティションキーのハッシュ値が指定された剰余を生成する行を保持します。

アプリケーションで上記以外のパーティション分割を使用する必要がある場合は、代わりに継承やUNION ALLビューなどの代替方法を使用できます。このようなメソッドは柔軟性を提供しますが、組み込みの宣言型パーティショニングのパフォーマンス上の利点の一部はありません。

宣言的分割

PostgreSQLでは、テーブルがパーティションに分割されていることを宣言できます。分割されたテーブルは、パーティションテーブルと呼ばれる。宣言には、前述のパーティショニング方法と、パーティションキーとして使用される列または式のリストが含まれます。

パーティション自体は、パーティション化されたテーブルとして定義され、サブパーティション化をもたらす。すべてのパーティションには、パーティション分割された親と同じ列が必要ですが、パーティションには、他のパーティションとは異なる独自のインデックス、制約、およびデフォルト値がある場合があります。パーティションテーブルとパーティションの作成の詳細については、「CREATE TABLE」をご参照ください。

通常のテーブルをパーティションテーブルに、またはその逆にすることはできません。ただし、既存のレギュラーテーブルまたはパーティションテーブルをパーティションテーブルのパーティションとして追加したり、パーティションテーブルからパーティションを削除してスタンドアロンテーブルにすることは可能です。これにより、多くのメンテナンスプロセスが簡素化され、高速化されます。 ATTACH PARTITIONおよびDETACH PARTITIONサブコマンドの詳細については、ALTER TABLEを参照してください。

個々のパーティションは、舞台裏の継承を使用してパーティション分割テーブルにリンクされます。しかし、以下に説明するように、宣言的に区分されたテーブルまたはその区分で継承の一般的な特徴のすべてを使用することは不可能である。特に、パーティションは、パーティションであるパーティションテーブル以外の親を持つことはできず、テーブルはパーティションテーブルと通常のテーブルの両方から継承することもできません。つまり、パーティションテーブルとそのパーティションは、継承階層を通常のテーブルと共有することはありません。

パーティションテーブルのCHECK制約とNOT NULL制約の両方が、常にすべてのパーティションによって継承されます。INHERITなしとマークされたCHECK制約は、パーティションテーブルに作成できません。
パーティションがない限り、ONLYを使用してパーティションテーブルのみに制約を追加または削除することがサポートされます。パーティションが存在すると、ONLYを使用すると、UNIQUEおよびPRIMARY KEY以外の制約に対してエラーが発生します。代わりに、パーティション自体の制約を追加し、(それらが親テーブルに存在しない場合) 削除することができます。
パーティションテーブルにはデータ自体がないため、パーティションテーブルでTRUNCATE ONLYを使用しようとすると、常にエラーが返されます。
パーティションは、親に存在しない列を持つことはできません。 CREATE TABLEでパーティションを作成するときに列を指定することはできません。また、ALTER TABLEを使用してパーティションに列を事後に追加することもできません。テーブルは、ALTER TABLEを持つパーティションとして追加できます... ATTACH PARTITIONの列が親と完全に一致する場合のみ。
親テーブルに同じ制約が存在する場合、パーティションの列にNOT NULL制約を削除することはできません。

パーティションは外部テーブルであってもよいが、外部テーブルの内容がパーティショニングルールを満たすことはユーザの責任であるため、かなりの注意が必要である。他にもいくつかの制限があります。詳細については、「FOREIGN TABLEの作成」をご参照ください。

行のパーティションキーを更新すると、別の異なるパーティションのパーティション境界に一致し、そのパーティションに移動することができます。

例：

大規模なアイスクリーム会社のデータベースを構築しているとします。同社は、各地域でのアイスクリームの販売だけでなく、毎日ピーク温度を測定しています。概念的には、次のようなテーブルが必要です。

テーブル測定の作成 (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
);

このテーブルの主な用途は管理のためのオンラインレポートを準備することであるため、ほとんどのクエリは先週、月、または四半期のデータにのみアクセスすることを知っています。保存する必要のある古いデータの量を減らすために、最新の3年分のデータのみを保持することにしました。毎月の初めに、最も古い月のデータを削除します。この状況では、分割を使用して、測定テーブルのさまざまな要件をすべて満たすことができます。

この場合、宣言型パーティションを使用するには、次の手順を実行します。

PARTITION by句を指定して、測定テーブルをパーティションテーブルとして作成します。この句には、パーティションメソッド (この場合はRANGE) とパーティションキーとして使用する列のリストが含まれます。
```
テーブル測定の作成 (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
) 範囲による区分 (logdate); 
```
パーティションを作成します。各パーティションの定義は、親のパーティション分割方法とパーティションキーに対応する境界を指定する必要があります。新しいパーティションの値が1つまたは複数の既存のパーティションの値と重複するように境界を指定すると、エラーが発生することに注意してください。
このように作成されたパーティションは、あらゆる方法でnormalPostgreSQLtables (または、場合によっては外部テーブル) です。パーティションごとにテーブルスペースとストレージパラメータを指定することができます。
この例では、各パーティションは、一度に1か月分のデータを削除する要件に一致するように、1か月分のデータを保持する必要があります。したがって、コマンドは次のようになります。
```
CREATE TABLE measurement_y2006m02測定の部分
    ('2006-02-01 ') から ('2006-03-01') までの値

CREATE TABLE measurement_y2006m03測定の部分
    ('2006-03-01 ') から ('2006-04-01') までの値

...
CREATE TABLE measurement_y2007m11測定の一部
    ('2007-11-01 ') から ('2007-12-01') までの値。CREATE TABLE measurement_y2007m12測定の一部
    ('2007-12-01 ') から ('2008-01-01') までの値
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01測定の一部
    ('2008-01-01 ') から ('2008-02-01') までの値
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace; 
```
(範囲の上限は排他的境界として扱われるため、隣接するパーティションは境界値を共有できることを思い出してください。)
サブパーティション分割を実装する場合は、個々のパーティションの作成に使用するコマンドでPARTITION BY句を再度指定します。
```
CREATE TABLE measurement_y2006m02測定の部分
    ('2006-02-01 ') から ('2006-03-01') までの値
    範囲によるパーティー (peaktemp); 
```
measurement_y2006m02のパーティションを作成した後、measurement_y2006m02にマップされるmeasurementに挿入されたデータ (またはパーティション制約が満たされている場合に許可されるmeasurement_y2006m02に直接挿入されたデータ) は、peaktemp列に基づいてパーティションの1つにリダイレクトされます。指定されたパーティション・キーは、親のパーティション・キーと重複する可能性があるが、サブパーティションの境界を指定する際には、サブパーティションが受け入れるデータのセットが、パーティション自体の境界が許可するもののサブセットを構成するように注意を払う必要がある。
既存のパーティションの1つにマップしない親テーブルにデータを挿入すると、エラーが発生します。適切なパーティションを手動で追加する必要があります。
パーティションのパーティション境界条件を記述するテーブル制約を手動で作成する必要はありません。このような制約は自動的に作成されます。
パーティション分割されたテーブルのキー列にインデックスを作成します。 (キーインデックスは厳密には必要ではありませんが、ほとんどのシナリオでは役立ちます。) これにより、各パーティションに一致するインデックスが自動的に作成され、後で作成またはアタッチするパーティションにもそのようなインデックスが作成されます。パーティション化されたテーブル上で宣言されたインデックスまたは一意の制約は、パーティション化されたテーブルと同じように「仮想」です。実際のデータは、個々のパーティションテーブル上の子インデックスにあります。
```
CREATE INDEX ON measurement (logdate);
```
postgresql.confでenable_partition_pruning設定パラメーターが無効になっていないことを確認します。そうである場合、クエリは必要に応じて最適化されません。

上記の例では、毎月新しいパーティションを作成するので、必要なDDLを自動的に生成するスクリプトを記述するのが賢明かもしれません。

制限事項

パーティション分割テーブルには、次の制限が適用されます。

パーティションテーブルに一意または主キー制約を作成するには、パーティションキーに式や関数呼び出しを含めないでください。また、制約の列にはすべてのパーティションキー列が含まれている必要があります。この制限は、制約を構成する個々のインデックスが独自のパーティション内で一意性を直接強制することしかできないために存在します。したがって、パーティション構造自体は、異なるパーティションに重複がないことを保証する必要があります。
パーティション分割テーブル全体にまたがる除外制約を作成する方法はありません。このような制約を各リーフパーティションに個別に置くことのみが可能である。再び、この制限は、クロスパーティション制限を実施することができないことから生じる。
INSERTのBEFORE ROWトリガーは、新しい行の最終宛先であるパーティションを変更できません。
同じパーティションツリーに一時的な関係と永続的な関係を混在させることはできません。したがって、パーティションテーブルが永続的である場合、そのパーティションも永続的である必要があり、パーティションテーブルが一時的である場合も同様です。一時的な関係を使用する場合、パーティションツリーのすべてのメンバーは同じセッションからのものでなければなりません。

継承を使用したパーティション分割

組み込みの宣言型パーティショニングは最も一般的なユースケースに適していますが、より柔軟なアプローチが役立つ場合があります。パーティショニングは、テーブル継承を使用して実装できます。これにより、宣言型パーティショニングではサポートされないいくつかの機能が可能になります。

宣言型パーティション分割の場合、パーティションはパーティション分割されたテーブルとまったく同じ列のセットを持つ必要がありますが、テーブル継承を使用すると、子テーブルは親に存在しない余分な列を持つ可能性があります。
テーブル継承は、多重継承を可能にする。
宣言的分割は、範囲、リスト、およびハッシュ分割のみをサポートしますが、テーブル継承により、ユーザーが選択した方法でデータを分割できます。 (ただし、制約除外が子テーブルを効果的に剪定できない場合、クエリのパフォーマンスが低下する可能性があります。)

例：

すべての「子」テーブルが継承される「ルート」テーブルを作成します。このテーブルにはデータは含まれません。すべての子テーブルに均等に適用する場合を除き、このテーブルにチェック制約を定義しないでください。インデックスや一意の制約を定義することにも意味がありません。この例では、ルートテーブルは最初に定義された測定テーブルです。

テーブル測定の作成 (
        city_id int not null,
        logdate date not null,
        peaktemp int,
        unitsales int
    );

それぞれがルートテーブルから継承するいくつかの「子」テーブルを作成します。通常、これらのテーブルは、ルートから継承されたセットに列を追加しません。宣言型パーティショニングと同様に、これらのテーブルはあらゆる点でnormalPostgreSQLtables (または外部テーブル) です。

CREATE TABLE measurement_y2006m02 () INHERITS (測定);
CREATE TABLE measurement_y2006m03 () INHERITS (測定);
...
CREATE TABLE measurement_y2007m11 () INHERITS (測定);
CREATE TABLE measurement_y2007m12 () INHERITS (測定);
CREATE TABLE measurement_y2008m01 () INHERITS (測定);

重複しないテーブル制約を子テーブルに追加して、それぞれで許可されるキー値を定義します。

典型的な例は次のとおりです。

チェック (x = 1)
チェック (郡IN ( 'Oxfordshire '、'Buckinghamshire' 、'Warwickshire' ))
チェック (outletID >= 100とoutletID < 200 )

制約によって、異なる子テーブルで許可されるキー値の間に重複がないことが保証されていることを確認します。よくある間違いは、次のような範囲の制約を設定することです。

チェック (100と200の間の出力)
チェック (200と300の間の出力)

キー値200がどの子テーブルに属しているのか不明であるため、これは間違っています。代わりに、範囲はこのスタイルで定義する必要があります。

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01 'およびlogdate < DATE '2006-03-01')
) INHERITS (測定);

テーブルmeasurement_y2006m03の作成 (
    CHECK ( logdate >= DATE '2006-03-01 'およびlogdate < DATE '2006-04-01')
) INHERITS (測定);

...
テーブルmeasurement_y2007m11の作成 (
    CHECK ( logdate >= DATE '2007-11-01 'およびlogdate < DATE '2007-12-01')
) INHERITS (測定);

テーブルmeasurement_y2007m12の作成 (
    CHECK ( logdate >= DATE '2007-12-01 'およびlogdate < DATE '2008-01-01')
) INHERITS (測定);

テーブルmeasurement_y2008m01の作成 (
    CHECK ( logdate >= DATE '2008-01-01 'およびlogdate < DATE '2008-02-01')
) INHERITS (測定);

子テーブルごとに、キー列にインデックスを作成し、必要な他のインデックスも作成します。

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

私たちのアプリケーションが言うことができるようにしたい測定に挿入...データを適切な子テーブルにリダイレクトします。適切なトリガー関数をルートテーブルにアタッチすることで、それを調整できます。データが最新の子にのみ追加される場合は、非常に単純なトリガー関数を使用できます。

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
$$としてトリガーを返します
開始
    INSERT INTO measurement_y2008m01値 (NEW.*);
    RETURN NULL;
エンド;
$$
言語plpgsql;

関数を作成した後、トリガー関数を呼び出すトリガーを作成します。

TRIGGERの作成insert_measurement_trigger
    測定を挿入する前に
    各列実行機能measurement_insert_trigger();

トリガー関数を毎月再定義して、常に現在の子テーブルに挿入する必要があります。しかしながら、トリガ定義は更新される必要はない。

データを挿入して、行を追加する子テーブルをサーバーに自動的に見つけることができます。たとえば、より複雑なトリガー関数を使用してこれを行うことができます。

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
$$としてトリガーを返します
開始
    IF ( NEW.logdate >= DATE '2006-02-01 'AND)
         NEW.logdate <日付 '2006-03-01 ')
        INSERT INTO measurement_y2006m02値 (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01 'AND)
            NEW.logdate < DATE '2006-04-01 ')
        INSERT INTO measurement_y2006m03値 (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01 'AND)
            NEW.logdate <日付 '2008-02-01 ')
        INSERT INTO measurement_y2008m01値 (NEW.*);
    ELSE
        RAISE EXCEPTION '範囲外の日付。  measurement_insert_trigger() 関数を修正します! ';
    END IF;
    RETURN NULL;
エンド;
$$
言語plpgsql;

トリガーの定義は以前と同じです。各IFテストは、その子テーブルのCHECK制約と完全に一致する必要があります。

この関数は1か月の場合よりも複雑ですが、必要になる前にブランチを追加できるため、頻繁に更新する必要はありません。

説明

実際には、ほとんどの挿入物がその子に入る場合、最初に最新の子をチェックするのが最善かもしれません。簡単にするために、この例の他の部分と同じ順序でトリガーのテストを示しました。

適切な子テーブルに挿入をリダイレクトする別の方法は、ルートテーブルにトリガーではなくルールを設定することです。設定例：

ルールmeasurement_insert_y2006m02を作成します
測定への挿入について
    (logdate >= DATE '2006-02-01 'およびlogdate < DATE '2006-03-01')
代わりに行う
    INSERT INTO measurement_y2006m02値 (NEW.*);
...
ルールmeasurement_insert_y2008m01を作成します
測定への挿入について
    (logdate >= DATE '2008-01-01 'およびlogdate < DATE '2008-02-01')
代わりに行う
INSERT INTO measurement_y2008m01値 (NEW.*);

ルールはトリガーよりも大幅にオーバーヘッドがありますが、オーバーヘッドは行ごとに1回ではなく、クエリごとに1回支払われるため、この方法は一括挿入の場合に有利な場合があります。ただし、ほとんどの場合、トリガーメソッドはパフォーマンスが向上します。

COPYはルールを無視することに注意してください。 COPYを使用してデータを挿入する場合は、ルートに直接コピーするのではなく、正しい子テーブルにコピーする必要があります。COPYはトリガーを起動するため、トリガーアプローチを使用する場合は通常どおりに使用できます。

ルールアプローチのもう1つの欠点は、ルールのセットが挿入日をカバーしていない場合にエラーを強制する簡単な方法がないことです。

postgresql.confでconstraint_exclusion設定パラメーターが無効になっていないことを確認します。

ご覧のとおり、複雑なテーブル階層にはかなりの量のDDLが必要になる可能性があります。上記の例では、毎月新しい子テーブルを作成するので、必要なDDLを自動的に生成するスクリプトを記述するのが賢明かもしれません。

ノート

継承を使用して実装されるパーティション分割には、次の注意点があります。

CHECK制約のすべてが相互に排他的であることを検証する自動的な方法はありません。子テーブルを生成し、関連付けられたオブジェクトを作成および /または変更するコードを作成する方が、それぞれを手で書くよりも安全です。
インデックスと外部キーの制約は、継承の子ではなく単一のテーブルに適用されるため、注意すべきいくつかの注意点があります。
ここに示されるスキームは、行のキー列の値が決して変化しないか、または少なくとも別のパーティションに移動することを要求するほど十分に変化しないと仮定する。 CHECK制約のために失敗することを試みるUPDATE。このような場合に対処する必要がある場合は、適切な更新トリガーを子テーブルに配置できますが、構造の管理がはるかに複雑になります。
手動のVACUUMまたはANALYZEコマンドを使用している場合は、各子テーブルで個別に実行する必要があることを忘れないでください。次のようなコマンド:

分析測定;

パーティションプルーニングなしでは、上記のクエリは測定テーブルの各パーティションをスキャンします。パーティションプルーニングを有効にすると、プランナーは各パーティションの定義を調べ、クエリのWHERE句を満たす行を含めることができなかったため、パーティションをスキャンする必要がないことを証明します。プランナーがこれを証明できる場合、クエリプランからパーティションを除外 (プルーン) します。

EXPLAINコマンドとenable_partition_pruning設定パラメーターを使用することで、パーティションがプルーニングされたプランとプルーニングされていないプランの違いを示すことができます。このタイプのテーブルセットアップの典型的な最適化されていない計画は次のとおりです。

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01 ';
                                    クエリ計画
------------------------------------------------------------------- ----------------
 集計 (コスト=188.76 .. 188.77行=1幅=8)
   -> 追加 (コスト=0.00 .. 181.05行=3085幅=0)
         -> Seq measurement_y2006m02のスキャン (コスト=0.00 .. 33.12行=617幅=0)
               フィルター :( logdate >= '2008-01-01 '::date)
         -> Seq measurement_y2006m03のスキャン (コスト=0.00 .. 33.12行=617幅=0)
               フィルター :( logdate >= '2008-01-01 '::date)
...
         -> Seq measurement_y2007m11のスキャン (コスト=0.00 .. 33.12行=617幅=0)
               フィルター :( logdate >= '2008-01-01 '::date)
         -> Seq measurement_y2007m12のスキャン (コスト=0.00 .. 33.12行=617幅=0)
               フィルター :( logdate >= '2008-01-01 '::date)
         -> Seq measurement_y2008m01のスキャン (コスト=0.00 .. 33.12行=617幅=0)
               フィルター :( logdate >= '2008-01-01 '::date)

パーティションの一部またはすべてが、フルテーブルのシーケンシャルスキャンの代わりにインデックススキャンを使用する場合がありますが、ここでのポイントは、このクエリに答えるために古いパーティションをスキャンする必要がまったくないことです。パーティションプルーニングを有効にすると、同じ答えを提供する大幅に安価なプランが得られます。

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01 ';
                                    クエリ計画
------------------------------------------------------------------- ----------------
 集計 (コスト=37.75 .. 37.76行=1幅=8)
   -> Seq measurement_y2008m01のスキャン (コスト=0.00 .. 33.12行=617幅=0)
         フィルター :( logdate >= '2008-01-01 '::date)

説明

パーティションプルーニングは、インデックスの存在ではなく、パーティションキーによって暗黙的に定義された制約によってのみ実行されます。したがって、キー列にインデックスを定義する必要はありません。特定のパーティションに対してインデックスを作成する必要があるかどうかは、パーティションをスキャンするクエリが通常、パーティションの大部分をスキャンするか、ごく一部をスキャンするかによって異なります。後者の場合にはインデックスが有用であるが、前者の場合には有用ではない。

パーティションプルーニングは、所与のクエリの計画中だけでなく、その実行中にも実行することができる。これは、PREPAREステートメントで定義されたパラメーター、サブクエリから取得した値の使用、または入れ子になったループ結合の内側のパラメーター化された値の使用など、クエリ計画時に値がわからない式が句に含まれている場合に、より多くのパーティションをプルーニングできるため便利です。実行中のパーティションプルーニングは、次のいずれかのタイミングで実行できます。

クエリプランの初期化中。パーティション・プルーニングは、ここでは、実行の初期化フェーズ中に既知であるパラメータ値に対して実行することができる。この段階でプルーニングされたパーティションは、クエリのEXPLAINまたはEXPLAIN ANALYZEに表示されません。 EXPLAIN出力の「サブプラン削除済み」プロパティを観察することで、このフェーズで削除されたパーティションの数を決定できます。
クエリプランの実際の実行中。パーティションプルーニングは、実際のクエリ実行中にのみ知られる値を使用してパーティションを削除するためにここで実行されてもよい。これには、サブクエリからの値と、パラメータ化されたネストループ結合などの実行時パラメータからの値が含まれます。これらのパラメータの値は、クエリの実行中に何度も変化し得るので、パーティションプルーニングによって使用されている実行パラメータのうちの1つが変化するたびに、パーティションプルーニングが実行される。このフェーズでパーティションがプルーニングされたかどうかを判断するには、EXPLAIN ANALYZE出力のloopsプロパティを注意深く調べる必要があります。異なるパーティションに対応するサブプランは、それぞれが実行中にプルーニングされた回数に応じて、異なる値を有することができる。毎回剪定された場合、いくつかは (決して実行されない) と表示される場合があります。

パーティションプルーニングは、enable_partition_pruning設定を使用して無効にできます。

説明

現在、実行時パーティションのプルーニングは、AppendおよびMergeAppendノードタイプにのみ適用されます。 ModifyTableノードタイプにはまだ実装されていませんが、このデータベースの将来のリリースで変更される可能性があります。

パーティション化と制約の除外

制約除外は、パーティションプルーニングに似たクエリ最適化手法です。主にレガシー継承メソッドを使用して実装されるパーティション分割に使用されますが、宣言型パーティション分割など、他の目的に使用できます。

制約除外は、各テーブルのCHECK制約 (名前を与える) を使用するのに対し、パーティションプルーニングは、宣言型パーティション分割の場合にのみ存在するテーブルのパーティション境界を使用する点を除いて、パーティションプルーニングと非常によく似た方法で機能します。もう1つの違いは、制約除外が計画時にのみ適用されることです。実行時にパーティションを削除する試みはありません。

制約除外がCHECK制約を使用するという事実は、パーティションプルーニングと比較して遅くなるため、利点として使用できる場合があります。内部パーティション境界に加えて、宣言的にパーティション分割されたテーブルでも制約を定義できるため、制約除外できる可能性があります。クエリ計画から追加のパーティションを削除します。

constraint_exclusionのデフォルト (および推奨) 設定はonでもoffでもありませんが、partitionと呼ばれる中間設定であるため、継承パーティションテーブルで作業している可能性が高いクエリにのみ適用されます。 オンの設定により、プランナーはすべてのクエリでCHECK制約を調べます。

制約の除外には、次の注意点が適用されます。

制約除外は、クエリの実行中にも適用できるパーティションプルーニングとは異なり、クエリの計画中にのみ適用されます。
制約除外は、クエリのWHERE句に定数 (または外部から指定されたパラメーター) が含まれている場合にのみ機能します。たとえば、CURRENT_TIMESTAMPなどの非不変関数との比較を最適化することはできません。これは、関数の値が実行時にどの子テーブルに入るかをプランナが知ることができないためです。
分割制約を単純に保つ。そうしないと、プランナーは、子テーブルを訪問する必要がないことを証明できない可能性がある。上記の例で示したように、リスト分割には単純な等価条件を使用し、範囲分割には単純な範囲テストを使用します。 B-tree-indexable列のみがパーティションキーで許可されているため、パーティショニング制約には、B-tree-indexable演算子を使用したパーティショニング列と定数の比較のみを含める必要があります。
親テーブルのすべての子に対するすべての制約は、制約除外中に検査されるので、多数の子は、クエリ計画時間をかなり増加させる可能性がある。したがって、レガシー継承ベースのパーティショニングは、おそらく最大100の子テーブルでうまく機能します。何千もの子を使用しようとしないでください。

宣言分割のベストプラクティス

クエリの計画と実行のパフォーマンスが不十分な設計によって悪影響を受ける可能性があるため、テーブルを分割する方法の選択は慎重に行う必要があります。

最も重要な設計上の決定の1つは、データを分割する列です。多くの場合、最適な選択は、パーティション分割されたテーブルで実行されるクエリのWHERE句に最も一般的に表示される列または列のセットでパーティション分割することです。パーティション制限制約と互換性のあるWHERE句を使用して、不要なパーティションを剪定できます。ただし、PRIMARY KEYまたはUNIQUE制約の要件によって、他の決定を強制される場合があります。不要なデータの削除も、分割戦略を計画する際に考慮すべき要素です。パーティション全体をかなり迅速に分離することができるので、一度に除去されるべきすべてのデータが単一のパーティションに配置されるような方法でパーティション戦略を設計することが有益であり得る。

テーブルを分割するパーティションのターゲット数を選択することも重要な決定です。十分なパーティションがないということは、インデックスが大きくなりすぎ、データの局所性が悪くなり、キャッシュヒット率が低くなる可能性があることを意味します。ただし、テーブルをあまりにも多くのパーティションに分割すると、問題が発生する可能性があります。パーティションが多すぎると、以下でさらに説明するように、クエリ計画時間が長くなり、クエリ計画と実行の両方の間のメモリ消費が多くなる可能性があります。テーブルを分割する方法を選択するときは、将来どのような変更が発生するかを考慮することも重要です。たとえば、顧客ごとに1つのパーティションを選択し、現在少数の大規模顧客がいる場合、数年後に多数の小規模顧客がいることに気付いた場合の影響を考慮します。この場合、LISTによって分割しようとし、顧客の数がデータを分割することが実際的である数を超えて増加しないことを期待するよりも、HASHによって分割することを選択し、妥当な数の分割を選択する方がよい場合がある。

サブパーティショニングは、他のパーティションよりも大きくなることが予想されるパーティションをさらに分割するために有用であり得る。もう1つのオプションは、パーティションキーの複数の列で範囲パーティションを使用することです。これらのいずれも、パーティションの数が過剰になる可能性があるため、抑制をお勧めします。

クエリの計画と実行中にパーティション分割のオーバーヘッドを考慮することが重要です。クエリ・プランナは、一般的なクエリが少数のパーティションを除いてすべてをプルーニングすることを可能にするという条件で、一般的に、数千のパーティションまでのパーティション階層をかなりうまく扱うことができる。プランナがパーティションプルーニングを実行した後、より多くのパーティションが残ると、計画時間が長くなり、メモリ消費量が高くなります。多数のパーティションを有することについて懸念されるもう1つの理由は、特に多数のセッションが多数のパーティションに接触する場合、サーバのメモリ消費が時間とともに著しく増大する可能性があることである。これは、各パーティションに触れる各セッションのローカルメモリにメタデータをロードする必要があるためです。

データウェアハウスタイプのワークロードでは、OLTPタイプのワークロードよりも多数のパーティションを使用することが理にかなっています。一般に、データウェアハウスでは、処理時間の大部分がクエリ実行中に費やされるため、クエリ計画時間はそれほど問題ではありません。これら2つのタイプのワークロードのいずれかでは、大量のデータの再分割が非常に遅くなる可能性があるため、適切な決定を早期に行うことが重要です。意図される作業負荷のシミュレーションは、分割戦略を最適化するためにしばしば有益である。より多くのパーティションがより少ないパーティションよりも優れていると仮定しないでください。