目录
使用场景
物化视图是将数据落地的方法之一,当我们在使用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';