PostgreSQL 分区与维护窗口策略（Range/Hash、Autovacuum 与验证）

概述分区可以按时间或键拆分大表, 实现裁剪与维护隔离。结合Autovacuum参数与定期维护窗口, 保持统计与存储健康, 降低全表操作代价。关键实践与参数分区类型: RANGE按时间, HASH按键分布索引策略: 局部索引覆盖常用查询维护窗口: 在低峰期执行 `ANALYZE` `REINDEX` 与归档Autovacuum: 调整阈值与比例参数以适配写入吞吐示例/配置/实现CREATE TABLE orders (

id BIGSERIAL PRIMARY KEY,

customer_id BIGINT NOT NULL,

status TEXT NOT NULL,

created_at TIMESTAMPTZ NOT NULL

) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_11 PARTITION OF orders

FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

CREATE INDEX idx_orders_2025_11_cust_status_created

ON orders_2025_11 (customer_id, status, created_at DESC);

ALTER TABLE orders_2025_11 SET (

autovacuum_vacuum_scale_factor = 0.05,

autovacuum_analyze_scale_factor = 0.05

);

EXPLAIN ANALYZE

SELECT id, status FROM orders

WHERE created_at >= '2025-11-15' AND created_at < '2025-11-30'

AND customer_id = 123

ORDER BY created_at DESC

LIMIT 50;

验证分区裁剪: `EXPLAIN` 显示仅命中目标分区, 全表扫描减少索引命中: 记录返回行与扫描行比例改善Autovacuum效果: 观察表与索引膨胀与统计更新及时维护窗口: 在指定窗口执行维护, 峰值期间无显著影响注意事项分区边界需与业务时间粒度对齐定期创建与卸载过期分区以控制数据量局部索引需随新分区创建同步关注写入高峰下的Autovacuum竞争