pg 物化视图相关

一、 物化视图简介

类似oracle，pg的物化视图也是物理是实际存在的表。在执行某些查询时效率较低， 而且使用传统方法（例如索引）无法显著提高效率，这时常用的方法是将需要查询的数据事先查好并储存起来， 这样就不需要每次查询都从头执行一次。这种“ 缓存” 机制其实就是物化视图。

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

二、 物化视图刷新方式

1. 物化视图刷新语法

REFRESH MATERIALIZED VIEW [CONCURRENTLY] table_name [WITH [NO] DATA]

方式一：

REFRESH MATERIALIZED VIEW table_name

方式二：

REFRESH MATERIALIZED VIEW CONCURRENTLY table_name

2. CONCURRENTLY选项的特点

• 该参数的原理和优缺点与索引的CONCURRENTLY类似，以时间来换取查询锁，刷新的速度会变慢。
• 使用CONCURRENTLY选项，可以在刷新时不阻塞对该物化视图的查询，但在多行受影响时刷新速度会下降；
• 不使用CONCURRENTLY选项时，使用更少的资源并且完成更迅速，但是会锁定其他试图从该物化视图读数据的连接。该选项在少量行受影响时， 可能速度会更快

3. 能使用CONCURRENTLY选项的情况

• 物化视图上必须至少有一个仅包含列名（不是函数索引等）且包含所有行（不是选择索引）的UNIQUE索引，否则会报错
• 物化视图未被填充时，不能使用该选项，否则会报错
• 即使使用了CONCURRENTLY选项，每次也只能刷新一个物化视图（只是在刷新时不阻塞读当前物化视图，但多个物化视图刷新语句还是不能并发执行）

三、 物化视图刷新实现

1. 入口函数

ExecRefreshMatView(RefreshMatViewStmt *stmt,const char *queryString,ParamwListInfo params, char *completionTag)

typedef struct RefreshMatViewStmt
{
NodeTag type;
bool concurrent;
bool skipData;
RangeVar *relation;
} RefreshMatViewStmt;

2. ExecRefreshMatView函数

方式一（ 无concurrently选项）

• 设置锁的模式：访问排他锁 AccessExclusiveLock
• 正确性检查
• 创建临时表
• 调用refresh_by_heap_swap函数完成刷新

方式二（ 有concurrently选项）

• 设置锁的模式：排他锁 Exclusivelock
• 正确性检查
• 唯一索引检查
• 创建临时表空间
• 创建临时表
• 调用refresh_by_match_merge函数完成刷新

3. refresh_by_heap_swap

通过交换原来的表和临时表的物理文件， 并重新建立索引， 完成刷新

4. refresh_by_match_merge

通过原来的表与临时表外部连接， 根据连接结果得到两个表的差异（创建差异表）， 并做删除操作， 接着再做插入操作， 来完成刷新， 最后删除临时表

5. refresh_by_match_merge刷新实现步骤及原理

原表与临时表

创建差异表

CREATE TEMP TABLE diffname AS 
SELECT mv.ctid AS tid, newdata FROM matviename mv FULL JOIN tempname newdata ON (newdata.tid=mv.tid AND newdata = mv) 
WHERE newdata IS NULL OR mv IS NULL 
ORDER BY tid;

删除原表已被更新数据

DELETE FROM matviewname mv WHERE ctid = ANY (SELECT diff.tid FROM diffname diff
WHERE diff.tid IS NOT NULL AND diff.newdata IS NULL);

将更新的数据插回原表

INSERT INTO matviewname SELECT (diff.newdata).* FROM diffname diff WHERE tid IS NULL;

删除差异表和临时表

Drop table diffname,tempname;

四、 物化视图示例

五、 重建索引

PG12引入REINDEX CONCURRENTLY，以解决在REINDEX期间不能执行查询的问题，锁模式设置为Share Update ExclusiveLock。

其实现思路如下:

• 在系统表中创建新的索引
• 构建新索引
• 使新索引与在构建期间出现的变化同步(catch up)
• 重命名新索引为旧索引的名称，并切换所有依赖旧索引的地方到新索引，旧索引设置为invalid
• 标记旧索引为dead状态
• 删除旧索引

参考

https://www.postgresql.org/docs/12/sql-refreshmaterializedview.html