PostgreSQL 性能瓶颈的 7 大常见问题及解决方案

当每个慢请求都得到相同的回答时，PostgreSQL 的性能优化工作就会变得糟糕：“加个索引”或“增加内存”。有时这是对的。但有时数据库可能是在等待锁、将排序溢出到磁盘、被空闲连接淹没，或者因为自动清理（autovacuum）落后而读取了比预期多十倍的表页面。

一个有用的习惯是在做出任何更改之前先识别瓶颈。缓慢的 API 端点只是症状。数据库通常可以告诉你时间花在了哪里：扫描、连接、排序、从磁盘读取、等待另一个事务，还是打开了太多会话。

1. 低效的查询执行计划

性能缓慢的最常见原因之一是 SQL 查询优化不佳。PostgreSQL 的查询规划器很复杂，但在处理复杂查询或过时的统计信息时，有时会生成低效的执行计划。

识别瓶颈

使用 EXPLAIN 和 EXPLAIN ANALYZE 来了解 PostgreSQL 如何执行你的查询。EXPLAIN 显示计划中的执行方式，而 EXPLAIN ANALYZE 会实际运行查询并提供实际的时间和行数。

-- 查看执行计划：
EXPLAIN SELECT * FROM users WHERE email LIKE 'john.doe%';

-- 查看计划和实际执行细节：
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'john.doe%';

注意查找：

在应该使用索引的大表上出现顺序扫描。
与实际行数相比，行数估计误差很大。
当哈希连接或合并连接可能更合适时，使用了嵌套循环连接。

解决方案

添加合适的索引：确保为 WHERE、JOIN、ORDER BY 和 GROUP BY 子句中使用的列创建了索引。对于带有前导通配符（%）的 LIKE 子句，B-tree 索引通常无效；可以考虑全文搜索或三元组索引。
重写查询：有时，更简单或结构不同的查询可以产生更好的计划。
更新统计信息：PostgreSQL 使用统计信息来估计谓词的选择性。过时的统计信息可能会误导规划器。
```
ANALYZE table_name;
-- 或者针对所有表：
ANALYZE;
```
调整查询规划器参数：work_mem 和 random_page_cost 可以影响规划器的选择，但应谨慎调整。

2. 缺失或无效的索引

索引对于快速数据检索至关重要。没有索引，PostgreSQL 必须执行顺序扫描，读取表中的每一行来查找匹配的数据，这对于大表来说非常缓慢。

识别瓶颈

EXPLAIN ANALYZE 输出：在查询计划中查找大表上的 Seq Scan。
数据库监控工具：像 pg_stat_user_tables 这样的工具可以显示表扫描次数。

解决方案

创建 B-tree 索引：这是最常见的类型，适用于等值（=）、范围（<、>、<=、>=）和不带前导通配符的 LIKE 操作。
```
CREATE INDEX idx_users_email ON users (email);
```
使用其他索引类型：
- GIN/GiST：用于全文搜索、JSONB 操作和几何数据类型。
- 哈希索引：用于等值检查（在较新的 PostgreSQL 版本中由于 B-tree 的改进而较少使用）。
- BRIN（块范围索引）：用于数据物理上相关的大表。
部分索引：只索引行的子集，当查询经常针对特定条件时很有用。
```
CREATE INDEX idx_orders_pending ON orders (order_date) WHERE status = 'pending';
```

表达式索引：索引函数或表达式的结果。

CREATE INDEX idx_users_lower_email ON users (lower(email));

避免冗余索引：索引过多会减慢写操作（INSERT、UPDATE、DELETE）并消耗磁盘空间。

3. 自动清理活动过多或不足

PostgreSQL 使用多版本并发控制（MVCC）系统，这意味着 UPDATE 和 DELETE 操作不会立即删除行。相反，它们会将行标记为已废弃。VACUUM 回收这些空间并防止事务 ID 回卷。自动清理（autovacuum）自动化了这个过程。

识别瓶颈

高 CPU/IO 负载：自动清理可能消耗大量资源。
表膨胀：表现为 pg_class.relpages 和 pg_class.reltuples 与实际数据大小或预期行数之间存在较大差异。
pg_stat_activity：查找长时间运行的 autovacuum worker 进程。
pg_stat_user_tables：监控 n_dead_tup（死元组数量）以及 last_autovacuum/last_autoanalyze 时间。

解决方案

调整自动清理参数：在 postgresql.conf 或按表设置中调整参数。
- autovacuum_vacuum_threshold：触发清理的最小死元组数量。
- autovacuum_vacuum_scale_factor：考虑清理的表大小比例。
- autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor：ANALYZE 的类似参数。
- autovacuum_max_workers：并行自动清理工作进程的数量。
- autovacuum_work_mem：每个工作进程可用的内存。
按表设置的示例：
```
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);
```
手动执行 VACUUM：用于立即回收空间或在自动清理跟不上时使用。
```
VACUUM (VERBOSE, ANALYZE) table_name;
```
仅在绝对必要时使用 VACUUM FULL，因为它会锁定表并重写整个表，可能造成严重干扰。
关注长时间运行的事务：长时间运行的事务可能会保持旧行版本开放，阻止清理。
监控事务 ID 年龄：理解 vacuum_freeze_min_age、autovacuum_freeze_max_age 和数据库 age(datfrozenxid) 对于防止回卷紧急情况至关重要。

4. 硬件资源不足（CPU、RAM、IOPS）

PostgreSQL 的性能直接依赖于底层硬件。CPU 或 RAM 不足，或磁盘 I/O 缓慢，都可能造成严重的瓶颈。

识别瓶颈

系统监控工具：Linux 上的 top、htop、iostat、vmstat；Windows 上的性能监视器。
pg_stat_activity：查找等待锁的查询（wait_event_type = 'IO'、'LWLock' 等）。
高 CPU 使用率：持续接近 100%。
高磁盘 I/O 等待时间：系统花费大量时间等待磁盘操作。
可用内存低 / 高交换使用率：表明 RAM 不足。

解决方案

CPU：确保有足够的核心，特别是对于并发工作负载。PostgreSQL 在较新版本中利用多核进行并行查询执行和后台进程。
RAM（shared_buffers、work_mem）：
- shared_buffers：数据块的缓存。常见建议是系统 RAM 的 25%，但应根据工作负载进行调整。
- work_mem：用于排序、哈希和其他中间操作。work_mem 不足会强制溢出到磁盘。
磁盘 I/O：
- 使用 SSD：对于数据库工作负载，比 HDD 快得多。
- RAID 配置：优化读写性能（例如 RAID 10）。
- 分离 WAL 驱动器：将预写日志（WAL）放在单独的快速驱动器上可以提高写入性能。
网络：确保客户端-服务器通信有足够的带宽和低延迟，特别是在分布式环境中。

硬件症状需要证据。如果 CPU 高而磁盘等待低，请查找昂贵的计划、表达式密集型查询、JSON 处理或过多的活动工作进程。如果 I/O 等待高，请查看 EXPLAIN (ANALYZE, BUFFERS) 中的缓冲区读取、检查点行为以及热表是否适合内存。如果交换已激活，请在增加更多查询并发之前减少连接压力或内存设置。

5. `postgresql.conf` 配置不当

PostgreSQL 的 postgresql.conf 文件包含数百个控制其行为的参数。默认设置通常比较保守，没有针对特定工作负载或硬件进行优化。

识别瓶颈

整体性能低下：所有查询时间都很慢。
过多的磁盘 I/O：与可用 RAM 相比。
内存使用：系统显示内存压力迹象。
查阅性能调优指南：了解常见的优化值。

解决方案

需要考虑的关键参数：

shared_buffers：（如上所述）数据块的缓存。从系统 RAM 的约 25% 开始。
work_mem：排序/哈希的内存。根据显示磁盘溢出的 EXPLAIN ANALYZE 输出进行调整。
maintenance_work_mem：用于 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 的内存。较大的值可以加快这些操作。
effective_cache_size：帮助规划器估计操作系统和 PostgreSQL 可用于缓存的内存量。
wal_buffers：WAL 写入的缓冲区。如果写入负载高，则增加。
checkpoint_completion_target：将检查点写入分散到一段时间内，减少 I/O 峰值。
max_connections：适当设置；过高会耗尽资源。
log_statement：对调试有用，但记录所有语句会影响性能。

提示： 使用像 pgtune 这样的工具根据你的硬件获取起始建议。始终先在测试环境中测试更改，然后再应用到生产环境。

PostgreSQL 配置的一个陷阱是将每个设置都视为速度旋钮。work_mem 就是一个很好的例子。它是按操作分配的，而不是为整个服务器分配一次。单个查询可能多次使用它，并且许多并发查询可能会迅速将其倍增。将全局值从 4MB 提高到 128MB 可能有助于一个报表查询，但在流量高峰时会损害整个服务器。要测试已知查询，请先使用会话级更改：

SET work_mem = '128MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

如果计划停止溢出到磁盘并且延迟改善，你就学到了一些有用的东西。你仍然需要决定是重写查询、添加索引、为报表角色设置内存，还是更改全局值。

6. 连接池问题

建立新的数据库连接是一项昂贵的操作。在频繁进行短时数据库交互的应用程序中，反复打开和关闭连接可能会成为严重的性能瓶颈。

识别瓶颈

高连接数：pg_stat_activity 显示大量连接，其中许多处于空闲状态。
应用程序启动/响应时间慢：当频繁建立数据库连接时。
服务器资源耗尽：归因于连接管理的高 CPU 或内存使用率。

解决方案

实现连接池：使用像 PgBouncer 或 Odyssey 这样的连接池工具。这些工具维护一个打开的数据库连接池，并为传入的客户端请求重用它们。
- PgBouncer：一个轻量级、高性能的连接池。它可以在事务、会话或语句池模式下运行。
- Odyssey：一个更现代、功能更丰富的连接池，支持 SCRAM-SHA-256 等协议。
适当配置池：根据应用程序需求和数据库容量调整池大小、超时和池模式。
应用程序端池：一些应用程序框架提供内置的连接池功能。确保正确配置它们。

连接池问题通常在部署扩展后出现。一个具有 20 个连接池的应用程序实例可能没问题。三十个具有相同池设置的实例可以在任何实际流量到达之前创建 600 个可能的数据库会话。PostgreSQL 使用每个连接一个进程，因此空闲会话不是免费的。保持应用程序池较小，在预期有许多短生命周期请求时在前面放置 PgBouncer，并按应用程序名称监控 pg_stat_activity，以便你知道谁拥有这些会话。

7. 锁竞争

当多个事务尝试同时访问和修改相同的数据时，如果它们获取了冲突的锁，则可能需要相互等待。过多的锁竞争可能会使应用程序陷入停滞。

识别瓶颈

pg_stat_activity：查找 wait_event_type 为 Lock 的行。
应用程序性能下降：特定操作变得极其缓慢。
死锁：事务无限期地相互等待。
长时间运行的事务：长时间持有锁。

解决方案

优化事务：保持事务简短精炼。尽快提交或回滚。
审查应用程序逻辑：识别潜在的竞态条件或低效的锁定模式。
使用适当的锁级别：PostgreSQL 提供各种锁级别（例如 ACCESS EXCLUSIVE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE）。理解并使用所需的最不严格的锁。
SELECT ... FOR UPDATE / SELECT ... FOR NO KEY UPDATE：在需要锁定行以进行修改以防止其他事务在事务完成之前更改它们时，谨慎使用这些。
定期执行 VACUUM：如前所述，VACUUM 有助于清理死元组，有时可以通过防止冗长的 VACUUM 操作间接减少锁竞争。

检查 pg_locks：查询 pg_locks 以查看哪些进程正在阻塞其他进程。

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

当 PostgreSQL 变慢时，在更改系统之前收集证据：pg_stat_statements 用于工作负载形状，EXPLAIN (ANALYZE, BUFFERS) 用于查询路径，pg_stat_activity 用于等待和连接，以及主机指标用于 CPU、内存和 I/O。当你知道时间实际花在哪里时，修复方案就清晰多了。

PostgreSQL 性能瓶颈的 7 大常见问题及解决方案

1. 低效的查询执行计划

识别瓶颈

解决方案

2. 缺失或无效的索引

识别瓶颈

解决方案

3. 自动清理活动过多或不足

识别瓶颈

解决方案

4. 硬件资源不足（CPU、RAM、IOPS）

识别瓶颈

解决方案

5. postgresql.conf 配置不当

识别瓶颈

解决方案

6. 连接池问题

识别瓶颈

解决方案

7. 锁竞争

识别瓶颈

解决方案

5. `postgresql.conf` 配置不当