“物化视图”是一个数据库对象，它存储预先计算的数据库查询的结果，并可以根据需要轻松刷新该结果。物化视图是几乎所有高级数据库系统的一个组成部分。自然，PostgreSQL 也提供了物化视图的支持，并为最终用户提供了一个强大的工具来处理更耗时的请求。
现在的主要问题是：有哪些陷阱，以及首先如何利用物化视图？让我们深入了解一下。
创建物化视图
在我们实际查看物化视图并弄清楚它们是如何工作之前，我们必须导入一些示例数据，我们可以将其用作计算的基础：
demo=# CREATE TABLE t_demo (grp int, data numeric);
CREATE TABLE
demo=# INSERT INTO t_demo SELECT 1, random()
FROM generate_series(1, 5000000);
INSERT 0 5000000
demo=# INSERT INTO t_demo SELECT 2, random()
FROM generate_series(1, 5000000);
INSERT 0 5000000
我们创建了 1000 万行，分为 2 组。要在 PostgreSQL 中创建物化视图，我们可以使用以下语法规范：
demo=# \h CREATE MATERIALIZED VIEW
Command: CREATE MATERIALIZED VIEW
Description: define a new materialized view
Syntax:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]
基本上，物化视图具有名称、一些参数，并且基于查询。下面是一个例子：
demo=# CREATE MATERIALIZED VIEW mat_view AS
SELECT grp, avg(data), count(*)
FROM t_demo
GROUP BY 1;
SELECT 2
这里真正需要注意的是物化视图与基础表相比的大小：
demo=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
-------+----------+-------------------+-------+-------------+--------+-------------
public | mat_view | materialized view | hs | permanent | 16 kB |
public | t_demo | table | hs | permanent | 423 MB |
(2 rows)
423 MB 与 16 KB 是一个真正的区别。如果要查询物化视图，可以像查询普通 PostgreSQL 表一样进行：
demo=# SELECT * FROM mat_view;
grp | avg | count
-----+--------------------------+---------
1 | 0.500091604165533141153 | 5000000
2 | 0.499893365960709086730 | 5000000
(2 rows)
demo=# explain SELECT * FROM mat_view;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on mat_view (cost=0.00..21.30 rows=1130 width=44)
(1 row)
查看执行计划（explain）表明，普通表和物化视图之间没有区别。我们可以简单地查询数据。
此时，唯一不理想的是查询优化器的估计（= 1130 行）。但是，这很容易解决。该过程与任何其他表相同：
demo=# ANALYZE;
ANALYZE
demo=# explain SELECT * FROM mat_view;
QUERY PLAN
---------------------------------------------------------
Seq Scan on mat_view (cost=0.00..1.02 rows=2 width=26)
(1 row)
ANALYZE 重新创建了新的优化器统计信息并很好地修复了估计。Autovacuum 还能够重新创建优化器统计信息。
创建物化视图……使用……
然而，还有更多。PostgreSQL 提供了一些与该领域相关的其他重要功能。这些特征之一是USING子句。这样做的目的是什么？目前，PostgreSQL 仅支持一种存储格式（堆）。但是，在未来，我们希望看到更多的存储格式，例如 [zheap](https://www.cybertec-postgresql.com/en/zheap-reinvented-postgresql-storage/0 或 zedstore 的想法USING是能够为给定的要求选择最佳的存储格式。目前，堆是默认格式，这完全没问题。
创建物化视图……使用……
该WITH-clause是相同 CREATE TABLE 。它允许您定义存储参数，例如 autovacuum 行为FILLFACTOR等。下面是一个例子：
demo=# CREATE MATERIALIZED VIEW mat_view_2
WITH (autovacuum_enabled = false) AS
SELECT grp, avg(data), count(*)
FROM t_demo
GROUP BY 1;
SELECT 2
通常，实体化视图的存储方式与表一样，因此只需遵循与创建标准表时相同的建议即可。
创建物化视图……表空间……
TABLESPACE关键字也是如此。在默认设置中，物化视图将存储在默认表空间中，即$PGDATA：
demo=# SHOW data_directory;
data_directory
------------------
/Users/hs//db13
(1 row)
SHOW是找出它在哪里的简单方法。
创建物化视图……数据与无数据……
默认情况下，实体化视图以包含结果的方式创建。但是，如果对象是使用该NO DATA选项创建的，则它是空的。仅创建定义。
必须注意这样一个事实，在这种情况下，视图在刷新以包含数据之前无法查询。我们还没有看到该NO DATA选项在数据库支持中被过于频繁地使用。仍然值得一提的是，这个选项确实存在。
修改 PostgreSQL 中的物化视图
有时，物化视图必须稍后修改。一个经典的例子是重命名视图的列而不重新创建它。我们来看看视图的定义：
demo=# \d mat_view
Materialized view "public.mat_view"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
grp | integer | | |
avg | numeric | | |
count | bigint | | |
修改实体化视图是使用ALTER MATERIALIZED VIEW命令来完成的，该命令是方便各种更改的强大工具。语法与ALTER TABLE可以为您执行的操作非常相似：
demo=# \h ALTER MATERIALIZED VIEW
Command: ALTER MATERIALIZED VIEW
Description: change the definition of a materialized view
Syntax:
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
action [, ... ]
ALTER MATERIALIZED VIEW name
DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
RENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
RENAME TO new_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
SET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name
[ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]
where action is one of:
ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET
( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE
{ PLAIN | EXTERNAL | EXTENDED | MAIN }
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
这里值得注意的是，您还可以执行CLUSTER（= 索引组织）、SET STATISTICS（= 调整 PostgreSQL 优化器的直方图大小）等操作。该过程与普通表相同。
让我们尝试一下并简单地重命名列：
demo=# ALTER MATERIALIZED VIEW mat_view
RENAME COLUMN avg TO average;
ALTER MATERIALIZED VIEW
demo=# \d mat_view
Materialized view "public.mat_view"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
grp | integer | | |
average | numeric | | |
count | bigint | | |
在我们继续刷新之前，我想将您的注意力集中在索引上：
demo=# CREATE INDEX idx_average ON mat_view (average);
CREATE INDEX
为物化视图建立索引以确保快速查询是一个好主意。同样，存在与表相同的规则。简单地索引不同的条目，并在读取物化视图时提供良好的选择性。
刷新物化视图
物化视图包含查询结果的快照。它不会定期更新，除非用户强制 PostgreSQL 这样做。在 Oracle 中，物化视图支持稍微复杂一些。希望 PostgreSQL 很快会在这方面赶上一点。
但是，目前，必须使用以下REFRESH MATERIALIZED VIEW命令手动刷新 PostgreSQL 中的物化视图：
demo=# \h REFRESH MATERIALIZED VIEW
Command: REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]
运行刷新很简单：
demo=# REFRESH MATERIALIZED VIEW mat_view; REFRESH MATERIALIZED VIEW
PostgreSQL 知道必须重新执行以刷新物化视图内容的查询。只有一个缺点：PostgreSQL 必须在刷新时锁定对象，这意味着在刷新时没有人可以访问它。
同时刷新物化视图
有时，容忍锁定的物化视图是不可接受的。在这种情况下，需要并发更新。但是，有一个问题：
demo=# REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view;
ERROR: cannot refresh materialized view "public.mat_view" concurrently
HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.
物化视图需要一个唯一的列来支持并发刷新。这意味着您必须确定一个“主键”并创建一个唯一索引：
demo=# CREATE UNIQUE INDEX idx_grp ON mat_view (grp);
CREATE INDEX
demo=# REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view;
REFRESH MATERIALIZED VIEW
这里需要注意的重要一点是，您需要小心一点，并确保表膨胀不会接管。从技术上讲，它是一个DELETE / INSERT，容易导致表膨胀。确保制定了适当的VACUUM政策。
最后……
物化视图是包括 PostgreSQL 在内的大多数数据库的重要特性。它们可以帮助加速大型计算——或者至少可以缓存它们。
