Postgres技术问询：使用COPY导入数据前/后创建索引哪个更高效？

对于你的场景（小时级静态表、批量COPY导入、后续无数据修改），完全应该在所有数据加载完成之后再创建索引，效率会高得多。下面具体解释原因：

• 批量构建索引的开销远低于实时维护：
  如果在COPY之前就创建了索引，Postgres会在每插入一行数据时就更新一次索引结构——这意味着百万甚至千万级别的数据行，要做百万次的索引插入操作，不仅耗时，还会产生大量的WAL日志，拖慢整个加载过程。而在数据全部加载完成后，用CREATE INDEX语句批量构建索引，Postgres会一次性扫描全表，直接生成完整的索引结构，这个过程的IO和CPU开销都要小几个数量级。

• 静态表场景下没有提前建索引的必要：
  你提到表加载完成后通常不会修改，直至被删除。这种静态数据场景下，提前建索引完全没有意义——COPY过程中根本不需要用索引来查询数据，反而会成为加载的负担。等数据全部到位后再建索引，一次性搞定就够了。

• 额外提升COPY的加载速度：
  去掉索引的负担后，COPY本身的速度也会显著提升，毕竟不需要在写入数据的同时处理索引的更新逻辑，能最大化利用Postgres的批量写入性能。

如果你的表在创建索引期间需要被访问（不过根据你的描述应该不需要），可以考虑使用CREATE INDEX CONCURRENTLY，但它的速度会比普通CREATE INDEX慢一些，所以如果表加载完成后暂时不需要对外提供服务，优先用普通的CREATE INDEX即可。

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