使用列存索引加速物化视图刷新_云原生数据库 PolarDB(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.7.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.7.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;
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;
    
    -- 设置新旧视图名称
    old_view_name := view_name;
    new_view_name := view_name || '_new';

    -- IMCI的性能参数
    SET LOCAL polar_csi.enable_query = on;
    SET LOCAL polar_csi.cost_threshold = 0;
    SET LOCAL polar_csi.exec_parallel = 6;
    SET LOCAL polar_csi.memory_limit = 10240;

    -- 打印查询计划
    RAISE NOTICE 'Query plan for materialized view refresh:';
    FOR explain_result IN EXECUTE format('EXPLAIN CREATE MATERIALIZED VIEW %s AS %s', qualified_new_name, view_definition) LOOP
        RAISE NOTICE '%', explain_result;
    END LOOP;
    
    BEGIN
        -- 创建新的物化视图
        EXECUTE format('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;
        
        -- 删除旧物化视图
        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;