如何在PostgreSQL超大分区表上创建索引以最小化业务影响

背景与约束 #

• 目标为16个分区的分区表，总数据量20亿行
• 必须用CREATE INDEX CONCURRENTLY，锁表时间严格控制在几秒内
• 使用Liquibase，所有操作需设置runInTransaction=false
• 业务绝对不能停机，持续可用性优先
• 测试环境数据量/流量远低于生产，模拟结果无参考价值

可选方案 #

方案1：逐个创建分区索引并立即关联 #

-- changeset me@company.com:JIRA_ticket runInTransaction:false splitStatements:false
CREATE INDEX index ON ONLY table (columns);
CREATE INDEX CONCURRENTLY index_00 ON table_00 (columns);
ALTER INDEX index ATTACH PARTITION index_00;
CREATE INDEX CONCURRENTLY index_01 ON table_01 (columns);
ALTER INDEX index ATTACH PARTITION index_01;
-- 重复14次剩余分区操作

方案2：先创建所有分区索引，再批量关联 #

-- changeset me@company.com:JIRA_ticket runInTransaction:false splitStatements:false
CREATE INDEX index ON ONLY table (columns);
CREATE INDEX CONCURRENTLY index_00 ON table_00 (columns);
CREATE INDEX CONCURRENTLY index_01 ON table_01 (columns);
-- 重复14次剩余分区索引创建
ALTER INDEX index ATTACH PARTITION index_00;
ALTER INDEX index ATTACH PARTITION index_01;
-- 重复14次剩余索引关联操作

方案对比：业务干扰最小的选择是方案2 #

理由如下：

1. 锁操作更可控：ALTER INDEX ... ATTACH PARTITION仅需获取表的SHARE UPDATE EXCLUSIVE锁，锁持时间通常为毫秒级。方案2将所有关联操作集中在最后执行，相比方案1分散穿插的方式，减少了锁的获取次数，降低了业务与锁操作冲突的概率。
2. 索引创建阶段无额外竞争：CREATE INDEX CONCURRENTLY本身不会阻塞DML操作，方案2在批量创建索引阶段，业务可正常读写，不会因中间穿插的关联操作产生额外锁竞争。
3. 故障恢复成本更低：若某分区索引创建失败，方案2仅需重新创建该分区索引即可；方案1若在创建+关联的中间步骤失败，需清理已关联的索引再重新操作，流程更复杂。

进一步消除锁/避免停机的优化方法 #

• 错峰执行：选择业务流量最低的时段（如凌晨）执行操作，即使出现短暂锁竞争，影响范围也最小。
• 拆分关联批次：将方案2的16次关联操作拆分为2-3个批次，每批次间隔几分钟，进一步降低锁操作的集中影响。
• 实时监控指标：执行过程中监控pg_locks视图，跟踪SHARE UPDATE EXCLUSIVE锁的持有情况，同时关注CPU、IO、连接数等指标，异常时立即暂停操作。
• 配置Liquibase容错机制：在changeset中设置failOnError=false（需评估风险），结合重试功能，避免单个分区操作失败导致整个任务中断。
• 生产环境预演关联操作：选一个低流量分区，单独执行ALTER INDEX ... ATTACH PARTITION，实际测量锁持有时间，确保符合预期（通常远低于几秒）。

内容的提问来源于stack exchange，提问作者Tom Fagerland