PostgreSQL无需客户端保持连接的后台索引创建特性问询

现有支持特性说明 #

PostgreSQL 12及以上版本已经有成熟的生态能力支持完全脱离客户端连接的后台索引创建，不需要依赖客户端会话保持。 #

方案1：使用官方pg_cron扩展（最稳定推荐） #

pg_cron是PostgreSQL官方团队维护的定时任务扩展，任务完全由数据库后台进程执行，和客户端会话完全解耦：

• 安装配置步骤：
  1. 安装对应版本的pg_cron软件包，修改postgresql.conf配置：

     shared_preload_libraries = 'pg_cron'
     cron.database_name = 'your_database_name'
     

  2. 重启数据库生效后，创建扩展并授权：

     CREATE EXTENSION IF NOT EXISTS pg_cron;
     GRANT USAGE ON SCHEMA cron TO your_operation_user;
     

• 提交后台建索引任务：

  -- 提交立即执行的建索引任务，推荐加CONCURRENTLY避免锁表
  SELECT cron.schedule(
    'online_index_creation',
    NOW(),
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_table_column ON your_target_table(target_column);'
  );
  

• 任务查询方式：
  • 查看待执行任务：SELECT * FROM cron.job;
  • 查看历史执行结果：SELECT * FROM cron.job_run_details ORDER BY start_time DESC;

方案2：使用pg_background扩展（轻量单次任务适用） #

适合不需要定时能力，仅需要提交单次异步后台任务的场景：

• 安装扩展后直接提交任务：

  CREATE EXTENSION IF NOT EXISTS pg_background;
  SELECT background_worker_submit('CREATE INDEX CONCURRENTLY idx_table_column ON your_target_table(target_column);');
  

• 提交后任务直接在数据库独立后台进程运行，客户端断开无任何影响，可通过SELECT * FROM background_worker_status();查询执行进度。

方案3：无扩展临时解决方案 #

如果没有权限安装数据库扩展，可以用操作系统层nohup命令实现脱离终端的执行：

nohup psql -U your_username -d your_database -c "CREATE INDEX CONCURRENTLY idx_table_column ON your_target_table(target_column);" > index_creation.log 2>&1 &

该方案会将psql进程挂到操作系统后台运行，关闭当前SSH/终端会话不会中断执行，所有执行日志会写入index_creation.log文件供后续排查。 #

通用注意事项 #

• 生产环境创建索引必须加CONCURRENTLY参数，避免锁表阻塞业务正常读写，仅会比普通建索引速度慢10%~30%。
• 提交任务前可先查询目标表大小预估执行时间：SELECT pg_size_pretty(pg_total_relation_size('your_target_table'));
• 执行过程中可随时查询创建进度：SELECT * FROM pg_stat_activity WHERE query ILIKE '%CREATE INDEX%' AND state = 'active';

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