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

環境の準備

1. 要件を満たす PolarDB for PostgreSQL (Compatible with Oracle) クラスター。

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

   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. (オプション) プロダクト利用規約とサービスレベル契約を確認します。

      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;

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 'Operating in schema: %', target_schema;

    -- マテリアライズドビューが存在することを確認します。
    IF NOT EXISTS (
        SELECT 1 FROM pg_matviews
        WHERE matviewname = view_name
        AND schemaname = target_schema
    ) THEN
        RAISE EXCEPTION 'Materialized view "%" does not exist in schema "%"', 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 'Failed to retrieve definition for materialized view "%"', 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 'Query plan for materialized view refresh:';
    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 'Role "%" does not exist', new_owner;
            END IF;

            EXECUTE format('ALTER MATERIALIZED VIEW %s OWNER TO %I', qualified_new_name, new_owner);
            RAISE NOTICE 'Changed owner from "%" to "%"', 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 'Creating index: %', index_creation_sql;
            EXECUTE index_creation_sql;
        END LOOP;

        -- ビューの権限をコピーします。
        RAISE NOTICE 'Restoring permissions to new view %.%', 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 'Failed to refresh materialized view: %', 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)