外观
PostgreSQL 性能参数
PostgreSQL性能参数配置是数据库性能优化的核心，合理的参数设置可以充分发挥硬件资源潜力，提高数据库响应速度和吞吐量。本文档详细介绍PostgreSQL性能相关参数，包括内存配置、优化器参数、WAL配置、并行查询、自动VACUUM等，帮助DBA根据实际需求进行参数调优。
内存相关性能参数
内存配置是PostgreSQL性能优化的基础，合理分配内存资源可以显著提高数据库的查询和写入性能。
shared_buffers
默认值：
- PostgreSQL 12+：
128MB - PostgreSQL 11及以下：
16MB
说明：PostgreSQL用于缓存数据块的共享内存大小，直接影响数据库读取性能。PostgreSQL会将经常访问的数据块缓存在shared_buffers中，减少磁盘I/O。
生产环境推荐值：
- 服务器总内存的
25% - 最大不超过
8GB（除非是专门的数据库服务器且内存非常充足）
适用场景与调优建议：
- 内存充足的服务器：增加到
25-30%，但不要超过8GB - 内存受限的服务器：保持默认或适当增加到
10-15% - 写入密集型应用：适当减少shared_buffers，为WAL和其他操作留出更多内存
- 读取密集型应用：适当增加shared_buffers，提高缓存命中率
性能影响：
- 较高的值可以减少磁盘I/O，提高读取性能
- 过大的值可能导致操作系统缓存减少，反而降低整体性能
- 建议与effective_cache_size配合调整，effective_cache_size通常设置为系统内存的75%
effective_cache_size
默认值：4GB
说明：查询优化器用于估算可用缓存总量的参数，包括PostgreSQL的shared_buffers和操作系统缓存。这个参数不会分配实际内存，只是帮助优化器生成更好的执行计划。
生产环境推荐值：
- 服务器总内存的
75% - 例如：64GB内存的服务器，推荐设置为
48GB
适用场景与调优建议：
- 所有环境都应根据实际内存大小调整
- 对于内存充足的服务器，可设置为更高比例
- 对于内存受限的服务器，应相应减少
性能影响：
- 直接影响查询优化器的执行计划选择
- 合理的设置可以让优化器选择更优的索引和连接方式
- 过高或过低的设置都可能导致低效的执行计划
work_mem
默认值：4MB
说明：每个查询操作（如排序、哈希表、合并连接）可用的内存大小。每个连接可能同时执行多个操作，因此实际内存使用可能是work_mem的数倍。
生产环境推荐值：
- 小型服务器（4GB内存）：
4MB-8MB - 中型服务器（16GB内存）：
8MB-16MB - 大型服务器（64GB+内存）：
16MB-64MB
适用场景与调优建议：
- 复杂查询场景：适当增加work_mem，提高排序和哈希操作性能
- 高并发环境：适当减少work_mem，避免内存耗尽
- 批量导入场景：临时增加work_mem，提高导入速度
- 计算公式：
work_mem = (系统内存 * 0.25) / max_connections
性能影响：
- 影响复杂查询的执行速度，特别是涉及排序、哈希和合并连接的查询
- 过高可能导致内存不足和系统交换
- 过低可能导致频繁的临时文件创建，影响查询性能
maintenance_work_mem
默认值：64MB
说明：维护操作（如VACUUM、CREATE INDEX、ALTER TABLE）可用的内存大小。这些操作通常是单线程执行的，因此可以分配较大的内存。
生产环境推荐值：
- 小型服务器：
128MB-256MB - 中型服务器：
256MB-1GB - 大型服务器：
1GB-4GB
适用场景与调优建议：
- 频繁创建索引：增加maintenance_work_mem，提高索引创建速度
- 大型表VACUUM：增加maintenance_work_mem，提高VACUUM效率
- 内存受限服务器：保持默认或适当增加
- 建议不超过服务器总内存的
10%
性能影响：
- 显著影响维护操作的执行速度
- 过大的值可能导致内存不足，特别是在多个维护操作同时执行时
优化器性能参数
优化器参数控制PostgreSQL查询优化器的行为，直接影响执行计划的生成质量。
random_page_cost
默认值：4.0
说明：查询优化器用于估算随机页面访问成本的参数。该值与seq_page_cost（顺序页面访问成本）的比值决定了优化器是选择索引扫描还是顺序扫描。
生产环境推荐值：
- SSD存储：
1.1-1.3 - NVMe存储：
1.0-1.2 - 机械硬盘：
3.0-4.0
适用场景与调优建议：
- SSD/NVMe存储：降低该值，鼓励优化器使用索引扫描
- 机械硬盘：保持默认或适当调整
- 与effective_io_concurrency配合调整，获得最佳效果
性能影响：
- 直接影响执行计划选择，不合理的值可能导致低效的执行计划
- 对于SSD存储，过高的值会导致优化器倾向于顺序扫描，即使索引扫描更快
- 对于机械硬盘，过低的值会导致优化器过度使用索引，可能降低性能
effective_io_concurrency
默认值：1
说明：查询优化器用于估算I/O系统并行处理能力的参数。该参数影响位图索引扫描和并行查询的性能。
生产环境推荐值：
- SSD存储：
200-400 - NVMe存储：
400-800 - 机械硬盘：
5-10
适用场景与调优建议：
- 高并发I/O系统：增加该值，提高并行I/O处理能力
- 低并发I/O系统：保持默认或适当降低
- 与random_page_cost配合调整，获得最佳效果
性能影响：
- 影响并行查询和位图扫描的效率
- 对于SSD/NVMe存储，适当增加可以显著提高查询性能
- 对于机械硬盘，过高的值可能导致I/O竞争，降低性能
seq_page_cost
默认值：1.0
说明：查询优化器用于估算顺序页面访问成本的参数。与random_page_cost配合使用，决定优化器的扫描方式选择。
生产环境推荐值：
- 高性能存储：
0.5-1.0 - 普通存储：保持默认
1.0
适用场景与调优建议：
- 高性能存储：适当降低，反映实际的低I/O成本
- 普通存储：保持默认值
性能影响：
- 影响顺序扫描和索引扫描的选择
- 过低的值可能导致优化器过度使用顺序扫描
- 过高的值可能导致优化器过度使用索引扫描
WAL相关性能参数
WAL（Write-Ahead Log）配置直接影响数据库的写入性能和数据可靠性。
wal_compression
默认值：
- PostgreSQL 14+：
on - PostgreSQL 13及以下：
off
说明：控制是否压缩WAL文件，减少WAL文件大小和I/O。压缩WAL会增加CPU开销，但可以减少磁盘I/O。
生产环境推荐值：
- 所有版本都建议设置为
on
适用场景与调优建议：
- 写入密集型应用：开启压缩，减少WAL I/O
- CPU受限的服务器：评估CPU开销，根据实际情况调整
- 网络复制环境：开启压缩，减少网络传输量
性能影响：
- 优点：减少WAL文件大小、降低磁盘I/O、减少网络传输量
- 缺点：增加CPU开销
- 对于大多数现代服务器，CPU开销可以忽略，而I/O收益显著
wal_buffers
默认值：
- PostgreSQL 12+：
-1（自动调整，通常为shared_buffers的1%，最大16MB） - PostgreSQL 11及以下：
64kB
说明：用于缓存WAL数据的共享内存大小。WAL数据在写入磁盘之前会先缓存在wal_buffers中。
生产环境推荐值：
- 自动调整：
-1（推荐） - 手动调整：
32MB-128MB（仅当自动调整效果不佳时）
适用场景与调优建议：
- 写入密集型应用：适当增加，减少WAL写入次数
- 批量写入场景：增加到
64MB-128MB，提高批量写入性能 - 普通应用：自动调整即可
性能影响：
- 影响写入性能，特别是批量写入和高并发写入
- 过小的值会导致频繁的WAL写入，增加磁盘I/O
- 过大的值可能浪费内存，因为WAL会定期刷新到磁盘
synchronous_commit
默认值：on
说明：控制事务提交时WAL写入的同步级别，直接影响事务提交延迟和数据可靠性。
可选值：
on：事务提交等待WAL写入并刷新到磁盘remote_write：事务提交等待WAL写入主库磁盘，并发送到从库local：事务提交等待WAL写入主库磁盘off：事务提交不等待WAL写入磁盘
生产环境推荐值：
- 金融交易系统：
on（最高可靠性） - 普通Web应用：
remote_write（平衡性能和可靠性） - 日志系统：
off（最高性能，可接受一定数据丢失风险）
适用场景与调优建议：
- 高可靠性要求：使用
on或remote_write - 高性能要求：使用
remote_write或off - 主从架构：考虑使用
remote_write，确保数据发送到从库
性能影响：
- 直接影响事务提交延迟
on：最高可靠性，最高延迟off：最低可靠性，最低延迟remote_write：平衡性能和可靠性，适合大多数生产环境
并行查询性能参数
并行查询可以利用多核CPU提高查询性能，特别是对于大型表的复杂查询。
max_parallel_workers_per_gather
默认值：
- PostgreSQL 12+：
2 - PostgreSQL 11及以下：
2
说明：每个Gather节点可以使用的最大并行工作进程数。并行查询的总并行度受此参数和max_parallel_workers限制。
生产环境推荐值：
- CPU核心数的
1/2 - 例如：8核CPU设置为
4，16核CPU设置为8
适用场景与调优建议：
- 复杂查询场景：适当增加，提高并行度
- 高并发环境：适当减少，避免CPU资源耗尽
- OLAP应用：增加到CPU核心数的
1/2或更高 - OLTP应用：保持默认或适当减少
性能影响：
- 影响并行查询的并行度和资源使用
- 过高的值可能导致CPU资源竞争，降低整体性能
- 过低的值无法充分利用多核CPU
max_parallel_workers
默认值：
- PostgreSQL 12+：
8 - PostgreSQL 11及以下：不支持
说明：所有并行查询可以使用的最大并行工作进程总数。这个参数限制了系统级别的并行度。
生产环境推荐值：
- CPU核心数
- 例如：8核CPU设置为
8，16核CPU设置为16
适用场景与调优建议：
- 多核服务器：设置为CPU核心数
- 高并发环境：适当减少，为其他进程留出CPU资源
性能影响：
- 限制全局并行查询的总资源使用
- 确保系统有足够的CPU资源处理其他任务
parallel_tuple_cost
默认值：0.1
说明：查询优化器用于估算并行查询中元组传递成本的参数。这个参数影响优化器是否选择并行执行计划。
生产环境推荐值：
- 默认值
0.1（大多数情况下表现良好） - 鼓励并行查询：
0.05-0.1 - 限制并行查询：
0.2-0.5
适用场景与调优建议：
- 鼓励并行查询：降低该值
- 限制并行查询：增加该值
- 与parallel_setup_cost配合调整
性能影响：
- 影响查询优化器是否选择并行执行计划
- 过低的值可能导致优化器过度使用并行查询
- 过高的值可能导致优化器不使用并行查询，即使并行查询更快
parallel_setup_cost
默认值：1000.0
说明：查询优化器用于估算并行查询启动成本的参数。这个参数影响优化器是否选择并行执行计划。
生产环境推荐值：
- 默认值
1000.0（大多数情况下表现良好） - 鼓励并行查询：
500.0-1000.0 - 限制并行查询：
1500.0-2000.0
适用场景与调优建议：
- 鼓励并行查询：降低该值
- 限制并行查询：增加该值
- 与parallel_tuple_cost配合调整
性能影响：
- 影响查询优化器是否选择并行执行计划
- 对于小表查询，过高的值会阻止优化器使用并行查询
- 对于大表查询，过低的值会导致优化器过度使用并行查询
存储相关性能参数
存储相关参数控制PostgreSQL的磁盘使用和I/O行为。
temp_file_limit
默认值：-1（无限制）
说明：单个会话可以使用的临时文件最大大小。临时文件主要用于排序、哈希表和大型查询结果。
生产环境推荐值：
- 小型服务器：
1GB-5GB - 中型服务器：
5GB-20GB - 大型服务器：
20GB-50GB
适用场景与调优建议：
- 共享服务器：设置合理限制，防止单个会话耗尽磁盘空间
- 专用服务器：可以设置较大限制或保持无限制
- 与work_mem配合调整，work_mem越大，临时文件使用越少
性能影响：
- 防止单个会话耗尽磁盘空间
- 过小的值可能导致大型查询失败
- 建议监控临时文件使用情况，根据实际需求调整
temp_tablespaces
默认值：''（使用默认表空间）
说明：指定存储临时表和临时文件的表空间。将临时表空间设置在高速存储上可以提高临时操作的性能。
生产环境推荐值：
- 如有高速存储（如SSD/NVMe）：设置为专用的临时表空间
- 否则：保持默认
适用场景与调优建议：
- 大型复杂查询：将临时表空间设置在高速存储上
- 数据仓库环境：专用的临时表空间可以显著提高ETL性能
- OLTP环境：根据实际临时表使用情况调整
性能影响：
- 对于涉及大量临时数据的查询，使用高速存储的临时表空间可以显著提高性能
- 减少对默认表空间的I/O竞争
自动VACUUM性能参数
自动VACUUM是PostgreSQL维护数据库性能的重要机制，合理配置可以避免表膨胀和性能下降。
autovacuum_vacuum_scale_factor
默认值：0.2（20%）
说明：触发自动VACUUM的死元组比例阈值。当表中的死元组比例超过此阈值（加上autovacuum_vacuum_threshold）时，会触发自动VACUUM。
生产环境推荐值：
- 频繁更新的表：
0.02-0.05（2%-5%） - 普通更新的表：
0.1-0.15（10%-15%） - 很少更新的表：保持默认
0.2
适用场景与调优建议：
- 写入密集型应用：降低该值，更频繁地执行自动VACUUM
- 大型表：降低该值，避免死元组过多导致的表膨胀
- 与autovacuum_analyze_scale_factor配合调整
性能影响：
- 过低的值会导致自动VACUUM过于频繁，增加CPU和I/O开销
- 过高的值会导致表膨胀，降低查询性能
- 建议根据表的更新频率和大小调整
autovacuum_analyze_scale_factor
默认值：0.1（10%）
说明：触发自动ANALYZE的元组变化比例阈值。当表中的元组变化比例超过此阈值（加上autovacuum_analyze_threshold）时，会触发自动ANALYZE。
生产环境推荐值：
- 频繁更新的表：
0.01-0.025（1%-2.5%） - 普通更新的表：
0.05-0.075（5%-7.5%） - 很少更新的表：保持默认
0.1
适用场景与调优建议：
- 数据分布变化频繁的表：降低该值，更频繁地更新统计信息
- 查询频繁的表：降低该值，确保优化器有准确的统计信息
性能影响：
- 过低的值会导致自动ANALYZE过于频繁，增加CPU和I/O开销
- 过高的值会导致统计信息过时，优化器生成低效的执行计划
autovacuum_max_workers
默认值：3
说明：同时运行的自动VACUUM/ANALYZE工作进程的最大数量。
生产环境推荐值：
- 小型服务器：
2-3 - 中型服务器：
3-5 - 大型服务器：
5-8
适用场景与调优建议：
- 多表环境：适当增加，提高自动VACUUM的并行处理能力
- CPU受限的服务器：根据CPU核心数调整
- 与autovacuum_naptime配合调整
性能影响：
- 影响自动VACUUM的并行处理能力
- 过高的值会导致CPU和I/O竞争，降低整体性能
- 过低的值会导致自动VACUUM跟不上表的更新速度
生产环境参数调优最佳实践
从小开始，逐步调整：
- 不要一次性修改多个参数
- 每次修改1-2个参数，观察效果后再进行其他调整
- 使用监控工具评估调整效果
根据实际负载调整：
- 不同类型的应用需要不同的参数配置
- OLTP应用：注重并发性能和事务延迟
- OLAP应用：注重查询性能和吞吐量
- 混合应用：平衡两者需求
监控关键指标：
- 监控缓存命中率、磁盘I/O、CPU使用率
- 监控查询执行计划、慢查询、锁等待
- 监控自动VACUUM运行情况、表膨胀率
测试环境验证：
- 在测试环境验证参数调整效果
- 使用真实数据和负载进行测试
- 比较调整前后的性能差异
记录调整历史：
- 记录每次参数调整的原因、调整值和效果
- 便于后续分析和回滚
- 建立参数调整的知识库
定期审查配置：
- 定期审查参数配置，根据业务发展和硬件升级进行调整
- 关注PostgreSQL版本变化，新版本可能有更好的默认值和新参数
平衡性能和可靠性：
- 不要为了追求极致性能而牺牲可靠性
- 根据业务需求选择合适的权衡点
- 例如：synchronous_commit参数的选择
性能参数配置示例
OLTP应用参数配置（16GB内存，8核CPU，SSD存储）
bash
# 内存配置
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 8MB
maintenance_work_mem = 1GB
# 优化器配置
random_page_cost = 1.1
effective_io_concurrency = 200
# WAL配置
wal_compression = on
wal_buffers = -1
synchronous_commit = remote_write
# 并行查询配置
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
# 自动VACUUM配置
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.025
autovacuum_max_workers = 5
# 存储配置
temp_file_limit = 10GB数据仓库应用参数配置（64GB内存，16核CPU，NVMe存储）
bash
# 内存配置
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
# 优化器配置
random_page_cost = 1.0
effective_io_concurrency = 400
# WAL配置
wal_compression = on
wal_buffers = 64MB
synchronous_commit = off
# 并行查询配置
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
parallel_tuple_cost = 0.05
parallel_setup_cost = 500
# 自动VACUUM配置
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_max_workers = 8
# 存储配置
temp_file_limit = 50GB
temp_tablespaces = 'temp_ts' # 假设temp_ts是NVMe上的表空间总结
PostgreSQL性能参数配置是一个复杂的过程，需要根据服务器硬件、应用类型和负载特性进行调整。本文档介绍了PostgreSQL的主要性能参数，包括内存配置、优化器参数、WAL配置、并行查询、存储相关和自动VACUUM参数，提供了生产环境的推荐值和调优建议。
在实际配置中，DBA应遵循以下原则：
- 从小开始，逐步调整
- 根据实际负载和硬件情况调整
- 监控调整效果，使用数据驱动的方法
- 平衡性能和可靠性
- 定期审查和更新配置
通过合理的参数调优，可以充分发挥PostgreSQL的性能潜力，为业务提供高效可靠的数据服务。