我要评论前言
在日常运维中，我们往往更关注查询慢的问题，而忽略了写入慢。事实上，写入性能问题同样令人头疼：
- 一条简单的
insert跑了半小时还没结束 - 高并发下插入响应时间从30ms飙升到1.7秒
- 批量导入数据时速度慢得难以接受
postgresql的写入慢问题往往比查询慢更隐蔽、原因更复杂。本文将从事务提交、wal机制、mvcc、检查点、索引开销等多个维度，系统性地剖析写入慢的根源，并提供可落地的调优方案。
一、写入慢的常见原因全景图
postgresql的写入性能问题，通常可以归为以下几大类：
| 类别 | 典型表现 | 严重程度 |
|---|---|---|
| 事务与提交开销 | 单条insert逐条提交，tps极低 | ⭐⭐⭐⭐⭐ |
| wal写入瓶颈 | 提交延迟高，io:walwrite等待事件频繁 | ⭐⭐⭐⭐⭐ |
| 检查点风暴 | 周期性写入延迟飙升，tps呈锯齿状波动 | ⭐⭐⭐⭐ |
| 索引与约束开销 | 每行插入都要更新索引、检查外键 | ⭐⭐⭐⭐ |
| mvcc与死元组堆积 | 表膨胀，写入越来越慢 | ⭐⭐⭐ |
| 锁竞争 | 高并发下序列锁、页锁争用 | ⭐⭐⭐ |
| 特殊场景 | toast表的oid生成竞争等 | ⭐⭐ |
二、排查写入慢的必备工具
2.1 查看等待事件
postgresql的等待事件是定位写入慢原因的第一手线索：
select pid, state, wait_event, wait_event_type, usename,
extract(epoch from (now() - query_start)) as wait_seconds,
substr(query, 0, 150) as query_preview
from pg_stat_activity
where state != 'idle' and extract(epoch from (now() - query_start)) > 1
order by wait_seconds desc;
常见的写入相关等待事件：
| 等待事件 | 含义 | 可能原因 |
|---|---|---|
io:walwrite | 等待wal写入磁盘 | 磁盘慢、wal配置不当 |
lock:extend | 等待扩展表空间 | 高并发插入同一表 |
lwlock:oidgen | 等待oid生成锁 | toast表oid耗尽 |
datafileread | 等待读取数据页 | 缓冲区不足、磁盘i/o慢 |
2.2 分析检查点与后台写入
select checkpoints_timed, checkpoints_req,
buffers_checkpoint, buffers_clean, buffers_backend,
buffers_backend_fsync
from pg_stat_bgwriter;
checkpoints_req过高 → 检查点过于频繁buffers_backend占比大 → 后端进程直接写盘，说明shared_buffers不足
2.3 使用explain分析写入
explain (analyze, buffers) insert into some_table (...) values (...);
注意观察：
trigger for constraint的耗时——外键检查开销buffers: shared hitvsshared read——缓存命中情况
三、写入慢的根因与调优方案
3.1 事务提交开销过大
问题表现：逐条提交insert，每条都要刷wal，tps极低。
根因：postgresql默认开启自动提交，每条insert都是一个独立事务，每次提交都要等待wal落盘。
解决方案：
-- 错误做法：自动提交模式下逐条插入 insert into t values (1); insert into t values (2); -- 每次都是一次提交 -- 正确做法：手动事务批量提交 begin; insert into t values (1); insert into t values (2); -- ... 批量插入 commit; -- 只提交一次
批量提交能将数百次磁盘i/o减少到一次。
3.2 wal写入瓶颈
问题表现：提交延迟高，io:walwrite等待事件频繁。
根因：wal是预写日志，每次事务提交都要确保wal落盘。如果wal与数据文件在同一块慢速磁盘上，写入性能会严重受限。
解决方案：
（1）wal独立磁盘
将wal目录单独挂载到高速ssd/nvme上：
# 在postgresql.conf中配置 wal_sync_method = fsync # 将pg_wal目录软链接到高速磁盘
独立wal磁盘后，同步提交的性能完全取决于wal磁盘的速度和延迟。
（2）调整wal缓冲区
wal_buffers = 16mb # 可设为shared_buffers的1/32起步
（3）批量提交优化
对于短事务密集的场景，可调整commit_delay和commit_siblings，让一批事务一起提交，减少iops：
commit_delay = 100000 # 微秒 commit_siblings = 5 # 至少5个并发事务时才延迟
3.3 检查点风暴
问题表现：tps呈周期性锯齿状波动，每30-40秒出现一次性能陡降。
根因：检查点需要将所有脏数据页刷写到磁盘，会产生巨大的i/o负载峰值。如果配置不当，检查点过于频繁或过于集中，就会造成“写入风暴”。
解决方案：
（1）拉长检查点间隔
checkpoint_timeout = 15min # 默认5min，可适当加大 max_wal_size = 20gb # 默认1gb，高写入负载建议10-20gb min_wal_size = 2gb
（2）平滑检查点i/o
checkpoint_completion_target = 0.9 # 默认0.9，用90%的检查点间隔时间完成刷写
设置checkpoint_completion_target = 0.9意味着检查点的i/o会分散在整个周期的90%时间内完成，避免瞬间i/o峰值。
（3）监控检查点频率
如果pg_stat_bgwriter中checkpoints_req过高，说明max_wal_size太小，需要增大。
3.4 索引与外键约束开销
问题表现：插入单条数据很快，但批量插入极慢。
根因：每插入一行，所有索引都要更新；每个外键都会触发触发器检查。
解决方案：
（1）批量导入时临时删除索引
-- 导入前 drop index idx_table_col; -- 批量导入数据 copy table from 'data.csv'; -- 导入后重建 create index idx_table_col on table(col);
在已有数据上创建索引比逐行更新索引更快。
（2）临时禁用外键约束
-- 导入前删除外键 alter table child drop constraint fk_parent; -- 导入数据 copy child from 'data.csv'; -- 导入后重建 alter table child add constraint fk_parent foreign key (parent_id) references parent(id);
（3）使用copy代替insert
copy是postgresql专门为高效数据加载设计的命令，绕过了解析、规划、触发器等多重开销。在插入数万行以上数据时，copy通常比insert快10倍以上。
copy table from '/path/to/data.csv' csv header;
（4）使用预备语句
如果无法使用copy，可以使用prepare创建预备insert语句，避免重复解析和规划的开销：
prepare insert_t (int, text) as insert into t values ($1, $2); execute insert_t(1, 'a'); execute insert_t(2, 'b');
3.5 mvcc与死元组堆积
问题表现：表越来越大，写入越来越慢，vacuum跟不上。
根因：postgresql的mvcc机制中，update和delete会产生死元组（dead tuples）。死元组堆积会导致表膨胀，写入时需扫描更多数据页，i/o开销增大。
解决方案：
（1）确保autovacuum正常工作
select relname, last_autovacuum, last_autoanalyze,
n_dead_tup, n_live_tup
from pg_stat_user_tables
where relname = 'your_table';
如果n_dead_tup持续增长而last_autovacuum很久没有更新，说明autovacuum跟不上。
（2）调优autovacuum参数
对于写入密集型表，可适当调低autovacuum_vacuum_scale_factor，让它更早触发清理：
autovacuum_vacuum_scale_factor = 0.05 # 默认0.2 autovacuum_vacuum_threshold = 1000 # 默认50
（3）调整填充因子（fillfactor）
对于频繁更新的表，降低fillfactor可以为hot更新预留空间，避免索引更新：
alter table your_table set (fillfactor = 70);
频繁更新的表，fillfactor一般不建议超过85%。有案例显示将fillfactor从100降到30后，update性能提升到了与insert相当的水平。
3.6 锁竞争
问题表现：高并发下写入延迟急剧上升。
根因：
- 序列锁竞争：使用
serial或bigserial作为主键时，高并发插入会竞争序列锁 - 页锁竞争：高并发插入同一表时，
lock:extend等待事件频繁
解决方案：
（1）优化序列缓存
-- 查看当前缓存值 select cache_size from pg_sequences where sequencename = 'id_seq'; -- 增大缓存 alter sequence id_seq cache 1000; -- 默认1
（2）使用uuid替代序列
使用随机uuid可以避免序列锁竞争，但会牺牲一定的索引性能。
（3）分区表
将大表按时间或哈希分区，分散写入热点。
3.7 特殊场景：toast表oid生成竞争
问题表现：简单的insert跑了半小时以上，等待事件为lwlock:oidgen。
根因：当表包含大字段（如text、jsonb）时，数据会存储在toast表中。toast表的oid分配器在高并发写入时可能成为瓶颈。
解决方案：
- 升级postgresql版本（新版本对此有优化）
- 避免在频繁写入的表中使用大字段
- 将大字段拆分到独立表
四、写入性能调优参数速查表
| 参数 | 推荐值（写密集型） | 作用 |
|---|---|---|
shared_buffers | 内存的25%-40% | 增大缓冲区，减少磁盘i/o |
wal_buffers | 16mb-64mb | 增大wal缓冲区，减少wal写入次数 |
max_wal_size | 10gb-20gb | 减少wal段切换频率 |
checkpoint_timeout | 15min-30min | 减少检查点频率 |
checkpoint_completion_target | 0.9 | 分散检查点i/o负载 |
commit_delay | 100000 | 批量提交，减少iops |
commit_siblings | 5-10 | 配合commit_delay使用 |
maintenance_work_mem | 1gb-2gb | 加速索引创建和vacuum |
autovacuum_vacuum_scale_factor | 0.05-0.1 | 更早触发vacuum |
effective_io_concurrency | ssd设为200 | 提高异步i/o并发度 |
注意：参数调整需结合实际硬件和工作负载，建议使用pgtune生成基线配置后再微调。
五、实战案例
案例1：高并发插入超时
问题：某用户事件表，插入量达到300次/秒时响应时间飙升至1.7秒，450次/秒时直接超时。
排查：
- 单条insert执行计划显示外键触发器耗时占比高
- 序列
cache=1导致锁竞争严重
解决：
- 序列缓存调整为1000
- 将外键改为延迟约束
- 确认被引用表的主键索引存在
案例2：insert运行30分钟未结束
问题：业务反馈一条简单的insert跑了半小时以上。
排查：
select pid, wait_event, wait_event_type, query from pg_stat_activity where wait_event = 'oidgen';
发现大量会话卡在lwlock:oidgen等待事件上。
根因：toast表的oid生成器在高并发下成为瓶颈。
解决：将大字段从主表拆分，减少toast表写入。
六、总结
postgresql写入慢的问题，往往不是单一原因造成的。调优时建议按以下优先级排查：
- 先看应用层：是否使用了批量提交？是否可以用copy？
- 再看等待事件：
pg_stat_activity中的等待事件指向什么瓶颈？ - 三看检查点：
pg_stat_bgwriter中检查点是否过于频繁？ - 四看索引与约束：写入时是否有过多索引需要维护？
- 五看vacuum：死元组是否堆积，表是否膨胀？
- 六看硬件：wal是否在独立的高速磁盘上？
调优的核心原则：
减少不必要的i/o次数，将随机i/o转化为顺序i/o，用内存换磁盘。
当你遇到写入性能问题时，先看等待事件，再查配置参数，最后优化sql与表结构——这是最有效的调优路径。
以上就是postgresql写入慢的原因排查与性能调优实战的详细内容，更多关于postgresql写入慢的原因与调优的资料请关注代码网其它相关文章！
发表评论