DevOps知识中心
DevOps知识中心

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

通过这 10 大性能和安全最佳实践，释放您 PostgreSQL 数据库的全部潜力。本综合指南涵盖了从索引和查询优化、有效清理（vacuuming）到连接池等基本主题，以及强身份验证、最小权限访问和网络加固等关键安全措施。了解如何调整 `postgresql.conf`、监控硬件和实施强大的备份策略。提升您的 PostgreSQL 管理技能，以确保您的应用程序实现最佳速度、可靠性和数据保护。

51 浏览量

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

PostgreSQL 因其健壮性、可靠性和先进的功能集而闻名，使其成为关键应用的热门选择。然而，仅仅使用 PostgreSQL 是不够的；要真正发挥其强大功能，您必须实施兼顾性能和安全性的最佳实践。忽视这些方面可能导致查询缓慢、数据损坏以及潜在的安全漏洞。

本文深入探讨了十项重要的 PostgreSQL 最佳实践，旨在帮助您优化数据库性能、提升其安全态势并确保长期可靠性。从微调配置、优化查询到保护您的数据，这些可操作的技巧将为您有效管理 PostgreSQL 实例提供坚实的基础。无论您是经验丰富的 DBA 还是希望提高数据库管理技能的开发人员，采用这些实践都将对您的 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. 优化查询并有效地设计您的模式（Schema）

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

查询的最佳实践：

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

3. 配置 Autovacuum 以实现最佳维护

PostgreSQL 采用多版本并发控制（MVCC）模型，这意味着 UPDATE 和 DELETE 操作不会立即删除旧版本数据。这些“死亡元组”（dead tuples）会随着时间积累，导致表膨胀（table bloat）和性能下降。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. 实施连接池（Connection Pooling）

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

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

为什么要使用连接池？

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

5. 慎重调整 postgresql.conf 参数

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

需要考虑的关键参数：

  • shared_buffers：PostgreSQL 用于缓存数据页的内存量。通常设置为总 RAM 的 25%，但在专用服务器上可以高达 40%。
  • work_mem：排序和哈希操作在写入磁盘之前使用的内存。应设置得足够高以避免磁盘排序，但要小心，因为它是按会话分配的。
  • maintenance_work_mem：用于 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 的内存。可以设置得比 work_mem 高得多。
  • wal_buffers：WAL（预写式日志）数据在刷新到磁盘之前使用的内存。虽小但很重要。
  • effective_cache_size：通知查询规划器有多少内存可用于磁盘缓存（由 PostgreSQL 和 OS 共同提供）。设置为总 RAM 的 50-75%。
  • max_connections：允许的最大并发连接数。

警告：

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

6. 监控并合理配置您的硬件（Right-Size）

即使数据库调整得再完美，硬件不足也会成为瓶颈。定期监控服务器的 CPU、RAM、磁盘 I/O（IOPS、吞吐量）和网络使用情况。pg_stat_statements、pg_stat_activity 以及 OS 级别的监控工具（例如 vmstat、iostat、top）提供了有价值的见解。

关键监控领域：

  • CPU 利用率： 高 CPU 可能表明查询效率低下或处理能力不足。
  • 内存使用情况： 查找过度的交换（swapping），这表明 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 命令精确分配对数据库、模式、表、序列和功能的权限。
  • 使用 REVOKE PUBLIC： PostgreSQL 默认授予 PUBLIC 一些权限（对新数据库的 CONNECT，对新模式的 USAGE）。如果不需要，请 REVOKE 这些权限。

示例：创建一个只读用户

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' # if client certs are required

注意：

确保 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

结论

有效管理 PostgreSQL 数据库需要对性能优化和安全性采取积极主动的方法。通过系统地实施这十项最佳实践——从智能索引和查询设计到稳健的身份验证、网络安全和灾难恢复规划——您可以显著提升 PostgreSQL 环境的稳定性、速度和弹性。

请记住，数据库管理是一个持续的过程。定期监控、审计和适应不断变化的工作负载和安全形势，对于长期保持最佳性能和安全性至关重要。在这些领域投入精力，您的 PostgreSQL 数据库将在未来多年可靠、高效地为您的应用程序服务。