PHP小码农

Appearance

CREATE MATERIALIZED VIEW ​

CREATE MATERIALIZED VIEW 把一条查询的结果物化落盘。它比 CREATE VIEW 多出许多可调项：是否立即填充数据、用哪种存储引擎、放哪个表空间、用什么存储参数，以及建完要不要立刻建索引。本节把完整语法、WITH NO DATA 的用法、多表/聚合/FDW 场景、依赖与删除，以及查看定义的各种手段讲全。

前置知识

阅读本节前，建议先了解：物化视图概述、创建表（CREATE TABLE）

基础概念 ​

创建时到底发生了什么 ​

text
CREATE MATERIALIZED VIEW mv_x AS <query> [WITH DATA];

  1. 在 pg_class 里创建 relkind='m' 的关系（一张堆表）
  2. 在 pg_rewrite 里写入 _RETURN 规则（保存查询定义）
  3. 若 WITH DATA（默认）：
        立即执行 <query>，把结果 INSERT 进这张表
        执行完成前，整个语句不返回

因此有两个直接后果：

现象原因
大表物化视图创建很慢创建时就跑了整条查询
创建期间会产生大量写入与 WAL结果集被真实插入
可以用 WITH NO DATA 跳过执行建空壳，稍后刷新

语法要素一览 ​

语法片段作用是否常用
IF NOT EXISTS已存在时不报错脚本常用
( column_name, ... )显式指定列名需要稳定列名时用
USING method指定表访问方法（默认 heap）很少用
WITH ( storage_parameter ... )存储参数（fillfactor 等）少用
TABLESPACE ts指定表空间大对象分层存储时用
AS query定义查询必需
WITH DATA / WITH NO DATA是否立即填充WITH NO DATA 很常用

列名与类型从哪来 ​

与 CREATE TABLE AS 一致：列名与类型由查询的输出决定。

sql
CREATE MATERIALIZED VIEW mv_types AS
SELECT
  id,
  amount * 1.1        AS amount_gross,   -- 类型 numeric
  now()               AS created_at,     -- timestamptz
  count(*) OVER ()    AS total_rows      -- bigint
FROM sales;
sql
\d mv_types
text
                    Materialized view "public.mv_types"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 id            | bigint                   |           |          |
 amount_gross  | numeric                  |           |          |
 created_at    | timestamp with time zone |           |          |
 total_rows    | bigint                   |           |          |

注意 Nullable 全是空：创建时不带 NOT NULL 约束（因为无法确认可空性）。需要约束时可以视图后再补，但通常没必要。

WITH NO DATA 的价值 ​

WITH NO DATA 创建一个未填充的物化视图（ispopulated = false）：

text
WITH NO DATA 的典型用途：
  1. 迁移 / 部署脚本：先把对象建好（DDL 快），数据稍后由任务刷新
  2. 依赖顺序：多个物化视图互相依赖时，先全部建壳，再按序刷新
  3. 想控制数据填充的时间窗口（避开业务高峰）
  4. 只想先固化查询定义（例如做审计 / 比对）

代价是：未填充的物化视图被查询时会直接报错。

语法与用法 ​

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

-- 常见的存储参数
--   fillfactor = 70..100
--   autovacuum_enabled = on|off
--   autovacuum_vacuum_scale_factor = 0..1
--   parallel_workers = integer

-- 建索引（并发刷新的前提）
CREATE [ UNIQUE ] INDEX index_name ON mv_name ( column [, ...] );

-- 删除
DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

最简可运行示例：

sql
CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint  NOT NULL,
  amount  numeric(12,2) NOT NULL,
  status  text    NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (user_id, amount, status) VALUES
  (1, 100.00, 'paid'), (1, 200.00, 'paid'),
  (2, 300.00, 'pending'), (3, 400.00, 'paid');

CREATE MATERIALIZED VIEW mv_paid_summary AS
SELECT user_id, count(*) AS cnt, sum(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY user_id;
text
CREATE TABLE
INSERT 0 4
SELECT 2
sql
SELECT * FROM mv_paid_summary ORDER BY user_id;
text
 user_id | cnt | total
---------+-----+--------
       1 |   2 | 300.00
       3 |   1 | 400.00
(2 rows)

详细说明 ​

WITH NO DATA：建空壳，稍后填充 ​

sql
CREATE MATERIALIZED VIEW mv_orders_pending
WITH (fillfactor = 100)
AS
SELECT user_id, count(*) AS cnt
FROM orders
WHERE status = 'pending'
GROUP BY user_id
WITH NO DATA;
text
CREATE MATERIALIZED VIEW
sql
-- 确认未填充
SELECT matviewname, ispopulated FROM pg_matviews WHERE matviewname = 'mv_orders_pending';
text
    matviewname     | ispopulated
--------------------+-------------
 mv_orders_pending  | f
(1 row)
sql
-- 查询会直接报错
SELECT * FROM mv_orders_pending;
text
ERROR:  materialized view "mv_orders_pending" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
sql
-- 填充它（WITH DATA）
REFRESH MATERIALIZED VIEW mv_orders_pending;

SELECT matviewname, ispopulated FROM pg_matviews WHERE matviewname = 'mv_orders_pending';
text
    matviewname     | ispopulated
--------------------+-------------
 mv_orders_pending  | t
(1 row)

依赖顺序很关键

如果 mv_b 依赖 mv_a，而 mv_a 是 WITH NO DATA 创建的，那么 REFRESH mv_b 会失败。正确顺序是「先全建壳 → 再从底向上 REFRESH」。

建时指定列名：稳定接口 ​

不显式指定列名时，列名来自查询输出。一旦查询改动，列名可能漂移。指定列名可以把接口固定下来：

sql
CREATE MATERIALIZED VIEW mv_daily_metrics (day, order_cnt, gmv) AS
SELECT
  date_trunc('day', created_at) AS d,
  count(*)                      AS c,
  sum(amount)                   AS s
FROM orders
GROUP BY 1;
sql
\d mv_daily_metrics
text
                Materialized view "public.mv_daily_metrics"
  Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------
 day       | timestamp with time zone |           |          |
 order_cnt | bigint                   |           |          |
 gmv       | numeric                  |           |          |

列名数量必须与查询输出完全一致，否则报错：

sql
-- 错误！列名个数不匹配
CREATE MATERIALIZED VIEW mv_bad (a, b) AS
SELECT 1, 2, 3;
text
ERROR:  too many column names were specified

表空间与存储参数 ​

表空间用于把大对象放到独立磁盘（也是运维分层存储的常见手段）：

sql
-- 前提：表空间已存在
-- CREATE TABLESPACE fast_disk LOCATION '/data/fast';

CREATE MATERIALIZED VIEW mv_metrics_fast
TABLESPACE fast_disk
AS
SELECT date_trunc('hour', created_at) AS hour, count(*) AS cnt
FROM orders GROUP BY 1;

存储参数与表一致，常用的是 fillfactor 与并行相关参数：

sql
CREATE MATERIALIZED VIEW mv_parallel AS
SELECT user_id, count(*) AS cnt, sum(amount) AS total
FROM orders GROUP BY user_id
WITH (
  fillfactor = 100,
  parallel_workers = 4
);
text
CREATE MATERIALIZED VIEW
sql
-- 查看 / 修改
SELECT relname, reloptions FROM pg_class WHERE relname = 'mv_parallel';
ALTER MATERIALIZED VIEW mv_parallel SET (fillfactor = 90);
ALTER MATERIALIZED VIEW mv_parallel RESET (fillfactor);
text
  relname    |     reloptions
-------------+--------------------
 mv_parallel | {fillfactor=100,parallel_workers=4}
(1 row)
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW

对物化视图来说，fillfactor 主要影响刷新时的写入行为（刷新会重写内容）。频繁刷新时保留一定空间可能减少页分裂，但实际收益通常有限。

在物化视图上建索引 ​

索引是物化视图性能的关键，也必须在建完物化视图之后单独创建（CREATE MATERIALIZED VIEW 本身不支持带索引定义）：

sql
-- 普通索引：加速过滤与排序
CREATE INDEX idx_mv_paid_user ON mv_paid_summary (user_id);

-- 唯一索引：并发刷新的硬性前提
CREATE UNIQUE INDEX uq_mv_paid_user ON mv_paid_summary (user_id);
sql
-- 确认索引存在
SELECT indexrelid::regclass AS index_name, indisunique
FROM pg_index WHERE indrelid = 'mv_paid_summary'::regclass;
text
     index_name      | indisunique
---------------------+-------------
 uq_mv_paid_user     | t
 idx_mv_paid_user    | f
(2 rows)

建索引的时机

大物化视图的推荐流程是「先 WITH NO DATA 建壳 → 建索引 → REFRESH」。这样索引是批量构建的，比先填充再建索引更快，也避免刷新后立刻重建索引。

sql
-- 推荐流程
CREATE MATERIALIZED VIEW mv_big (key_col, val) AS SELECT ... WITH NO DATA;
CREATE UNIQUE INDEX uq_mv_big ON mv_big (key_col);
REFRESH MATERIALIZED VIEW mv_big;
ANALYZE mv_big;

基于多表 JOIN 的物化视图 ​

物化视图最常见的用途就是把多表 JOIN 的结果固化：

sql
CREATE TABLE users (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  city text NOT NULL
);

INSERT INTO users (name, city) VALUES ('Alice', '上海'), ('Bob', '北京'), ('Cara', '上海');

CREATE MATERIALIZED VIEW mv_order_with_user AS
SELECT
  o.id         AS order_id,
  u.id         AS user_id,
  u.name       AS user_name,
  u.city,
  o.amount,
  o.status
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

SELECT * FROM mv_order_with_user ORDER BY order_id;
text
CREATE TABLE
INSERT 0 3
SELECT 3
 order_id | user_id | user_name | city | amount | status
----------+---------+-----------+------+--------+--------
        1 |       1 | Alice     | 上海 | 100.00 | paid
        2 |       1 | Alice     | 上海 | 200.00 | paid
        4 |       3 | Cara      | 上海 | 400.00 | paid
(3 rows)

基于聚合与窗口函数的物化视图 ​

聚合是物化视图的「主场」，因为普通视图的聚合每次都要重算：

sql
CREATE MATERIALIZED VIEW mv_city_sales AS
SELECT
  u.city,
  count(*)         AS order_cnt,
  sum(o.amount)    AS total_amount,
  avg(o.amount)    AS avg_amount,
  sum(o.amount) / sum(sum(o.amount)) OVER () AS share   -- 窗口函数
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.city;

SELECT city, order_cnt, total_amount, round(share, 4) AS share
FROM mv_city_sales ORDER BY city;
text
 city | order_cnt | total_amount |  share
------+-----------+--------------+---------
 上海 |         3 |       700.00 |  1.0000
(1 row)

（只有一个城市时份额是 1；多个城市时就能看出占比。）注意：窗口函数会让视图不可合并，但在物化视图里无所谓——结果已经被物化。

物化视图可以基于物化视图（多级物化） ​

结论：可以。多级物化是数据仓库分层的常见做法。

sql
CREATE MATERIALIZED VIEW mv_user_total AS
SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id;

CREATE UNIQUE INDEX uq_mv_user_total ON mv_user_total (user_id);

-- 基于上一层物化视图再物化
CREATE MATERIALIZED VIEW mv_total_top AS
SELECT user_id, total
FROM mv_user_total
ORDER BY total DESC
LIMIT 10;

SELECT * FROM mv_total_top ORDER BY total DESC;
text
 user_id | total
---------+--------
       3 | 400.00
       1 | 300.00
(2 rows)

刷新顺序必须自底向上：

sql
-- 正确顺序
REFRESH MATERIALIZED VIEW mv_user_total;
REFRESH MATERIALIZED VIEW mv_total_top;

因为 mv_total_top 读取的是 mv_user_total 的内容，若先刷上层，上层用的是旧的下层数据。

物化视图 + 外部表（FDW）汇总远端数据 ​

这是一个非常实用的场景：把远端数据拉到本地汇总，避免每次查询都跨网络拉数据。

sql
-- 前提：已安装 postgres_fdw 并配置好远端服务器
-- CREATE EXTENSION postgres_fdw;
-- CREATE SERVER remote_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '...', dbname '...');
-- CREATE USER MAPPING FOR CURRENT_USER SERVER remote_srv OPTIONS (user '...', password '...');
-- CREATE FOREIGN TABLE ft_remote_sales (...) SERVER remote_srv OPTIONS (schema_name 'public', table_name 'sales');

CREATE MATERIALIZED VIEW mv_remote_daily AS
SELECT
  sale_date,
  count(*)      AS cnt,
  sum(amount)   AS total
FROM ft_remote_sales
GROUP BY sale_date;

优点：本地查询毫秒级，不再跨网络。 代价：REFRESH 时要重新拉取远端全量数据，网络与远端负载都要考虑。

删除物化视图与 CASCADE ​

sql
-- 被依赖时默认 RESTRICT
DROP MATERIALIZED VIEW mv_user_total;
text
ERROR:  cannot drop materialized view mv_user_total because other objects depend on it
DETAIL:  materialized view mv_total_top depends on materialized view mv_user_total
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
sql
-- 危险！会连带删除依赖它的物化视图
DROP MATERIALIZED VIEW mv_user_total CASCADE;
text
NOTICE:  drop cascades to materialized view mv_total_top
DROP MATERIALIZED VIEW

IF EXISTS 让脚本幂等：

sql
DROP MATERIALIZED VIEW IF EXISTS mv_not_exist;
text
NOTICE:  materialized view "mv_not_exist" does not exist, skipping
DROP MATERIALIZED VIEW

查看定义：\d+、pg_get_viewdef 与系统视图 ​

sql
\d+ mv_paid_summary
text
                    Materialized view "public.mv_paid_summary"
 Column  |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 user_id | bigint  |           |          |         | plain   |             |              |
 cnt     | bigint  |           |          |         | plain   |             |              |
 total   | numeric |           |          |         | main    |             |              |
Indexes:
    "uq_mv_paid_user" UNIQUE, btree (user_id)
    "idx_mv_paid_user" btree (user_id)
View definition:
 SELECT user_id,
    count(*) AS cnt,
    sum(amount) AS total
   FROM orders
  WHERE status = 'paid'::text
  GROUP BY user_id;
sql
-- 编程方式获取定义
SELECT pg_get_viewdef('mv_paid_summary'::regclass, true) AS definition;
text
                              definition
------------------------------------------------------------------------
  SELECT user_id,                                                      +
     count(*) AS cnt,                                                   +
     sum(amount) AS total                                               +
    FROM orders                                                         +
   WHERE status = 'paid'::text                                          +
   GROUP BY user_id;
(1 row)
sql
-- pg_matviews 汇总视图
SELECT matviewname, ispopulated, hasindexes, tablespace, definition
FROM pg_matviews WHERE matviewname = 'mv_paid_summary';
text
    matviewname    | ispopulated | hasindexes | tablespace |            definition
-------------------+-------------+------------+------------+----------------------------------
 mv_paid_summary   | t           | t          |            |  SELECT user_id,                +
                   |             |            |            |     count(*) AS cnt,            +
                   |             |            |            |     sum(amount) AS total        +
                   |             |            |            |    FROM orders                  +
                   |             |            |            |   WHERE (status = 'paid'::text) +
                   |             |            |            |   GROUP BY user_id;
(1 row)
sql
-- 用 pg_class 精确定位（含存储参数与大小）
SELECT
  c.oid::regclass        AS mv_name,
  c.relkind,
  c.reloptions,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'm'
ORDER BY pg_total_relation_size(c.oid) DESC;
text
       mv_name       | relkind | reloptions | total_size
---------------------+---------+------------+------------
 mv_parallel         | m       |            | 8192 bytes
 mv_orders_pending   | m       |            | 8192 bytes
 mv_paid_summary     | m       |            | 16 kB
 mv_daily_metrics    | m       |            | 8192 bytes
 mv_order_with_user  | m       |            | 8192 bytes
 mv_city_sales       | m       |            | 8192 bytes
(6 rows)

实战示例 ​

场景：为电商数仓搭建一层「用户消费汇总」物化视图，采用「先建壳 + 建索引 + 刷新」的推荐流程，并验证 FDW 汇总与多级物化。

第 1 步：建基表与数据

sql
DROP TABLE IF EXISTS fact_orders CASCADE;

CREATE TABLE fact_orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint  NOT NULL,
  sku        text    NOT NULL,
  amount     numeric(12,2) NOT NULL,
  status     text    NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO fact_orders (user_id, sku, amount, status, created_at)
SELECT
  1 + (g % 5000),
  'SKU-' || (g % 200),
  (10 + (g % 990))::numeric(12,2),
  (ARRAY['paid','paid','paid','pending','cancelled'])[1 + (g % 5)],
  now() - (g || ' minutes')::interval
FROM generate_series(1, 500000) AS g;

CREATE INDEX idx_fact_orders_status ON fact_orders (status);
CREATE INDEX idx_fact_orders_user   ON fact_orders (user_id);
ANALYZE fact_orders;
text
DROP TABLE
CREATE TABLE
INSERT 0 500000
CREATE INDEX
CREATE INDEX
ANALYZE

第 2 步：用「先建壳 + 建索引 + 刷新」流程创建物化视图

sql
-- 2.1 先建空壳（DDL 立即返回）
CREATE MATERIALIZED VIEW mv_user_spend AS
SELECT
  user_id,
  count(*)                 AS order_cnt,
  sum(amount)              AS total_amount,
  round(avg(amount), 2)    AS avg_amount,
  min(created_at)          AS first_order_at,
  max(created_at)          AS last_order_at
FROM fact_orders
WHERE status = 'paid'
GROUP BY user_id
WITH NO DATA;
text
CREATE MATERIALIZED VIEW
sql
-- 2.2 建唯一索引（为并发刷新铺路）
CREATE UNIQUE INDEX uq_mv_user_spend ON mv_user_spend (user_id);

-- 2.3 刷新填充
REFRESH MATERIALIZED VIEW mv_user_spend;

-- 2.4 收集统计信息
ANALYZE mv_user_spend;
text
CREATE INDEX
REFRESH MATERIALIZED VIEW
ANALYZE

第 3 步：验证结果与体积

sql
SELECT count(*) AS user_rows FROM mv_user_spend;
text
 user_rows
-----------
      5000
(1 row)
sql
SELECT pg_size_pretty(pg_total_relation_size('mv_user_spend')) AS mv_size,
       pg_size_pretty(pg_total_relation_size('fact_orders'))  AS base_size;
text
 mv_size | base_size
---------+-----------
 616 kB  | 57 MB
(1 row)

基表 57 MB，物化视图只有 616 kB——这就是「预聚合」的价值。

第 4 步：对比查询耗时

sql
EXPLAIN (ANALYZE, TIMING ON, SUMMARY ON, COSTS OFF)
SELECT user_id, sum(amount) FROM fact_orders WHERE status = 'paid' AND user_id = 1234 GROUP BY user_id;
text
                                QUERY PLAN
--------------------------------------------------------------------------
 GroupAggregate (actual time=1.812..1.813 rows=1 loops=1)
   Group Key: user_id
   ->  Index Scan using idx_fact_orders_user on fact_orders (actual time=0.234..1.608 rows=69 loops=1)
         Index Cond: (user_id = 1234)
         Filter: (status = 'paid'::text)
 Planning Time: 0.298 ms
 Execution Time: 1.951 ms
sql
EXPLAIN (ANALYZE, TIMING ON, SUMMARY ON, COSTS OFF)
SELECT user_id, total_amount FROM mv_user_spend WHERE user_id = 1234;
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using uq_mv_user_spend on mv_user_spend (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (user_id = 1234)
 Planning Time: 0.121 ms
 Execution Time: 0.038 ms

单用户查询提速约 50 倍；若换成「全量排行 / 全表汇总」，差距会是百倍级：

sql
EXPLAIN (ANALYZE, TIMING ON, SUMMARY ON, COSTS OFF)
SELECT user_id, total_amount FROM mv_user_spend ORDER BY total_amount DESC LIMIT 10;
text
                                    QUERY PLAN
--------------------------------------------------------------------------
 Limit (actual time=0.445..0.474 rows=10 loops=1)
   ->  Index Scan using uq_mv_user_spend on mv_user_spend (actual time=0.442..0.469 rows=10 loops=1)
 Planning Time: 0.135 ms
 Execution Time: 0.512 ms

第 5 步：建立排行榜（多级物化视图）

sql
CREATE MATERIALIZED VIEW mv_top_spenders AS
SELECT user_id, total_amount
FROM mv_user_spend
ORDER BY total_amount DESC
LIMIT 100;

SELECT count(*) FROM mv_top_spenders;
text
 count
-------
   100
(1 row)
sql
-- 刷新必须自底向上
REFRESH MATERIALIZED VIEW mv_user_spend;
REFRESH MATERIALIZED VIEW mv_top_spenders;

第 6 步：验证依赖链

sql
SELECT
  dependent.relname  AS dependent,
  referenced.relname AS depends_on
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class dependent  ON dependent.oid = r.ev_class
JOIN pg_class referenced ON referenced.oid = d.refobjid
WHERE dependent.relname IN ('mv_user_spend', 'mv_top_spenders')
  AND dependent.oid <> referenced.oid;
text
   dependent     |   depends_on
-----------------+----------------
 mv_user_spend   | fact_orders
 mv_top_spenders | mv_user_spend
(2 rows)

依赖链清晰：fact_orders → mv_user_spend → mv_top_spenders。这也是「刷新顺序」的依据。

第 7 步：清理

sql
DROP MATERIALIZED VIEW mv_top_spenders;
DROP MATERIALIZED VIEW mv_user_spend CASCADE;
DROP TABLE fact_orders;
text
DROP MATERIALIZED VIEW
NOTICE:  drop cascades to 0 other objects
DROP MATERIALIZED VIEW
DROP TABLE

注意事项 ​

  • 默认 WITH DATA 会在创建时执行整条查询，大表上可能耗时很久。提前评估或改用 WITH NO DATA。

  • WITH NO DATA 的物化视图查询会报错，必须先 REFRESH。

    sql
    -- 错误！materialized view "mv_x" has not been populated
SELECT * FROM mv_x;

  • 列名列表个数必须与查询输出一致，否则 too many column names were specified。

  • 物化视图不支持在 CREATE 时同时定义索引，必须分开执行。

  • 并发刷新必须有唯一索引，建物化视图时顺手加上，别等需要时才补。

  • 多级物化的 REFRESH 顺序必须自底向上，否则上层基于旧数据。

  • DROP ... CASCADE 会连带删除依赖的物化视图，生产环境先查依赖。

  • 物化视图无法感知基表结构变更，基表加列/改列后需要重建物化视图。

  • 未填充的物化视图不能用 SELECT，但 pg_matviews.ispopulated 可以用来巡检。

  • 表空间路径必须已存在且权限正确，否则创建失败。

最佳实践 ​

  • 大物化视图统一采用「建壳 → 建索引 → 刷新 → ANALYZE」四步流程：

    sql
    CREATE MATERIALIZED VIEW mv_x AS SELECT ... WITH NO DATA;
CREATE UNIQUE INDEX uq_mv_x ON mv_x (key_col);
REFRESH MATERIALIZED VIEW mv_x;
ANALYZE mv_x;

  • 创建时显式声明列名，让接口稳定、便于下游引用：

    sql
    CREATE MATERIALIZED VIEW mv_report (day, order_cnt, gmv) AS SELECT ...;

  • 命名统一用 mv_ 前缀，并与对应的普通视图区分。

  • 显式创建唯一索引，为并发刷新留出后路：

    sql
    CREATE UNIQUE INDEX uq_mv_x ON mv_x (key_col);

  • 用 IF NOT EXISTS 让部署脚本可重复执行：

    sql
    CREATE MATERIALIZED VIEW IF NOT EXISTS mv_x AS SELECT ...;

  • 给物化视图配与查询匹配的索引（过滤列、排序列、覆盖列），这是它相对视图的核心优势。

  • FDW 汇总场景评估远端负载：REFRESH 会拉全量，远端也要建好索引。

  • 物化视图放在独立表空间做冷热分层，避免与在线业务争抢 IO。

  • 建完立刻 ANALYZE，让优化器对物化视图有正确估算。

  • 用 pg_matviews + pg_class 做物化视图资产盘点（大小、是否填充、是否有索引）：

    sql
    SELECT m.matviewname, m.ispopulated, m.hasindexes,
       pg_size_pretty(pg_total_relation_size(m.matviewname::regclass)) AS size
FROM pg_matviews m ORDER BY pg_total_relation_size(m.matviewname::regclass) DESC;

下一节

继续学习：REFRESH MATERIALIZED VIEW

参考链接 ​