规模化PostgreSQL：(基本上)免费节省空间

Braintree Payments运营着数十个PostgreSQL群集，数据超过100TB。在这种规模下，即使磁盘空间增长率发生几个百分点的变化，也会对数据库群集的可写寿命产生重大影响。不幸的是，许多节省磁盘空间的想法都需要更改应用程序，因此需要将其放入产品时间表中。

但今天，我想重点介绍一种技术，它可以节省我们大约10%的磁盘空间，并且只需要很少的工作就可以超越现有的流程。简而言之，在创建表时仔细选择列顺序可以消除原本需要的填充。

这项技术并不是革命性的：2ndQuadant在On Rock and Sand，EDB在PostgreSQL中的数据对齐，GitLab在PostgreSQL中排序表列，StackOverflow问题上的经典“Column Tetris”答案，都有很好的文档记录，我相信还有更多。我希望我们带来的是对这些想法进行编码的工具，这样您就不必重新发明轮子(或手动应用该技术)。

下面，我将描述我们用来确定理想列排序的规则和试探法。但是规则列表听起来很像算法的定义。这意味着我们可以在系统而不是人的层面上解决问题。我们没有向编写数据库DDL更改的每个工程师发送大量电子邮件并期望他们记住这些规则，而是编写了一个名为pg_column_byte_Packer的Ruby gem来在我们的开发周期中自动化解决方案。我们稍后会更多地讨论这一点，但首先让我们更深入地看看问题空间。

PostgreSQL的堆存储与C语言结构中的字段非常相似，它写入保证对齐边界的列。例如，保证具有8字节对齐的列从可被8整除(零索引)的字节索引开始。堆存储引擎自动引入维护此对齐所需的任何填充。

我们可以使用PostgreSQL的目录表来反思各种系统行为和对象，对齐也不例外。每种数据类型都列在pg_Catalog.pg_type中，您可以确定该目录表的tyalign列中的任何数据类型所需的对齐方式。PostgreSQL的文档很好地总结了如何解释本专栏。

从高层次的角度来看，通过按数据类型对齐的降序对每个表的列进行排序，我们可以最大限度地减少对齐填充造成的空间量损失。

例如，假设在我们的64位系统上有一个包含两列的表：Bigint列(需要8字节对齐)和整数列(需要4字节对齐)。如果我们将整数列放在第一位，我们将拥有以下占用16字节的数据布局：

但是，如果我们将Bigint列放在第一位，我们的数据布局将只占用12个字节：

但我们还想同时处理其他几个案件：

可变长度数据类型(如文本)可以根据其大小具有不同的对齐要求。虽然我们显然不能在创建表时查看数据，但我们确实希望根据列的长度约束(如果有的话)来获取提示。

BINARY(BYTEA数据类型)列的长度也是类似的可变的(因此是可变对齐的)，我们假设二进制数据的长度通常是长的。

NOT NULL列肯定比随机的可空列更有可能包含数据，因此提前对它们进行排序是有意义的(以便在读取过程中更快地解包)。

带有默认值的列更有可能包含数据(尽管比非NULL稍微少一些)，因此提前对它们进行排序是有意义的(也是为了在读取过程中更快地解包)。

主键列不仅总是有数据，而且通常也是访问最频繁的列(因为它们往往是联接条件)，所以我们在它们对齐组的开始处对THEN进行排序。

我在前面提到过，我们已经将这组列排序规则合并到我们最近开源的Ruby Gem PG_Column_Byte_Packer中。我们实现了两种互补的方法，试图从整体上解决问题。

首先，当所有列都包含在一个create_table中时，我们会自动修补ActiveRecord的迁移代码，以便在运行迁移时对这些列进行动态重新排序(或者，如果您正在使用我们的pg_ha_Migrations gem来维护正常运行时间保证，则可以使用safe_create_table！)。打电话。

其次，我们提供了一个API来重新排序由PostgreSQL的pg_dump实用程序生成的SQL文件中CREATE TABLE语句中的列。

现在开始在您的应用程序中使用该工具将立即使新的表受益。但我们不想止步于此，因为我们有许多现有的大桌子！当然，重新排序现有列意味着我们需要重写表。因此，我们创建了全新的数据库并应用了使用上述pg_dump SQL文件修改功能更新的模式文件。最后，我们在逻辑上将所有数据复制到这些新数据库，并透明地从旧数据库切换。这也是我们如何在PostgreSQL中实现零停机主要版本升级的基础，但这将是未来文章的主题！

我们希望你们中的许多人能从我们这里的工作中受益。我们也很想看看您对改进它有什么想法。