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 つの方法をサポートしています。次の表に違いを示します。必要に応じて方法を選択できます。

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

   列ストアインデックス読み取り専用ノードの追加

   次のいずれかの方法で、列ストアインデックス読み取り専用ノードを追加できます。

   説明

   クラスターには読み取り専用ノードが含まれている必要があります。単一ノードのクラスターに列ストアインデックス読み取り専用ノードを追加することはできません。

   コンソールでの追加

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

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

        

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

        

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

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

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

      2. スイッチオーバー時間を選択します。

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

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

      

   4. 支払いが完了したら、クラスターの詳細ページに戻り、列ストアインデックス読み取り専用ノードが追加されるまで待ちます。ノードのステータスが [実行中] に変わります。

   購入時の追加

   PolarDB 購入ページで、[ノード数] パラメーターの [列ストアインデックス読み取り専用ノード] の数を選択します。

   

   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 クラスターでは、列ストアインデックスは polar_csi 拡張機能としてデータベースクラスターにデプロイされます。使用する前に、指定したデータベースに拡張機能を作成する必要があります。

   説明

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

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

   次のいずれかの方法で polar_csi 拡張機能をインストールできます。

   コンソールからのインストール

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

   2. 左側のナビゲーションウィンドウで、[設定と管理] > [拡張機能の管理] を選択します。[拡張機能の管理] タブで、[未インストールの拡張機能] を選択します。

   3. ページの右上隅で、ターゲットデータベースを選択します。polar_csi 拡張機能を見つけ、[操作] 列の [インストール] をクリックします。表示される [拡張機能のインストール] ダイアログボックスで、ターゲットの [データベースアカウント] を選択し、[OK] をクリックしてターゲットデータベースに拡張機能をインストールします。

      

   コマンドラインからのインストール

   データベースクラスターに接続し、ターゲットデータベースで必要な権限を持つ次の文を実行して 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 テーブルを作成し、それらに IMCI を作成して、テストデータを挿入します。

1. ビジネスシステムに切り替えます。この例では testdb を使用します。

   \c testdb;

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

   -- customers テーブルと IMCI を作成します。
   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 テーブルと IMCI を作成します。
   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)