实时物化视图

术语

• 基表（Base Tables）：指物化视图定义中使用到的普通表。
• 增量（Delta）：指基表的数据发生变化时，与物化视图中的数据相比，增加和删除的数据集合。
• 刷新（Refresh）：指物化视图的维护操作，使得物化视图的数据和根据视图定义查询当前基表获得的数据一致。
• 应用增量（Apply Delta）：指对实时物化视图插入/删除计算出来的物化视图增量数据，以保持实时物化视图与基表的数据一致性。

使用限制

性能影响

• 实时物化视图大幅度提升查询性能的同时，对基表写入性能会有较大的影响，建议在读多写少的场景下使用。
• 实时更新物化视图对基表写入性能的影响程度受实时物化视图定义、基表写入负载、基表结构和基表索引等因素的影响，建议您在生产环境创建实时物化视图前，在测试环境测试带有实时物化视图的基表的写入性能，确认写入性能满足要求的情况下再在生产环境使用实时物化视图。
• 以下方式有助于降低维护实时物化视图的代价：
  • 同一个基表上的实时物化视图不要太多。
  • 对基表批量写入。例如，使用COPY/INSERT INTO SELECT的方式批量导入数据。
  • 基表都有主键，并且实时物化视图定义中的投影列中包含所有基表的主键。

原理介绍

• 创建实时物化视图
  • 对物化视图查询改写，额外计算出维护实时物化视图所需要的隐藏列。
  • 对基表创建Trigger，以便实现实时物化视图的增量刷新。
  • 在满足一定条件的时候，对实时物化视图创建唯一索引，加速实时物化视图的增量刷新。
• 增量刷新实时物化视图
  • 基表的数据变化触发相应的Trigger。
  • 通过Trigger获取到基表的增量数据。
  • 根据实时物化视图的定义和当前基表的增量数据计算实时物化视图的增量。
  • 将计算出的增量应用到实时物化视图上，完成实时物化视图的增量刷新。
• 删除实时物化视图
  • 删除实时物化视图对应基表上的增量刷新Trigger。
  • 删除实时物化视图本身。

使用指南

• 前提条件
  在使用实时物化视图功能前，需要先将内核小版本升级至V1.1.27后，再在数据库中创建polar_ivm插件。

  CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;

  说明 您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号：

  show polar_version;

• 创建实时物化视图

  CREATE MATERIALIZED VIEW table_name[ (column_name [, ...] ) ]
  REFRESH FAST
  ON COMMIT
  AS query
  [ WITH [ NO ] DATA ]

  参数说明：

  参数	说明
  table_name	需要创建的实时物化视图的名称（可以被模式限定）。
  column_name	新物化视图中的一个列名。如果没有提供列名，则会从查询的输出列名中获取。
  WITH DATA	默认选项，立刻创建完整的实时物化视图。
  WITH NO DATA	只创建出实时物化视图的结构，但物化视图上没有数据，也不会在该视图上保持实时刷新。 查询该实时物化视图会报错，直到对该实时物化视图执行REFRESH MATERIALIZED VIEW命令。
  query	实时物化视图的视图定义，一个SELECT、TABLE或者VALUES命令。该查询将在一个安全受限的操作中运行。

• 增量刷新实时物化视图

  REFRESH MATERIALIZED VIEW table_name

  说明 其中。

  • table_name：表示需要刷新的实时物化视图的名称（可以被模式限定）
  • 通过WITH DATA方式创建的实时物化视图通常不需要手动执行刷新来确保视图和基表的一致性。
  • 通过WITH NO DATA方式创建的实时物化视图执行刷新后，会通过视图定义生成实时物化视图的数据，并对后续基表的更改开启实时刷新。
• 删除实时物化视图

  DROP MATERIALIZED VIEW [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

  参数说明：

  参数	说明
  IF EXISTS	如果该实时物化视图不存在则不抛出一个错误，而是发出一个提示。
  table_name	需要移除的实时物化视图的名称（可以是模式限定的）。
  CASCADE	自动删除依赖于该实时物化视图的对象（例如，其他物化视图或常规视图），然后再删除所有依赖于自动删除对象的对象。
  RESTRICT	如果有任何对象依赖于该实时物化视图，则拒绝删除该实时物化视图。默认选择该参数。

示例

1. 创建实时物化视图的依赖插件。

   CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog ;

2. 创建基表并导入初始数据。

   CREATE TABLE t( a INT, b VARCHAR);
   INSERT INTO t VALUES
     (1,'a'),
     (2,'b'),
     (3,'c'),
     (4,'d'),
     (5,'e');

3. 创建实时物化视图。

   CREATE MATERIALIZED VIEW mv
   REFRESH FAST
   ON COMMIT
   AS
   SELECT max(a),min(a),b FROM t GROUP BY b;

4. 对基表进行DML操作。
   • 查询实时物化视图数据。

     SELECT * FROM mv ORDER BY b;

     显示结果如下：

      max | min | b
     -----+-----+---
        1 |   1 | a
        2 |   2 | b
        3 |   3 | c
        4 |   4 | d
        5 |   5 | e
     (5 rows)

     结果显示实时物化视图的数据与基表数据保持一致。
   • 向基表中插入新数据，查询实时物化视图数据。

     INSERT INTO t VALUES(6,'f');
     
     SELECT * FROM mv ORDER BY b;

     显示结果如下：

      max | min | b
     -----+-----+---
        1 |   1 | a
        2 |   2 | b
        3 |   3 | c
        4 |   4 | d
        5 |   5 | e
        6 |   6 | f
     (6 rows)

     结果显示实时物化视图的数据与基表数据保持一致。
   • 删除基表数据，查询实时物化视图数据。

     DELETE FROM t WHERE a = 2;
     
     SELECT * FROM mv ORDER BY b;

     显示结果如下：

      max | min | b
     -----+-----+---
        1 |   1 | a
        3 |   3 | c
        4 |   4 | d
        5 |   5 | e
        6 |   6 | f
     (5 rows)

     结果显示实时物化视图的数据与基表数据保持一致。
   • 更新基表数据，查询实时物化视图数据。

     UPDATE t SET a = a + 1;
     
     SELECT * FROM mv ORDER BY b;

     显示结果如下：

      max | min | b
     -----+-----+---
        2 |   2 | a
        4 |   4 | c
        5 |   5 | d
        6 |   6 | e
        7 |   7 | f
     (5 rows)

     结果显示实时物化视图的数据与基表数据保持一致。
5. 删除实时物化视图。

   DROP MATERIALIZED VIEW mv;