PostgreSQL大表Update耗时优化及IO相关问题咨询

背景信息 #

• 表规模：commission分区表（本次操作涉及commission_93分区），总记录约2.5亿条
• 执行的Update语句：

UPDATE commission
SET    knowledge_end_date = '2024-07-31T01:47:41.053562+00:00' :: timestamptz
WHERE  ( client_id = 93
         AND NOT is_deleted
         AND knowledge_end_date IS NULL
         AND payee_email_id = '*******'
         AND ( period_end_date BETWEEN
                     '2024-04-01T00:00:00+00:00' :: timestamptz AND
                     '2024-06-30T23:59:59.999999+00:00' :: timestamptz
                OR period_start_date BETWEEN
                   '2024-04-01T00:00:00+00:00' :: timestamptz AND
                       '2024-06-30T23:59:59.999999+00:00' :: timestamptz )
         AND criteria_id IN
             (
                 '354dec74-2f1e-4413-8c3e-ac3b2ffde584' :: uuid,
                 '563f15a2-e5f8-46c0-94c3-65cec8e9f841' :: uuid,
                 '1f0626d9-5d5a-4781-8c34-2385b8c0edf0' :: uuid,
                 'e7d2fa13-383c-45ff-a9c1-55fb6c353634' :: uuid,
                 'c017efdb-7774-4374-97ba-0a4938924945' :: uuid,
                 '6a9c6d90-a1ca-4af7-8a05-393f2314367a' :: uuid,
                 '0afaf878-47e3-4845-8203-0d9dbdbe152a' :: uuid,
                 '4e459ae7-0868-4604-b4f3-62679a05db59' :: uuid,
                 '8953abd5-1881-4bf5-ba1a-34c03eeb6331' :: uuid
             ) ) 

• 执行计划：

Update on public.commission  (cost=0.70..808370.78 rows=5690 width=650) (actual time=47317.841..47317.842 rows=0 loops=1)
      Update on public.commission_93
      Buffers: shared hit=2321291 read=13061 written=261
      I/O Timings: read=39525.082
      ->  Index Scan using commission_93_client_id_is_deleted_knowledge_end_date_payee_idx on public.commission_93  (cost=0.70..808370.78 rows=5690 width=650) (actual time=0.023..198.990 rows=62497 loops=1)
            Output: commission_93.temporal_id, commission_93.knowledge_begin_date, '2024-07-31 01:47:41.053562+00'::timestamp with time zone, commission_93.is_deleted, commission_93.additional_details, commission_93.period_start_date, commission_93.period_end_date, commission_93.payee_email_id, commission_93.commission_plan_id, commission_93.criteria_id, commission_93.line_item_type, commission_93.tier_id, commission_93.amount, commission_93.client_id, commission_93.commission_snapshot_id, commission_93.context_ids, commission_93.line_item_id, commission_93.primary_kd, commission_93.secondary_kd, commission_93.secondary_snapshot_id, commission_93.show_do_nothing, commission_93.commission_date, commission_93.original_tier_id, commission_93.ctid
            Index Cond: ((commission_93.client_id = 93) AND (commission_93.is_deleted = false) AND (commission_93.knowledge_end_date IS NULL) AND ((commission_93.payee_email_id)::text = '********'::text))
            Filter: ((((commission_93.period_end_date >= '2024-04-01 00:00:00+00'::timestamp with time zone) AND (commission_93.period_end_date <= '2024-06-30 23:59:59.999999+00'::timestamp with time zone)) OR ((commission_93.period_start_date >= '2024-04-01 00:00:00+00'::timestamp with time zone) AND (commission_93.period_start_date <= '2024-06-30 23:59:59.999999+00'::timestamp with time zone))) AND (commission_93.criteria_id = ANY ('{354dec74-2f1e-4413-8c3e-ac3b2ffde584,563f15a2-e5f8-46c0-94c3-65cec8e9f841,1f0626d9-5d5a-4781-8c34-2385b8c0edf0,e7d2fa13-383c-45ff-a9c1-55fb6c353634,c017efdb-7774-4374-97ba-0a4938924945,6a9c6d90-a1ca-4af7-8a05-393f2314367a,0afaf878-47e3-4845-8203-0d9dbdbe152a,4e459ae7-0868-4604-b4f3-62679a05db59,8953abd5-1881-4bf5-ba1a-34c03eeb6331}'::uuid[])))
            Rows Removed by Filter: 61754
            Buffers: shared hit=16983

• 环境：AWS Aurora PostgreSQL 12，实例类型db.r6g.2xlarge

问题解答 #

1. 读取阶段与写入阶段Buffers差异大的原因 #

• 读取阶段：仅扫描过滤索引commission_93_client_id_is_deleted_knowledge_end_date_payee_idx筛选行，操作的是索引块，不需要加载表数据，因此shared hit仅16983。
• 写入阶段：
  1. 需要加载所有符合条件行对应的表数据块（6万多条行涉及的表页），这部分产生大量缓存命中或磁盘读取。
  2. PostgreSQL的Update采用"先删后插"逻辑，会写入新的表行版本，同时更新所有包含knowledge_end_date的索引（包括当前过滤索引），索引更新会额外消耗IO和缓存。
  3. Aurora分布式存储特性：更新操作需要同步到底层存储节点，会触发额外的块读取/缓存操作，进一步放大Buffer使用量。

2. 降低IO耗时的方法 #

• 优化索引：
  • 创建覆盖索引，将过滤所需的字段全部包含到索引中，避免回表读取表数据：

    CREATE INDEX idx_commission_93_filter ON commission_93 
    (client_id, is_deleted, knowledge_end_date, payee_email_id, criteria_id) 
    INCLUDE (period_start_date, period_end_date);
    

    这样索引扫描就能直接完成所有过滤，不需要加载表数据块，大幅减少IO。
  • 清理无用索引：如果表上有多个包含knowledge_end_date的非必要索引，Update时会逐个更新，增加IO开销，可删除此类索引。
• 分批更新：将6万多行的Update拆分为多个小批次（例如每次更新1000行），避免一次性加载大量表块，降低缓存压力和IO峰值：

  WITH batch AS (
    SELECT ctid FROM commission_93
    WHERE  client_id = 93
           AND NOT is_deleted
           AND knowledge_end_date IS NULL
           AND payee_email_id = '*******'
           AND ( period_end_date BETWEEN '2024-04-01T00:00:00+00:00'::timestamptz AND '2024-06-30T23:59:59.999999+00:00'::timestamptz
                 OR period_start_date BETWEEN '2024-04-01T00:00:00+00:00'::timestamptz AND '2024-06-30T23:59:59.999999+00:00'::timestamptz )
           AND criteria_id IN ('354dec74-2f1e-4413-8c3e-ac3b2ffde584', ...)
    LIMIT 1000
  )
  UPDATE commission_93
  SET knowledge_end_date = '2024-07-31T01:47:41.053562+00:00'::timestamptz
  FROM batch
  WHERE commission_93.ctid = batch.ctid;
  

  循环执行直到没有行被更新。
• 预热缓存：在Update前，提前用SELECT语句将目标行的表数据加载到内存缓存：

  SELECT 1 FROM commission_93
  WHERE  [与Update相同的WHERE条件]
  LIMIT ALL;
  

  这样Update时大部分数据已在缓存中，减少磁盘读取。
• 更新统计信息：执行ANALYZE commission_93，让优化器生成更准确的执行计划，避免不必要的IO操作。

3. 通过Postgres配置调整加速写入的可行性 #

• 临时关闭副本同步：
  • 会话级设置SET synchronous_commit = off;，主节点不需要等待只读副本确认写入，能减少写入延迟。但注意：如果主节点故障，可能丢失未同步的事务，仅适合非核心业务或可接受少量数据丢失的场景，不建议全局关闭。
• 调整Checkpoint参数：
  • Aurora部分checkpoint参数由AWS管控，但可调整以下参数：
    • 提高maintenance_work_mem：给checkpoint分配更多内存，降低写IO压力。
    • 设置checkpoint_completion_target = 0.9：让checkpoint平滑完成，避免IO突增。
  • 调整前需在低峰期测试，避免影响其他业务。
• 其他参数优化：
  • 提高work_mem：让排序、哈希操作在内存完成，减少临时文件IO，对本次Update收益有限，但可优化整体查询性能。
  • 禁用track_commit_timestamp：如果不需要跟踪事务提交时间，关闭该参数能减少写入开销。

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