在最近与一位DBA交谈后，他迈出了将大型数据库从Oracle迁移到Postgres的第一步。

我认为没有足够的文章介绍PostgreSQL的特性和黑客，从硬件中挤出最后一部分，安全地容纳好几个tb大小的数据库以实现可伸缩性。特别是在有相当多的选择的情况下，我很惊讶有这么多的人担心PostgreSQL在伸缩性方面是非常有限的。也许确实曾经是这样(我在2011年开始使用Postgres)，但在2018年，事情已经相当稳固了——所以请继续读下去，看看如何轻松地处理tb。

标准Postgres设施

如果你不喜欢出汗太多,然后做一些开拓性的尺度。应该是最安全的方法坚持证明Postgres的开箱即用的特性,首先我建议看看以下关键词和一些简短的解释,也许是你需要的一切。

轻量级/特殊用途索引

对于支持数百个奇怪查询的复杂OLTP系统，索引实际上占用的磁盘空间比保存数据的表文件要多得多，这是很常见的。为了改善这一点(特别是对于不经常使用的索引)，可以通过适当使用部分、BRIN、GIN甚至少量实验性BLOOM索引来大幅减小索引的大小。总共支持7种不同的索引类型……但是大多数人只知道和使用默认的b -树——这在多tb设置中是个大错误!

部分索引只允许数据的一个子集——例如，在销售系统中，我们可能对快速访问状态为“FINISHED”的订单不感兴趣(一些夜间报告通常会处理这个问题，它们可能会花很多时间)，那么我们为什么要索引这样的行呢?

GIN可能是最知名的非默认索引类型，它已经存在很长时间了(全文搜索)，简而言之，它非常适合索引有很多重复值的列——考虑各种状态或老的Mr/Mrs/Miss。GIN只将每个惟一的列值存储一次，对于默认的b树，您将拥有1,000,000个叶节点，其中包含整数“1”。

BRIN(block-range又名min-max指数)另一方面,是新的和非常不同——它是一种有损压缩指数与一个很小的磁盘占用,并不是所有的列值建立索引,但只有最大和最小值的行范围(1 MB的默认表)——这仍然很有效有序的值,例如适合时间序列数据或其他“日志”类型的表。

BLOOM可能有点奇怪，但如果你能找到一个很好的用例(“位图/矩阵搜索”)，它的效率会比传统索引高出20倍——这里有一个看起来过于抽象的用例。

但是为什么我要把索引这个有点不太原创的话题放在列表的最前面呢?因为这个解决方案的最大优点是您不需要更改任何应用程序—DBA可以让它轻松地在幕后工作，就像一次性工作一样!完美的。

表分区

Postgres分区已经15年我相信……但“弄脏你的手”的方式——一个不得不做一些附加的低级管理分区,添加检查约束和直接插入行纠正子表,或路由通过父表插入触发器。所有这些都是从Postgres version 10开始的历史，有声明式分区，并且在version 11中变得更好，在版本11中，功能可以被称为功能完整的功能，支持主键和外键。

但何苦呢?分区的优点是:它可以干净地分离“冷数据”和“热数据”,这给了我们一些不错的选项如使用VACUUM FULL最大限度压缩旧数据或把它放在另一个媒体(参见下面的“表空间”)作为一个副作用较小的索引,以少得多的“shared_buffers”空间所以我们有更多的空间数据。对于统一访问的数据场景(通过名称/电子邮件/散列)，这种影响最大，大型索引的所有部分仍然需要遍历/读取/缓存，但实际使用的只是其中很小的一部分。同样类似于索引，在良好的应用程序条件下，dba无需在后台更改任何代码就可以实现分区。

表空间

如上所述——借助表空间可以有选择地将表/索引移动到不同的磁盘介质。这里可以实现不同的目标之一——为了节省资金，使用较慢/负担得起的磁盘分区来处理“冷”数据,只在快速/昂贵的媒体上保存最新/重要的数据,对重复次数很多的数据使用一些特殊的压缩文件系统，对大量非持久性数据使用一些网络共享甚至远程节点上的内存文件系统——有一些选项。而且表空间的管理实际上也非常简单，由于完全锁定，只有在实时操作期间传输现有的表/索引才会有问题。

最大限度地使用多进程特性

从Postgres 9.6开始，可以对数据的一些常见操作进行并行化。在Postgres 10/11中，默认情况下也启用了相关参数“max_parallel_workers_per_gather”，其值为2，因此是max。使用了2个后台进程。对于“大数据”用例，增加更多(以及一些相关参数)是有意义的。此外，对并行化操作的支持也会随着每一个新的主要发行版的增加而增加。例如，即将发布的version 11现在可以执行并行散列连接、索引扫描和UNION-s。

使用副本进行查询负载平衡

现在我们已经走出了“单节点”或“扩展”的领域……但是考虑到非常合理的硬件价格和可用的Postgres集群管理软件(Patroni是我们的最爱)，它不仅适用于更大的“商店”，而且应该适用于每个人。当然，这种扩展只能在主要是读取数据的情况下使用……按照目前(以及未来几年)的官方规定，一个接受写操作的集群中只能有一个“主/主”节点。此外，根据您选择的技术栈，您可能需要处理一些技术细节(特别是连接路由)，但实际上，Postgres 10确实在驱动程序级别增加了对多主机用例的支持——因此电池也包括在内!更多信息请看这里。同样，从Postgres 9.6开始，副本可以在“镜像”模式下运行，这样您选择哪个节点运行就无关紧要了!不过，作为一个友好的警告——这只能在读取查询是纯OLTP的情况下工作，也就是说非常快。

有一些妥协的方法

所以现在我们已经完成了传统的事情…但是如果你准备走一条非常规的路

也许对您的应用程序做一些细微的调整，或者尝试一些命名有趣的扩展，您可能会从单节点硬件中挤出最后一滴性能。我的意思是:

混合/外部表

我称之为混合表,实际上是基于Postgres的优秀的MED SQL标准的实现也被称为外部数据包装（FDW）,基本上,他们看起来像正常Postgres表读取查询,但数据可能存在或管道从字面上任何地方——它可能是来自Twitter、LDAP或Amazon S3,看到疯狂的数据源支持的完整列表。实际上，最常用的外部数据包装程序可能是使正常(正确格式化的)文件看起来像表，例如将服务器日志公开为表，以便更容易监视。

你可能会问扩展部分在哪里?FDW方法工作得非常好，因为它能够通过使用一些聪明的文件格式或仅仅压缩来减少数据量，这通常会减少数据大小10-20x，以便数据适合节点!这对于“冷”数据非常有效，为“热”数据的真实表留下更多的磁盘空间/缓存可用。由于Postgres 10，它也很容易实现——这里是示例代码。

另一个非常有前途的用例是使用柱状数据存储格式(ORC)—查看“c_store”扩展项目以获得更多信息。它特别适合帮助扩展大型数据仓库，其中的表可以小到10倍，查询速度可以快到100%。

为什么我没有把这个特性添加到上面的“标准Postgres设施”部分，尽管外部数据包装基础设施已经牢固地构建到Postgres中?好的，缺点是您通常不能通过SQL更改数据和添加索引或约束，因此它的使用有一定的限制。

外表继承，即分片!（Foreign table inheritance a.k.a. sharding!）

与上一点基本相同——但是引入了表分区并将子表驻留在远程节点上!数据可以被植入到附近的Postgres服务器，并根据需要通过网络自动提取。实际上，它们不必是Postgres表!它很可能是MySQL、Oracle或MS SQL任何其他流行的服务器，对某些查询子集工作得很好。这有多酷?虽然只有“postgres_fdw”支持所有的写操作、事务和聪明的过滤器下推，所以通过网络传递的数据量最小化，但是可以从postgres_fdw到postgres的交互中得到最好的结果。

terabyte-hunting快乐!

原文：https://www.cybertec-postgresql.com/en/ideas-for-scaling-postgresql-to-multi-terabyte-and-beyond/

本文：http://jiagoushi.pro/node/1180

讨论：请加入知识星球【首席架构师圈】或者小号【jiagoushi_pro】