PolarDB:IMCI を使用したマテリアライズドビューのリフレッシュの高速化

環境の準備

1. 対象の PolarDB for PostgreSQL クラスター。

2. 列ストアインデックス機能を有効にします。

   IMCI を有効にする方法は、ご利用の PolarDB for PostgreSQL クラスターのマイナーエンジンバージョンによって異なります。

   PostgreSQL 16 (2.0.16.9.8.0 以降) または PostgreSQL 14 (2.0.14.17.35.0 以降)

   これらのバージョンの PolarDB for PostgreSQL クラスターでは、2 つの方法が利用できます。次の表にその違いをまとめます。

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

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

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

   説明

   クラスターには少なくとも 1 つの読み取り専用ノードが含まれている必要があります。シングルノードクラスターに IMCI 読み取り専用ノードを追加することはできません。

   コンソール

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

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

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

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

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

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

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

      3. (オプション) プロダクト利用規約とサービスレベル契約を確認します。

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

   4. 支払い完了後、クラスター詳細ページに戻り、IMCI 読み取り専用ノードが追加されるまでお待ちください。ノードのステータスが実行中に変わると、準備が整います。

   購入時

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

   PostgreSQL 16 (2.0.16.8.3.0 から 2.0.16.9.8.0) または PostgreSQL 14 (2.0.14.10.20.0 から 2.0.14.17.35.0)

   これらのバージョンの PolarDB for PostgreSQL クラスターでは、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;

3. ターゲットデータベース (ビジネスデータベース) に、pg_hint_plan 拡張をインストールします。この拡張を使用すると、特別なコメント形式のヒントを使用して、すでに選択されているクエリプランを調整できます。

   CREATE EXTENSION pg_hint_plan;

4. postgres システムデータベースに、pg_cron (定期タスク) 拡張をインストールします。この拡張は、指定した時間または間隔でタスクを自動的に実行します。

   1. データベースに切り替えます。

      \c postgres;

   2. 拡張をインストールします。

      CREATE EXTENSION pg_cron;

データの準備

ターゲットデータベース (ビジネスデータベース) で、customers テーブルと orders テーブルを作成し、それらに列ストアインデックスを構築して、テストデータを挿入します。

1. ターゲットデータベース (ビジネスデータベース) に切り替えます。この例では testdb を使用します。

   \c testdb;

2. テーブルを作成し、データを挿入します。

   -- customers テーブルとその列ストアインデックスを作成します。
   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       customer_name VARCHAR(100),
       email VARCHAR(100)
   );
   CREATE INDEX idx_customers_csi ON customers USING csi;
   
   -- orders テーブルとその列ストアインデックスを作成します。
   CREATE TABLE orders (
       order_id SERIAL PRIMARY KEY,
       order_date DATE,
       amount DECIMAL(10, 2),
       customer_id INT REFERENCES customers(customer_id)
   );
   CREATE INDEX idx_orders_csi ON orders USING csi;
   
   -- customers テーブルにデータを挿入します。
   INSERT INTO customers (customer_name, email) VALUES
   ('Alice', 'alice@example.com'),
   ('Bob', 'bob@example.com'),
   ('Charlie', 'charlie@example.com');
   
   -- orders テーブルにデータを挿入します。
   INSERT INTO orders (order_date, amount, customer_id) VALUES
   ('2025-06-01', 200.00, 1),
   ('2025-06-02', 150.00, 2),
   ('2025-06-03', 300.00, 1),
   ('2025-06-04', 100.00, 3);

リフレッシュ関数の作成

リフレッシュプロセスは関数にラップされます。以下の関数は、インデックスと所有権を保持しながら新しいビューを安全に交換するため、推奨されます。

-- view_name はマテリアライズドビューの名前です。schema_name はビューが存在するスキーマです (デフォルトは current_schema)。new_owner は新しく作成されたビューに割り当てられるオーナーです。
CREATE OR REPLACE FUNCTION refresh_materialized_view_safely_using_csi(
    view_name TEXT,
    schema_name TEXT DEFAULT NULL,
    new_owner TEXT DEFAULT NULL
)
RETURNS BOOL
LANGUAGE plpgsql
AS $$
DECLARE
    view_definition TEXT;
    new_view_name TEXT;
    old_view_name TEXT;
    index_record RECORD;
    index_creation_sql TEXT;
    explain_result TEXT;
    target_schema TEXT;
    qualified_old_name TEXT;
    qualified_new_name TEXT;
    current_owner TEXT;
    grant_record RECORD;
BEGIN
    -- ターゲットスキーマを決定します (入力パラメーターまたは現在のスキーマを使用)。
    IF schema_name IS NULL THEN
        target_schema := current_schema();
    ELSE
        target_schema := schema_name;
    END IF;

    -- 完全修飾テーブル名を構築します。
    qualified_old_name := format('%I.%I', target_schema, view_name);
    qualified_new_name := format('%I.%I', target_schema, view_name || '_new');

    RAISE NOTICE 'スキーマで操作しています: %', target_schema;

    -- マテリアライズドビューが存在することを確認します。
    IF NOT EXISTS (
        SELECT 1 FROM pg_matviews
        WHERE matviewname = view_name
        AND schemaname = target_schema
    ) THEN
        RAISE EXCEPTION 'マテリアライズドビュー "%" はスキーマ "%" に存在しません', view_name, target_schema;
    END IF;

    -- マテリアライズドビューの定義と現在のオーナーを取得します。
    SELECT m.definition, p.rolname INTO view_definition, current_owner
    FROM pg_matviews m
    JOIN pg_class c ON m.matviewname = c.relname AND m.schemaname = target_schema
    JOIN pg_roles p ON c.relowner = p.oid
    WHERE m.matviewname = view_name
    AND m.schemaname = target_schema;

    IF view_definition IS NULL THEN
        RAISE EXCEPTION 'マテリアライズドビュー "%" の定義の取得に失敗しました', view_name;
    END IF;

    -- 古いビューと新しいビューの名前を設定します。
    old_view_name := view_name;
    new_view_name := view_name || '_new';

    -- IMCI パフォーマンスパラメーター。
    SET LOCAL polar_csi.cost_threshold = 0;

    -- クエリプランを出力します。
    RAISE NOTICE 'マテリアライズドビューリフレッシュのクエリプラン:';
    FOR explain_result IN EXECUTE format('/*+ SET(polar_csi.enable_query on) */ EXPLAIN CREATE MATERIALIZED VIEW %s AS %s', qualified_new_name, view_definition) LOOP
        RAISE NOTICE '%', explain_result;
    END LOOP;

    BEGIN
        -- 新しいマテリアライズドビューを作成します。
        EXECUTE format('/*+ SET(polar_csi.enable_query on) */ CREATE MATERIALIZED VIEW %s AS %s', qualified_new_name, view_definition);

        -- 新しいオーナーが指定されている場合は、オーナーを設定します。
        IF new_owner IS NOT NULL THEN
            -- ユーザーが存在することを確認します。
            IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = new_owner) THEN
                RAISE EXCEPTION 'ロール "%" は存在しません', new_owner;
            END IF;

            EXECUTE format('ALTER MATERIALIZED VIEW %s OWNER TO %I', qualified_new_name, new_owner);
            RAISE NOTICE 'オーナーを "%" から "%" に変更しました', current_owner, new_owner;
        END IF;

        -- 古いビューから新しいビューにすべてのインデックスをコピーします。
        FOR index_record IN
            SELECT indexname, indexdef
            FROM pg_indexes
            WHERE tablename = old_view_name
            AND schemaname = target_schema
        LOOP
            -- 古いビュー名を新しいビュー名に置き換えます。
            index_creation_sql := regexp_replace(
                index_record.indexdef,
                ' ON ' || target_schema || '.' || old_view_name || ' ',
                ' ON ' || target_schema || '.' || new_view_name || ' ',
                'i'
            );

            -- UNIQUE インデックスの特殊なケースを処理します。
            index_creation_sql := regexp_replace(
                index_creation_sql,
                'INDEX ' || index_record.indexname || ' ON',
                'INDEX ' || index_record.indexname || '_new ON',
                'i'
            );

            RAISE NOTICE 'インデックスを作成しています: %', index_creation_sql;
            EXECUTE index_creation_sql;
        END LOOP;

        -- ビューの権限をコピーします。
        RAISE NOTICE '新しいビュー %.% に権限を復元しています', target_schema, new_view_name;
        FOR grant_record IN
        SELECT
            (acl).grantee::regrole::text AS grantee,
            (acl).privilege_type
        FROM
            pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        CROSS JOIN aclexplode(c.relacl) AS acl
        WHERE
            n.nspname = target_schema
        AND c.relname = old_view_name
        LOOP
            CONTINUE WHEN grant_record.grantee IS NULL;
            -- RAISE NOTICE 'Granting % ON %.% TO %',
            --     grant_record.privilege_type, target_schema, new_view_name, grant_record.grantee;

            EXECUTE format(
                'GRANT %s ON %I.%I TO %s',
                grant_record.privilege_type,
                target_schema,
                new_view_name,
                quote_ident(grant_record.grantee)
            );
        END LOOP;

        -- 古いマテリアライズドビューを削除します。
        EXECUTE format('DROP MATERIALIZED VIEW %s', qualified_old_name);

        -- 新しいマテリアライズドビューを元の名前に変更します。
        EXECUTE format('ALTER MATERIALIZED VIEW %s RENAME TO %I', qualified_new_name, old_view_name);

        -- インデックスの名前を変更します (_new サフィックスを削除)。
        FOR index_record IN
            SELECT indexname
            FROM pg_indexes
            WHERE tablename = old_view_name
            AND schemaname = target_schema
        LOOP
            IF position('_new' in index_record.indexname) > 0 THEN
                EXECUTE format(
                    'ALTER INDEX %I.%I RENAME TO %I',
                    target_schema,
                    index_record.indexname,
                    replace(index_record.indexname, '_new', '')
                );
            END IF;
        END LOOP;

        RETURN TRUE;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE EXCEPTION 'マテリアライズドビューのリフレッシュに失敗しました: %', SQLERRM;
            RETURN FALSE;
    END;
END;
$$;

パラメーター

リフレッシュの実行

SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders');

SELECT * FROM cron.job;

jobid  |  schedule   |                                           command                                            | nodename | nodeport | database | username | active |          jobname
-------+-------------+----------------------------------------------------------------------------------------------+----------+----------+----------+----------+--------+----------------------------
     1 | */5 * * * * | SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders', 'public', 'polarpg') | /data/.  |     3000 | testdb   | polarpg  | t      | refresh_mv_customer_orders
(1 row)

SELECT cron.unschedule('refresh_my_materialized_view');

SELECT * FROM cron.job_run_details;

 jobid | runid | job_pid | database | username |                                           command                                            |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------------------------------------------------------------------------------------------+-----------+----------------+-------------------------------+-------------------------------
     1 |     1 |   76537 | testdb   | polarpg  | SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders', 'public', 'polarpg') | succeeded | 1 row          | 2025-08-27 08:35:00.007231+00 | 2025-08-27 08:35:00.024946+00
(1 rows)