精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

提升性能和安全性的 10 大 PostgreSQL 最佳实践

实用的 PostgreSQL 最佳实践，涵盖更快的查询、更安全的访问、更好的维护以及可恢复的备份。

PostgreSQL 性能与安全十大最佳实践

当您的数据库开始承载实际生产流量时，PostgreSQL 最佳实践就显得至关重要。一个健康的配置能确保查询可预测、保护数据，并在出现故障时提供恢复路径。

请将这十条检查清单作为新 PostgreSQL 服务器或现有系统性能下降时的实用审查列表。

1. 优化索引并理解 EXPLAIN ANALYZE

索引对于加速数据检索至关重要，但选择不当或过多的索引实际上会降低写入操作的性能。理解何时以及如何使用不同类型的索引（B-tree、GIN、GiST、BRIN 等）至关重要。

始终使用 EXPLAIN ANALYZE 来了解 PostgreSQL 如何执行您的查询。它提供有关查询计划的详细信息，包括每个步骤的执行时间，帮助您识别瓶颈和索引优化的机会。

实践示例：使用 EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT customer_name, order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2023-01-01'
ORDER BY order_date DESC;

分析输出结果将揭示在 o.order_date 或 c.customer_id（如果尚未是主键）上建立索引是否有益。

提示

定期使用 pg_stat_statements（如果启用）审查慢查询，并对其应用 EXPLAIN ANALYZE。

2. 优化查询并有效设计模式

除了索引之外，高效的查询编写和深思熟虑的模式设计对性能有显著影响。在生产代码中避免使用 SELECT *，而应仅选择所需的列。使用适当的 WHERE 子句尽早过滤数据，并理解连接类型。规范化数据库模式以减少数据冗余，但要务实；对于特定的读密集型场景，反规范化可能是有益的。

查询最佳实践

  • 避免子查询，优先使用连接： 通常，JOIN 操作比子查询更高效地组合数据。
  • 将 LIMIT 与 ORDER BY 结合使用： 对于分页或检索前 N 条记录，确保 ORDER BY 与 LIMIT 一起使用，并具有适当的索引。
  • 选择正确的数据类型： 使用更小、更精确的数据类型（例如，如果范围允许，使用 SMALLINT 代替 BIGINT）可以减少存储空间并提高性能。

3. 配置自动清理以实现最佳维护

PostgreSQL 使用多版本并发控制（MVCC）模型，这意味着 UPDATE 和 DELETE 操作不会立即删除旧数据版本。这些“死元组”会随着时间的推移而累积，导致表膨胀和性能下降。VACUUM 和 ANALYZE 分别对于清理死元组和更新统计信息至关重要。

AUTOVACUUM 是 PostgreSQL 内置的用于自动化这些任务的进程。正确配置 postgresql.conf 中的 autovacuum 参数至关重要。

关键 autovacuum 参数

  • autovacuum = on（默认）
  • autovacuum_vacuum_scale_factor（默认：0.2，即表大小的 20%）
  • autovacuum_vacuum_threshold（默认：50）
  • autovacuum_analyze_scale_factor（默认：0.1）
  • autovacuum_analyze_threshold（默认：50）

您可能需要为非常繁忙的表调整这些参数，设置更低的阈值或比例因子。

命令示例

查看 autovacuum 活动：

SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';

4. 实施连接池

建立新的数据库连接在 CPU 和内存方面是一项昂贵的操作。对于具有许多短生命周期连接或大量并发用户的应用程序，这种开销会显著影响性能。像 PgBouncer 或 Pgpool-II 这样的连接池位于您的应用程序和 PostgreSQL 之间，维护一个打开的连接池并根据需要重用它们。

这减少了连接建立的开销，更有效地管理并发连接，甚至可以提供负载均衡能力。

为什么使用连接池？

  • 减少连接建立/拆除的开销。
  • 限制与数据库的总连接数，防止资源耗尽。
  • 提高应用程序的可扩展性。

5. 深思熟虑地调整 postgresql.conf 参数

postgresql.conf 文件包含许多控制 PostgreSQL 行为、资源使用和性能的参数。通用默认值通常比较保守；根据服务器的硬件和工作负载调整这些参数至关重要。

需要考虑的关键参数

  • shared_buffers：PostgreSQL 用于缓存数据页的内存量。许多专用服务器从总 RAM 的 25% 左右开始，然后在测试后进行调整。
  • work_mem：排序和哈希操作在写入磁盘之前使用的内存。设置足够高以避免磁盘排序，但要小心，因为它是按会话分配的。
  • maintenance_work_mem：用于 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 的内存。可以设置得比 work_mem 高得多。
  • wal_buffers：WAL（预写日志）数据在刷新到磁盘之前使用的内存。虽小但很重要。
  • effective_cache_size：告知查询规划器 PostgreSQL 和操作系统可能有多少内存可用于磁盘缓存。许多部署将其设置为 RAM 的很大一部分，然后通过实际查询验证计划。
  • max_connections：允许的最大并发连接数。

警告

对 postgresql.conf 的更改通常需要数据库重启或重新加载（pg_ctl reload）。不正确的调整可能会降低性能或导致稳定性问题。

6. 监控并合理调整硬件规模

即使数据库调优完美，硬件不足也会成为瓶颈。定期监控服务器的 CPU、RAM、磁盘 I/O（IOPS、吞吐量）和网络使用情况。像 pg_stat_statements、pg_stat_activity 和操作系统级监控（例如 vmstat、iostat、top）等工具提供了宝贵的见解。

关键监控领域

  • CPU 利用率： CPU 高可能表明查询效率低下或处理能力不足。
  • 内存使用： 查找过多的交换，表明 RAM 不足。
  • 磁盘 I/O： 磁盘访问速度慢会严重限制数据库性能。考虑更快的存储（SSD/NVMe）或 RAID 配置。
  • 网络延迟： 应用程序和数据库之间的高延迟会减慢请求速度。

合理调整硬件规模涉及分配足够的资源（CPU、RAM、快速存储）来处理您当前和预计的工作负载。云提供商使扩展更容易，但资源的有效利用始终很重要。

7. 实施强身份验证并限制 pg_hba.conf

安全始于强身份验证。始终强制执行强密码策略并使用安全的身份验证方法。PostgreSQL 支持在 pg_hba.conf（基于主机的身份验证）中定义的各种方法。对于生产环境，在密码身份验证中优先使用 scram-sha-256 而不是 md5 或 password，因为它更安全。

在 pg_hba.conf 中将对数据库的访问限制为仅受信任的主机或网络。除非绝对必要并配合强大的防火墙规则，否则避免使用 host all all 0.0.0.0/0 scram-sha-256。

pg_hba.conf 示例

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             my_app_user     192.168.1.0/24          scram-sha-256

提示

定期审计您的 pg_hba.conf 文件，确保仅授予必要的访问权限。

8. 遵循最小权限原则（RBAC）

最小权限原则规定用户和进程只应拥有执行任务所需的最低权限。在 PostgreSQL 中，这通过基于角色的访问控制（RBAC）实现。

  • 创建特定角色： 不要使用 postgres 超级用户进行应用程序访问。
  • 授予最小权限： 使用 GRANT 和 REVOKE 命令精确分配对数据库、模式、表、序列和函数的权限。
  • 审查 PUBLIC 权限： PostgreSQL 默认向 PUBLIC 授予一些权限，例如数据库的 CONNECT 和旧默认设置中 public 模式的 USAGE。如果您的应用程序不需要，请撤销广泛的访问权限。

示例：创建只读用户

CREATE ROLE app_readonly_user WITH LOGIN PASSWORD 'strongpassword';
GRANT CONNECT ON DATABASE mydatabase TO app_readonly_user;
GRANT USAGE ON SCHEMA public TO app_readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly_user;

9. 使用防火墙和 SSL/TLS 保护网络访问

数据库服务器绝不应直接暴露在公共互联网上。实施强大的防火墙规则，将到 PostgreSQL 默认端口（5432）的传入连接限制为仅受信任的应用程序服务器或特定 IP 地址。

此外，使用 SSL/TLS 加密应用程序和 PostgreSQL 之间的所有通信。这可以防止窃听和中间人攻击。在 postgresql.conf 中配置 ssl = on，并确保您的客户端配置为使用 SSL（sslmode=require 或 verify-full）。

postgresql.conf SSL 配置

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
# ssl_ca_file = 'root.crt' # 如果需要客户端证书

注意

确保 postgresql.conf 中的 listen_addresses 设置为特定 IP 或 *（仅当外部有防火墙时）。

10. 实施强大的备份和恢复策略

数据丢失是灾难性的。强大的备份和恢复策略是必不可少的。不仅要备份，还要定期测试恢复过程，以确保您的备份有效，并且可以在恢复时间目标（RTO）内成功恢复。

备份方法

  • pg_dump / pg_dumpall： 逻辑备份（SQL 脚本），适用于较小的数据库或仅模式备份。易于使用，但对于大型数据库可能较慢。
  • pg_basebackup： 物理基础备份，用于创建数据目录的完整副本。对于时间点恢复（PITR）至关重要。
  • WAL 归档： 与 pg_basebackup 结合，连续归档（传输预写日志段）允许 PITR，使您能够将数据库恢复到任何时间点。

将备份存储在异地并加密。考虑自动化备份解决方案并监控其成功/失败。

示例：pg_dump

pg_dump -Fc -f mydatabase_$(date +%Y%m%d).bak mydatabase

示例：pg_basebackup

pg_basebackup -h localhost -p 5432 -U backup_user -D /var/lib/postgresql/backups/base_backup_$(date +%Y%m%d) -F tar -z -v

总结

从慢查询、备份和访问控制开始。这三个方面能及早捕获最痛苦的故障。然后根据您自己工作负载的测量结果调整内存、自动清理、连接池和硬件。