Azure PostgreSQL高频大批量插入性能分析工具及优化咨询

算力阿峰

2026-7-29

PostgreSQL批量插入性能分析与优化问题

背景

我正在设计一个向多张表插入大量数据的Ingest流程，当前使用Azure PostgreSQL灵活服务器。目前写入/插入速度是提升负载吞吐量的瓶颈——我们通过队列处理REST API的JSON负载，目标完成率为100个负载/分钟。

我想知道是否存在可分析INSERT性能的PostgreSQL/数据库工具，类似用EXPLAIN ANALYZE分析SELECT性能的方式？我知道索引和外键会影响INSERT速度，同时推测表的列类型、插入字符串长度也会有影响。

我们使用Python的psycopg2包，通过execute_values执行批量INSERT INTO ... VALUES操作，以下是两张大表的插入速度实测数据：

实测插入性能数据

性能较好、受行数影响较小的Resource表

"Resource Insert: 35 ms, 120 rows, 0.2916666666666667 ms/row" # 1 Slide模型
"Resource Insert: 36 ms, 120 rows, 0.3 ms/row"
"Resource Insert: 47 ms, 120 rows, 0.39166666666666666 ms/row"
"Resource Insert: 99 ms, 381 rows, 0.25984251968503935 ms/row" # 10 Slides
"Resource Insert: 97 ms, 381 rows, 0.2545931758530184 ms/row"
"Resource Insert: 117 ms, 381 rows, 0.30708661417322836 ms/row"
"Resource Insert: 654 ms, 2991 rows, 0.21865596790371114 ms/row" # 100 Slides
"Resource Insert: 683 ms, 2991 rows, 0.22835172183216315 ms/row"
"Resource Insert: 665 ms, 2991 rows, 0.2223336676696757 ms/row"
"Resource Insert: 5604 ms, 29091 rows, 0.19263689800969372 ms/row" # 1000 Slides
"Resource Insert: 5498 ms, 29091 rows, 0.1889931593963769 ms/row"
"Resource Insert: 5021 ms, 29091 rows, 0.17259633563645113 ms/row"
"Resource Insert: 4743 ms, 29091 rows, 0.16304011549963907 ms/row"
"Resource Insert: 8428 ms, 29091 rows, 0.2897115946512667 ms/row"
"Resource Insert: 7788 ms, 29091 rows, 0.26771166340105185 ms/row"
"Resource Insert: 7367 ms, 29091 rows, 0.25323983362551994 ms/row"

插入性能较差、随行数增加扩展性不佳的Resource Value表

"Resource Val Insert: 378 ms, 113 rows, 3.3451327433628317 ms/row" ~ 1 Slide + Acc,Pat,Cli,TestOrder,Block
"Resource Val Insert: 365 ms, 113 rows, 3.230088495575221 ms/row" 
"Resource Val Insert: 356 ms, 113 rows, 3.150442477876106 ms/row"
"Resource Val Insert: 422 ms, 113 rows, 3.734513274336283 ms/row"
"Resource Val Insert: 439 ms, 113 rows, 3.8849557522123894 ms/row"
"Resource Val Insert: 354 ms, 113 rows, 3.1327433628318584 ms/row"
"Resource Val Insert: 1498 ms, 365 rows, 4.104109589041096 ms/row" # 10 Slides
"Resource Val Insert: 1509 ms, 365 rows, 4.134246575342465 ms/row"
"Resource Val Insert: 1568 ms, 365 rows, 4.295890410958904 ms/row"
"Resource Val Insert: 1607 ms, 365 rows, 4.402739726027397 ms/row"
"Resource Val Insert: 1553 ms, 365 rows, 4.254794520547946 ms/row"
"Resource Val Insert: 14187 ms, 2885 rows, 4.917504332755633 ms/row" # 100 Slides
"Resource Val Insert: 14239 ms, 2885 rows, 4.935528596187175 ms/row"
"Resource Val Insert: 13494 ms, 2885 rows, 4.677296360485268 ms/row"
"Resource Val Insert: 14489 ms, 2885 rows, 5.0221837088388215 ms/row"
"Resource Val Insert: 14044 ms, 2885 rows, 4.867937608318891 ms/row"
"Resource Val Insert: 131665 ms, 28085 rows, 4.688089727612605 ms/row" # 1000 Slides
"Resource Val Insert: 133214 ms, 28085 rows, 4.743243724408047 ms/row"
"Resource Val Insert: 132070 ms, 28085 rows, 4.7025102367812 ms/row"

相关表DDL（可能影响性能的因素）

性能中等的resource表

Table "public.resource"
      Column       |            Type             | Collation | Nullable |   Default    | Storage  | Stats target | Description
-------------------+-----------------------------+-----------+----------+--------------+----------+--------------+-------------
 resource_id       | integer                     |           | not null |              | plain    |              |
 uuid              | uuid                        |           | not null |              | plain    |              |
 name              | character varying           |           |          |              | extended |              |
 url               | character varying           |           |          |              | extended |              |
 desc              | character varying           |           |          |              | extended |              |
 barcode           | character varying           |           |          |              | extended |              |
 barcode_type      | character varying           |           |          |              | extended |              |
 meta              | jsonb                       |           |          |              | extended |              |
 cls               | integer                     |           | not null |              | plain    |              |
 archived          | boolean                     |           | not null |              | plain    |              |
 view_template     | character varying           |           | not null |              | extended |              |
 created_timestamp | timestamp without time zone |           | not null |              | plain    |              |
 updated_timestamp | timestamp without time zone |           | not null |              | plain    |              |
 owner_resource_id | integer                     |           |          |              | plain    |              |
 tenant            | character varying           |           | not null | CURRENT_USER | extended |              |
Indexes:
    "resource_pk" PRIMARY KEY, btree (resource_id, tenant)
    "_uuid_tenant_uc" UNIQUE CONSTRAINT, btree (uuid, tenant)
    "param_group_unique_name" UNIQUE, btree (name) WHERE cls = 600
    "resource_resource_id_key" UNIQUE CONSTRAINT, btree (resource_id)
    "ix_resource_barcode" btree (barcode)
    "ix_resource_desc" btree ("desc")
    "ix_resource_name" btree (name)
    "ix_resource_uuid" btree (uuid)
    "lab7_role_unique_name" btree (lower(name::text)) WHERE cls = 221 OR cls = 222 OR cls = 220
    "resource_created_timestamp_idx" btree (created_timestamp DESC)
    "resource_updated_timestamp_idx" btree (updated_timestamp DESC)
Check constraints:
    "resource_check" CHECK (resource_id <> owner_resource_id)
Foreign-key constraints:
    "resource_owner_resource_id_fkey" FOREIGN KEY (owner_resource_id) REFERENCES resource(resource_id)
Referenced by:
    TABLE "address" CONSTRAINT "address_address_id_fkey" FOREIGN KEY (address_id) REFERENCES resource(resource_id)
    # .... and many more references! This is our most basic table that acts sort of like an abstract base class

插入性能较差的resource_val表

\d+ resource_val
                                                Table "public.resource_val"
         Column          |       Type        | Collation | Nullable |   Default    | Storage  | Stats target | Description
-------------------------+-------------------+-----------+----------+--------------+----------+--------------+-------------
 resource_val_id         | integer           |           | not null |              | plain    |              |
 resource_var_id         | integer           |           |          |              | plain    |              |
 value_group_id          | integer           |           |          |              | plain    |              |
 sort_id                 | integer           |           |          |              | plain    |              |
 value                   | character varying |           |          |              | extended |              |
 expression              | character varying |           |          |              | extended |              |
 dropdown                | jsonb             |           |          |              | extended |              |
 bound_resource_id       | integer           |           |          |              | plain    |              |
 error_msg               | character varying |           |          |              | extended |              |
 dropdown_error_msg      | character varying |           |          |              | extended |              |
 tenant                  | character varying |           | not null | CURRENT_USER | extended |              |
 step_instance_sample_id | integer           |           |          |              | plain    |              |
Indexes:
    "resource_val_pkey" PRIMARY KEY, btree (resource_val_id)
    "idx_res_val_hash" hash (value)
    "ix_r_val_bound_resource_id" btree (bound_resource_id)
    "ix_r_val_sis_id" btree (step_instance_sample_id)
Foreign-key constraints:
    "resource_val_bound_resource_id_fkey" FOREIGN KEY (bound_resource_id) REFERENCES resource(resource_id)
    "resource_val_resource_val_id_fkey" FOREIGN KEY (resource_val_id) REFERENCES resource(resource_id)
    "resource_val_resource_var_id_fkey" FOREIGN KEY (resource_var_id) REFERENCES resource_var(resource_var_id)
    "resource_val_step_instance_sample_id_fkey" FOREIGN KEY (step_instance_sample_id) REFERENCES step_instance_sample(association_id)
    "resource_val_value_group_id_fkey" FOREIGN KEY (value_group_id) REFERENCES resource_val_group(id)

问题解答

1. 分析INSERT性能的工具与方法

EXPLAIN ANALYZE：直接用于INSERT语句（注意测试环境使用，会真实执行插入），能展示执行计划中索引更新、外键检查等步骤的耗时。
pg_stat_statements扩展：开启后可跟踪所有SQL语句的执行统计，包括INSERT的调用次数、总耗时、平均耗时等，适合批量操作的长期监控。
pg_stat_activity：实时查看INSERT语句的状态，排查是否存在锁等待、IO阻塞等问题。
pg_locks：检查插入时的锁竞争情况，比如外键关联表的锁等待。
Azure PostgreSQL监控：通过Azure门户查看写入IOPS、事务日志生成量、CPU使用率等指标，定位资源瓶颈。

2. resource_val表性能优化建议

结合DDL和性能数据，该表的性能问题主要来自以下方面，可针对性优化：

外键约束开销：5个外键会导致每条插入都要检查关联表数据，批量插入时累积开销大。可：
- 插入前确保关联数据已存在，临时关闭外键约束（SET foreign_key_checks = 0），插入完成后重新开启；
- 调整插入顺序，先插入所有父表数据，再插入resource_val数据。
索引更新开销：除主键外的3个索引会在批量插入时频繁更新。可：
- 插入前临时删除非必要索引，插入完成后重建；
- 评估idx_res_val_hash哈希索引的必要性，哈希索引更新开销高于BTREE索引，若等值查询需求低可替换或删除。
数据类型与大小：value、expression等长字符串列和dropdown jsonb列会增加IO开销。可：
- 缩小字符串列的长度限制，或改用更匹配的类型；
- 将大体积的dropdown数据拆分到单独表中。
批量插入优化：
- 调整execute_values的批量大小，测试不同批次行数（如1000/5000行）的性能差异；
- 减少事务提交次数，或开启autocommit，避免频繁提交的开销；
- 改用COPY命令（psycopg2的copy_from方法），其写入效率远高于批量INSERT。
Azure配置优化：
- 确认使用Premium SSD存储，保证IO性能；
- 调整work_mem、maintenance_work_mem等参数，优化批量操作的内存使用；
- 开启pg_prewarm扩展预热关联表缓存，减少外键检查时的IO等待。

内容的提问来源于stack exchange，提问作者JLowe-N

Azure PostgreSQL高频大批量插入性能分析工具及优化咨询

背景 #

实测插入性能数据 #

性能较好、受行数影响较小的Resource表 #

插入性能较差、随行数增加扩展性不佳的Resource Value表 #

相关表DDL（可能影响性能的因素） #

性能中等的resource表 #

插入性能较差的resource_val表 #

问题解答 #

1. 分析INSERT性能的工具与方法 #

2. resource_val表性能优化建议 #

背景