PolarDB:パーティションテーブルでの IMCI の使用

ステップ1：環境の準備

1. クラスターのバージョンと設定が次の要件を満たしていることを確認します。

   • クラスターのバージョン：

     Oracle 構文互換性 2.0 (マイナーエンジンバージョン 2.0.14.10.20.0 以降)

     説明

     コンソールで、または SHOW polardb_version; ステートメントを実行してマイナーエンジンバージョンを確認できます。マイナーエンジンバージョンが要件を満たさない場合は、マイナーエンジンバージョンをアップグレードしてください。

   • wal_level パラメーターを logical に設定する必要があります。この設定は、ロジカルデコーディングに必要な情報を先行書き込みログ (WAL) に追加します。

     説明

     コンソールでwal_level パラメーターを設定することができます。このパラメーターを変更するとクラスターが再起動されるため、ビジネスへの影響を考慮し、慎重に操作を行ってください。

   • ソーステーブルにはプライマリキーが必要で、列ストアインデックスの作成時にはそのプライマリキー列を含める必要があります。プライマリキーに SERIAL または BIGSERIAL データ型を使用すると、データ同期の効率が大幅に向上するため、推奨します。

   • 各テーブルに作成できる列ストアインデックスは 1 つだけです。

2. IMCI 機能を有効にします。

   IMCI を有効化する方法は、 または PolarDB for PostgreSQL (Compatible with Oracle) クラスターのマイナーエンジンバージョンによって異なります。

   Oracle 構文互換性2.0 (2.0.14.17.35.0 以降)

   これらのバージョンの または PolarDB for PostgreSQL (Compatible with Oracle) クラスターでは、2 つの方法が利用できます。以下の表に違いを示します。

   比較項目	[推奨] IMCI 読み取り専用ノードの追加	事前インストール済みの列ストアインデックス拡張機能を直接使用
   方法	コンソールで IMCI 読み取り専用ノードを手動で追加できます。	操作は不要です。拡張機能を直接使用できます。
   リソース割り当て	列ストアエンジンは、利用可能なすべてのメモリを含むノードのリソースを専有します。	列ストアエンジンはノードメモリの 25% に制限されます。残りのメモリはローストアエンジンに割り当てられます。
   業務への影響	トランザクション処理 (TP) と分析処理 (AP) のワークロードは異なるノードに分離され、互いに影響しません。	TP と AP のワークロードは同じノード上で実行され、互いに影響する可能性があります。
   コスト	IMCI 読み取り専用ノードには追加料金がかかり、通常のコンピューティングノードと同じ料金で課金されます。	追加コストはかかりません。

   IMCI 読み取り専用ノードの追加

   IMCI 読み取り専用ノードを追加するには、2 つの方法があります。

   説明

   クラスターには少なくとも 1 つの読み取り専用ノードが必要です。単一ノードクラスターに IMCI 読み取り専用ノードを追加することはできません。

   コンソール

   1. PolarDB コンソールにログインし、クラスターのリージョンを選択します。 次のいずれかの方法でノードの追加/削除 ウィザードを開くことができます。

      • クラスター ページで、操作 列の ノードの追加/削除 をクリックします。

      • 対象のクラスターの 概要 ページで、データベースノード セクションの ノードの追加/削除 をクリックします。

   2. 列ストアインデックス読み取り専用ノードの追加 を選択し、OK をクリックします。

   3. クラスターのアップグレード/ダウングレードページで、IMCI 読み取り専用ノードを追加し、支払いを完了します。

      1. 1 つの列ストアインデックス読み取り専用ノードの追加 をクリックし、ノード仕様を選択します。

      2. 切り替え時間を選択します。

      3. (オプション) 製品利用規約とサービスレベルアグリーメント (SLA) を確認します。

      4. 今すぐ購入 をクリックします。

   4. 支払いが完了したら、クラスター詳細ページに戻り、IMCI 読み取り専用ノードが追加されるのを待ちます。ノードは、そのステータスが実行中に変わると使用可能になります。

   購入時

   PolarDB 購入ページで、ノード数 セクションで、[IMCI 読み取り専用ノード] の数を指定します。

   Oracle 構文互換性2.0 (2.0.14.10.20.0 から 2.0.14.17.35.0 未満)

   これらのバージョンの または PolarDB for PostgreSQL (Compatible with Oracle) クラスターでは、IMCI 機能は polar_csi 拡張機能として提供されています。IMCI を使用するには、まず対象のデータベースに拡張機能を作成する必要があります。

   説明

   • polar_csi 拡張機能はデータベースレベルでスコープされます。クラスター内の複数のデータベースで IMCI を使用するには、各データベースに polar_csi 拡張機能を作成する必要があります。

   • 拡張機能のインストールに使用するデータベースアカウントは、特権アカウントである必要があります。

   polar_csi 拡張機能をインストールするには、2 つの方法があります。

   コンソール

   1. PolarDB コンソールにログインし、左側のナビゲーションペインで クラスター をクリックします。次に、クラスターが配置されている[リージョン]を選択し、クラスター ID をクリックしてクラスター詳細ページを開きます。

   2. 左側のナビゲーションペインで、設定と管理 > プラグインの管理 を選択します。プラグインの管理 タブで、プラグインがインストールされていません を選択します。

   3. ページの右上隅で、ターゲットデータベースを選択します。 polar_csi 拡張機能の行で、操作列の インストール をクリックします。 プラグインのインストール ダイアログボックスで、ターゲットの データベースアカウント を選択し、確認 をクリックしてターゲットデータベースに拡張機能をインストールします。

   CLI

   データベースクラスターに接続し、polar_csi 拡張機能を作成するための十分な権限があるデータベースで、次のステートメントを実行します。

   CREATE EXTENSION polar_csi;

ステップ2：データの準備

この例では、多段パーティション化テーブルを作成し、約 3億2000万行 (~16 GB) のシミュレーションデータを挿入し、パーティション条件に基づいて統計分析を実行します。

テスト用のパーティションテーブルのスキーマは次のとおりです。

• sales: プライマリテーブル。

• sales_2023：年別にパーティション分割されています。

  • sales_2023_a: 月別にパーティション化され、1 月から 6 月がパーティション a として定義されています。

  • sales_2023_b: 月単位でパーティション分割されており、7 月～12 月がパーティション b として定義されています。

• sales_2024: 年別にパーティション化されています。

  • sales_2024_a: 月単位でパーティション化され、1月から6月がパーティション a として定義されます。

  • sales_2024_b は、月別にパーティション分割されており、7月から12月がパーティション b として定義されています。

1. 時間列 sale_date をパーティションキーとして、sales という名前の多階層パーティションテーブルを作成します。定義は次のとおりです。

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. データを生成し、パーティションテーブルに挿入します (約 16 GB)。

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. テーブルに、sale_id、product_id、sale_date、および amount 列を含む IMCI を作成します。

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

ステップ3：クエリの実行

異なる実行エンジンを使用してクエリを実行します。異なるパーティション条件に基づいて 3 つのクエリ (Q1、Q2、Q3) が生成されます。

• IMCI を使用します。

  --- IMCIを有効にし、クエリの並列度を4に設定します。
  SET polar_csi.enable_query to on;
  SET polar_csi.exec_parallel to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

• IMCI を無効にし、ローストアエンジンを使用します。

  --- IMCIを無効にし、ローストアエンジンを使用し、クエリの並列度を4に設定します。
  SET polar_csi.enable_query to off;
  SET max_parallel_workers_per_gather to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale-date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;