PostgreSQL 进阶训练----PostgreSQL 分区表实战，管理海量数据

单表数据量破亿，查询从秒级拖到分钟级，VACUUM 跑到天荒地老——这不是段子，是无数 DBA 的真实噩梦。PostgreSQL 分区表，就是那把切开大表的手术刀。

一、为什么你必须关心分区表？

当单表数据量逼近甚至超过数据库物理内存时，传统优化手段开始失效：索引救不了全表扫描，查询优化器也无能为力。

分区表的核心思路极其朴素：把一张逻辑上的大表，按规则切成多张物理上的小表。查询时，优化器自动排除无关分区——这叫"分区裁剪"（Partition Pruning）。实测数据显示，按月分区后，时间范围查询从 15 秒降到 0.3 秒，不是夸张，是常规操作。

PostgreSQL 10 引入的声明式分区是官方主推方案，语法简洁、管理方便，彻底取代了早期繁琐的继承式分区。

二、三种分区策略，选对是关键

选型铁律：分区键必须是高频查询的 WHERE 条件列。 选错了，优化器无法裁剪，反而比不分区更慢。

三、实战：按月分区订单表

以电商订单表为例，数据量 5 亿+，按 order_date 范围分区。

第一步：创建父表（不存数据）

sqlCREATE TABLE orders (
    order_id   BIGSERIAL,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (order_id, order_date)  -- 必须包含分区键
) PARTITION BY RANGE (order_date);

⚠️ 坑点：主键必须包含分区键。 这是 PostgreSQL 的硬性约束，因为跨分区无法保证全局唯一。

第二步：创建分区

sqlCREATE TABLE orders_202501 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_202502 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

区间规则是左闭右开 [t1, t2)，下边界包含，上边界不包含。

第三步：插入数据，自动路由

sqlINSERT INTO orders (customer_id, order_date, amount)
VALUES (1001, '2025-01-15', 299.00);
-- 自动落入 orders_202501，应用层完全无感知

第四步：建索引

sql-- 全局索引，自动应用到所有分区（PG11+）
CREATE INDEX idx_orders_date ON orders (order_date);

-- 唯一索引同样必须包含分区键
CREATE UNIQUE INDEX idx_orders_customer ON orders (customer_id, order_date);

四、分区维护：增删改查全指南

删除旧数据的正确姿势：不要 DELETE，直接 DROP PARTITION。 DELETE 产生大量死元组，VACUUM 开销巨大；DROP 分区是瞬间释放磁盘空间，零额外开销。

建议写个 cron 每月自动建下月分区：

bash#!/bin/bash
NEXT=$(date -d "+1 month" +"%Y_%m")
psql -c "CREATE TABLE orders_${NEXT} PARTITION OF orders
    FOR VALUES FROM ('$(date -d "+1 month" +"%Y-%m-01")')
    TO ('$(date -d "+2 months" +"%Y-%m-01")');"

五、避坑清单

1. 分区键选错 = 白干。 查询不走分区键，全表扫描，性能反而更差。
2. 分区数量别贪多。 OLTP 场景建议几十到几百个，太多会拖慢查询规划。
3. 每个分区建议几百 GB 以内。 太大则裁剪效果打折，太小则管理成本飙升。
4. 一定要跑 ANALYZE。 分区表的统计信息需要定期更新，否则优化器可能选错执行计划。
5. 默认分区（DEFAULT）是安全网。 PG11+ 支持 DEFAULT 分区，捕获所有不匹配的数据，避免插入报错。

写在最后

分区表不是银弹，但它是处理海量数据最确定性的手段。核心就一句话：让查询只扫该扫的数据，让维护只动该动的分区。 选对分区键，管好分区数，你的大表就不再是负担，而是可控的资产。