从3.2G到280M：PostgreSQL部分索引流水表瘦身实践

问题背景：亿级订单流水表的索引冗余痛点

绝大多数电商、支付类业务的订单流水表，随着业务迭代很容易突破亿级数据量。我们团队此前维护的某电商平台订单流水表，单表行数达到1.2亿行，其中95%的订单都是已完成、已取消的完结状态，仅5%的订单属于待支付、待发货的活跃状态。为了适配不同业务线的查询需求，早期我们陆续在这张表上新建了7个联合索引，总索引体积达到3.2G，带来了三个非常突出的性能问题：第一，订单写入时的索引维护开销极高，大促峰值场景下插入TPS最高只能到1200，很容易出现写入堆积；第二，磁盘占用持续上涨，全量备份耗时超过40分钟，故障恢复速度完全达不到SLA要求；第三，运营后台查询已完结订单的分页接口平均耗时超过1.2s，高并发场景下经常触发数据库慢查询告警。我们最初评估过分库分表、全表分区等改造方案，但这类方案需要修改大量业务SQL，迁移周期至少两周，业务侧风险极高，最终我们选择基于PostgreSQL的partial-index（部分索引）特性做索引瘦身，仅用2小时就完成全量上线，几乎零业务改造成本。

方案对比：三种流水表优化路径实测

我们针对亿级流水表的索引优化需求，横向对比了三种主流方案的落地效果：

传统全量索引方案：也就是我们此前使用的方案，优点是所有查询条件都能覆盖，业务完全无感知，缺点是索引冗余度超过90%，写入维护成本极高，完全无法适配高并发写入场景。
表分区方案：按照订单状态+创建时间做二级分区，优点是数据隔离性好，冷热数据可以分开存储，缺点是改造成本极高，需要修改近30条涉及跨分区查询的业务SQL，迁移过程中需要停服切流，业务侧风险不可控。
partial-index部分索引方案：仅为占比极低的活跃订单、占比极高的完结订单分别建立小范围索引，优点是几乎不需要修改业务SQL，改造量仅为分区方案的10%，索引体积缩减幅度超过90%，唯一的限制是需要业务查询的过滤条件和部分索引的裁剪规则严格对齐。最终我们选择了部分索引方案，实测上线后整体业务性能提升幅度远高于预期。

实操步骤与代码示例

整个落地过程分为三个核心步骤，所有SQL均为PostgreSQL原生语法，可以直接在生产环境执行：

步骤1：统计数据分布确认裁剪空间

首先我们需要统计订单流水表中不同状态的数据占比，确认部分索引的裁剪边界是否合理：

sql

-- 订单流水表示例结构
CREATE TABLE IF NOT EXISTS order_flow (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    status SMALLINT NOT NULL DEFAULT 0, -- 0待支付 1待发货 2已完成 3已取消
    amount DECIMAL(10,2) NOT NULL,
    create_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    update_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 统计不同状态订单占比，确认索引裁剪空间
SELECT status,count(*) as cnt,
round(count(*)::numeric / sum(count(*)) over() *100,2) as ratio
FROM order_flow GROUP BY status;

我们的实测结果显示，status<2的活跃订单占比仅为4.7%，status>=2的完结订单占比为95.3%，非常适合做部分索引裁剪。

步骤2：替换全量冗余索引为部分索引

删除此前的全量联合索引，按照活跃、完结两个场景分别建立针对性的部分索引：

sql

-- 删除原有的全量冗余联合索引，保留order_no的唯一全量索引
DROP INDEX IF EXISTS idx_order_flow_userid_status;
DROP INDEX IF EXISTS idx_order_flow_status_creatat;
DROP INDEX IF EXISTS idx_order_flow_updateat;

-- 仅为活跃订单（status<2）建覆盖查询的联合索引，适配C端用户查询待处理订单场景
CREATE INDEX idx_order_flow_active_partial ON order_flow (user_id,status,create_at)
INCLUDE (order_no,amount)
WHERE status < 2;

-- 仅为已完结订单（status>=2）建轻量分页查询索引，适配运营后台查询历史订单场景
CREATE INDEX idx_order_flow_finished_partial ON order_flow (status,create_at DESC)
INCLUDE (user_id,order_no)
WHERE status >=2;

步骤3：验证索引命中效果

执行explain语句确认业务查询可以正常命中新建的部分索引：

sql

-- 验证活跃订单查询命中部分索引
EXPLAIN ANALYZE SELECT * FROM order_flow 
WHERE user_id = 123456 AND status = 0 AND create_at > '2024-01-01';

-- 验证完结订单分页查询命中部分索引
EXPLAIN ANALYZE SELECT * FROM order_flow 
WHERE status =2 AND create_at > '2024-05-01' ORDER BY create_at DESC LIMIT 20;

上线后我们实测总索引体积从原来的3.2G缩减到280M，缩减幅度达到91%，订单写入峰值TPS从1200提升到6500，完结订单分页查询平均耗时从1.2s降到210ms，完全满足大促场景的性能要求。

避坑总结：3个高频实践坑点

我们在落地过程中踩了不少实际的坑，总结出三个必须注意的实践规则：

部分索引的过滤条件必须和业务查询条件完全等价，不能存在语义差异。很多开发者会遇到部分索引建完之后查询依然走全表扫描的问题，比如部分索引定义的裁剪条件是status < 2，但业务SQL写的是status in (0,1)，PostgreSQL的查询优化器无法识别这两个条件是等价的，就会放弃走部分索引。修正方法是通过pg_indexes系统表核对部分索引的predicate字段，确保业务查询的过滤条件和索引定义的裁剪条件完全一致，不要做语义等价改写。
全局唯一约束不能使用部分索引实现。如果业务要求订单号全局唯一，绝对不能把唯一索引建成部分索引，否则不同状态的订单可能出现重复的order_no，导致业务逻辑异常。修正方法是唯一约束类的索引不要做部分裁剪，单独保留全量唯一索引，避免出现数据一致性问题。
批量更新订单状态时要控制批次大小，避免部分索引膨胀。如果一次性批量更新100万行订单的状态从待处理改成已完结，部分索引会自动删除对应条目，但如果批量更新没有走索引，会产生大量死元组，导致索引体积快速膨胀。修正方法是批量更新状态的时候每次提交1000行，分批执行，同时调大对应表的autovacuum阈值，及时清理死元组，避免索引膨胀。整体来看，partial-index方案对于90%以上的流水表高并发查询场景都有极高的投入产出比，不需要做架构级别的重构就能拿到非常可观的降本提效收益。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

从3.2G到280M：PostgreSQL部分索引流水表瘦身实践 ​

问题背景：亿级订单流水表的索引冗余痛点 ​

方案对比：三种流水表优化路径实测 ​

实操步骤与代码示例 ​

步骤1：统计数据分布确认裁剪空间 ​

步骤2：替换全量冗余索引为部分索引 ​

步骤3：验证索引命中效果 ​

避坑总结：3个高频实践坑点 ​