PostgreSQL大表插入性能优化求助：高并发下插入超时

问题描述 #

• 场景：某存储用户事件的表，峰值每秒数百条插入时性能急剧下降
• 性能表现：
  • 插入量1-250次/秒时，响应时间约30ms
  • 插入量300次/秒时，响应时间飙升至1.7秒
  • 插入量450次/秒时，插入直接超时

表结构与插入语句 #

表结构 #

CREATE SEQUENCE id_seq
    AS bigint START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE some_table (
    "id" int8 NOT NULL DEFAULT nextval('id_seq'::regclass),
    "created" timestamptz NOT NULL DEFAULT now(),
    ...
    "externalid1" int8,
    "externalid2" int8,
    "sessionid" int8,
    ...
    "requestuuid" text NOT NULL DEFAULT ''::text,
    ...
    CONSTRAINT "fk1" FOREIGN KEY ("externalid1") REFERENCES "other_table_1"("id"),
    CONSTRAINT "fk2" FOREIGN KEY ("externalid2") REFERENCES "other_table_2"("id") ON DELETE CASCADE,
    PRIMARY KEY (id)
);

插入语句 #

INSERT INTO some_table (created,...,sessionid,...) 
VALUES (NOW(),...,%d,...)

注：sessionid为每次插入时随机生成的数值

执行计划 #

单条插入EXPLAIN ANALYZE结果 #

Insert on some_table  (cost=0.00..0.01 rows=0 width=0) (actual time=0.301..0.301 rows=0 loops=1)
  ->  Result  (cost=0.00..0.01 rows=1 width=272) (actual time=0.090..0.090 rows=1 loops=1)
Planning Time: 0.075 ms
Trigger for constraint fk1 on some_table_3: time=0.894 calls=1
Trigger for constraint fk2 on some_table_3: time=0.385 calls=1
Execution Time: 1.829 ms

200次/秒负载下EXPLAIN (ANALYZE, BUFFERS)结果 #

Insert on some_table  (cost=0.00..0.01 rows=0 width=0) (actual time=0.060..0.060 rows=0 loops=1)
  Buffers: shared hit=3
  ->  Result  (cost=0.00..0.01 rows=1 width=272) (actual time=0.010..0.010 rows=1 loops=1)
        Buffers: shared hit=1
Planning Time: 0.032 ms
Trigger for constraint fk1 on some_table_1: time=0.129 calls=1
Trigger for constraint fk2 on some_table_1: time=0.059 calls=1
Execution Time: 0.272 ms

已尝试的无效方案 #

• 批量插入：不可行，事件需确保不丢失，崩溃时批量内数据存在丢失风险
• 删除外键和索引：索引为数据处理必需（表存储近30天3亿条数据），外键用于旧数据清理，仅作为最后手段
• 哈希分区：按sessionid哈希分为10个分区，性能反而下降，推测分区开销抵消了小表优势
• 数据库升级：从pg-13升级到pg-16.3，无明显性能提升

优化建议 #

1. 优化序列缓存 #

当前id_seq的CACHE值为1，高并发下会引发大量序列锁竞争。调整为更大的缓存值，减少锁冲突：

ALTER SEQUENCE id_seq CACHE 1000;

建议根据并发量调整缓存值（如5000），平衡序列连续性与锁竞争

2. 外键约束优化 #

从执行计划看，外键触发器耗时占比高，可通过以下方式优化：

• 确认other_table_1.id和other_table_2.id已存在主键/唯一索引（默认主键已满足，但需验证）
• 将外键改为延迟约束，把实时检查改为事务提交时检查，降低锁竞争：

ALTER TABLE some_table ALTER CONSTRAINT fk1 DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE some_table ALTER CONSTRAINT fk2 DEFERRABLE INITIALLY DEFERRED;

3. 调整核心数据库配置 #

针对高并发插入场景，调整以下参数（需根据服务器硬件调整）：

• shared_buffers：设置为系统内存的25%（如32G内存设置8G）
• wal_buffers：设置为64MB，减少WAL写入频率
• checkpoint_completion_target：设置为0.9，延长检查点周期，降低IO压力
• synchronous_commit：若业务允许最终一致性，设置为off或local，减少等待WAL写入磁盘的时间（强一致性场景保持默认on）
• max_connections：根据CPU核心数调整（如CPU*10），避免过多连接导致上下文切换
• work_mem：调大至64MB，避免排序/哈希操作溢出到磁盘

4. 索引与表维护 #

• 清理索引碎片：定期执行REINDEX TABLE some_table;，避免索引膨胀影响插入性能
• 冗余索引检查：删除不必要的索引，仅保留主键、外键关联的必要索引
• 对于sessionid查询，若需索引，考虑创建部分索引（如仅索引近7天数据），平衡插入与查询性能

5. IO性能优化 #

• 将WAL目录单独挂载到SSD磁盘，降低写入延迟
• 使用iostat/vmstat监控磁盘IO负载，确认是否存在IO饱和
• 调整wal_writer_delay为10ms，优化WAL批量写入策略

6. 事务与连接优化 #

• 使用短事务：确保每条插入都在独立短事务中完成，避免长事务持有锁
• 引入连接池（如PgBouncer）：减少连接创建销毁开销，控制并发连接数，降低锁竞争

7. 重新评估分区策略 #

放弃按sessionid哈希分区，尝试按created时间分区：

• 按天分区，适配30天数据存储需求，插入时仅命中当前分区，减少锁竞争
• 分区数控制在30个左右，避免过多分区带来的开销

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