如何在AWS Aurora Serverless v2的PostgreSQL 15大表中慢速创建索引？

1. 优先使用PostgreSQL原生CREATE INDEX CONCURRENTLY #

这是最省心的生产友好方案，Aurora Serverless v2完全兼容该特性：

• 核心优势：不会阻塞表的INSERT/UPDATE/DELETE操作，彻底避免影响生产业务。
• 执行逻辑：分两阶段扫描全表，第一阶段构建初始索引结构，第二阶段同步处理第一阶段期间产生的数据变更，因此耗时比普通CREATE INDEX长，但完全符合低干扰需求。
• 示例命令：

  CREATE INDEX CONCURRENTLY idx_mytable_target_col ON mytable (target_col);
  

• 若需进一步减慢速度、降低资源占用，可临时调低会话级内存参数（不影响全局配置）：

  SET maintenance_work_mem = '64MB'; -- 调小后索引创建速度变慢，减少资源竞争
  CREATE INDEX CONCURRENTLY idx_mytable_target_col ON mytable (target_col);
  RESET maintenance_work_mem; -- 恢复默认值
  

2. 手动分块构建索引（自定义速度控制） #

如果需要极致精细的速度控制（比如每天仅处理固定行数，耗时数周），可通过PL/pgSQL脚本分批创建部分索引，最终合并为全表索引：

CREATE OR REPLACE FUNCTION build_index_in_batches()
RETURNS void AS $$
DECLARE
  batch_size integer := 50000; -- 每批处理行数，可根据资源情况调整
  max_id integer;
  current_start integer := 0;
  current_end integer;
  temp_idx_name text;
BEGIN
  -- 获取主键范围（假设表有自增主键`id`，无主键可改用有序字段如`created_at`）
  SELECT max(id) INTO max_id FROM mytable;

  WHILE current_start < max_id LOOP
    current_end := LEAST(current_start + batch_size, max_id);
    temp_idx_name := 'idx_mytable_temp_' || current_start;

    -- 创建当前批次的部分索引，用CONCURRENTLY避免阻塞业务
    EXECUTE format(
      'CREATE INDEX CONCURRENTLY %I ON mytable (target_col) WHERE id > %s AND id <= %s',
      temp_idx_name, current_start, current_end
    );

    -- 暂停指定时长，降低资源占用（示例为暂停5分钟）
    PERFORM pg_sleep(300);

    current_start := current_end;
  END LOOP;

  -- 创建全表索引（可选，若业务可直接使用多个部分索引查询可跳过此步）
  CREATE INDEX CONCURRENTLY idx_mytable_target_col ON mytable (target_col);

  -- 清理临时批次索引
  FOR temp_idx_name IN SELECT indexname FROM pg_indexes WHERE tablename = 'mytable' AND indexname LIKE 'idx_mytable_temp_%' LOOP
    EXECUTE format('DROP INDEX %I', temp_idx_name);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

执行函数启动分批构建：

SELECT build_index_in_batches();

3. 使用pg_repack工具（低资源在线索引重建） #

pg_repack是PostgreSQL官方兼容的第三方扩展，支持在线低资源重建索引，无锁表风险：

• 先在Aurora中安装扩展：

  CREATE EXTENSION pg_repack;
  

• 通过命令行执行重建，用--jobs参数控制并发数（值越小速度越慢，资源占用越低）：

  pg_repack --dbname=your_database --table=mytable --index=idx_mytable_target_col --jobs=1
  

该工具会自动分批处理数据，对生产业务的资源干扰极小。

内容的提问来源于stack exchange，提问作者Joey Yi Zhao