PostgreSQL 14仅为新分区创建索引的可行性与最佳实践问询

1. 仅为新分区创建索引是否值得？ #

完全值得。你的旧分区最多14天就会被删除，给这类即将清理的分区建索引纯粹浪费CPU、IO和存储资源，还会增加删除分区时的额外开销（尽管分区删除是元数据操作，但索引占用空间的释放也需要耗时）。如果旧分区数据量较大，建索引的成本会非常可观，完全没必要为短期留存的数据做这种投入。

2. 若可行，是否需在所有现有分区都创建索引后再在表级创建？ #

不需要，反而建议先在父表创建索引，再按需给保留期内的现有分区补建索引：

• 只要在父表上创建索引，PostgreSQL 14会自动将该索引继承给后续新建的分区，无需额外操作，就能确保新分区自动带索引。
• 对于当前仍在保留期内的现有分区：如果业务查询需要用到这些分区的索引，再单独为它们创建；如果这些旧分区的查询频率极低，可直接跳过，等它们被删除即可。
• 注意：若父表未创建索引，单独给分区建的索引是独立的，后续新分区不会自动继承，所以要实现新分区自动带索引，必须在父表层面创建。

3. 若不可行，是否创建并发索引是最优方案？ #

如果业务要求所有现有分区（包括即将删除的）必须有索引（比如某些查询需覆盖全量保留数据），那只能给所有分区建索引，此时并发索引（CREATE INDEX CONCURRENTLY）是最优选择：

• 普通CREATE INDEX会锁定父表和分区，阻塞读写操作，而CREATE INDEX CONCURRENTLY不会持有排他锁，对业务影响极小。
• 注意事项：该命令不能在事务块中执行，执行时间会比普通索引长，需要监控进度；给父表执行该命令时，PostgreSQL会自动为所有现有分区创建并发索引，后续新分区也会继承该索引。

额外注意事项 #

• 若仅给部分现有分区建索引，需确保查询条件能过滤掉无索引的分区，否则查询可能因全表扫描变慢。
• 删除分区时，分区上的索引会被自动删除，无需手动清理。

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