PostgreSQL 运维实战系列，第五期：容量规划与分区策略设计

0. 前言：数据库不是无限增长的容器

前四期我们完成了生产环境搭建、高可用部署、性能调优和故障诊断。但还有两个问题经常被忽视直到最后一刻才暴露：数据库还能撑多久？数据量大了怎么办？

一个表从 1GB 增长到 1TB，查询延迟从 10ms 飙升至 10 秒，索引膨胀、VACUUM 跑不动、备份时间从小时变成天——这不是数据库的问题，这是“没有在正确的时间做正确的容量准备”的问题。

本期聚焦两个紧密关联的主题：

容量规划：回答“数据库还能用多久”“什么时候需要扩容”“预算该怎么要”
分区策略：回答“表太大了怎么拆”“数据生命周期怎么管”“维护如何自动化”

1. 容量规划：从“拍脑袋”到“预测模型”

1.1 为什么大多数 DBA 的容量规划是错的

90% 的 DBA 曾因经验不足或认知偏差踩过容量规划的“坑”——最常见的误区是“只看今天的数据量，忽略明天的增长模式”，导致扩容永远在追着故障跑。

数据库容量规划的本质是“资源天气预报”，就像天气预报需要分析气温、湿度、风向等多维数据，容量规划需要同时考虑存储、CPU、内存、I/O 的耦合关系。

数据增长不是匀速的：

线性增长：用户注册量——每天多 1000 人，稳定可预测
周期性增长：电商订单——工作日低、周末高、双十一脉冲
季节性增长：企业财务系统——月底、季末、年底各有高峰
指数增长：短视频用户行为日志——一旦业务爆发，就是噩梦

核心原则：容量规划不能靠“感觉”，必须靠“数据”。

1.2 容量规划公式：存储、CPU、内存、I/O 四维模型

存储容量：数据本体 + 索引堆栈 + WAL 日志 + 临时文件

数据的四个空间来源分别为：

表数据本体：基准可直接由 pg_total_relation_size('table_name') 捕获
索引开销：每个 B-tree 索引通常占主表数据量的 50%~100%。一个 500GB 的表拥有一组复合或二级索引，再加上几个大字段，总占用可能翻倍
WAL 日志与临时文件：归档或流复制场景中需额外预算空间，单次大查询的临时文件可能达到 GB 级
死元组膨胀：不及时 VACUUM 的表，死元组占比可能超过 20%

长期预测实施建议：

每日采集 pg_total_relation_size 和 n_tup_ins - n_tup_del 写入历史表
基于过去 30 天数据做线性回归预测未来 90 天的增长趋势
设置阈值：预测剩余空间少于 60 天用量时触发预警通知

2. 分区策略：设计比执行更重要

2.1 什么时候该分区？——误判比拖延更可怕

分区不是银弹。不少开发者对分区抱有“巨大表一分区就变快”的幻想——但实际上，分区的首要目标是改善维护体验（加速数据清理、简化 VACUUM、控制索引大小）。查询速度在不使用分区键时不降级已是成功。

什么时候该分区：

表超过 3000 万行或 50GB，维护操作（VACUUM、索引重建）耗时过长
需要按时间批量删除旧数据（每月/每季度删除过期数据）——分区能秒级 DETACH，而 DELETE 则需要数小时
查询模式天然带时间范围（WHERE created_at BETWEEN ...）或地域分组
索引本身已大到无法全部放入内存

什么时候不该分区：

总数据量在 100GB 以内且增长平稳——无端增加复杂度
小表（<10GB）分库分表是徒劳，额外的约束检查反而可能降低查询性能
应用程序无法或不方便在查询条件中带上分区键

2.2 类型对比与选型表

分区类型	语法	典型场景	优势	限制
RANGE	`PARTITION BY RANGE (created_at)`	时间序列表、日志、事件流	最自然的滚动淘汰方式，分区裁剪效果最好	分区键必须用于 WHERE，跨分区点查性能可能比哈希分区略差
LIST	`PARTITION BY LIST (region)`	按地区、状态、类型分组的数据	离散值管控很简单	只支持单列分区键，不适用范围分区
HASH	`PARTITION BY HASH (user_id)`	海量用户数据平摊，负载极度均匀	消除数据热点，均匀分布	不适用范围查询；扩容时整个集群需要人工干预重分布数据，适合预规划一次性建好足够数量的分区（如 64 或 128）

2.3 声明式分区语法速查

按日/月/季度范围分区是最常用的架构，通常搭配 created_at（时间戳）索引：

-- 创建父表
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 按月添加分区（建议自动化脚本执行）
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- 在父表上建自动应用到所有分区的索引（PG 11+）
CREATE INDEX idx_events_created_at ON events (created_at);
CREATE INDEX idx_events_user_id ON events (user_id);

跨分区唯一约束的限制：如果分区键不是主键的一部分，就无法在分区表上建立跨所有分区的全局唯一约束（例如一个全局唯一的 email 列）。在这种情况下，通常借助全局唯一索引（如果业务允许）或在应用层手动处理唯一性。

3. 分区表索引策略：本地 vs 全局的取舍

3.1 两种索引的本质区别

一个重要概念必须贯穿整个决策：表分了区后，索引怎么建？ 两种完全不同的思路是本地索引和全局索引。

本地索引：在每个分区内单独建造一个索引且结构相同，增删分区时只需操作对应索引分区，无需重建全局索引。但如果查询条件里没有分区键，就要合并多个分区索引的扫描结果，效率难以接受。
全局索引：建立在父表上的单一 B-tree 索引，覆盖全表所有分区，支持跨任意字段的唯一约束与非分区键点查，但当 DETACH 或 DROP 分区后，全局索引部分条目指向已被删除的分区，需要重建或标记为不可用。

决策检查点：每月归档有固定维护窗口的，优先本地索引；OLTP 系统几乎全部走 ID 单行点查的，非 local 不可行；跨分区查询且返回量少的，全局索引带来的单次范围扫描比扫描所有本地索引更高效。

3.2 实战对比：哪种更快？

在一份压测对比中，同样 1 亿条 sensor_data 记录：

局部索引：WHERE sensor_id=123 时耗时 840ms，索引大小 32GB
全局索引：WHERE sensor_id=123 时耗时仅 62ms，索引大小 5.2GB
但跨分区时间范围查询时，全局索引反而比局部索引慢（310ms vs 120ms）

结论：没有绝对的胜负，取舍全看业务场景——高频点查优先走全局索引，范围扫描则留本地索引。

3.3 索引维护清单

日常维护检查以下几点，避免索引成为运维黑洞：

-- 定位未使用的分区索引（idx_scan=0）
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
ORDER BY schemaname, tablename;

-- 重建膨胀严重的索引
REINDEX INDEX CONCURRENTLY idx_name;

4. pg_partman：分区运维的自动驾驶仪

4.1 为什么需要 pg_partman——手工创建分区的痛

最真实的痛感：数据量一上去，手工 CREATE TABLE（按月拆分）费神又容易出错，忘记创建下月分区会让当月数据直接插入父表，父表原本没有数据，一旦存入相当于所有查询绕过分区裁剪扫描父表（空），性能急速退化但逻辑上“能跑通”，这是致命的静默性能漏洞。pg_partman（PostgreSQL Partition Manager）就是为此设计的专用扩展，自动创建和维护基于时间或 ID 的多组分区表。

-- 安装 pg_partman（需要超级用户）
CREATE EXTENSION pg_partman;

-- 按时间分区：月粒度 + 提前创建 3 个月
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_interval => '1 month',
    p_premake => 3
);

-- 配置数据生命周期：保留 12 个月，超龄分区自动剥离
UPDATE partman.part_config 
SET retention = '12 months', 
    retention_keep_table = false,
    automatic_maintenance = 'on'
WHERE parent_table = 'public.events';

4.2 利用 pg_cron 实现自动维护周期

在 partman_config 中开启了 automatic_maintenance，只需在 cron 中每天调用一次 run_maintenance_proc，它就能完成“创建下月分区 + 剥离过期分区”整套流程：

-- 利用 pg_cron 调度（PG 14+ native）
CREATE EXTENSION pg_cron;
SELECT cron.schedule(
    'partition-maintenance',
    '0 2 * * *',   -- 每天凌晨 2 点执行
    'SELECT partman.run_maintenance_proc()'
);

4.3 零停机归档实战

最后一步，历史分区不应只有删除，归档到更廉价的存储介质也需要自动化：

-- 方法 1：零停机 DETACH（剥离但保留数据）
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;
-- 剥离后可单独 pg_dump 压缩存储，或移至只读 HDD 表空间。

-- 方法 2：分区表 + 外部表归档（pg_archive 或 postgres_fdw）
-- 将冷数据迁移到低成本的归档实例上挂载为外部表，历史查询仍可透明访问

5. 数据生命周期管理：冷热分离存储

5.1 表空间分层存储

“热数据”与“冷数据”的物理隔离原则是：把高频访问的活跃热分区放在高性能 SSD 上，历史冷分区迁移到 HDD 或压缩表中，分区表本身带来的 [DETACH + ATTACH] 操作对比浪费 CPU 和 IO。表空间是实现这一目标的底层抽象，它是存储在特定位置的一个命名空间（目录），让你可以将不同对象的 I/O 压力分散到不同磁盘上：

-- 创建 SSD 和 HDD 两个表空间
CREATE TABLESPACE fastspace LOCATION '/ssd1/pgdata';
CREATE TABLESPACE slowspace LOCATION '/hdd1/pgdata';

-- 创建分区时指定存储介质
CREATE TABLE events_2025_01 PARTITION OF events 
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
    TABLESPACE fastspace;

-- 季度迁移：将旧分区从 SSD 迁移至 HDD
ALTER TABLE events_2024_10 SET TABLESPACE slowspace;

5.2 冷数据归档的两种主流路径

独立归档库 + postgres_fdw 外部表：将过期分区迁至更廉价的归档数据库，主库通过 FDW 仍可跨库查询
对象存储归档：对极冷且极少再查询的旧数据，导出 Parquet 或压缩 CSV 存入 S3/MinIO

自动化归档脚本可用 pg_cron + DETACH 实现完全自动化运作。最重要的验收环节是：归档前后比对行数和关键字段摘要，防止静默丢失数据。

6. 分区查询优化：分区裁剪失效是最大的坑

6.1 分区裁剪原理——两个阶段如何工作

PostgreSQL 执行分区查询时会根据 WHERE 子句中的常量值判断哪些分区的 CHECK 约束不匹配，从而在规划期或执行期提前排除无关分区——这就是“分区裁剪”（partition pruning），也是分区表比单表性能更优的根本原因，可以大幅减少 I/O 和计算开销。

分区裁剪失效典型场景：

用函数包裹分区键列：WHERE date(created_at) = '2026-01-01' —— 优化器无法从函数中反推出原始列范围
JOIN 分区表与另一个子查询：WHERE created_at = (SELECT max(...) FROM ...) —— 参数直到执行时才确定
隐式类型转换：created_at 是 timestamptz，却用 DATE 类型比较，绕过索引放弃裁剪

最佳做法是显式写出完整范围或先计算后再查询，用法接近提前声明分区键的值。

6.2 检查分区裁剪是否生效

-- 在 EXPLAIN 输出中重点看这一行：
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM events 
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- ✅ 理想结果："Partitions selected: 1 out of 24"（仅扫描了 1 个目标分区）
-- ❌ 糟糕结果："Partitions selected: 24 out of 24"（全表扫描）

7. 分区维护清单与备份策略

以下操作建议纳入每周/月度巡检：

维护项	执行频率	检查方法
检查分区裁剪是否生效	每次新查询上线前	`EXPLAIN ANALYZE` 确认 selected partitions 数量
统计分区数量（建议 ≤ 1000）	每月	`SELECT count(*) FROM pg_inherits WHERE inhparent = 'parent_table'::regclass;`
无索引全分区扫描扫描量监控	每日 query log+dashboard	pg_stat_statements 筛选总行数爆炸，跨 10+ 分区的查询
监控过期滞留分区（已 DETACH 但未删除）	每月	`pg_class` 中 relkind = ‘r’ 且 relname like ‘%_old’
pg_dump 备份同时考虑分区	日常演练	对父表 dump 即可导出全部分区数据

特别提醒：容量规划与分区设计不分家

容量预测能反哺分区设计——例如，如果按天分区一天的写入量就达到 100GB，应考虑切换到按小时分区缩小单个子表大小；如果一周的数据填不满一个 1GB 的块，按季度分区或合并多个小表可能更适合。

写在最后：规划未来的能力

一个优秀的 DBA 评判标准，不在于他能在数据库宕机时多快恢复，而在于他是否在宕机前就已经做好了所有准备——提前 3 个月预测到了磁盘写满的时间，并在预警后的第一个周末完成了在线扩容。

分区策略和容量规划是这种“预防能力”的两根支柱。把分区表视为设计阶段的重要决策，让 pg_partman 跑在 cron 里自动完成每月的分区创建和归档；把容量监控固化每天的巡检脚本里，让数据库的增长变成可预测、可管理的常态。

下一期预告：DBA 的自动化运维工具箱，涵盖自动化巡检、告警收敛、混沌工程，以及借助 AI 诊断查询计划的进阶思路。

参考资料

Partitioning at Scale: Range, Hash, and Automated Maintenance – Rockdata & JusDB Team [10†L3-L14]

How to Automate PostgreSQL Partitioning with pg_partman – Cybrosys Research, 2025

SQL分区表索引策略_本地索引与全局索引对比 – php.cn, 2026

PostgreSQL分区表性能优化实战手册（附压测对比） – Alibaba Cloud Developer

postgresql历史数据如何归档最优_postgresql归档管理方案 – php.cn

postgresql磁盘占用如何持续预测_postgresql容量规划体系 – php.cn

数据库容量规划常见误区：90%DBA都踩过的5个坑 – CSDN, 2025