パーティションとは何ですか。その使用シナリオと利点 - PolarDB

このトピックでは、 PolarDB for PostgreSQL (Compatible with Oracle) のパーティションテーブルの機能と利点の概要について説明します。

概要

PolarDB for PostgreSQL (Compatible with Oracle) でテーブルをパーティション分割することで、テーブルまたはインデックスをパーティションと呼ばれる小さな部分に物理的に分離し、管理を容易にします。各パーティションは、名前とストレージプロパティを持つオブジェクトです。データベース管理者にとって、パーティションはまとめてまたは個別に管理でき、作業の柔軟性が大幅に向上します。しかしながら、アプリケーションは、テーブルの分割を認識していない。既存のクエリステートメントとDMLコマンドは、テーブル分割のために変更する必要はありません。

パーティションテーブルのパーティションは、列名、データ型、制約などの論理プロパティの点で同じですが、圧縮、物理ストレージの構成、テーブルスペースなど、異なる物理プロパティを持つことができます。

パーティショニングは、多くのタイプのアプリケーション、特に大量のデータを必要とするアプリケーションにメリットをもたらします。 OLTPデータベースの場合、パーティション分割は管理性と可用性を向上させますが、OLAPデータベースの場合、主な利点はパフォーマンスと管理性にあります。

シナリオ

テーブルスペースがデータベースサーバーの物理メモリよりも大きい場合など、テーブルが大きすぎる場合にパーティションを作成します。これにより、データベースのパフォーマンスが向上します。たとえば、テーブルのサイズが2 GBを超える場合は、テーブルをパーティション分割することができます。
履歴データを格納し、新しいデータを受け取ることが期待されるテーブルを分割します。たとえば、1年のデータを格納するために大きなテーブルが使用されます。最新の月のデータを別の更新可能なパーティションに保存し、前の月のデータを他の読み取り専用パーティションに保存できます。

メリット

より高いパフォーマンス
特に、最も頻繁にアクセスされる行が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;

パーティションキー

パーティションキーは、単一の列または複数の列の組み合わせです。これは、データレコードが属するパーティションを決定するために使用されます。パーティションテーブルの場合、各行を1つのパーティションに明示的に割り当てる必要があります。 PolarDB for PostgreSQL (Compatible with Oracle) は、挿入、更新、削除などのデータレコードに対する操作を、パーティションキーに基づいて関連するパーティションに自動的にルーティングします。

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

パーティション分割戦略

PolarDB for PostgreSQL (Oracle互換) は、複数のパーティション分割戦略をサポートしています。

範囲分割
データは、日付やビジネスの特定の識別子など、パーティショニングキーの値の範囲に基づいて分散されます。各パーティションは、包括的でない上限を有する。たとえば、あるパーティションのパーティションキー値が1から10であり、別のパーティションのパーティションキー値が10から20である場合、10は最初のパーティションではなく2番目のパーティションに属します。実施例1における測定テーブルは、範囲分割テーブルである。
間隔範囲分割は、範囲分割から拡張された分割戦略です。詳細については、「間隔範囲のパーティション分割」をご参照ください。
リスト分割
例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テーブルは、リスト分割テーブルです。パーティションで許可されるパーティションキー値は、両方とも明示的に指定されます。たとえば、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 (Compatible with Oracle) は無制限のパーティショニングレベルをサポートしていますが、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);

構文

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