pgsql-30 分区表技术

2025-09-25 约 1822 字预计阅读 4 分钟

1. 概述

当单表数据量达到数千万甚至上亿行时，会遇到查询变慢、VACUUM 耗时长、历史数据难清理等问题。**分区（Partitioning）**把一张逻辑大表按规则拆成多个物理子表，好处是：

分区裁剪（Partition Pruning）：查询只扫描相关分区，跳过无关数据。
维护高效：删除历史数据用 DROP/DETACH 整个分区（秒级），而非慢速 DELETE。
VACUUM/索引更小更快：每个分区独立维护。
可分层存储：冷分区放慢盘/归档。

PostgreSQL 10 起支持声明式分区（Declarative Partitioning），语法简洁，此前需用表继承手动实现。

对比 MySQL：MySQL 也有 PARTITION BY 分区，但有较多限制（分区键必须包含在所有唯一键里、不支持外键等）。PG 的声明式分区更灵活、社区还有 pg_partman 做自动化。注意分区解决的是"单机大表"，真正的"分库分表"（跨机器水平拆分）在 PG 里用 Citus 扩展实现。

2. 三种分区类型

2.1 范围分区（Range）—— 最常用

按值的范围划分，典型是按时间（日志、订单、指标）。

-- 声明分区表（按 created_at 范围分区）
CREATE TABLE orders (
    id         BIGSERIAL,
    user_id    BIGINT,
    amount     NUMERIC(12,2),
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建各月分区
CREATE TABLE orders_2026_06 PARTITION OF orders
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE orders_2026_07 PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- 默认分区：接收不匹配任何分区的数据（可选）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

⚠️ 分区键（这里 created_at）必须包含在主键/唯一约束中——这是分区表最常见的限制，PG 和 MySQL 都如此。所以上面主键要用 (id, created_at)。

2.2 列表分区（List）

按离散值集合划分，典型是按地区/类型/租户。

CREATE TABLE sales (
    id BIGSERIAL, region TEXT, amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE sales_cn PARTITION OF sales FOR VALUES IN ('beijing','shanghai','guangzhou');
CREATE TABLE sales_us PARTITION OF sales FOR VALUES IN ('newyork','losangeles');

2.3 哈希分区（Hash）

按哈希值均匀打散，用于数据无天然范围、只想均摊负载的场景。

CREATE TABLE events (id BIGINT, data JSONB) PARTITION BY HASH (id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

3. 分区裁剪（性能关键）

查询条件命中分区键时，优化器只扫描相关分区。

-- 只会扫描 orders_2026_07 一个分区
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-07-10' AND created_at < '2026-07-20';
-- 计划中只出现该分区，其余被 pruning 掉

确保 enable_partition_pruning = on（默认开）。
查询必须带分区键条件才能裁剪；不带分区键的查询会扫描所有分区（反而更慢）。这是分区键选择的关键——要选最常出现在 WHERE 里的列。

4. 索引与约束

-- 在父表上建索引，会自动在每个（含未来）分区上创建
CREATE INDEX idx_orders_user ON orders (user_id);

-- 主键必须包含分区键
ALTER TABLE orders ADD PRIMARY KEY (id, created_at);

PG 11+ 支持父表建索引自动传播到分区。
全局唯一约束若不含分区键则无法跨分区保证唯一——这是设计难点。

5. 分区维护

5.1 增删分区（历史数据管理利器）

-- 挂载已有表为分区（如预先建好、导入数据后再 ATTACH）
ALTER TABLE orders ATTACH PARTITION orders_2026_08
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- 卸载分区（变回普通表，数据保留，可归档）
ALTER TABLE orders DETACH PARTITION orders_2026_06;

-- 直接删除旧分区（秒级清理历史数据，远快于 DELETE）
DROP TABLE orders_2026_06;

这是分区最大的运维价值：清理"3 个月前的数据"只需 DROP 对应分区，无需扫描 + DELETE + VACUUM。

5.2 自动化：pg_partman

手动按月建分区易遗漏。生产常用 pg_partman 扩展自动创建未来分区、清理过期分区，配合 pg_cron 定时执行。

CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'range', 'monthly');

6. 什么时候该分区？什么时候该分库分表？

方案	解决什么	适用
不分区（单表+索引）	常规	千万级以下
PG 分区表	单机大表管理、历史清理	单表上亿、有天然分区键（时间）
Citus 分布式	跨机器水平扩展	超大规模、单机放不下

分区不是"数据量大就要上"——分区键选不好（查询不带分区键）反而变慢。
分区解决单机的管理效率，不解决单机容量/算力上限；那是 Citus（分库分表）的领域。

7. 面试高频问答

Q1：分区表能带来什么好处？ 分区裁剪减少扫描量、按分区独立维护（VACUUM/索引更小）、用 DROP/DETACH 秒级清理历史数据、冷热分层存储。

Q2：PostgreSQL 有哪几种分区？分别适合什么？ 范围（按时间等连续值，最常用）、列表（按离散值如地区/租户）、哈希（无天然范围时均摊负载）。

Q3：分区键怎么选？ 选最常出现在查询 WHERE 条件里的列，才能触发分区裁剪；否则查询扫全部分区反而更慢。分区键还必须包含在主键/唯一约束中。

Q4：分区表和分库分表有什么区别？ 分区是单机内把大表拆成多个子表，解决管理效率；分库分表是把数据分散到多台机器，解决容量和算力上限。PG 分库分表用 Citus 扩展实现。

Q5：如何高效清理历史数据？ 分区表直接 DROP/DETACH 对应的旧分区（秒级），远优于 DELETE 大量行（慢且产生大量死元组需 VACUUM）。

8. 下一步

学习连接池与连接管理，优化高并发下的连接开销。

目录