PostgreSQL大表插入性能优化及IO利用率过高问题排查

背景 #

通过分块INSERT语句将tmp_details表数据插入到details表，同时做数据修改，但查询执行耗时极长。当前指标：读IOPS达2500+，写IOPS约100，IO利用率100%，CPU利用率不足10%，RAM利用率低于40%，使用ctid进行200万行分块插入。

服务器配置 #

• PostgreSQL版本：15.6
• 内存：32 GB
• CPU核心数：16
• 磁盘：500 GB SSD
• 操作系统：Linux Ubuntu 22.04

PostgreSQL配置 #

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

表详情 #

表中uniquekey、key_priority_radcs、key_priority_rtdps、is_processed、key_priority_ratrs字段已建立索引。因JOIN产生重复行，使用了DISTINCT ON子句。尝试过ctid分1000万行插入，但每次迭代需全量扫描C、D表，耗时仍久，改为一次性插入6000万行后提交。希望后端应用并行执行，但当前IO饱和无意义。

插入查询 #

EXPLAIN
INSERT
    INTO
    cbsupi.source_cbsupi_details (codglacct,
    refusrno,
    key_priority_radcs,
    recon_created_date,
    dattxnposting,
    status,
    uniquekey,
    coddrcr,
    cbsacqiss,
    codacctno,
    amttxnlcy,
    acnotrim,
    priority_no,
    rrn,
    recon_updated_date,
    recon_date_1_to_2,
    recon_date_1_to_3,
    reconciliation_date_time ) (
    SELECT
        DISTINCT ON
        (A.uniquekey) A.codglacct,
        A.refusrno,
        A.key_priority_radcs,
        A.recon_created_date,
        A.dattxnposting,
        A.status,
        A.uniquekey,
        A.coddrcr,
        A.cbsacqiss,
        A.codacctno,
        A.amttxnlcy,
        A.acnotrim,
        A.priority_no,
        A.rrn,
        '2025-01-07 19:50:41' AS recon_updated_date,
        CASE
            WHEN C.key_priority_rtdps IS NOT NULL THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS recon_date_1_to_2,
        CASE
            WHEN D.key_priority_ratrs IS NOT NULL THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS recon_date_1_to_3,
        CASE
            WHEN (C.key_priority_rtdps IS NOT NULL
                AND D.key_priority_ratrs IS NOT NULL) THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS reconciliation_date_time
    FROM
        cbsupi.source_cbsupi_tmp_details A
    LEFT JOIN switchupi.source_switchupi_tmp_details C ON
        (A.key_priority_radcs = C.key_priority_rtdps)
    LEFT JOIN npciupi.source_npciupi_tmp_details D ON
        (A.key_priority_radcs = D.key_priority_ratrs)
    WHERE
        A.is_processed IS NULL ) ON
    CONFLICT (uniquekey) DO
UPDATE
SET
    recon_updated_date = EXCLUDED.recon_updated_date,
    recon_date_1_to_3 = EXCLUDED.recon_date_1_to_3,
    key_priority_radcs = EXCLUDED.key_priority_radcs,
    status = EXCLUDED.status,
    reconciliation_date_time = EXCLUDED.reconciliation_date_time,
    codacctno = EXCLUDED.codacctno,
    amttxnlcy = EXCLUDED.amttxnlcy,
    recon_date_1_to_2 = EXCLUDED.recon_date_1_to_2,
    rrn = EXCLUDED.rrn,
    codglacct = EXCLUDED.codglacct,
    refusrno = EXCLUDED.refusrno,
    dattxnposting = EXCLUDED.dattxnposting,
    coddrcr = EXCLUDED.coddrcr,
    cbsacqiss = EXCLUDED.cbsacqiss,
    acnotrim = EXCLUDED.acnotrim,
    priority_no = EXCLUDED.priority_no;

查询执行计划 #

"QUERY PLAN"
Insert on source_cbsupi_details  (cost=72270111.44..73213761.44 rows=0 width=0)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: source_cbsupi_details_pkey
"  ->  Subquery Scan on ""*SELECT*""  (cost=72270111.44..73213761.44 rows=62910000 width=811)"
        ->  Unique  (cost=72270111.44..72584661.44 rows=62910000 width=823)
              ->  Sort  (cost=72270111.44..72427386.44 rows=62910000 width=823)
                    Sort Key: a.uniquekey
                    ->  Hash Left Join  (cost=10739152.00..50771187.50 rows=62910000 width=823)
                          Hash Cond: (a.key_priority_radcs = d.key_priority_ratrs)
                          ->  Hash Left Join  (cost=5337191.00..25537830.00 rows=62910000 width=800)
                                Hash Cond: (a.key_priority_radcs = c.key_priority_rtdps)
                                ->  Seq Scan on source_cbsupi_tmp_details a  (cost=0.00..2092124.00 rows=62910000 width=767)
                                      Filter: (is_processed IS NULL)
                                ->  Hash  (cost=4118441.00..4118441.00 rows=60000000 width=33)
                                      ->  Seq Scan on source_switchupi_tmp_details c  (cost=0.00..4118441.00 rows=60000000 width=33)
                          ->  Hash  (cost=4124101.00..4124101.00 rows=62910000 width=33)
                                ->  Seq Scan on source_npciupi_tmp_details d  (cost=0.00..4124101.00 rows=62910000 width=33)
JIT:
  Functions: 24
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"

补充说明 #

单独执行SELECT语句仅需265秒，但INSERT语句耗时超1小时，怀疑是单次提交产生过多日志导致。想了解：开启自动提交是否可行？是否存在无需全表遍历的分块插入方式？

核心问题 #

1. 如何提升查询性能并降低IO利用率？
2. 能否从应用并行执行此类插入而不触发IO利用率上限？
3. 分块插入是否更优，还是一次性插入更好？

1. 提升性能、降低IO利用率的措施 #

（1）缓解WAL日志写入压力

单次插入6000万行会产生大量WAL日志，触发频繁checkpoint导致IO饱和，可调整以下参数：

• 临时调大max_wal_size到16GB-32GB（确保磁盘空间充足），减少checkpoint频率；
• 保持checkpoint_completion_target = 0.9，让checkpoint平滑执行；
• 启用wal_compression = on（PostgreSQL 14+支持），压缩WAL日志降低写IO。

（2）优化JOIN与去重逻辑

当前执行计划是先JOIN再去重，中间结果集过大（6291万行），改为先去重再JOIN缩小数据集：

SELECT
    A.codglacct,
    A.refusrno,
    A.key_priority_radcs,
    -- 其他字段省略
    CASE WHEN C.key_priority_rtdps IS NOT NULL THEN '2025-01-07 19:50:41' ELSE NULL END::TIMESTAMP AS recon_date_1_to_2,
    -- 其他CASE逻辑省略
FROM (
    SELECT DISTINCT ON(uniquekey) *
    FROM cbsupi.source_cbsupi_tmp_details
    WHERE is_processed IS NULL
) A
LEFT JOIN switchupi.source_switchupi_tmp_details C ON A.key_priority_radcs = C.key_priority_rtdps
LEFT JOIN npciupi.source_npciupi_tmp_details D ON A.key_priority_radcs = D.key_priority_ratrs;

（3）避免C、D表全表扫描

虽然关联字段有索引，但执行计划显示是Seq Scan，可做以下操作：

• 执行ANALYZE switchupi.source_switchupi_tmp_details; ANALYZE npciupi.source_npciupi_tmp_details;更新统计信息，引导优化器选择索引扫描；
• 临时调大work_mem到64MB-128MB，让Hash Join的哈希表完全放入内存，避免生成磁盘临时文件（可通过temp_files指标确认是否有临时文件）。

（4）拆分插入与更新逻辑

如果冲突行比例不高，可拆分操作减少ON CONFLICT DO UPDATE开销：

-- 先插入无冲突新行
INSERT INTO cbsupi.source_cbsupi_details (...)
SELECT ... FROM (...) A
LEFT JOIN ... C
LEFT JOIN ... D
WHERE NOT EXISTS (SELECT 1 FROM cbsupi.source_cbsupi_details WHERE uniquekey = A.uniquekey);

-- 再单独更新冲突行
UPDATE cbsupi.source_cbsupi_details t
SET ...
FROM (...) A
LEFT JOIN ... C
LEFT JOIN ... D
WHERE t.uniquekey = A.uniquekey;

2. 应用并行执行的可行性 #

当前IO已饱和，直接并行会加剧压力，需先优化IO瓶颈后再考虑：

• 按uniquekey范围分片A表，每个并行进程处理一个分片，避免重复扫描C、D表；
• 提前用pg_prewarm将C、D表加载到缓存，或构建临时索引；
• 并行度控制在CPU核心数的一半以内（如8个进程），避免上下文切换开销。

3. 分块插入vs一次性插入的选择 #

分块插入更优，但要优化分块方式：

• 放弃ctid分块，改用uniquekey的哈希值或范围分片，每次处理100万-500万行；
• 提前将C、D表的关联字段数据缓存到内存，避免每次分块都全表扫描；
• 每个分块用独立事务，平衡事务开销与WAL生成量，避免单次事务过大导致IO突增。

关于自动提交的疑问 #

自动提交不适合批量操作，每次提交都会触发WAL刷盘，增加IO开销。批量操作应使用显式事务，每个分块对应一个事务。

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