Aurora Postgres Serverless存储过程执行中断问题排查求助

在拥有2000万条记录的versions表上执行PL/pgSQL存储过程时，执行至约56万条记录处意外中断。当前环境为Aurora Postgres Serverless v2（配置6 ACUs，对应12GB内存），需求是批量替换text列中HTML内容的指定URL模式，对性能无要求但求逻辑简单。需排查中断是代码疏漏还是需借助监控工具定位，同时确认当前逐行更新的实现是否过于简陋。

存储过程代码：

create or replace procedure schema_v2_update_href_again()
language plpgsql
as $$
declare
  max_id bigint;
begin

  select max(version_id) 
  from versions
  into max_id;
  
  for id in 0..max_id loop
    update versions
    set content = REGEXP_REPLACE(content, 'href="./US/eng/1/article/1/', 'href="./US/eng/1/article/', 'g')
    where version_id = id;
    
    if id % 10000 = 0 then
      raise notice 'id: %', id;
      commit;
    end if;

  end loop;

end; $$;

versions表结构：

create table versions(
  version_id bigserial primary key,
  group_address entity_address not null default (0, 0),
  part_id bigint not null references parts(part_id),
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  content_type smallint not null,
  content text not null,
  root_entity_id bigint,
  root_entity_table table_name
);

create index on versions using hash (content);
create index on versions (part_id);

代码层面问题 #

1. 无效循环与资源浪费：version_id是bigserial类型，默认从1自增，但循环从0开始，会执行一次无意义的UPDATE（无version_id=0的记录）。若表中存在version_id不连续的情况（如记录被删除），循环会对大量不存在的ID执行空操作，持续消耗资源，可能触发环境的超时或资源限制。
2. 事务提交逻辑缺陷：每10000个ID提交一次事务，但这里的计数是按ID顺序而非实际处理的有效记录数。若某段ID范围内无有效记录，会出现空提交；反之，若某段ID内有效记录过多，未提交的事务可能堆积日志，引发磁盘IO瓶颈或事务超时。
3. 单条更新的稳定性风险：逐行执行UPDATE会产生大量细碎的事务日志，Aurora Serverless的存储或IO资源可能因持续高负载触发自动限流，导致连接中断。

监控层面排查 #

需查看Aurora的日志系统（如AWS CloudWatch中的PostgreSQL日志、Aurora性能日志），重点排查以下信息：

• 连接中断类错误：如connection terminated、idle_in_transaction_session_timeout
• 资源限制类错误：如out of memory、WAL write error
• 锁或事务超时：如lock timeout、transaction timeout

当前实现确实过于简陋，核心问题包括：

• 效率极低：2000万条记录需执行2000万次单条UPDATE，即使对性能无要求，长时间运行也极易触发环境的超时、资源限制等问题。
• 无效操作过多：遍历0到max_id的所有ID，包含大量不存在的记录，完全浪费计算资源。
• 事务控制不合理：按ID计数提交事务，无法对应实际处理的记录量，可能导致事务日志堆积或空提交。

方案1：批量处理有效记录 #

避免遍历无效ID，按批次处理实际存在的记录，减少资源消耗与中断风险：

create or replace procedure schema_v2_update_href_again()
language plpgsql
as $$
declare
  batch_size integer := 10000;
  current_max_id bigint := 0;
  next_batch_start bigint;
begin
  loop
    -- 获取下一批次的起始ID
    select min(version_id)
    from versions
    where version_id > current_max_id
    into next_batch_start;

    exit when next_batch_start is null;

    -- 批量更新当前批次的记录
    update versions
    set content = REGEXP_REPLACE(content, 'href="./US/eng/1/article/1/', 'href="./US/eng/1/article/', 'g')
    where version_id between next_batch_start and next_batch_start + batch_size - 1;

    commit;
    raise notice 'Processed up to ID: %', next_batch_start + batch_size - 1;
    current_max_id := next_batch_start + batch_size - 1;
  end loop;
end; $$;

方案2：单次全量更新（若允许） #

如果所有记录都需要替换目标URL模式，可直接执行单次UPDATE，无需存储过程：

update versions
set content = REGEXP_REPLACE(content, 'href="./US/eng/1/article/1/', 'href="./US/eng/1/article/', 'g');

注：2000万条记录的单次更新会产生大量WAL日志，若Aurora Serverless资源不足，可按part_id或created_at拆分批次执行。

额外建议 #

• 正则校验：确认正则表达式是否匹配预期内容，若需匹配字面量的./，当前写法正确；若需匹配任意字符加/，需将.转义为\.。
• 日志分析：优先通过Aurora日志定位中断的具体错误，再针对性调整代码或配置。
• 索引优化：当前content字段的哈希索引对本次更新无帮助，可考虑临时删除以减少更新时的索引维护开销（更新完成后重建）。

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