PostgreSQL中分离单个分区索引（保留其他）的方法及替代方案

问题背景 #

我们有一张已完成分区和索引配置的表，此前通过以下高效方式构建分区索引：

• 执行create index parent_idx on only parent using (id)创建父索引
• 通过alter index parent_idx attach partition child_idx将已存在的子表索引附加到父索引，跳过创建child_idx的耗时过程

现在需要仅删除某张子表的索引，同时保留其他子表的索引，且避免重建其他子表的索引（直接删除父索引会连带删除所有子索引，不符合需求）。

解决方案 #

1. 拆分目标子索引与父索引的关联 #

执行以下SQL，将需要删除的子表索引从父索引的关联中分离，使其成为独立索引：

ALTER INDEX parent_idx DETACH PARTITION child_idx_to_remove;

该操作仅修改PostgreSQL系统元数据，不会重建索引，耗时极低。

2. 删除独立后的子索引 #

此时可以直接删除该独立索引，不会影响父索引或其他子表的索引：

DROP INDEX child_idx_to_remove;

验证操作有效性 #

可通过以下SQL确认操作结果：

-- 查看父索引关联的所有子索引
SELECT indexrelid::regclass AS child_index, indrelid::regclass AS child_table
FROM pg_index 
WHERE indisparent AND indrelid = 'parent'::regclass;

-- 查看目标子表的索引剩余情况
SELECT indexrelid::regclass AS remaining_index
FROM pg_index 
WHERE indrelid = 'child_table_to_remove'::regclass;

内容的提问来源于stack exchange，提问作者Mikhail Boyarsky