小团队PostgreSQL性能调优：80/20法则指南

关于 PostgreSQL 调优的不适真相

大多数生产环境中的 PostgreSQL 安装都运行在默认设置上，这些设置最初是为拥有 256MB 内存的机器编写的，以确保安全。几十年来，这些默认设置没有实质性的改变。在现代应用中使用每月 40 美元的云实例，从第一天开始，甚至在编写任何查询之前，你就已经将数据库潜在性能的 70-80% 留在了桌面上。

反面同样重要：大多数应用程序根本不需要高级调优。如果你的数据集能放入内存，查询使用索引，而且你不会用数千个并发连接冲击服务器，Postgres 将能处理比你预期更多的流量。本指南的目标不是把每位工程师都变成数据库管理员（DBA）。目标是确保你没有留下容易获得的性能提升机会。

真正重要的三个内存设置

Postgres 提供了数百个配置选项。其中三个选项通过配置就能获得绝大多数的性能提升。先确保这三个设置正确；如果仍有问题，再考虑其他所有设置。

shared_buffers

shared_buffers 是 Postgres 用于自身数据页共享缓存的内存量。默认值是 128MB，对于任何现代服务器来说都低得不合理。广泛接受的起点是系统总 RAM 的 25%。在拥有 16 GB RAM 的服务器上，将其设置为 4GB。

Postgres 并不是主机上唯一的内存消费者。操作系统的页面缓存也会保存数据库文件页面，并且在许多工作负载中，其淘汰效率比 Postgres 自身的缓冲池更高。将 shared_buffers 设置得过高会从操作系统页面缓存中窃取 RAM，并可能使性能变差。保持在系统 RAM 的 40% 或以下。

work_mem

work_mem 控制每个排序或哈希操作在溢出到磁盘前可以使用多少内存。默认值是 4MB。当排序溢出到磁盘时，查询执行时间可能会增加一个数量级。你会在 EXPLAIN ANALYZE 输出中看到这一点，在哈希节点上显示为 “Batches: N”，或在排序节点上显示为 “Sort Method: external merge Disk”——这两种迹象都表明 work_mem 太低。

问题是：这个值是每个排序操作的，而单个复杂查询可以并行运行多个排序操作。一个会话也可以打开多个并行工作线程。有效的内存上限大约是 work_mem × max_connections × average_operations_per_query。在共享服务器上，将 work_mem 设置为 64MB 并有 200 个连接时，峰值时可能消耗 12 GB RAM。从 16MB 到 32MB 开始，只为需要它的角色或会话提高该值，对于已知的大型分析查询，在会话级别使用 SET work_mem = '256MB'。

effective_cache_size

effective_cache_size 不会分配内存。它是给查询规划器的一个提示，关于操作系统页面缓存加上 shared_buffers 可以合理提供多少内存。更高的值告诉规划器索引扫描成本更低，因为所需的页面很可能已经在缓存中。将其设置为系统总 RAM 大约的 75%。在 16 GB 服务器上，这意味着 12GB。

将此值设置得过低会导致规划器在索引实际上更快的情况下仍然倾向于顺序扫描，因为它假设页面将从磁盘获取。

无需查询规划学位也能读懂 EXPLAIN ANALYZE

EXPLAIN ANALYZE 是针对单个查询最强大的诊断工具。输出结果看起来令人望而生畏，但实际上有四个要点涵盖了大多数问题。

实际行数与估计行数

计划中的每个节点都显示 “rows=N”（规划器的估计值）和 “actual rows=N”（运行时的实际值）。当估计值相差十倍或更多时，规划器可能基于过时的统计信息做出了糟糕的计划选择。运行 ANALYZE tablename 来刷新统计信息。如果估计偏差仍然存在，考虑使用 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500 为导致问题的特定列增加 default_statistics_target。

顺序扫描与索引扫描

在循环中对大表进行顺序扫描几乎总是一个问题。寻找循环次数高或实际行数高的 “Seq Scan” 节点。对同一张表进行具有类似选择性的索引扫描通常可以将执行时间减少一到三个数量级。

排序方法：外部合并磁盘

如上所述，这个短语表示排序溢出到磁盘。为会话或查询增加 work_mem，或者添加一个提供预排序输出并完全消除排序步骤的索引。

大行数上的嵌套循环

当内层表很小时，嵌套循环连接是高效的。当两边都很大时，哈希连接或合并连接更快。规划器通常能做出正确的选择，但过时的统计信息可能导致它低估行数并错误地选择嵌套循环。解决方法是使用新的统计信息，并且在调查期间，如果需要，可以通过 enable_nestloop = off 会话参数使用显式的连接提示。

运行 EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 而不是普通的 EXPLAIN ANALYZE。BUFFERS 选项显示了多少共享块是从缓存命中而不是从磁盘获取的，这能立即告诉你查询是否受 I/O 限制。

索引策略：选择合适的工具

Postgres 支持多种索引类型。使用错误的类型不仅会错失优化机会——在某些情况下，它还会通过增加写入开销而不提高读取性能来主动损害性能。

B-tree：覆盖大多数情况的默认选择

B-tree 索引支持标量值的相等、范围和排序操作。如果你不确定使用哪种索引类型，可以从 B-tree 开始。它们适用于文本、数字、时间戳、UUID 和大多数其他常见列类型。它们支持 =、<、>、BETWEEN、LIKE 'prefix%' 和 ORDER BY。

GIN：全文搜索和数组

GIN（通用倒排索引）索引复合值的单个元素——数组元素、JSONB 键或 tsvector 中的词元。当使用 @>、&& 或 @@ 运算符进行查询时，使用 GIN。GIN 索引比 B-tree 更大且构建更慢，但对于数组或 JSONB 列的包含查询则快得多。

GiST：几何和范围类型

GiST 支持几何类型（point、polygon、circle）、范围类型（daterange、tsrange），并且是 PostGIS 的支持索引。当数据类型不映射到简单的标量比较时，使用 GiST。对于地理邻近查询，使用 GiST 和 PostGIS 是标准方法。

部分索引：只索引你查询的内容

部分索引只包含满足 WHERE 子句的行。如果你的 95% 的查询都过滤 status = 'active'，而只有 3% 的行是活跃状态，那么部分索引可以消除 97% 的索引大小：

CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active';

部分索引经常被那些首先考虑覆盖索引的团队所忽视。每当一列具有高度倾斜的值分布时，都应该评估部分索引。

覆盖索引：消除堆获取

当 Postgres 使用索引查找匹配行，然后从表堆中获取额外的列时，第二次读取称为堆获取。如果查询频繁执行，并且可以使用 INCLUDE 将额外的列包含在索引中，则可以完全消除堆获取：

CREATE INDEX idx_users_email ON users (email) INCLUDE (id, name);

覆盖索引在频繁执行、高查询量的场景中最有价值，此时索引选择性高且包含的列较窄。

N+1 查询问题：API 性能的隐形杀手

当代码获取 N 条记录列表，然后为每条记录发出一个额外查询以获取相关数据时，就会出现 N+1 问题。获取 100 个订单，然后查询每个订单的客户详情，会产生 101 个查询而不是 2 个。每个查询单独耗时 1-5 毫秒，单独看似乎无害。但它们合在一起，每个 API 请求会消耗 100-500 毫秒。

如何检测 N+1 问题

最可靠的检测方法是 pg_stat_statements。通过将 pg_stat_statements 添加到 shared_preload_libraries 并运行 CREATE EXTENSION pg_stat_statements 来启用它。然后查询：

SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 20;

一个有数万次调用、单次调用时间短但总时间长的查询是典型的 N+1 问题特征。解决方案几乎总是使用 JOIN 或批量 IN (...) 查询，并结合结果的内存关联。

应用级日志是另一种检测方法。Rails、Django 和 SQLAlchemy 等框架可以配置为记录每个 SQL 语句。按频率排序。每个请求运行数十次的参数化查询是批量处理的候选对象。

使用 PgBouncer 进行连接池化

每个 Postgres 连接消耗约 5-10 MB 服务器内存并需要一个后端进程。一个打开 200 个持久连接的应用在执行单个查询前已经消耗了 1-2 GB RAM。当应用水平扩展时，连接数量也随之增加，Postgres 最终会花更多时间管理连接而非服务查询。

PgBouncer 位于应用和 Postgres 之间，将多个应用连接多路复用到较小的实际 Postgres 连接池中。在 事务池化模式（最激进的设置）下，Postgres 连接仅在单个事务期间保持，然后返回到池中。一个 50 个连接的 PgBouncer 池可以轻松服务数千个应用连接。

有效的 PgBouncer 配置

• pool_mode = transaction — 最佳效率。在某些配置下，与会话级功能（如咨询锁和预处理语句）不兼容。
• max_client_conn — 设置为预期的峰值应用程序连接数加上缓冲空间。从1000开始。
• default_pool_size — 每个数据库/用户对的实际Postgres连接数。常用公式是(2 × CPU核心数) + 有效磁盘轴数。在配备SSD存储的4核服务器上，从10-20开始。
• server_idle_timeout — 空闲服务器连接在被关闭前存活的时间。600秒是一个合理的默认值。

在使用RDS或Cloud SQL等托管数据库且在基础设施级别不允许使用PgBouncer的团队，应该使用这些平台提供的托管连接池器（RDS Proxy、Cloud SQL Auth Proxy），而不是完全跳过连接池。

VACUUM和自动VACUUM：您不能忽视的维护工作

Postgres使用MVCC（多版本并发控制）来处理并发事务。每次UPDATE和DELETE都会创建一个新的行版本，而不是就地修改。死行版本会随时间累积，必须通过VACUUM进行清理。如果死元组没有被回收，表膨胀会增加，查询会因为扫描更多死行而变慢，最终在称为事务ID环绕的灾难性事件中，事务ID计数器会回绕。

自动VACUUM默认启用并自动处理这个问题——但其默认配置是为轻量级工作负载调整的。高写入表需要更激进的设置。

关键自动VACUUM设置

• autovacuum_vacuum_scale_factor — 触发VACUUM前必须死亡的表大小的比例。默认是0.2（20%）。在大表上，20%的死元组可能是数百万行。对于高写入表，使用每表存储参数将其降至0.01或0.02。
• autovacuum_vacuum_threshold — 触发VACUUM的最小死行数，不考虑比例因子。默认是50。在大而稳定的表上提高到1000。
• autovacuum_max_workers — 并发自动VACUUM工作进程数。默认是3。在拥有许多表或高写入吞吐量的服务器上提高到5-6。
• autovacuum_vacuum_cost_delay — 应用于自动VACUUM的节流，以减少I/O影响。默认是2ms。在SSD存储上，设置为0或1ms，以允许自动VACUUM全速运行。

当只有少数表有问题时，使用每表覆盖比更改全局设置更清晰：

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);

监控膨胀

查询 pg_stat_user_tables 以查看每个表的 n_dead_tup。活跃表上持续存在数百万个死元组意味着自动清理（autovacuum）未能跟上节奏。检查 pg_stat_activity 中是否有长时间运行的事务 — 一个持续数小时的事务会阻止清理回收其在启动后修改的任何行。

使用 pg_stat_statements 查找慢查询

pg_stat_statements 是在任何生产 Postgres 实例上应首先启用的扩展。它跟踪每个不同查询形状的累积执行统计信息，并通过参数替换进行标准化。快速审计中最有用的查询是：

SELECT query, calls, total_exec_time, mean_exec_time, stddev_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

按 total_exec_time 降序排序，以找出消耗最多总体数据库时间的查询 — 而不仅仅是执行最慢的单个查询。一个执行时间为 50 毫秒但每小时运行 100,000 次的查询，比一个执行时间为 5 秒但每天只运行一次的查询成本更高。

stddev_exec_time 相对于 mean_exec_time 较高表示不一致性 — 该查询有时快有时慢，通常由缓存未命中、锁等待或检查点干扰引起。

在负载测试前使用 SELECT pg_stat_statements_reset() 重置统计信息，以获得一个干净的基线。

配置变更前后的真实数据

以下示例是将本指南中的设置应用于先前未调整的实例后所获得的性能提升的代表性案例。

何时添加只读副本

在主实例经过适当调优之前，读取副本经常被提议作为扩展方案。在将shared_buffers保持在默认值并容忍N+1查询的情况下添加副本，是一种通过水平扩展不良实践的高成本方式。

在以下情况下考虑使用读取副本：

• 主实例的CPU瓶颈来自于查询执行，而不是少量可以优化的编写不佳的查询。
• 报告和分析查询在业务时间内导致主实例上的锁争用或检查点压力。
• 读写比例确实严重倾斜——90%以上是读取——并且主实例已经经过调优。
• 高可用性要求强制要求一个备用实例，无论负载如何。

使用RDS或Cloud SQL运行的团队可以在几分钟内启用读取副本。在耗尽单实例调优之前就这样做的诱惑是可以理解的，但它增加了操作复杂性——连接路由、复制延迟感知和潜在的一致性问题——大多数小团队并不需要这些。

足够好的Postgres：何时停止调优

一个正确配置了内存设置、拥有最新表统计信息、选择了合适索引、使用PgBouncer进行连接管理并且自动清理功能健康的Postgres实例，无需任何进一步干预就能处理大多数生产Web应用的负载。对于小团队来说，最佳选择是花一天时间专注于上述更改，然后退一步。

以下迹象表明您已经真正耗尽了单实例优化，需要进一步采取措施：

• CPU利用率持续高于70%，且pg_stat_statements中的顶级查询已经过优化。
• 工作集超过了最大可行实例大小的可用内存。
• 写入吞吐量超过了单个主实例可以提交的量——在硬件上通常是每秒数十万次写入，大多数Web应用不必担心这个问题。
• 表大小达到数百亿行，分区修剪将显著减少查询范围。

如果这些条件都不适用，那么下一个性能提升更有可能来自应用层缓存（Redis、Memcached）或CDN，而不是更深入的数据库调优。知道数据库不是瓶颈与知道如何调优数据库一样有价值。

实用的起步检查清单

• 将 shared_buffers 设置为 RAM 的 25%，effective_cache_size 设置为 RAM 的 75%，work_mem 设置为一个保守的 16–32 MB。重启 Postgres。
• 启用 pg_stat_statements 并在生产流量下运行 24–48 小时后再做结论。
• 按 total_exec_time 对 pg_stat_statements 进行排序。调查前 10 条记录。
• 对每个查询运行 EXPLAIN (ANALYZE, BUFFERS)。查找大表上的顺序扫描、磁盘溢出排序和行数估计错误。
• 检查 pg_stat_user_tables 中的 n_dead_tup。持续存在高死亡元组计数的表需要调整 autovacuum 或手动执行 VACUUM。
• 使用 SELECT count(*) FROM pg_stat_activity 计算打开的连接数。如果经常超过 100，请评估使用 PgBouncer。
• 添加缺失的索引。在查询模式清晰的情况下，优先使用部分索引和覆盖索引。
• 只有在完成以上所有步骤后：才考虑是否需要读取副本或垂直扩展。

关键要点

• 三项配置更改 — shared_buffers、work_mem 和 effective_cache_size — 在开箱即用的安装上产生的增益比几乎所有其他更改加起来还要多。
• pg_stat_statements 是查询调查的正确起点。切勿仅凭直觉进行优化。
• 索引类型很重要：标量使用 B-tree，数组和 JSONB 使用 GIN，几何和范围类型使用 GiST。部分索引和覆盖索引使用不足但效果显著。
• N+1 查询问题可以通过查看 pg_stat_statements 中的查询调用次数来检测，而不仅仅是每个查询的持续时间。
• 事务池模式的 PgBouncer 可以解决连接耗尽问题，这是任何单查询调整都无法解决的。
• Autovacuum 的默认配置是保守的。高写入表需要按表覆盖配置。
• 读取副本和分区解决了实际问题 — 但这些问题并不是拖慢大多数小型团队应用程序的原因。

常见问题

如何知道我的 Postgres 实例是否已正确调优？

已调优的实例具有正确的内存设置，没有长时间运行的 autovacuum 积压，连接数远低于服务器限制（或由连接池管理），并且 pg_stat_statements 中没有明显优化机会的查询 — 意味着没有应该有索引的大表上的顺序扫描，也没有频繁执行查询的磁盘溢出排序。

如果我使用的是 RDS 之类的托管数据库，是否还需要 PgBouncer？

RDS 支持 RDS Proxy 作为其托管等效方案。对于大多数使用 RDS 的小型团队，RDS Proxy 是更简单的选择，因为它能自动处理故障转移感知。权衡之处在于成本和延迟开销。在应用服务器上运行的 PgBouncer 实际上是免费的，并且增加了亚毫秒级的延迟。

我应该多久运行一次 ANALYZE？

Autovacuum 会基于与 VACUUM 相同的成本阈值自动运行 ANALYZE。在批量数据加载（导入、大量删除、批量更新）后，手动运行 ANALYZE 很有用，因为此时自动触发器可能尚未触发。在任何改变了表中超过 10% 行的操作后，明确运行 ANALYZE。

小型团队何时应该聘请 DBA？

当性能问题持续需要比本指南中技术更深入的调查，当数据完整性需求需要复制和故障转移架构，或者当数据库基础设施已成为业务上的重大风险时。对于大多数少于 50 名工程师的团队，像这样一致应用的 PostgreSQL 性能调优指南可以大大推迟这一需求。