PostgreSQL / 系统课时
在线补建分区索引
在线补建分区索引
大分区需要新增索引时,直接在父表递归建立可能影响持续写入。本课先创建父索引定义,再逐个叶子并发建立并挂接,用有效性检查确认全树完成,同时保留中断后能恢复的操作顺序。
学习目标
- 区分父索引定义与叶子上的实际索引
- 逐个并发建立并挂接现有分区索引
- 检查无效索引与未完成的挂接步骤
准备两个现有分区
下面在 PostgreSQL 18 的独立练习库、psql 自动提交模式执行。表名只用于本课,两个分区各插入一行;小数据用于验证命令路径,不用于证明生产耗时。
CREATE TABLE index_orders_lab (day date NOT NULL, customer_id bigint, amount numeric)
PARTITION BY RANGE (day);
CREATE TABLE index_orders_lab_jan PARTITION OF index_orders_lab
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE index_orders_lab_feb PARTITION OF index_orders_lab
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO index_orders_lab VALUES ('2026-01-10', 7, 100), ('2026-02-10', 7, 200);
-- ONLY 先建立父索引定义,避免递归扫描现有叶子。
CREATE INDEX index_orders_lab_customer_idx ON ONLY index_orders_lab (customer_id);
SELECT indisvalid FROM pg_index
WHERE indexrelid = 'index_orders_lab_customer_idx'::regclass;预期结果:表与索引创建成功,父索引 indisvalid 为 f。它此时未覆盖全部叶子,不能只看 CREATE INDEX 成功就宣布上线完成。
在叶子上分别建立和挂接
-- CONCURRENTLY 不能放进 BEGIN,也不要让迁移框架包裹整个脚本。
CREATE INDEX CONCURRENTLY index_orders_lab_jan_customer_idx
ON index_orders_lab_jan (customer_id);
ALTER INDEX index_orders_lab_customer_idx
ATTACH PARTITION index_orders_lab_jan_customer_idx;
CREATE INDEX CONCURRENTLY index_orders_lab_feb_customer_idx
ON index_orders_lab_feb (customer_id);
ALTER INDEX index_orders_lab_customer_idx
ATTACH PARTITION index_orders_lab_feb_customer_idx;
SELECT indisvalid FROM pg_index
WHERE indexrelid = 'index_orders_lab_customer_idx'::regclass;预期结果:两个索引与两次挂接成功,父索引最终 indisvalid 为 t。PostgreSQL 18 不支持直接对分区父表使用 CREATE INDEX CONCURRENTLY;ONLY 与挂接的行为见 CREATE INDEX 参考。
把定义一致性纳入验收
挂接要求索引定义匹配,不是列名看起来相同即可。访问方法、表达式、排序、operator class、INCLUDE 列及部分索引谓词都应核对。生产脚本从一份受控定义生成各叶子 DDL,避免手工复制产生差异。
SELECT i.indexrelid::regclass AS index_name, i.indisvalid, i.indisready,
pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index i
WHERE i.indrelid IN (
'index_orders_lab'::regclass,
'index_orders_lab_jan'::regclass,
'index_orders_lab_feb'::regclass
)
ORDER BY i.indexrelid::regclass::text;预期结果:本例返回 3 行,均 valid、ready,定义中的目标表不同而索引键相同。另检查 pg_inherits 的索引父子关系,确认子索引确实挂接,不能把两个孤立的叶子索引当成完整父索引。
处理等待与失败残留
并发建立通常允许正常写入,但需要多阶段扫描,也可能等待旧事务结束,并消耗额外 I/O。它不等于零锁、零影响;记录开始时间、目标叶子、进度和业务延迟。同一叶子不要同时提交多个并发索引建立任务。
失败后先查 pg_index 和服务端错误,识别具体无效索引,再决定删除重建或继续后续步骤。CREATE INDEX IF NOT EXISTS 只判断同名对象是否存在,不会验证它有效或定义正确,不能拿它掩盖上次失败。
让维护脚本知道已经完成到哪一步
任务记录至少包含父索引定义、每个叶子的建立状态、挂接状态和最后校验时间。逐个处理可降低同时扫描所有大分区的压力;是否并行取决于磁盘和业务负载预算。
新增分区也必须进入索引覆盖检查。唯一约束还涉及分区键是否包含在约束列中,不能把普通索引流程直接当作任意全局唯一索引实现。最后用代表性查询检查索引选择与实际读取量;小表选择顺序扫描本身并不表示索引建立失败。