如何提升1TB表的BTREE索引创建速度？

针对你在1TB测试表上创建BTree索引时CPU、IOPS利用率偏低的问题，以下是可优先尝试的优化点：

• 检查RDS存储的IOPS配置与突发状态
  虽然实例标称16000 IOPS，但实际受限于存储类型的配置逻辑：

  • 若使用gp2存储，1TB容量对应的基准IOPS为3000，且依赖突发额度。可在CloudWatch中查看BurstBalance指标，若该值过低，说明突发IO已耗尽，IOPS会降到基线水平。此时建议切换为gp3存储并手动配置16000基准IOPS，摆脱对突发额度的依赖。
  • 确认RDS控制台中存储的IOPS配置是否生效，gp3的IOPS需手动设置，默认值可能远低于实例标称值。

• 最大化并行索引创建的CPU利用率
  你已设置max_parallel_workers=8，但维护操作的并行数由max_parallel_maintenance_workers控制（默认值为2）。结合2vCPU的实例规格，可将该参数调整为2，然后在创建索引时显式指定并行数：

  CREATE INDEX idx_your_column ON your_table USING BTREE (your_column) WITH (parallel_workers = 2);
  

  这会让PostgreSQL利用多CPU核处理索引创建，直接提升CPU利用率。

• 优化maintenance_work_mem参数
  当前设置为2GB，对于无其他查询的8GB内存实例，可进一步调高至4GB（实例内存的50%），让索引创建时的排序操作更多在内存中完成，大幅减少磁盘IO开销。注意RDS对该参数有上限限制，可直接在参数组中调整。

• 调整索引创建的存储参数
  针对测试环境的只读表，可通过以下方式降低IO负载：

  • 设置fillfactor=100（默认90），减少索引的磁盘占用：

    CREATE INDEX idx_your_column ON your_table USING BTREE (your_column) WITH (fillfactor = 100);
    

  • 若测试表可接受临时数据丢失风险，可先将表转为UNLOGGED（跳过WAL日志写入），创建索引后再转回普通表：

    ALTER TABLE your_table SET UNLOGGED;
    CREATE INDEX idx_your_column ON your_table USING BTREE (your_column);
    ALTER TABLE your_table SET LOGGED;
    

• 确认表的统计信息与碎片状态
  克隆快照后的表可能存在统计信息不全的情况，先执行ANALYZE your_table;更新统计信息，确保PostgreSQL能选择最优的索引创建策略。同时检查表的碎片率：

  SELECT relname, n_dead_tup, pg_table_size(relid)/1024/1024 AS table_size_mb 
  FROM pg_stat_user_tables WHERE relname = 'your_table';
  

  若死元组占比过高，先执行VACUUM your_table;清理碎片，减少索引创建时的IO扫描量。

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