PostgreSQL优化数据库参数

开箱即用的默认 PostgreSQL 配置并未针对任何特定工作负载进行调整。设置默认值以确保 PostgreSQL 在任何地方运行，消耗最少的资源，并且不会导致任何漏洞。它具有所有数据库参数的默认设置。数据库管理员或开发人员主要负责根据系统的工作负载调整 PostgreSQL。在这篇博文中，我们将深入研究 PostgreSQL 性能调优的世界，并建立一些用于设置数据库参数的基本指南，以根据工作负载提高 PostgreSQL 数据库的性能。

什么是 PostgreSQL 性能调优？

PostgreSQL 性能优化旨在通过调整配置和实施最佳实践来识别和解决瓶颈、提高查询速度以及最大化数据库吞吐量和响应能力，从而提高 PostgreSQL 数据库系统的效率。

关键领域包括：

1. 配置参数调整：此调整涉及根据特定硬件和要求更改内存分配、磁盘 I/O 设置和并发连接等变量。
2. 查询优化：分析查询执行计划，识别慢速查询，并通过适当的索引技术、查询重写或利用部分索引或物化视图等高级功能对其进行优化。
3. 硬件优化：您需要确保 CPU、内存和存储组件满足数据库工作负载的性能要求。
4. 统计和监控：通过启用和分析性能统计监控，您可以识别瓶颈并随时间跟踪查询性能。
5. 索引调优：PostgreSQL 提供了多种类型的索引，包括 B 树、散列和广义倒排索引 (GIN/GiST)。选择合适的索引类型，创建复合索引，定期对数据库进行分析和重建索引，可以大大提高查询速度。
6. 架构设计：评估数据库架构设计并进行调整，例如对大表进行分区、消除冗余数据以及为经常访问的信息对表进行非规范化，可以提高性能。
7. 连接池：通过实施连接池和缓存策略机制，最大限度地减少连接开销并改进频繁访问的数据的响应时间。
8. 负载平衡和复制：通过使用连接池、只读备库或实施主备库复制设置等技术将工作负载分布到多个服务器来水平扩展数据库系统。
9. 内存管理：由于 PostgreSQL 的性能在很大程度上依赖于高效的内存使用，因此监控和调整与内存相关的关键配置参数（如 shared_buffers、work_mem 和 effective_cache_size）会对性能产生重大影响。

PostgreSQL 性能优化是一个持续的过程，涉及监控、基准测试和调整以维护高性能的 PostgreSQL 数据库。此外，及时了解 PostgreSQL 的最新版本和改进也会有所帮助。

为什么 PostgreSQL 性能调优很重要？

PostgreSQL 数据库的性能对应用程序的整体有效性有重大影响。这就是为什么企业拥有高性能数据库至关重要的原因：

响应能力：想象一下永远等待您需要立即显示的数据。没有人有时间这样做！缓慢的查询和数据检索会导致令人沮丧的延迟，从而影响用户体验。为确保无缝交互，性能良好的数据库必不可少，可提供快速响应时间并满足用户期望。

吞吐量：应用程序的吞吐量直接受到数据库处理和服务查询的速度的影响。高性能数据库可显着减少查询执行时间，使应用程序能够处理更多并发请求并更快地交付数据。

可扩展性：随着应用程序扩展并需要处理更多数据和用户负载，数据库必须相应地扩展。一个性能良好的 PostgreSQL 数据库可以有效地管理增加的工作负载，确保应用程序即使在大量使用时也能保持响应并表现良好。

高效的资源利用：启用 CPU、内存和磁盘 I/O 等系统资源的有效利用可以优化您的 PostgreSQL 数据库，同时保持功能。这不仅可以通过最大限度地减少硬件要求来节省成本，而且还有可能减少云费用。

数据完整性：这对于确保可靠和高效的数据库至关重要。通过结合 ACID 合规性、事务管理和强大的错误处理等功能，数据库可以防止数据损坏或丢失。这对于依赖精确和一致的数据来满足其决策和运营需求的企业来说尤为重要。

竞争优势：用户保留率和客户满意度可以成就或破坏任何业务。始终确保出色的应用程序性能的高性能数据库可以为企业带来竞争优势。

了解查询对 PostgreSQL 性能的影响

请记住，虽然优化 PostgreSQL 服务器配置可以提高性能，但数据库开发人员在为应用程序编写查询时也必须勤奋。如果查询执行可以使用索引的全表扫描或执行繁重的连接或昂贵的聚合操作，那么即使调整了数据库参数，系统的性能仍然很差。在编写数据库查询时注意性能很重要。

尽管如此，数据库参数也非常重要，所以让我们来看看最有可能提高性能的8个可调的 PostgreSQL 参数。

下面是一些 PostgreSQL 参数，可以根据您的系统和特定工作负载调整这些参数以提高性能。

shared_buffer(共享缓冲区)

PostgreSQL 使用自己的缓冲区，也使用内核缓冲 IO。这意味着数据在内存中存储了两次，首先是在 PostgreSQL 缓冲区中，然后是内核缓冲区。与其他数据库不同，PostgreSQL 不提供直接 IO。这称为双缓冲。PostgreSQL 缓冲区称为shared_buffer，它是大多数操作系统最有效的可调参数。此参数设置 PostgreSQL 将使用多少专用内存作为缓存。

shared_buffer 的默认值设置得很低，您不会从中获益太多。它很低是因为某些机器和操作系统不支持更高的值。但在大多数现代机器中，您需要增加此值以获得最佳性能。

推荐值为机器总 RAM 的 25%。您应该尝试一些较低和较高的值，因为在某些情况下，我们可以通过超过 25% 的设置获得良好的性能。配置实际上取决于您的机器和工作数据集。如果您的工作数据集可以很容易地装入您的 RAM，那么您可能希望增加 shared_buffer 值以包含整个数据库，以便整个工作数据集可以驻留在缓存中。也就是说，您显然不想为 PostgreSQL 保留所有 RAM。

在生产环境中，观察到较大的 shared_buffer 值会提供非常好的性能，但您应该始终进行基准测试以找到合适的平衡点。

testdb=# SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)

注意：小心一些内核不允许更大的值，特别是在 Windows 中，没有使用更高的值。

wal_buffers

PostgreSQL 将其 WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。由 wal_buffers 定义的缓冲区的默认大小为 16MB，但如果您有很多并发连接，则更高的值可以提供更好的性能。

effective_cache_size(有效缓存大小)

effective_cache_size 提供可用于磁盘缓存的内存估计值。这只是一个指南，而不是确切的分配内存或缓存大小。它不分配实际内存，而是告诉优化器内核中可用的缓存量。如果这个值设置得太低，查询规划器可以决定不使用某些索引，即使它们会有帮助。因此，设置一个大的值总是有益的。

work_mem(工作内存)

此配置用于复杂排序。如果你必须做复杂的排序，然后增加 work_mem 的值以获得好的结果。内存中排序比溢出到磁盘的排序快得多。设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是针对每个用户的排序操作。因此，如果你有很多用户试图执行排序操作，那么系统会 为所有用户分配 work_mem * total 排序操作。 全局设置此参数会导致非常高的内存使用率。所以强烈建议在session级别修改这个。

testdb=# SET work_mem TO "2MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=509181.84..1706542.14 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=508181.79..514431.86 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)
(5 rows)

初始查询的排序节点的估计成本为 514431.86。成本是任意计算单位。对于上面的查询，我们只有 2MB 的 work_mem。出于测试目的，让我们将其增加到 256MB，看看是否对成本有任何影响。

testdb=# SET work_mem TO "256MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=355367.34..1552727.64 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=354367.29..360617.36 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)

查询成本从 514431.86 减少到 360617.36 — 减少了 30%。

maintenance_work_mem(维护工作内存)

maintenance_work_mem是用于维护任务的内存设置。默认值为 64MB。设置较大的值有助于执行VACUUM、RESTORE、CREATE INDEX、ADD FOREIGN KEY 和 ALTER TABLE 等任务。

postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)

postgres=# CHECKPOINT;
postgres=# set maintenance_work_mem to '256MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)

当 maintenance_work_mem 设置为仅 10MB 时，索引创建时间为 170091.371ms，但当我们将 maintenance_work_mem 设置增加到 256MB 时，索引创建时间减少到 111274.903 ms。

synchronous_commit(同步提交)

这用于强制提交将等待 WAL 写入磁盘，然后再向客户端返回成功状态。这是性能和可靠性之间的权衡。如果您的应用程序设计为性能比可靠性更重要，请关闭 synchronous_commit。这意味着在成功状态和保证写入磁盘之间会有时间间隔。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能会丢失。在这种情况下，事务提交非常快，因为它不会等待刷新 WAL 文件，但可靠性会受到影响。

checkpoint_timeout, checkpoint_completion_target(检查点超时，检查点完成目标)

PostgreSQL 将更改写入 WAL。检查点进程将数据刷新到数据文件中。此活动在 CHECKPOINT 发生时完成。这是一个昂贵的操作，会导致大量的 IO。整个过程涉及昂贵的磁盘读/写操作。用户总是可以在必要时发出 CHECKPOINT 或通过 PostgreSQL 的参数 checkpoint_timeout 和 checkpoint_completion_target 使系统自动化。

checkpoint_timeout 参数用于设置 WAL 检查点之间的时间。将此值设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但也会损害性能，因为每个检查点最终都会消耗宝贵的系统资源。checkpoint_completion_target 是检查点完成的检查点之间的时间分数。高频率的检查点会影响性能。为了平滑检查点，checkpoint_timeout 必须是一个较低的值。否则，操作系统将累积所有脏页，直到满足比率，然后进行大刷写。

改进 PostgreSQL 性能调优

可以调整更多参数以获得更好的性能，但这些参数的影响小于此处突出显示的参数。最后，我们必须始终牢记，并非所有参数都与所有应用程序类型相关。一些应用程序通过调整参数表现得更好，而另一些则不然。必须根据应用程序及其运行的操作系统的特定需求来调整 PostgreSQL 参数。