数据库pg指的是PostgreSQL，如何为PostgreSQL数据库进行优化配置？

一、PostgreSQL概览与常见痛点

PostgreSQL 是一款遵循 SQL 标准的开源关系型数据库管理程序，具备高可靠性、可 性和强大的安全特性。但常会遇到以下痛点：

• 查询响应慢，尤其在大表或高并发场景下。
• 写入吞吐量不达标，事务延迟显著。
• 磁盘 I/O 高峰导致 checkpoint 频繁、WAL 日志积压。不过，
• 连接数激增导致资源耗尽。出现 “too many connections” 错误。怎么说呢，
• 自动真空未能及时回收空间。引发表膨胀和锁争用，话说回来，

二、调整配置全流程

1. 安装与基本设置

在安装完 PostgreSQL 后 打开主配置文件 postgresql.conf确保以下基础参数已正确设置：

# 数据库实例名称
listen_addresses = '*'
# 监听端口
port = 5432
# 默认客户端编码
client_encoding = 'UTF8'

2. 内存调优

shared_buffers决定 PostgreSQL 用于缓存数据块的共享内存大小。老实说，一般建议设置为服务器物理内存的 25%~40%。

# 示例
shared_buffers = 8GB

work_mem每个排序或哈希操作使用的内存。根据并发查询数进行平衡，避免因过大导致 OOM。

# 示例
work_mem = 16MB

maintenance_work_memVACUUM、CREATE INDEX 等维护任务使用的内存，应比 work_mem 大。

# 示例
maintenance_work_mem = 256MB

3. 检查点与 WAL 调优

• wal_level = replica开启复制功能，同时提供足够的日志信息。
• checkpoint_timeout = 15min延长 checkpoint 间隔，降低磁盘 I/O 峰值。
• max_wal_size / min_wal_size根据业务写入量调大，以减少 checkpoint 的触发频率。
• wal_compression = on在 SSD 环境下可显著降低 WAL 大小。怎么说呢，

# 示例
wal_level = replica
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
wal_compression = on

4. 自动真空细化

合理配置 autovacuum 能防止表膨胀、死锁和查询性能下降：

# 启用全局自动真空
autovacuum = on
# 根据表大小调整阈值。避免过早或过迟触发
autovacuum_vacuum_threshold = 50 # 最小行数阈值
autovacuum_vacuum_scale_factor = 0.02 # 行数比例阈值
# 提高 vacuum 与 analyze 的并行度
autovacuum_max_workers = 10
autovacuum_naptime = 10s # 检查间隔
# 对写入密集型表单独调优
ALTER TABLE users SET (
autovacuum_vacuum_threshold = 100,autovacuum_vacuum_scale_factor = 0.01,autovacuum_analyze_threshold = 50,autovacuum_analyze_scale_factor = 0.005);

5. 并发连接与连接池化

直接将 max_connections 设置为极大值会消耗大量共享内存，推荐使用连接池工具。若必须提高单实例连接上限，请同步调高 shared_buffers/*_buffers*

# 示例：允许最多 500 个客户端连接
max_connections = 500
# 若不使用连接池。可适当降低工作负载下的工作内存，以防 OOM。work_mem = 8MB # 根据实际并发数调整

6. 查询规划器参数 & 缓存估算

effective_cache_size: 用于估算操作程序文件程序缓存大小，对查询计划很关键。建议设为机器总内存的约 50%~75%。

# 示例
effective_cache_size = 24GB

synchronous_commit=off : 在对数据一致性要求不极端严格时可关闭同步提交，提高写入吞吐量。

synchronous_commit = off #。commit_delay = 0 # 如需微调可设置延迟批量提交。commit_siblings = 5 # 同步提交时等待其他事务数量阈值。

7. 操作程序层面调优

• I/O 调度器：LXFS 或 EXT4 配合 Noop/Deadline) 在 SSD 上表现最佳。其实，
• Mmap 参数："transparent_hugepage=never" 可避免大页碎片化对 PostgreSQL 性能的不利影响。
• Kernal 参数示例：

# /etc/sysctl.conf 中加入：
vm.swappiness=10 # 降低交换使用频率
kernel.shmmax=8589934592 # 最大共享内存段大小
kernel.shmall=2097152 # 总共享内存页面数
net.core.somaxconn=1024 # 单端口最大挂起连接数
net.ipv4.tcp_tw_reuse=1 # 重用 TIME_WAIT 套接字，加速短链接回收。按理说，sysctl -p # 应用新参数
*请在生产环境前先在测试机上验证上述改动*。

8. 硬件层面的关键选择 


"pg_stat_user_tables.n_dead_tup""死元组比例>20% 时需要手动 VACUUM"

"pg_stat_database.blks_hit / blks_read""命中率低于90% 表明 effective_cache_size 设置不足"

"pg_stat_activity.max_connexions""接近 max_connections 上限，需要引入连接池或扩容"
">




要素 
推荐做法 



磁盘 
使用公司级 SSD；启用 RAID10 提供读写均衡与容错。

CPU 
多核处理器；开启 CPU 超线程后监控上下文切换成本。

网络 
 
 千兆以上专线；若跨地域部署则考虑逻辑复制加密通道。不过，

内存 
 
 至少满足 shared_buffers + maintenance_work_mem + OS 缓冲需求。

RAID 类型 
 
 RAID10 比更好 RAID5/6。

I/O 队列深度 t d> SSD 场景建议 queue_depth>=32；NVM eMMC 建议>=64。\t\t\t\ t d \t r e l a t i v e \t s t y l e =" border - collapse : collapse;">
  *注：所有硬件选型应结合业务峰值流量进行压力测试*
---
**关键结论**
- **先从 memory & wal 开始**——最直接提高读写性能。- **随后调优 autovacuum 与 connection pool**——解决长期累积的慢查询和“too many connections”。- **最终结合 OS 与硬件层面的细节**，才能让 PG 在高并发、大数据场景下保持稳定。

三、实战示例：从零到可投入生产的调整脚本 

/etc/postgresql/13/main/postgresql.conf<\/cite>. 为便于阅读。我已按照章节顺序排好顺序：

# ------------------------------
# 基础网络与身份认证设置
# ------------------------------
listen_addresses ='*' -- 接受任意 IP 的请求
port ='5432' -- 默认端口
# ------------------------------
# 内存相关参数
# ------------------------------
shared_buffers ='8GB' -- 占总 RAM 的 ~25%
work_mem ='16MB' -- 每个排序/哈希操作分配
maintenance_work_mem ='256MB' -- VACUUM/CREATE INDEX 使用
effective_cache_size ='24GB' -- 操作程序文件缓存预估
# ------------------------------
# WAL 与 Checkpoint 调优
# ------------------------------
wal_level ='replica' -- 支持流复制 & logical replication
wal_compression ='on' -- 减少磁盘占用
checkpoint_timeout ='15min' -- 延长 checkpoint 间隔
max_wal_size ='4GB' -- 防止频繁检查点产生
min_wal_size ='1GB' -- 保持最小 WAL 空间
# ------------------------------
# 自动真空细化
# ------------------------------
autovacuum ='on' -- 全局开启
autovacuum_max_workers=10 -- 并行真空进程数
autovacump_naptime ='10s' -- 检查间隔
autovacuum_vacuum_threshold=50
autovacium_vacuuum_scale_factor=0 .02
# 针对热点表单独调参
ALTER TABLE orders SET (
autovacuum_vacuuum_threshold=200,autvacuuum_vacuuum_scale_factor=0 .01 );# ------------------------------
# 并发 & 链接池化建议
# ------------------------------
max_connections ='500' -- 配合 pgBouncer 使用
## 如果不使用 pgBouncer。请把 max_connections 降低至200 以下
## pgBouncer 基本配置示例
mydb=host=127 .0 .0 .1 port=5432 dbname=mydb
listen_addr=*
listen_port=6432
auth_type=md5
pool_mode=session
default_pool_size=20
# ------------------------------
# 同步提交与事务调整
#
synchronous_commit ='off' // 对实时分析类业务可关闭，提高吞吐
commit_delay ='0' // 如需批量提交可适当增大
commit_siblings ='5'
## 注意：关闭同步提交后在极端断电情况下可能会丢失最近几秒的数据，请评估业务容忍度。### OS 参数 ###
vm.swappiness =10
kernel.shmmax = // 最大共享内存段
kernel.shmall =/ // 页面总数
net.core.somaxconn ="1024"
net.ipv4.tcp_tw_reuse ="1"
### 生效命令 ###
sysctl -p && systemctl restart postgresql && systemctl restart pgbouncer
<\/ code><\/ pre>

四、监控指标 & 持续迭代建议 



指标名称
监控阈值及异常表现






"pg_stat_bgwriter.checkpoints_timed"
"检查点间隔超过 checkpoint_timeout 时会出现 I/O 峰值"