使用列存索引加速物化視圖重新整理 - PolarDB

當處理海量資料（如十億層級）時，PostgreSQL物化視圖的重新整理過程會變得異常緩慢，導致資料新鮮度低，影響BI分析和報表產生的效率。PolarDB PostgreSQL版的列存索引（In-Memory Column Index, IMCI）功能能夠顯著縮短物化視圖的重新整理耗時，從而提升資料的新鮮度，並加速BI分析與報表產生的效率。

方案簡介

列存索引是PolarDB PostgreSQL版提供的分析加速引擎。它可以為行存表建立列存索引，並自動維護行存資料和列存索引的一致性。當執行複雜的彙總或關聯查詢時，資料庫可以利用列存索引進行計算，其效能遠超傳統的行存掃描。

本方案的核心流程是：為構建物化視圖的基表建立列存索引，以加速物化視圖的建立和重新整理過程。

前提條件

叢集版本：
- PostgreSQL 16（核心小版本為2.0.16.8.3.0及以上）
- PostgreSQL 14（核心小版本為2.0.14.10.20.0及以上）
說明
您可在控制台查看核心小版本號碼，也可以通過SHOW polardb_version;語句查看。如未滿足核心小版本要求，請升級核心小版本。
原表必須包含主鍵，且在建立列存索引時需要將主鍵列加入列存索引中。
wal_level參數的值需設定為logical，即在預寫式日誌WAL（Write-Ahead Logging）中增加支援邏輯編碼所需的資訊。
說明
您可以通過控制台設定wal_level參數。修改該參數後叢集將會重啟，請在修改參數前做好業務安排，謹慎操作。

注意事項

一張表只能建立一個列存索引。
不支援修改列存索引。如需在列存索引中添加列，請重建列存索引。

準備工作

環境準備

符合條件的PolarDB PostgreSQL版叢集。

開啟列存索引，對於不同的PolarDB PostgreSQL版核心版本，開啟列存索引的方式不同：

PostgreSQL 16（2.0.16.9.8.0及以上）或PostgreSQL 14（2.0.14.17.35.0及以上）

目前的版本下的PolarDB PostgreSQL版叢集，支援兩種開啟方式，具體差異如下，請按需選擇：

對比項	【推薦】添加列存索引唯讀節點	直接使用預先安裝的列存索引外掛程式
操作方式	通過控制台實現可視化操作，手動添加列存索引節點。	無需任何操作，即可直接使用。
資源分派	列存引擎獨佔所有資源，能夠充分利用所有記憶體。	列存引擎只能使用25%的記憶體，其餘記憶體則分配給行存引擎使用。
業務影響	TP（事務）與AP（分析）業務在不同節點上相互隔離，互不影響。	TP（事務）與AP（分析）業務在同一節點，會互相影響。
費用	需額外收取列存索引唯讀節點的費用，按照普通計算節點收費。	無費用。

添加列存索引唯讀節點

您可選擇以下兩種方式中任意一種方式添加列存索引唯讀節點：

說明

叢集中應包含一個唯讀節點，即單節點叢集不支援添加列存索引唯讀節點。

控制台添加

登入PolarDB控制台，選擇叢集所在地區。您可以按照如下兩種方式中的任意一種進入增刪節點嚮導頁面：
- 在集群列表頁面，單擊操作欄的增删节点。
- 在目的地組群的基本信息頁面，数据库节点地區，單擊增删节点。
選擇增加列存索引唯讀節點選項，並單擊確定。
在叢集變更配置頁面，添加列存索引唯讀節點並支付。
1. 單擊+增加一個列存索引唯讀節點，選擇節點規格。
2. 選擇切換時間。
3. （可選）查看產品服務合約、服務等級協議。
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 PostgreSQL版叢集，列存索引作為外掛程式polar_csi部署在資料庫叢集中，在使用之前需要在指定的資料庫中建立外掛程式。

說明

polar_csi外掛程式的範圍是Database層級，如果需要在叢集的多個Database中使用列存索引，需要為每個Database分別建立polar_csi外掛程式。
安裝外掛程式使用的資料庫帳號必須為高許可權帳號。

您可以選擇以下兩種方式中的任意一種安裝polar_csi外掛程式。

控制台安裝

登入PolarDB控制台，在左側導覽列單擊叢集列表，選擇叢集所在地區，並單擊目的地組群ID進入叢集詳情頁。
在左側導覽列選擇配置與管理 > 外掛程式管理，在管理外掛程式頁簽，選中未安裝外掛程式。
在頁面右上方選擇目標資料庫，單擊polar_csi外掛程式操作列安裝，在彈出的安裝外掛程式對話方塊，選擇目標資料庫帳號，單擊確定，即將外掛程式安裝到目標資料庫中。

命令列安裝

串連資料庫叢集，並在具有相應許可權的目標資料庫中執行以下語句，建立polar_csi外掛程式。

CREATE EXTENSION polar_csi;

在指定資料庫（業務庫）內安裝pg_hint_plan外掛程式，用於可通過特殊的備註陳述式提示來調整既定的執行計畫。
```
CREATE EXTENSION pg_hint_plan;
```
在postgres系統庫內建立pg_cron（定時任務）外掛程式，用於在指定的時間點或時間間隔內自動執行相關任務。
1. 切換資料庫。
```
\c postgres;
```
2. 安裝外掛程式。
```
CREATE EXTENSION pg_cron;
```

資料準備

在指定的資料庫（業務庫）中，分別建立customers表和orders表，並建立列存索引。同時，插入測試資料。

切換資料庫（業務庫），此處以testdb為例。
```
\c testdb;
```

建立資料與插入資料。

-- 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);

建立物化視圖

建立物化視圖時，通過Hint強制查詢最佳化工具使用列存索引執行計算。

/*+ SET(polar_csi.enable_query on) SET(polar_csi.cost_threshold 0) SET(polar_csi.exec_parallel 6) SET(polar_csi.memory_limit 10240) */CREATE MATERIALIZED VIEW mv_customer_orders AS
SELECT 
    c.customer_name AS customer_name,
    o.order_date AS order_date,
    o.amount AS amount
FROM 
    orders o
JOIN 
    customers c ON o.customer_id = c.customer_id;

Hint參數說明

參數	說明
`polar_csi.enable_query on`	允許查詢使用列存索引。
`polar_csi.cost_threshold 0`	將代價閾值設為0，強制使用列存索引。
`polar_csi.exec_parallel 6`	設定列存計算的並行度，建議不超過節點CPU核心數。
`polar_csi.memory_limit 10240`	設定計算可用記憶體（單位MB）。

重新整理物化視圖

建立重新整理物化視圖函數

此處封裝一個函數來實現這個過程。推薦使用以下函數進行重新整理，它可以安全地替換舊視圖，並保留索引和所有權。

說明

以下函數僅供查考，雖然能保證切換過程的安全性，但在實際業務環境使用前仍需充分測試。

-- view_name是物化視圖的名稱，schema_name是物化視圖所在的schema(預設current_schema),new_owner是建立後物化視圖的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
    -- 確定目標schema（使用輸入參數或當前schema）
    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;

    -- 擷取物化視圖的定義和當前owner
    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);

        -- 如果指定了新owner，則設定owner
        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;
$$;

參數說明

參數	說明
`refresh_materialized_view_safely_using_csi`	函數名稱，您可以根據實際業務需求調整。
`view_name`	物化視圖的名稱。
`schema_name`	物化視圖所在的schema，預設current_schema。
`new_owner`	建立新物化視圖後的新owner。
`polar_csi.enable_query on`	允許查詢使用列存索引。
`polar_csi.cost_threshold 0`	將代價閾值設為0，強制使用列存索引。
`polar_csi.exec_parallel 6`	設定列存計算的並行度，建議不超過節點CPU核心數。
`polar_csi.memory_limit 10240`	設定計算可用記憶體（單位MB）。

執行重新整理

手動重新整理

在業務需要時，手動調用函數執行重新整理。使用時，請替換為您實際建立的物化視圖名稱，此處以mv_customer_orders為例。

SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders');

使用`pg_cron`定時重新整理

說明

僅支援在postgres系統庫下建立任務，且需要高許可權帳號才可執行。
可以指定重建物化視圖的owner，以防止被高許可權帳號建立後普通使用者無法讀取。如果需要修改其他許可權相關的內容，則需調整前面定義的重新整理函數。
使用pg_cron進行定時重新整理時，請確保任務執行間隔嚴格大於物化視圖的實際重新整理耗時，以防任務堆積。由於要進行寫入，通常重新整理比單純的SELECT會慢很多。

建立定時任務

切換到postgres系統庫，在pg_cron中指定任務名稱、間隔時間以及要進行的操作等參數配置，詳細說明請參考pg_cron（定時任務）外掛程式。

切換資料庫。
```
\c postgres;
```

建立定時任務。使用時，請根據您的實際業務環境替換相關參數。

說明

<mv_name>替換為您實際建立的物化視圖名稱。
<database_name>替換為您實際的業務庫名稱。
<schema_name>替換為您實際的schema名稱。
<user_name>替換為您實際的schema名稱。

文法

SELECT cron.schedule_in_database(
    'refresh_mv_customer_orders',  -- 任務名稱 (可自訂)
    '*/5 * * * *',                 -- Cron運算式，例如每5分鐘執行一次
    $$SELECT refresh_materialized_view_safely_using_csi('<mv_name>', '<schema_name>', '<user_name>')$$,
    '<database_name>'
);

樣本

SELECT cron.schedule_in_database(
    'refresh_mv_customer_orders',  -- 任務名稱 (可自訂)
    '*/5 * * * *',                 -- Cron運算式，例如每5分鐘執行一次
    $$SELECT refresh_materialized_view_safely_using_csi('mv_customer_orders', 'public', 'polarpg')$$,
    'testdb'
);

查看已配置的定時任務

執行以下SQL語句查看已配置的定時任務。

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)

刪除定時任務

若您不在需要定時任務重新整理，可以使用以下SQL語句刪除定時任務。

SELECT cron.unschedule('refresh_my_materialized_view');

查看定時任務執行資訊

執行以下SQL語句查看定時任務的詳細執行資訊。

SELECT * FROM cron.job_run_details;