PostgreSQL分区表索引：如何自定义分区上的索引命名规则？

PostgreSQL没有内置配置项可直接修改分区表自动生成索引的默认命名规则，系统默认会按partition_name + index columns + 'idx'格式生成分区索引名称。但可以通过以下方式实现你需要的父表索引名 + 分区名命名格式：

1. 手动创建并指定分区索引名称 #

如果分区数量少或新增频率低，可手动为每个分区创建自定义名称的索引：

-- 先删除系统自动生成的分区索引（若已存在）
DROP INDEX measurement_202401_city_id_peaktemp_idx;

-- 手动创建符合自定义规则的索引
CREATE INDEX foo_idx_measurement_202401 ON measurement_202401 (city_id, peaktemp);

后续新增分区时重复上述步骤即可。

2. 利用事件触发器自动重命名索引 #

通过事件触发器，可在新分区创建完成后自动将系统生成的索引重命名为自定义格式。

步骤1：创建重命名函数 #

CREATE OR REPLACE FUNCTION rename_partition_indexes()
RETURNS event_trigger AS $$
DECLARE
    partition_rec RECORD;
    parent_idx TEXT;
    idx_rec RECORD;
BEGIN
    -- 遍历刚创建的measurement父表分区
    FOR partition_rec IN 
        SELECT * FROM pg_event_trigger_ddl_commands() 
        WHERE command_tag = 'CREATE TABLE' 
        AND objid IN (SELECT inhrelid FROM pg_inherits WHERE inhparent = 'measurement'::regclass)
    LOOP
        -- 获取父表目标索引名称（foo_idx）
        SELECT idx_class.relname INTO parent_idx
        FROM pg_index idx
        JOIN pg_class idx_class ON idx.indexrelid = idx_class.oid
        JOIN pg_class parent_tbl ON idx.indrelid = parent_tbl.oid
        WHERE parent_tbl.relname = 'measurement'
          AND idx_class.relname = 'foo_idx';

        -- 找到分区下系统生成的对应索引并重命名
        FOR idx_rec IN 
            SELECT relname 
            FROM pg_class 
            WHERE relnamespace = (SELECT relnamespace FROM pg_class WHERE oid = partition_rec.objid)
              AND relkind = 'i'
              AND relname LIKE (partition_rec.objid::regclass::text || '%')
        LOOP
            EXECUTE format('ALTER INDEX %I RENAME TO %I', 
                          idx_rec.relname, 
                          parent_idx || '_' || (partition_rec.objid::regclass::text));
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

步骤2：创建事件触发器 #

CREATE EVENT TRIGGER rename_partition_idx_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION rename_partition_indexes();

注意：事件触发器需要超级用户权限，需确保函数逻辑准确，避免误操作其他索引。

3. 使用分区管理工具自动化维护 #

可借助PostgreSQL第三方分区管理工具（如pg_partman），这类工具通常支持自定义分区索引命名规则，能自动为新增分区创建符合要求的索引。

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