PostgreSQL 14.3导入4100万行数据后CREATE INDEX疑似挂起排查

DevOps 小满

2026-8-30

问题根因

pg_stat_progress_create_index 视图仅在索引构建进入核心执行阶段（基表扫描、元组排序、索引页写入）时才会上报进度，锁等待、IO阻塞、并行工作进程异常、排序阶段临时文件刷盘时都不会上报进度，这是PostgreSQL 14版本的固有机制，不是BUG。
导入过程中会话短暂显示idle是正常现象：SQL转储导入是串行执行流程，前一条命令执行完成后，客户端需要接收服务端返回的执行结果、发送下一条建索引命令，这个间隙会话状态会显示为idle，不代表进程挂死。
4100万行表建索引长时间无响应的常见触发场景：
- 锁阻塞：如果库内存在持有xml_files表锁的长事务（比如未提交的写入事务、长查询），CREATE INDEX会一直停留在锁等待队列，不会开始实际构建，自然不会有进度上报。
- 内存配置不足：如果maintenance_work_mem设置过小，btree索引排序阶段无法在内存完成，会触发磁盘临时文件排序，速度比内存排序慢10~100倍，且排序阶段进度上报频率极低。
- IO瓶颈：如果数据盘为机械盘、或者存储带宽被其他任务占满，表扫描、临时文件读写的速度会被严重拖慢。
- 字段特性问题：insert_status通常是低基数字段（仅存几个固定状态值），btree索引构建时需要做大量重复值合并，并行工作进程的效率会比高基数字段低60%以上。
- 并行工作进程异常：如果配置的并行worker数超过服务器CPU核数剩余配额，worker进程会启动失败，主进程会退化为单线程构建，速度大幅下降。

排查步骤

按优先级依次执行以下操作确认问题：

检查锁阻塞情况，执行以下SQL：

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state,
  now() - blocking.xact_start AS blocking_duration
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid
JOIN pg_catalog.pg_locks blocking ON blocked.relation = blocking.relation AND blocked.locktype = blocking.locktype AND NOT blocked.pid = blocking.pid
JOIN pg_catalog.pg_stat_activity blocking_act ON blocking.pid = blocking_act.pid
WHERE NOT blocked.granted AND blocking.granted;

如果查询返回结果，说明当前建索引任务被其他会话阻塞，优先处理阻塞源即可。

操作系统层确认进程运行状态：拿到建索引进程的PID后，执行top -p <PID>、iostat -x 2观察指标：
- 如果进程CPU使用率持续高于20%、对应磁盘有稳定的读写IO，说明索引正在正常执行，只是处于不上报进度的阶段，继续等待即可。
- 如果进程CPU使用率接近0、磁盘无对应读写，说明进程处于锁等待或者异常挂起状态。
检查临时文件使用情况，执行SQL：

SELECT pid, temp_files, temp_bytes / 1024 / 1024 / 1024 AS temp_file_size_gb
FROM pg_catalog.pg_stat_activity
WHERE query ILIKE '%CREATE INDEX index_insert_status%';

如果temp_file_size_gb数值持续上涨，说明正在执行磁盘排序，属于正常执行状态。

检查数据库运行日志：查看PostgreSQL数据目录下的log文件夹最新日志，搜索index_insert_status、worker、ERROR关键词，确认并行worker是否存在启动失败、内存不足、超时退出的问题。

处理方案

锁阻塞场景：评估阻塞会话的业务影响后，执行SELECT pg_terminate_backend(<阻塞进程PID>);释放锁，建索引任务会自动进入执行阶段。
内存/IO不足导致构建过慢场景：
1. 先终止当前运行的建索引进程：SELECT pg_terminate_backend(<建索引进程PID>);
2. 当前会话下调高维护内存：SET maintenance_work_mem = '8GB';（根据服务器剩余内存调整，最大不要超过物理内存的1/3，避免触发OOM）
3. 调整并行worker配额：SET max_parallel_maintenance_workers = 4;（根据CPU剩余核数调整，最大不要超过总核数的1/2）
4. 手动执行建索引命令即可，构建速度会提升数倍。
低基数索引优化：如果insert_status字段的唯一值少于100个，且业务仅做等值查询，可以将btree索引替换为位图索引，构建速度比btree快3~5倍，查询性能也更优。
进程挂死场景：如果确认进程无锁、无IO、无CPU占用，直接终止进程后，使用CREATE INDEX CONCURRENTLY语法手动创建索引，该模式不会持有表级排他锁，不会阻塞表的正常读写。

注意：CONCURRENTLY模式下建索引不能在事务块内执行，且构建时间比普通模式长30%左右。

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