想让PostgreSQL查询快到飞起？分区表、物化视图、并行查询这三招灵不灵？

---

url: /posts/1fee7afbb9abd4540b8aa9c141d6845d/
title: 想让PostgreSQL查询快到飞起？分区表、物化视图、并行查询这三招灵不灵？
date: 2025-10-23T03:20:31+08:00
lastmod: 2025-10-23T03:20:31+08:00
author: cmdragon

summary:
分区表通过将大表拆分为小表，优化查询性能、数据维护和存储分层。PostgreSQL支持范围、列表和哈希分区。声明式分区步骤包括创建分区表、分区、索引及验证数据路由。分区维护涉及添加、删除和detach分区。分区剪枝自动跳过无关分区，提升查询效率。物化视图通过存储查询结果加速查询，需手动刷新。并行查询利用多CPU加速大表扫描、聚合和连接，但需函数并行安全。常见报错包括无匹配分区、并发刷新需唯一索引及并行查询未生效。

categories:

• postgresql

tags:

• 基础入门
  • 分区表
• 物化视图
• 并行查询
• PostgreSQL
• 查询优化
• 数据管理
• 性能优化

---

扫描二维码关注或者微信搜一搜：编程智域 前端至全栈交流与成长

发现1000+提升效率与开发的AI工具和实用程序：https://tools.cmdragon.cn/

分区表：大规模数据的高效管理

1.1 分区表概述

分区表是将逻辑上的大表拆分为物理上的小表的技术。当表的数据量超过服务器内存时，分区表能解决以下痛点：

• 查询性能优化：仅扫描相关分区（比如查2023年数据时，跳过2020年的分区）；
• 数据维护高效：删除旧数据只需 drop 分区（而非逐条删除）；
• 存储分层：将冷数据（如5年前的记录）存到廉价存储，热数据存到高速存储。

PostgreSQL 支持三种分区方式：

1. 范围分区（Range）：按连续范围划分（如日期、ID）；
2. 列表分区（List）：按离散值划分（如地区、状态）；
3. 哈希分区（Hash）：按哈希值模运算划分（如将用户ID均匀分到10个分区）。

1.2 声明式分区的实现步骤

声明式分区是 PostgreSQL 10+ 推荐的方式（无需手动写触发器），以按日期分区的测量表为例：

步骤1：创建分区表（父表）

-- 创建分区表，按logdate（日期）范围分区
CREATE TABLE measurement (
    city_id     int NOT NULL,
    logdate     date NOT NULL,
    peaktemp    int,
    unitsales   int
) PARTITION BY RANGE (logdate); -- 分区方法+分区键

步骤2：创建分区

每个分区对应一个时间范围，上界是排他的（如FROM '2023-01-01' TO '2023-02-01'包含1月但不包含2月1日）：

-- 创建2023年1月的分区
CREATE TABLE measurement_202301 PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- 创建2023年2月的分区（指定高速表空间）
CREATE TABLE measurement_202302 PARTITION OF measurement
FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
TABLESPACE fast_tablespace;

步骤3：创建索引

在父表上创建索引会自动同步到所有分区：

-- 对分区键logdate创建索引（加速查询与数据路由）
CREATE INDEX ON measurement (logdate);

步骤4：验证数据路由

插入数据时，PostgreSQL 会自动将行路由到对应分区：

INSERT INTO measurement VALUES (1, '2023-01-15', 25, 100); -- 自动存入measurement_202301
INSERT INTO measurement VALUES (2, '2023-02-20', 28, 150); -- 自动存入measurement_202302

1.3 分区维护：添加、删除与 Detach

1.3.1 删除旧分区

直接 drop 分区即可快速删除大量数据（无需逐行删除）：

-- 删除2023年1月的分区（瞬间完成）
DROP TABLE measurement_202301;