postgresql物化视图应用实列介绍_如何给postgres数据库创建物化视图

目录

 使用场景

创建语法

 使用场景

        物化视图是将数据落地的方法之一，当我们在使用fdw对远端库进行数据链接的时候，此时可以使用物化视图将远端数据库的表数据落地到本地，将会大大增加我们join效率。

创建语法

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

        创建物化视图时  可以使用USING method methodname 如果methodname 是已经创建好的，将会直接指定已将创建好的methodname 的访问配置，如果你没有指定methodname  那创建的物化视图将使用默认的method heap_tableam_handler，

关于storage_parameter 相关参数的值例举如下

- TABLESPACE - 此选项指定应创建物化视图的表空间。您可以在TABLESPACE关键字后指定表空间的名称。
 
- FILLFACTOR - 此选项指定物化视图的填充因子。填充因子是一个百分比值，确定每个物化视图页面上应留下多少空间。您可以在FILLFACTOR关键字后指定填充因子。
 
- AUTOVACUUM - 此选项指定是否应为物化视图启用自动清理。可能的值为ENABLE和DISABLE。如果指定ENABLE，则将为物化视图启用自动清理。如果指定DISABLE，则将为物化视图禁用自动清理。
 
- WITH OIDS - 此选项指定物化视图是否应包括对象ID。可能的值为TRUE和FALSE。如果指定TRUE，则物化视图将包括对象ID。如果指定FALSE，则物化视图将不包括对象ID。
 
- WITHOUT OIDS - 此选项指定物化视图是否应排除对象ID。可能的值为TRUE和FALSE。如果指定TRUE，则物化视图将排除对象ID。如果指定FALSE，则物化视图将不排除对象ID。
 
- CONNECTION LIMIT - 此选项指定可以连接到物化视图的最大并发连接数。您可以在CONNECTION LIMIT关键字后指定连接限制。
 
- TABLESPACE - 此选项指定应创建物化视图的表空间。您可以在TABLESPACE关键字后指定表空间的名称。
 
- WITH - 此选项指定物化视图的各种存储参数。您可以以parameter=value的形式指定存储参数。以下是可以使用的一些存储参数：
 
- fillfactor - 此参数指定物化视图的填充因子。
 
- autovacuum_enabled - 此参数指定是否应为物化视图启用自动清理。
 
- toast_tuple_target - 此参数指定TOAST表每页的目标元组数。
 
- parallel_workers - 此参数指定可以用于物化视图的并行工作程序数。
 
- maintenance_work_mem - 此参数指定可用于物化视图维护操作的内存量。
 
- max_parallel_workers - 此参数指定可以用于物化视图的最大并行工作程序数。
 
- autovacuum_vacuum_scale_factor - 此参数指定应在自动清理操作期间清理的表大小的分数。
 
- autovacuum_analyze_scale_factor - 此参数指定应在自动清理操作期间分析的表大小的分数。
 
- autovacuum_vacuum_cost_limit - 此参数指定自动清理操作的成本限制。
 
- autovacuum_vacuum_cost_delay - 此参数指定自动清理操作之间的延迟。 

可以根据情况进行配置相关参数，多数实际生产应用中使用默认值 不做特殊指定。

当前有一张表text 

将其创建成物化视图

 create materialized view v_dmuser as select  * from  dm_userbehavior_sample ;
 

 create materialized view v_dmuser as 
select  *  
 from dm_userbehavior_sample where user_id >500  with no data ;
--当使用with no data 参数控制时 此时的物化视图仅仅只是报错了一个表结构 默认情况下时with data  带数据创建
 

 当使用with no data 改物化视图并不会被允许展示

创建视图时  可以将原来的视图字段进行变更

 
create materialized view  v_dmuser(id1,ited) as  select user_id , item_id from dm_userbehavior_sample where user_id >500  ;

        创建物化视图 和实体表相同，会占用一定的空间

         清空物化视图数据，postgresql数据库并不支持truncate ,使用以下语句可以清空物化视图数据

refresh materialized view v_dmuser with  no data;

        当本文实验的基表dm_userbehavior_sample 数据发生变化时 可以使用将会重新加载数据；

refresh materialized view CONCURRENTLY v_dmuser

        当时视图在被刷新的过程中全程会被加上八级锁  基表会被叫上一级锁，并且每一次都会执行全量更新

此时CONCURRENTLY

refresh materialized view CONCURRENTLY v_dmuser

        其次需要在物化视图中增加一个 唯一索引才可以使用关键字CONCURRENTLY（需要视图中有唯一键，否则无法创建）

        与物化视图相关的系统表  可以查看一在创建物化视图相关的参数配置以及信息。

SELECT *
FROM pg_matviews
WHERE matviewname = 'v_dmuser';
 
SELECT *
FROM pg_class
WHERE relname = 'v_dmuser';