パーティションテーブルとは何か、そのシナリオと利点 - PolarDB

このトピックでは、PolarDB for PostgreSQL クラスターにおけるパーティションテーブルの特徴と利点の概要を説明します。

概要

PolarDB for PostgreSQL クラスターでは、テーブルまたはインデックスをパーティションと呼ばれるより小さな部分に物理的に分割して、管理を容易にすることができます。各パーティションは、独自の名前とストレージプロパティを持つオブジェクトです。データベース管理者にとって、パーティションはまとめて管理することも、個別に管理することもでき、作業の柔軟性が高まります。アプリケーションにとって、パーティションテーブルは非パーティションテーブルと違いはありません。パーティションテーブルをクエリする際に、SQL 文や DML コマンドを変更する必要はありません。

パーティションテーブルのパーティションは、列名、データ型、制約などの同じ論理プロパティを持つ必要がありますが、圧縮ステータス、物理ストレージ設定、表領域などの異なる物理プロパティを持つことができます。

パーティショニングは、多くの種類のアプリケーション、特に大量のデータボリュームを扱うアプリケーションに利点をもたらします。オンライントランザクション処理 (OLTP) データベースの場合、パーティショニングは管理性と可用性を向上させます。オンライン分析処理 (OLAP) データベースの場合、主な利点はパフォーマンスと管理性にあります。

シナリオ

テーブルが非常に大きい場合、たとえばデータベースサーバーの物理メモリよりも大きい場合、テーブルをパーティション分割してデータベースのパフォーマンスを向上させることができます。たとえば、テーブルが 2 TB を超える場合は、パーティショニングの使用を検討してください。
大規模なテーブルに履歴データが格納され、新しいデータが最新のパーティションに追加される場合に、パーティションテーブルを使用できます。たとえば、大規模なテーブルには 12 か月分の履歴データが格納される場合があります。当月のデータは、更新可能な別のパーティションに格納されます。前月までのデータは、別の読み取り専用パーティションに格納されます。

利点

パフォーマンスの向上
パーティショニングは、特に最も頻繁にアクセスされる行を 1 つまたは少数のパーティションに限定できる場合に、クエリのパフォーマンスを大幅に向上させることができます。パーティショニングは、インデックスツリーの上位レベルを置き換えます。クエリが 1 つまたは少数の特定のパーティションに関係する場合、データベースシステムはインデックスに依存する代わりに、それらのパーティションのみでシーケンシャルスキャンを実行できます。これにより、システムはテーブル全体に散在するレコードではなく、隣接したデータチャンクを処理するため、パフォーマンスと管理性が向上します。
管理の容易化
スタンドアロンオブジェクトとして、パーティションは個別にまたはまとめて管理でき、DDL 操作はテーブルまたはインデックス全体ではなく、パーティションに対して実行できます。したがって、インデックスやテーブルの再構築などのリソースを大量に消費するタスクを分割できます。一度に 1 つのパーティションを削除できます。問題が発生した場合、テーブル全体ではなく、関連するパーティションのみを移動する必要があります。また、パーティションを単位としてデータレコードに対してバッチ操作を実行することもできます。たとえば、テーブルからデータを削除する場合、DROP TABLE を使用して関連するパーティションを削除するか、ALTER TABLE DETACH PARTITION を使用して親テーブルからパーティションを削除するだけで済みます。これらの操作の後、ストレージ領域を再利用するために VACUUM 操作は必要ありません。これは、バッチ削除に対するもう 1 つの利点です。
リソース競合の削減
一部の OLTP データベースでは、パーティショニングにより、複数のパーティションで DML 文が実行される場合などのリソースの競合を減らすことができます。
可用性の向上
パーティションが利用できなくなっても、パーティションテーブルの残りの部分には引き続きアクセスできます。クエリオプティマイザーは、クエリへの影響を避けるために、利用できないパーティションをクエリプランから自動的に削除します。
ストレージコストの削減
アクセス頻度の低いパーティションを、より低速で安価なストレージメディアにダンプして、コストを削減できます。

上記の利点は、パーティションテーブルのサイズが大きい場合にのみ有効であることに注意してください。テーブルのサイズがデータベースサーバーの物理メモリのサイズに達したときに、テーブルをパーティション分割することをお勧めします。

仕組み

パーティショニングは構造に複雑さをもたらしますが、これはユーザーに対して透過的です。このセクションでは、パーティショニングの特徴とメカニズムについて説明します。この情報を理解することは、パーティションテーブルを効果的に使用するのに役立ちます。

例 1

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

パーティションキー

パーティションキーは、各行がどのパーティションに分散されるかを決定する単一の列または複数の列の組み合わせです。PolarDB for PostgreSQL は、パーティションキーに基づいて、挿入、更新、削除などの操作を関連するパーティションに自動的にルーティングします。

例 1 では、logdate は measurement という名前のパーティションテーブルのパーティションキーです。テーブル measurement のパーティションの境界は、logdate の値によって決定されます。

パーティショニングメソッド

PolarDB for PostgreSQL は、複数のパーティショニングメソッドをサポートしています。

レンジパーティショニング
レンジパーティショニングは、日付範囲やビジネスの識別子範囲など、パーティションキーの値の範囲に基づいてテーブルをパーティション分割します。各範囲の境界は、下限を含み、上限を含みません。たとえば、最初のパーティションの値の範囲が 1 から 10 で、2 番目のパーティションの値の範囲が 10 から 20 の場合、10 は最初のパーティションではなく、2 番目のパーティションに属します。例 1 の measurement テーブルは、レンジパーティションテーブルです。
インターバルレンジパーティショニングは、レンジパーティショニングから拡張されたパーティショニングメソッドです。詳細については、「インターバルレンジパーティショニング」をご参照ください。
リストパーティショニング
例 2
```
CREATE TABLE department(deptno INT4 Primary Key,dname VARCHAR(50), location VARCHAR(100)) PARTITION BY LIST (deptno);
CREATE TABLE department_p1 partition of department for values in (10, 20);
CREATE TABLE department_p1 partition of department for values in (30, 40);
```
リストパーティショニングは、離散値のリストに基づいてテーブルをパーティション分割します。例 2 の department テーブルは、リストパーティションテーブルです。テーブルの 2 つのパーティションは、パーティションキーの特定の値のみを含むように明示的に定義されています。たとえば、department_p1 パーティションには deptno の値が 10 または 20 の行のみが格納され、department_p2 パーティションには deptno の値が 30 または 40 の行のみが格納されます。
ハッシュパーティショニング
ハッシュパーティショニングでは、ハッシュ関数を使用してパーティションキーのハッシュ値を計算します。次に、このハッシュ値を指定されたモジュラスで除算し、この除算の余りに基づいて行をパーティションに割り当てます。
例 3
```
create table idxpart (i int) partition by hash (i);
create table idxpart0 partition of idxpart for values with (modulus 2, remainder 0);
create table idxpart1 partition of idxpart for values with (modulus 2, remainder 1);
```
この例では、idxpart テーブルはハッシュパーティション分割されています。idxpart0 パーティションには、i のハッシュ値を 2 で割った余りが 0 の行のみが格納されます。idxpart1 パーティションには、i のハッシュ値を 2 で割った余りが 1 の行のみが格納されます。

多階層パーティショニング

多階層パーティショニングとは、すでにパーティション分割されたテーブルをさらにパーティション分割することです。

PolarDB for PostgreSQL は、無制限の数のパーティショニングレベルをサポートしていますが、3 つ以上のレベルを作成しないことをお勧めします。パーティショニングレベルが多すぎると、パーティションの管理性が損なわれ、クエリのパフォーマンスに悪影響を及ぼす可能性があります。

異なるパーティショニングレベルで異なるパーティショニングメソッドを使用できます。たとえば、最初のレベルにレンジパーティショニング、2 番目のレベルにハッシュパーティショニング、3 番目のレベルにリストパーティショニングを使用できます。

例 4

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') PARTITION BY Hash (city_id);

CREATE TABLE measurement_y2006m03_hash1 PARTITION OF measurement_y2006m03
    for values with (modulus 2, remainder 0) PARTITION BY List (peaktemp);

CREATE TABLE measurement_y2006m03_hash1_l1 PARTITION OF measurement_y2006m03_hash1 for values in (10, 20);

構文

パーティションの作成、追加、マージ、分割、削除など、パーティションを管理するためのステートメントについては、「パーティションテーブルのコマンドリスト」をご参照ください。