如何在PostgreSQL中兼顾用户自定义数据的层级与扁平视图性能？

针对PostgreSQL中百万级层级数据的双视图性能问题，以下是几种无冗余（或低冗余）的优化方案，适配不同业务场景：

1. 物化视图存储扁平数据 #

将扁平视图的计算结果提前物化存储，避免每次查询都执行多层关联，是平衡性能与数据一致性的最优选择之一。

操作示例 #

以图书层级为例，用递归CTE生成扁平数据并创建物化视图：

CREATE MATERIALIZED VIEW book_flat_view AS
WITH RECURSIVE book_hierarchy AS (
    -- 起始节点：作者
    SELECT 
        id AS author_id,
        name AS author,
        NULL::INT AS year_id,
        NULL::TEXT AS year,
        NULL::INT AS category_id,
        NULL::TEXT AS category,
        NULL::INT AS book_id,
        NULL::TEXT AS book_title
    FROM authors
    UNION ALL
    -- 关联出版年份
    SELECT 
        bh.author_id,
        bh.author,
        y.id AS year_id,
        y.year::TEXT,
        NULL::INT AS category_id,
        NULL::TEXT AS category,
        NULL::INT AS book_id,
        NULL::TEXT AS book_title
    FROM book_hierarchy bh
    JOIN years y ON y.author_id = bh.author_id
    WHERE bh.year_id IS NULL
    UNION ALL
    -- 关联图书分类
    SELECT 
        bh.author_id,
        bh.author,
        bh.year_id,
        bh.year,
        c.id AS category_id,
        c.name AS category,
        NULL::INT AS book_id,
        NULL::TEXT AS book_title
    FROM book_hierarchy bh
    JOIN categories c ON c.year_id = bh.year_id
    WHERE bh.category_id IS NULL
    UNION ALL
    -- 关联图书名称（最终节点）
    SELECT 
        bh.author_id,
        bh.author,
        bh.year_id,
        bh.year,
        bh.category_id,
        bh.category,
        b.id AS book_id,
        b.title AS book_title
    FROM book_hierarchy bh
    JOIN books b ON b.category_id = bh.category_id
    WHERE bh.book_id IS NULL
)
SELECT author, year, category, book_title
FROM book_hierarchy
WHERE book_title IS NOT NULL;

性能优化 #

• 给物化视图的常用筛选/排序字段建索引：

  CREATE INDEX idx_book_flat_author_year ON book_flat_view(author, year);
  CREATE INDEX idx_book_flat_category ON book_flat_view(category);
  

• 数据同步：用pg_cron定时刷新，或通过触发器触发增量刷新（需配合唯一索引使用CONCURRENTLY避免锁表）：

  REFRESH MATERIALIZED VIEW CONCURRENTLY book_flat_view;
  

优缺点 #

• 优点：无冗余数据，层级数据仍由原表维护，查询性能接近普通表
• 缺点：数据存在延迟，适合实时性要求不高的场景；刷新需消耗资源，需根据业务调整频率

2. 路径枚举法优化层级存储 #

给每个节点存储完整的父路径，避免扁平视图查询时的多层关联，适合固定层级结构的场景。

操作示例 #

• 在各层级表中添加path字段，通过触发器维护完整路径：
  比如图书节点的path存储为Jake A./2002/Romance，由父分类的path拼接生成。
• 直接通过路径拆分生成扁平视图：

  SELECT 
      split_part(b.path, '/', 1) AS author,
      split_part(b.path, '/', 2) AS year,
      split_part(b.path, '/', 3) AS category,
      b.title AS book_title
  FROM books b;
  

• 给path字段建GIN索引，支持快速的路径筛选（如查询某作者下的所有图书）：

  CREATE INDEX idx_book_path ON books USING GIN (string_to_array(path, '/'));
  

优缺点 #

• 优点：层级查询与扁平查询性能均衡，无冗余数据，实现简单
• 缺点：层级节点名称修改时，需更新所有子节点的path，开销较大，适合层级结构稳定的场景

3. 底层表预存储上层字段 #

如果层级深度固定且修改不频繁，可在最底层表（如图书表）中预存储所有上层层级的字段，彻底避免关联查询。

操作示例 #

• 在books表中添加author_name、publish_year、category_name字段
• 通过触发器同步更新：

  -- 作者名修改时，同步更新关联图书的author_name
  CREATE OR REPLACE FUNCTION update_book_author()
  RETURNS TRIGGER AS $$
  BEGIN
      UPDATE books
      SET author_name = NEW.name
      WHERE books.author_id = NEW.id;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trigger_update_author
  AFTER UPDATE OF name ON authors
  FOR EACH ROW EXECUTE FUNCTION update_book_author();
  

优缺点 #

• 优点：扁平视图直接查询底层表，性能最优；层级视图仍用原表关联，不受影响
• 缺点：存在少量数据冗余（仅底层表存储上层字段）；层级变动时需触发批量更新，适合修改频率低的场景

方案选择建议 #

• 实时性要求低、层级结构复杂：优先选物化视图
• 层级固定、修改少：优先选底层表预存储字段
• 层级变动频繁但查询需求高：选路径枚举法

内容的提问来源于stack exchange，提问作者Oskan