PostgreSQL建索引能否不终止仅暂停？PG13迁PG14索引构建配置疑问

索引构建任务暂停方案 #

PostgreSQL未提供原生的索引构建暂停/继续接口，但可以通过操作系统信号实现临时挂起，无需终止任务从头重建：

1. 先查询对应索引构建进程的PID：

SELECT pid, query, state FROM pg_stat_activity 
WHERE query ~* '(CREATE INDEX|REINDEX)' AND state = 'active';

2. 给目标PID发送SIGSTOP信号挂起进程：

kill -SIGSTOP <目标PID>

3. 调整完配置后，发送SIGCONT信号恢复进程运行即可从断点继续执行：

kill -SIGCONT <目标PID>

注意：挂起进程不会丢失已完成的构建进度，但进程会持续持有对应表的排他锁，阻塞所有对该表的写入操作，请勿长时间挂起避免影响业务。

索引构建核心优化配置项 #

以下是对索引构建速度影响最大的配置，可对齐PG13的原有配置调整，大规格实例可适当上调：

• maintenance_work_mem：最核心的性能参数，控制索引构建时排序、哈希操作的内存上限，避免磁盘排序带来的大幅减速。建议设置为实例总内存的1/8~1/4，单进程最大不超过8GB；如果同时运行多个索引构建任务，需控制总内存占用不超过实例可用内存的1/2避免OOM。也可在当前会话单独设置无需全局生效：SET maintenance_work_mem = '4GB';
• max_parallel_maintenance_workers：控制并行索引构建的最大进程数，建议设置为CPU核心数的1/2~2/3，最多不超过8，多进程并行可大幅提升大表索引的构建速度。
• work_mem：每个并行索引构建的worker进程会单独占用一份work_mem，建议根据并行数调整到64MB~256MB即可。
• max_wal_size、checkpoint_timeout：调大两个参数可减少checkpoint触发频率，避免频繁刷盘带来的IO波动，max_wal_size建议至少设置为10GB，大表索引构建可调整到32GB_{64GB，`checkpoint_timeout`可调整为30min}1h。
• wal_buffers：索引构建会产生大量WAL日志，建议设置为16MB~64MB，减少WAL落盘次数。
• synchronous_commit：迁移阶段业务未切流的场景下，可临时设置为off，减少WAL刷盘等待开销，索引构建完成后再恢复原有配置。

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