高并发写入系统下的PostgreSQL 调优

业务场景是高并发写入系统（比如日志库、监控库、订单流水等），如何进行数据库调优

🚀 一、高并发写入系统的特点

⚙️ 二、PostgreSQL 参数推荐配置（重点）

下面是一套实战级配置模板，适用于高并发写入系统（如日志系统、监控数据仓库、埋点库、实时流水等）。

⚠️ 这些值不是“绝对值”，而是经验区间。具体还要根据你的内存大小和磁盘性能适配。
假设服务器内存 ≥ 16GB，NVMe SSD 或高速磁盘。

🧩 1. WAL 相关（Write Ahead Log）

# WAL 日志大小与检查点控制
max_wal_size = 16GB         # 可根据磁盘空间适当增加（8～32GB区间）
min_wal_size = 4GB          # 保持少量预留文件，避免频繁删除重建
checkpoint_timeout = 30min  # 检查点最大时间间隔，默认5分钟太短
checkpoint_completion_target = 0.9  # 平滑分摊IO，避免瞬时IO暴涨

# WAL 写入优化
wal_buffers = 16MB          # 默认太小（16MB～64MB区间），内存多可调大
synchronous_commit = off    # 不要求每次提交都刷盘，适合日志类业务（牺牲少量可靠性）
commit_delay = 0            # 可以保持默认
commit_siblings = 5         # 可保持默认

📘 说明：

• max_wal_size 大 → checkpoint 少，性能提升。

• synchronous_commit=off → 延迟大幅下降（写性能提升 30%～70%）。

• 如果你必须保证数据绝对安全（如金融账目），不要关闭 synchronous_commit。
  但日志类场景，关闭是合理的。

🧠 2. Checkpoint 调优

bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
checkpoint_warning = 10min

👉 目的：让后台进程提前写脏页，checkpoint 时不会出现突发IO高峰。

💾 3. 内存相关

shared_buffers = 4GB          # 建议为内存的 1/4（最大 8GB 左右）
effective_cache_size = 12GB   # 设置为内存的 3/4（优化查询计划）
work_mem = 32MB               # 单个排序/聚合操作内存，按查询复杂度调整
maintenance_work_mem = 512MB  # CREATE INDEX、VACUUM时使用

📤 4. WAL 与磁盘同步优化（高IO场景）

wal_level = replica              # 需要流复制可改为 'replica' 或 'logical'
fsync = on                       # 建议保留开启（除非可容忍崩溃数据丢失）
full_page_writes = off           # SSD 环境 + 容忍风险，可关闭提升性能
wal_compression = on             # 开启压缩节省空间

⚠️

• full_page_writes=off 虽能提升性能，但 宕机可能导致数据页不一致，需权衡。

• 如果是主从架构、或 WAL 归档依赖，一定保留 on。

🔁 5. autovacuum（自动清理）

高写入库非常容易膨胀，所以 autovacuum 非常重要。

autovacuum = on
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05   # 默认0.2太大
autovacuum_analyze_scale_factor = 0.02  # 让统计信息更及时

📘 说明：

“小步快跑”，频繁小规模清理，避免堆积导致表膨胀和查询慢。

📊 三、配置组合模板（推荐）

# 高并发写入场景 PostgreSQL 调优模板

max_wal_size = 16GB
min_wal_size = 4GB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
wal_buffers = 32MB
synchronous_commit = off
full_page_writes = off
wal_compression = on

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
maintenance_work_mem = 512MB

autovacuum = on
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0

🔍 四、验证效果的方法

可以用这几条 SQL 看效果：

-- 查看checkpoint触发频率
SELECT checkpoints_timed, checkpoints_req, checkpoint_time, buffers_checkpoint FROM pg_stat_bgwriter;

-- 查看wal大小
SHOW max_wal_size;
SHOW min_wal_size;

-- 查看磁盘写入速率（Linux）
iostat -x 1

✅ 理想状态：

• checkpoints_req（因 WAL 过大触发）比例小。

• 磁盘 IO 稳定，无周期性波峰。

💡 五、额外建议（实践经验）