初创技术项目 Postgres 配置技巧

您正在启动一个新项目或公司，并决定在 Postgres 上运行。困难的部分（选择数据库）已经结束，现在有趣的部分开始了：确保在未来几年内不再需要考虑这个问题。

从连接池到安全基础知识，再到扩展和索引，这篇文章将介绍一些鲜为人知的配置 Postgres 的技巧。

使用 PGBouncer 为 Postgres 设置连接池

默认情况下，Postgres 会为每个客户端连接从主操作系统进程中分叉出一个单独的进程。在容量较小的情况下，创建和销毁这些进程所需的时间并不重要，而且这些进程也不会被重复使用。您可以手动设置最大连接数，但随着规模的扩大，最终可能会遇到问题。连接池可以帮助你从本质上 “缓存” 这些进程，并在客户端连接和断开数据库时重复使用它们。

虽然可以在应用程序逻辑中建立连接池，但大多数人还是会选择第三方工具，在 Postgres 中就是 PGBouncer。它是一个开源、轻量级的连接池，可以安装在数据库服务器或应用服务器上。有 3 种级别的连接池可供选择：

• 会话池化：忠实于 “客户端连接无限期” 模式，在客户端连接的整个过程中保持连接开放。
• 事务池：连接只持续一个事务，之后会被送回池中。
• 语句池：连接只在一个查询中持续，因此如果一个事务中有多个连接，则根本无法使用。

大多数人会选择会话池，因为它最保守，丢弃连接的风险也最小，但每个应用程序都不一样，你需要根据自己的限制条件找出正确的模式。

注：大多数网络框架都将其留给第三方工具处理（参见 Django 论坛中关于 PGBouncer 的主题），但 Rails 实际上提供了原生连接池功能。

连接池对性能的影响

百万美元的问题：这真的有效吗？Percona 运行了一系列基准测试，以了解 PGBouncer 对性能的影响。在并发客户端数量较少（小于 60 个）的情况下，由于池的开销，PGBouncer 实际上会大大降低每秒事务处理量 (TPS)。但当并发客户端数超过 100 个时，性能优势就会显现出来。

 

PGBouncer

 

那么，您需要立即使用连接池来支持最初的几个用户吗？也许不需要。但是，一旦您的流量达到中/低水平，使用 PGBouncer 会对您有所帮助。

Postgres 安全入门

在开发项目的最初几周，通常只有一两个开发人员在一个空数据库上工作，安全性并不是最重要的。但是，当您向全世界发布应用程序时，您就需要考虑安全问题了。对于数据库，有时似乎有无数种不同的方法来锁定。

在主机或用户级别限制访问

让我们从访问开始。Postgres 通过两种方式限制访问权限：

1. 在主机层面-定义具有访问权限的 IP 地址和域
2. 在用户层面-定义数据库用户及其权限

PGDATA 目录中的 pg_hba.conf 文件是定义谁可以连接哪些数据库的地方。如果其中没有客户的条目，他们将无法访问数据库。假设你的应用服务器运行在其他地方，下面是允许它访问数据库的方法：

# Trust any connection via TCP/IP from this machine
host all 127.0.0.1 255.255.255.255 trust

除了 “信任来自本机的任何连接” 之外，还有大量不同的方法来验证客户端与数据库服务器之间的关系，包括密码、身份标识和证书。如果你放弃了 RDS 的舒适性，在与数据库相同的服务器上运行后端，你可以通过 Unix 套接字而不是 TCP/IP 进行连接。

授权和权限

一旦客户端本身通过了身份验证，就需要处理授权问题。SQL 标准定义了一个权限系统，Postgres 中的每个对象（如表、行等）都有不同的权限，可以分配给用户：如 SELECT 和 UPDATE ，还有 TRUNCATE、 REFERENCES、 TRIGGER等。您可以使用 GRANT 命令授予用户权限。

最佳做法是遵循最小权限原则，因此为客户创建的数据库用户只能访问其需要访问的内容。

行级安全

最后要介绍的是行级安全。行级安全是从表（而不是用户）的角度出发，限制哪些行可以被访问、更新等。默认情况下，表不启用 RLS，因此用户可以按照自己的访问策略行事。要为表启用 RLS，可以从以下步骤开始：

ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY

然后添加策略。比方说，您想限制只有已定义在 jedi 用户组中的可信人员才能读取 lightsaber_internals 表，这样只有 lightsaber 的所有者才能查看其内部细节。您可以这样做

ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY
CREATE POLICY jedi_only ON lightsaber_internals TO jedi
USING (jedi = lightsaber_jedi);

当你需要比表格更细粒度的安全性时（涉及 PII 等的情况），像这样的 RLS 策略就非常有用。

提前考虑扩展问题

在我工作过的每一家初创公司中，都会在某种程度上对数据库进行手动扩展。有一天，你一觉醒来，发现 Datadog 因为你的 Postgres 服务器完全没有空间而抓狂。你会进行调查，更新事件页面，最终升级磁盘大小，直到这种情况再次发生（不过下一次可能是内存问题）。提前解决这些问题会有所帮助！几点建议

1. 设置数据库监控

我工作过的大多数公司都使用 Datadog 进行数据库监控。如果你使用的是受管理的数据库服务，那么使用他们的原生工具也许还能应付一下。Datadog 博客上有一篇很好的文章，介绍了你应该关注的主要指标，如读写吞吐量、顺序扫描、写入磁盘的数据等。

2. 制定纵向扩展指南

当你的团队被呼唤时–这种情况总会发生–你最不希望看到的就是每个人都需要手忙脚乱地解决问题，而在大多数情况下，简单的扩展就能解决问题。当空间或计算能力不足时，最好为团队制定一个基本计划，说明哪些工作在计划范围内。

3. 手动或者自动维护数据库

在 Postgres 中 DELETE 数据或 UPDATE 数据（在功能上等同于删除和插入）时，Postgres 不会立即删除数据（😱）。取而代之的是，通过在 xmax 标头中存储删除的事务 ID，将其 “marked” 为已删除；这样做的原因是为了让 Postgres 中的 MVCC 更简单。但是，如果这些记录最终没有被真正删除，它们就会开始浪费磁盘空间，给你带来麻烦。

删除这些记录的最简单方法是使用 VACUUM 命令。你可以在死行积累到一定程度时手动运行 vacuum，甚至可以将其设置为每隔 X 分钟运行一次，但更好的策略是根据死行积累的数量进行自动维护（Autovacuum）。调整 Autovacuum 是一个细致入微的话题，超出了本篇文章的讨论范围： 我强烈建议阅读 2ndQuadrant 的相关文章。

4. 建立一个或两个读取副本

这一点很简单。如果预计流量会显著增加（即将发布等），可以轻松创建只读副本（或至少一个）；它们将有助于从主数据库实例中卸载一些工作。

如果您选择多个副本，那么当其中任何一个副本因故宕机时，您将获得提高可用性的额外好处。在大多数 DBaaS 提供商中，添加副本都非常简单；只需注意成本：尽管副本是只读的，但其价格通常与主 DB 实例相同。

为（预计的）最大表添加索引

数据库索引通过创建辅助数据结构来加快读取查询的速度，从而提高扫描速度。对于许多用例来说，为一两个表添加索引基本上是不费吹灰之力的事。在 Postgres 中，可以使用 CREATE INDEX 命令创建索引（咄）。当你查询一个表时，数据库会检查是否存在索引，如果存在，就使用它（顺便说一下，你可以用 EXPLAIN 来验证是否存在索引）。

Postgres 中最常用的索引类型（使用 CREATE INDEX 时的默认类型）是 B-Tree Index。从本质上讲，B-Tree 索引会对要创建索引的列进行排序，并存储指向排序行的指针。这样，你就可以在任何你想要的列上获得二进制搜索效率，而不仅仅是实际表的排序列（如果有的话）。关于这些树是如何实现的，你可以在这里的 Postgres 文档中阅读更深入的内容。

索引虽然有用，但并不全是好玩的游戏；它们会占用空间，而且如果不注意创建索引的数量和类型，它们实际上会开始降低数据库性能。没有人比 Postgres 文档本身说得更清楚了：

索引主要用于提高数据库性能（尽管使用不当会导致性能降低）。

在引擎盖下，创建索引时，Postgres 会生成一个查找表，其中包含索引和指向索引记录的指针。过多的查找表会占用磁盘空间，延长 INSERT 查询的时间，并迫使查询引擎在选择如何执行查询之前考虑更多选项。

添加一些 Postgres 扩展

Postgres 的独特之处在于它对第三方扩展的原生支持。你可以用 SQL 和 C 语言创建扩展，它们可以小到几条语句，也可以大到整个软件库。使用公开/开源扩展就像使用软件包一样；既然可以使用别人的代码，为什么还要自己写呢？下面是一些比较流行的 Postgres 扩展：

Timescale

Timescale 是一个 Postgres 扩展，用于处理时间序列数据。简而言之，它能让你的查询更快，并高效地存储时间序列数据。你可以在这里找到安装说明，如果你的业务确实是基于时间序列数据，也可以考虑 Timescale 的云托管选项（当然，你可能已经意识到这一点了）。

PostGIS

PostGIS 为 Postgres 添加了对地理数据（如线条、多边形、位置等）的存储、索引和查询支持。如果你使用的是云服务提供商，大多数都会预装 PostGIS。但如果你需要自己安装，可以在这里找到安装说明。

pg_stat_staements

pg_stat_statements 会在 Postgres 数据库中创建一个视图，其中包含数据库中每次查询的统计数据。你可以看到查询运行所需的时间（平均值、中位数、平均值等）、谁运行了查询、块缓存命中率、写入块的数量等统计信息（该视图中共有 44 列）。安装时，只需将其添加到 .conf 文件并重新启动服务器即可。

pg_audit

pg_audit 可以帮助那些需要接受详细审计的公司（如政府、金融等）。你可以通过设置 `log_statement=all` 来让 Postgres 记录数据库的每一条语句，但这并不意味着你需要的信息很容易搜索和找到。你可以在这里找到安装说明。

小结

Postgres是建立公司的最佳（也是最受欢迎的）选择。希望这些技巧能帮助您开始运行并为扩大规模做好准备。您还有其他建议或想法吗？请在这里告诉我们。