PostgreSQL性能调优

飞象数据 2025-04-01

原文地址：

https://www.pgedge.com/blog/postgresql-performance-tuning

PostgreSQL 以其可靠性、可扩展性和开源血统而闻名，并且随着每个版本的发布而不断发展和演变。PostgreSQL 17 引入了多项性能改进和功能，使其成为 OLTP（在线事务处理）和 OLAP（在线分析处理）工作负载的强大工具。

特征	PostgreSQL 15	PostgreSQL 16	PostgreSQL 17
逻辑复制	基本支持	改进故障转移恢复	无缝故障转移槽
并行查询支持	有限的	更好的并行连接	扩展并行聚合
增量排序	初步实施	支持更多场景	针对大型数据集进行了优化
WAL 压缩	引入	改进	更快速、更高效
索引	基本重复数据删除	BRIN 增强功能	多列 BRIN，更好的 B-Tree
自动vacuum	基本阈值	更智能的基于活动的调整	自适应阈值

本博客将探讨 PostgreSQL 17 的高级性能调优技术，并重点介绍与版本 15 和 16 相比的主要改进。

PostgreSQL 17 中的性能改进

PostgreSQL 17 在 PostgreSQL 15 和 16 的功能基础上进行了优化，解决了延迟、可扩展性和可管理性问题。一些突出的增强功能包括：

逻辑复制增强功能

故障转移槽同步： PostgreSQL 17 引入了故障转移槽，可确保逻辑复制在故障转移事件后能够无缝继续。此功能减少了故障转移期间的停机时间和数据丢失，显著改善了 PostgreSQL 16 对逻辑复制恢复的部分支持。
内存优化： PostgreSQL 17 中的逻辑解码现在在高事务负载期间消耗更少的内存。此改进提高了复制密集型系统的稳定性。

查询计划器优化

增量排序增强功能： PostgreSQL 13 中引入的增量排序在 PostgreSQL 17 中得到了进一步优化，可以以最小的内存使用量处理更大的数据集。规划器现在可以更准确地评估查询成本，从而减少具有复杂排序要求的查询的执行时间。
并行查询增强功能： PostgreSQL 17 扩展了涉及FULL OUTER JOIN
和聚合的查询的并行性，与 PostgreSQL 16 相比，性能显著提升。

储存和vacuum改进

自动清理调整： PostgreSQL 17 引入了更智能的自动清理阈值，可根据表活动进行调整。与 PostgreSQL 16 相比，这可以减少清理操作，提高清理效率，尤其是对于高写入工作负载。
校验和验证： 校验和功能已经过优化，以减少开销，确保数据完整性检查而不影响 I/O 性能。

WAL 压缩

增强的 WAL 压缩： 虽然 PostgreSQL 15 引入了基本的 WAL 压缩，但 PostgreSQL 17 通过更快的算法和与现代存储系统的更好集成完善了此功能。这减少了预写日志的大小，而不会影响写入吞吐量。

索引改进

BRIN 索引改进： 块范围索引（BRIN）在 PostgreSQL 17 中得到了进一步优化，以支持更广泛的用例，包括比 PostgreSQL 16 具有更好更新性能的多列索引。
改进的 B 树重复数据删除： B 树重复数据删除现在适用于更多场景，减少索引膨胀并提高查找速度。

调整 PostgreSQL 17 以获得最佳性能

为了最大限度地发挥 PostgreSQL 17 的潜力，您必须调整数据库的各个方面。以下是一份全面的指南：

工作量分析

了解数据库工作负载对于实际调优至关重要。工作负载大致可分为 OLTP 或 OLAP；每种类型都可从不同的优化中获益。

OLTP 工作负载

重点领域：
○ 低延迟交易处理。
○ 高并发。
○ 最大化交易吞吐量。
优化步骤：
○连接池： 使用轻量级池工具（如PgBouncer）来维持最佳连接水平。
○索引： 为经常访问的表创建定制的索引，以加快查询速度。
○清理： 确保定期自动清理以防止表膨胀，尤其是在高更新场景中。
○锁管理：使用pg_stat_activity 和pg_locks视图监控锁争用并优化导致瓶颈的查询。

OLAP 工作负载

重点领域：
○ 高效处理大型复杂查询。
○ 处理大量的表扫描、聚合和连接。
优化步骤：
○并行查询： 启用并行查询执行并分配足够的资源（parallel_workers_per_gather
）。
○分区： 对大型数据集使用表分区来提高查询性能。
○增量排序： 利用 PostgreSQL 17 改进的增量排序功能对大型数据集进行排序。

PostgreSQL 性能调优的关键参数

以下参数允许您优化 PostgreSQL 数据库性能。

内存参数

内存分配显著影响数据库性能。PostgreSQL 17 提供了更好的内存管理工具来满足不同的工作负载。

共享缓冲区
分配内存以缓存经常访问的数据。设置为总 RAM 的 25-40%，大型系统上限为 8GB，除非基准测试表明有其他好处。监控pg_stat_activity
和pg_stat_database
实现 99% 或更高的缓存命中率。
工作内存
确定排序和哈希连接的内存。设置为每个连接 4-16MB，根据查询复杂度进行扩展。使用EXPLAIN
( ANALYZE
) 来识别需要更大内存分配的操作。
维护工作内存
专用于VACUUM
、索引和维护任务。默认为 256MB–1GB，但在批量操作（如VACUUM FULL
或）期间临时增加CREATE INDEX
可以提高性能。

I/O 和 WAL 参数

wal_buffers
调整 WAL 写入的内存分配。默认值 16MB 通常就足够了，但增加此值可以提高写入密集型工作负载的性能。监控pg_stat_bgwriter 可以帮助您确定是否需要更高的值。
最大长度
控制 WAL 段的增长。不应使用“大”之类的模糊术语，而应采用几何级数增长法。在使用 HammerDB 的测试中，当增加到 50GB 以上时，性能会有所改善，确保不会发生请求的检查点。使用pg_stat_bgwriter 监控WAL活动并优化此设置。
wal_compression
启用 WAL 压缩以减少磁盘 I/O，但会降低 CPU 使用率。这对于 I/O 密集型工作负载非常有利，因为减少磁盘写入是其首要任务。请根据系统功能评估权衡。

查询和索引参数

有效缓存大小
估计可用于查询计划程序优化的操作系统级文件系统缓存。通常设置为总系统内存的 50-75%，以确保高效使用索引并避免不必要的顺序扫描。
每个聚集的最大并行工作者数
控制并行查询工作者的数量。增加大型数据集上的复杂查询的此值有助于利用 PostgreSQL 17 改进的并行性。
autovacuum_vacuum_cost_limit
确定自动清理在暂停之前可以执行的工作量。对于高写入工作负载，增加此值可确保及时清理并防止回绕故障。
random_page_cost
表示非顺序磁盘页面提取相对于顺序磁盘页面提取的成本。默认值为 4.0，但在现代 SSD 或高性能磁盘上，几乎普遍建议将其降低到 1.1–2.0。此调整更好地反映了当前存储技术的随机 I/O 能力，并鼓励在有利的情况下进行索引扫描。

优化策略

监视pg_stat_bgwriter
的WAL 活动，以便您了解如何正确调整wal_buffers
和max_wal_size
大小。
对 WAL 设置使用几何增加而不是模糊的“大”值。
适当调整SSD 的random_page_cost
以利于索引扫描。
设置effective_cache_size
以反映可用内存，从而做出更好的规划决策。
调整autovacuum_vacuum_cost_limit
，以确保在高写入环境中高效完成vacuum过程。

连接和池参数

最大连接数
限制活动连接数。过载会导致性能瓶颈。使用 PgBouncer 等连接池可以有效管理高并发性。
空闲事务会话_超时
防止长时间运行的空闲事务持有锁。建议事务工作负载的超时时间为 5 到 15 分钟。

WAL 配置

预写日志 (WAL) 可确保数据的持久性和一致性，但不适当的配置可能会影响写入密集型的工作负载。

wal_compression
压缩 WAL 记录以减少 I/O 密集型工作负载的 I/O 开销。此参数会减少磁盘空间使用量，但会稍微增加 CPU 开销。测试对系统的影响以确认净收益。
checkpoint_timeout 和max_wal_size
控制检查点的频率和大小，增加到checkpoint_timeout
10 到 15 分钟，max_wal_size
对于写入繁重的工作负载，最小为 1GB 到 2GB。较长的间隔可减少 I/O 开销，但会增加崩溃后的恢复时间。
wal_buffers
在 WAL 记录写入磁盘之前充当临时缓冲区；对于高写入工作负载，至少设置为 16MB。
检查点完成目标
此参数决定检查点 I/O 在检查点间隔内的分布均匀程度。接近 1.0（例如 0.9）的值会更均匀地分散 I/O 工作负载，从而减少检查点期间的 I/O 峰值和性能下降。

自动清理设置

PostgreSQL 的自动清理过程可管理表膨胀和事务回绕预防。PostgreSQL 17 的更智能阈值使其更加高效。

autovacuum_vacuum_cost_limit
此参数限制了 vacuum 操作的成本，以最大限度地减少它们对活动查询的影响。增加高写入表的设置以确保及时清理。请注意，此设置通常设置为 -1，这意味着它遵循 vacuum_cost_limit。但是，它可以与ALTER TABLE
为每个表应用不同的限制一起使用。
autovacuum_freeze_max_age
此参数可防止事务 ID 回绕问题。监控pg_stat_all_tables 的年龄 (relfrozenxid) 并根据需要调整阈值。较高的值可降低冻结频率，分散 I/O 负载，以防止当多个表需要同时冻结时发生拥堵。

监控 Autovacuum

使用pg_stat_all_tables 视图跟踪自动清理活动并确保它与您的工作负载保持一致。了解冻结最大期限如何影响冻结间隔 有助于优化不同表之间的自动清理行为。

查询优化

高效的查询设计对于利用 PostgreSQL 17 的高级功能至关重要。

查询优化的步骤

解决以下关键点来优化您的查询：

分析执行计划
○ 使用 EXPLAIN（ANALYZE）来识别慢速查询和瓶颈。
○ 优化连接，避免对大型数据集进行顺序扫描，并利用索引。
利用并行查询
○ 使用parallel_setup_cost
和parallel_tuple_cost
启用聚合和连接的并行查询。
○ 调整parallel_workers_per_gather
至最佳平行度。
增量排序
○ PostgreSQL 17 改进了增量排序性能。确保使用 ORDER BY 子句的查询结构化以使用此功能。

有效地使用查询优化

确保索引与 ORDER BY 子句匹配，以利用排序优化。
使用 EXPLAIN ANALYZE 来验证查询计划器是否使用增量排序。
考虑使用 LIMIT 和 OFFSET 将大排序分成更小的组（如果适用）。
利用索引扫描策略避免不必要的排序操作。

避免过度索引

过多的索引会导致更高的维护开销；您应该只为经常查询的列创建索引。

监控索引使用情况：

借助pg_stat_user_indexes`来识别很少使用或从未使用过的索引。
要查找未使用的索引，请运行以下查询：

SELECT schemaname, relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;
复制

此查询检索从未扫描过的索引，表明它们可能未被使用。
定期使用pg_stat_all_indexes
检查索引膨胀并考虑重新索引或删除未使用的索引。
平衡索引与查询性能和维护开销以优化数据库效率。

索引优化

索引对于提高查询性能至关重要，PostgreSQL 17 的索引改进提供了额外的优化机会。您应该评估索引的使用情况，以确保遵循一些最佳实践：

使用 BRIN 索引
○ BRIN（块范围索引）非常适合大型、顺序存储的数据集，例如时间序列数据。
○ BRIN 汇总数据块而不是索引每一行，从而减少了存储要求。
定期指数维护
○监控索引膨胀： 使用pg_stat_user_indexes
视图来识别膨胀的索引。

SELECT 
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    (pg_relation_size(indexrelid)::numeric / NULLIF(pg_relation_size(relid), 0)) AS index_to_table_ratio
FROM 
    pg_stat_user_indexes
JOIN 
    pg_class ON pg_stat_user_indexes.indexrelid = pg_class.oid
WHERE 
    pg_relation_size(indexrelid) > 10000000 -- Filter indexes larger than ~10MB
ORDER BY 
    index_to_table_ratio DESC;
复制

如果索引与其关联表相比过大，则可能已膨胀，应优先进行维护以提高效率。索引与表大小比率高（例如大于 1.0 的值）可能表示索引膨胀可能导致效率低下，从而影响查询性能和存储利用率。
○重建索引：定期运行REINDEX 以防止因索引膨胀而导致性能下降。
多列索引
○ PostgreSQL 17 增强了 BRIN 和 B 树索引，以更有效地支持多列索引。利用此功能进行具有多个 WHERE 条件的查询。

连接池

高效处理高并发工作负载需要使用PgBouncer等连接池工具。pgBouncer 通过重用现有连接来最大限度地减少连接建立开销。要充分发挥连接池的优势，您应该：

优化设置
○ 根据应用程序的行为配置 pgBouncer 池模式（会话、事务、语句）。
○ 设置max_client_conn
和default_pool_size
平衡连接限制和应用程序要求。
定期监控你的申请
○ 使用 PgBouncer 的管理控制台监控连接使用情况并动态调整设置。

结论

PostgreSQL 17 代表了数据库性能的重大飞跃，为现代工作负载提供了一系列优化。从逻辑复制中的无缝故障转移到更出色的自动清理机制和改进的索引，PostgreSQL 17 树立了新的标准。虽然 PostgreSQL 15 和 16 引入了关键功能，但 PostgreSQL 17 中的改进使其成为希望提高效率和可扩展性的企业的不二之选。

无论是管理高并发 OLTP 系统还是运行复杂的分析查询，PostgreSQL 17 都具有满足您需求的工具和改进。此版本可以通过精心调整来释放前所未有的性能水平，确保您的数据库基础架构为未来做好准备。