您正在启动一个新项目或公司,并决定在 Postgres 上运行。困难的部分(选择数据库)已经结束,现在有趣的部分开始了:确保在未来几年内不再需要考虑这个问题。
从连接池到安全基础知识,再到扩展和索引,这篇文章将介绍一些鲜为人知的配置 Postgres 的技巧。
使用 PGBouncer 为 Postgres 设置连接池
默认情况下,Postgres 会为每个客户端连接从主操作系统进程中分叉出一个单独的进程。在容量较小的情况下,创建和销毁这些进程所需的时间并不重要,而且这些进程也不会被重复使用。您可以手动设置最大连接数,但随着规模的扩大,最终可能会遇到问题。连接池可以帮助你从本质上 “缓存” 这些进程,并在客户端连接和断开数据库时重复使用它们。
虽然可以在应用程序逻辑中建立连接池,但大多数人还是会选择第三方工具,在 Postgres 中就是 PGBouncer。它是一个开源、轻量级的连接池,可以安装在数据库服务器或应用服务器上。有 3 种级别的连接池可供选择:
- 会话池化:忠实于 “客户端连接无限期” 模式,在客户端连接的整个过程中保持连接开放。
- 事务池:连接只持续一个事务,之后会被送回池中。
- 语句池:连接只在一个查询中持续,因此如果一个事务中有多个连接,则根本无法使用。
大多数人会选择会话池,因为它最保守,丢弃连接的风险也最小,但每个应用程序都不一样,你需要根据自己的限制条件找出正确的模式。
注:大多数网络框架都将其留给第三方工具处理(参见 Django 论坛中关于 PGBouncer 的主题),但 Rails 实际上提供了原生连接池功能。
连接池对性能的影响
百万美元的问题:这真的有效吗?Percona 运行了一系列基准测试,以了解 PGBouncer 对性能的影响。在并发客户端数量较少(小于 60 个)的情况下,由于池的开销,PGBouncer 实际上会大大降低每秒事务处理量 (TPS)。但当并发客户端数超过 100 个时,性能优势就会显现出来。
PGBouncer
那么,您需要立即使用连接池来支持最初的几个用户吗?也许不需要。但是,一旦您的流量达到中/低水平,使用 PGBouncer 会对您有所帮助。
Postgres 安全入门
在开发项目的最初几周,通常只有一两个开发人员在一个空数据库上工作,安全性并不是最重要的。但是,当您向全世界发布应用程序时,您就需要考虑安全问题了。对于数据库,有时似乎有无数种不同的方法来锁定。
在主机或用户级别限制访问
让我们从访问开始。Postgres 通过两种方式限制访问权限:
- 在主机层面-定义具有访问权限的 IP 地址和域
- 在用户层面-定义数据库用户及其权限
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是建立公司的最佳(也是最受欢迎的)选择。希望这些技巧能帮助您开始运行并为扩大规模做好准备。您还有其他建议或想法吗?请在这里告诉我们。
原文地址:https://www.wbolt.com/startup-ctos-handbook-to-running-on-postgres.html