如何优化Postgres表布局以获得最大的效率

1.前言

在对Postgres数据库建模时，您可能不会过多考虑表中列的顺序。毕竟，这看起来像是不会影响存储或性能的事情。但是，如果我告诉您，简单地重新排序列可以将表和索引的大小减少20%，您会怎么做？这不是什么晦涩的数据库技巧——这是Postgres如何在磁盘上对齐数据的直接结果。

在这篇文章中，我将探讨列对齐在Postgres中是如何工作的，为什么它很重要，以及如何优化你的表以提高效率。通过一些现实世界的例子，您将看到即使是列顺序的微小变化也可以带来可衡量的改进。

2.测量一行的空间大小

作为表行布局的直接结果，行可能的最小大小是24字节。

SELECT pg_column_size(ROW());
 pg_column_size
----------------
             24

然后，每添加一个新列到行中，所占用的空间就越大：

-- 添加一个整数列: 24 + 4 = 28 bytes
SELECT pg_column_size(ROW(1::int));
 pg_column_size
----------------
             28

-- 再加一个短整数列: 24 + 4 + 2 = 30 bytes
SELECT pg_column_size(ROW(1::int, 1::smallint));
 pg_column_size
----------------
             30

到目前为止一切顺利。这正是您所期望的：行中数据越多，所使用的磁盘空间就越大。磁盘使用率与数据类型成正比。

换句话说，如果我们有一个整数列，我们期望行大小为24 + 4 = 28字节。如果我们有一个整数列和一个smallint列，我们期望行大小为24 + 4 + 2 = 30字节。

那么，我们如何解释下面的输出呢？

SELECT pg_column_size(ROW(1::smallint, 1::int));
 pg_column_size
----------------
             32

这怎么可能？！我们刚刚看到，（integer, smallint）行产生30字节的存储空间，但是（smallint, integer）行消耗32字节的磁盘空间！其他数据类型也会发生这种情况：

-- (bigint, boolean) = 24 + 8 + 1 = 33 bytes
SELECT pg_column_size(ROW(1::bigint, true::boolean));
 pg_column_size
----------------
             33

-- (boolean, bigint) = 24 + ? + 8 = 40 bytes!?!?
SELECT pg_column_size(ROW(true::boolean, 1::bigint));
 pg_column_size
----------------
             40

具有（boolean, bigint）结构的行比（bigint, boolean）结构的行多使用21%的磁盘空间！

这是怎么回事？

3.数据对齐

答案是数据对齐。

Postgres很乐意为底层数据添加填充，以确保它在物理层正确对齐。对齐数据可以确保在处理数据时更快地访问数据。[0]

这实际上是一种时空权衡：为了更快地访问数据，我们增加了看似浪费的空间。

3.1 虚拟表示

让我们试着想象一下数据在磁盘上的样子。下面是一个正确对齐的（integer，smallint）行：

(integer, smallint) = 每行30个字节

将它与(smallint ,integer)不对齐的行进行对比：

(smallest, integer) = 每行32个字节

请注意，Postgres必须填充smallint列来强制执行必要的4字节对齐。

下面是另一个例子，现在使用（bigint, smallint, boolean）生成一行35字节。

相同的行，还有可能形成每行40字节：

3.2 计算对齐边界

是什么决定了Postgres使用的对齐方式？来自文档^[1]：

Typalign是存储此类型的值时所需的对齐方式。它适用于磁盘上的存储以及PostgreSQL内部值的大多数表示。当连续存储多个值时，例如在磁盘上完整行的表示中，在此类型的数据之前插入填充，使其从指定的边界开始。对齐引用是序列中第一个基准的开始。可能的值有：

C = char对齐，即不需要对齐。 S =短对齐（在大多数机器上为2字节）。 I = int对齐（在大多数机器上为4字节）。 D =双对齐（在许多机器上为8字节，但绝不是所有机器）。

我们可以通过直接查询pg_type来确认：

SELECT typname, typalign, typlen
FROM pg_type
WHERE typname IN ('int4', 'int2', 'int8', 'bool', 'varchar', 'text', 'float4', 'float8', 'uuid', 'date', 'timestamp');

  typname  | typalign | typlen
-----------+----------+--------
 bool      | c        |      1
 int8      | d        |      8
 int2      | s        |      2
 int4      | i        |      4
 text      | i        |     -1
 float4    | i        |      4
 float8    | d        |      8
 varchar   | i        |     -1
 date      | i        |      4
 timestamp | d        |      8
 uuid      | c        |     16

例如，您可以看到，int8将需要d（双精度，或8字节）的对齐，而int4只需要一半的空间。

Varchar和text的工作方式不同。尽管它们的对齐是i，但它们的类型是负的。为什么呢？因为它们的大小是可变的，也就是说，它们使用varlena结构。

这两个字段具有可变长度的事实实际上与对齐无关，除了这样一个事实，即这样的可变列将在4字节的边界内对齐（除非数据是TOASTed的，我们将在下面看到）。

同样值得指出的是，uuid类型是不同的。它有一个16字节的类型，但它有c对齐（这意味着它不需要事先对齐）。因此，您不需要担心是否在uuid前面有一个布尔列。

在Postgres代码库中，您会发现MAXALIGN宏中使用该值来确定固定宽度类型的必要对齐方式。

define TYPEALIGN(ALIGNVAL,LEN)  \
	(((uintptr_t) (LEN) + ((ALIGNVAL) - 1)) & ~((uintptr_t) ((ALIGNVAL) - 1)))

#define MAXALIGN(LEN)			TYPEALIGN(MAXIMUM_ALIGNOF, (LEN))

注意：文档说明在[…]类型之前插入填充。这意味着，如果我们有一个像（char, int4, char, int8）这样的结构，那么我们将在int4之前有3个字节的填充，在int8之前有7个字节的填充。

3.3 索引同样需要对齐边界

经常被忽视的是，数据对齐不仅影响表中的行，它也适用于索引。这可能令人惊讶，因为我们通常认为索引是紧凑的、优化的结构，其存在纯粹是为了加快查询速度。然而，Postgres确保索引中的数据遵循与表行相同的对齐规则，这意味着不对齐的列会像表一样增加索引的大小。

这实际上是我最初发现数据对齐在Postgres中的重要性的方式。我有一个（int8, int8）索引，我将其重组为（int4, int8），以使表/索引大小更小。想象一下，当我进行基准测试并意识到索引大小根本没有改变时，我有多么惊讶！

这是要记住的关键点：未对齐的列也会影响索引，可能会增加磁盘使用和内存消耗。因此，保持索引对齐可以对数据库性能和资源效率产生重大影响。

3.4 实际环境中看起来的样子

-- 建第一张表, 有这样的结构: (int4, int8, int4) 
CREATE TABLE table_1 (int4_1 INTEGER, int8_2 BIGINT, int4_3 INTEGER);
CREATE INDEX int_4_int_8_int_4 ON table_1 (int4_1, int8_2, int4_3);

-- 第二张表结构如下: (int4, int4, int8)
CREATE TABLE table_2 (int4_1 INTEGER, int4_2 INTEGER, int8_3 BIGINT);
CREATE INDEX int_4_int_4_int_8 ON table_2 (int4_1, int4_2, int8_3);

-- 分别都添加 10_000_000 行数据
INSERT INTO table_1 (int4_1, int8_2, int4_3) SELECT gs AS int4_1, gs AS int8_2, gs AS int4_3 FROM GENERATE_SERIES(1, 10000000) AS gs;
INSERT INTO table_2 (int4_1, int4_2, int8_3) SELECT gs AS int4_1, gs AS int4_2, gs AS int8_3 FROM GENERATE_SERIES(1, 10000000) AS gs;

-- 第一张表比第二张消耗更多空间
SELECT
  pg_size_pretty(pg_relation_size('table_1')) AS table_1_size,
  pg_size_pretty(pg_relation_size('table_2')) AS table_2_size;

 table_1_size | table_2_size
--------------+--------------
 498 MB       | 422 MB

-- 第一张表比第二张表消耗更多索引空间
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size
FROM
  pg_stat_user_indexes;

    index_name     | index_size
-------------------+------------
 int_4_int_8_int_4 | 386 MB
 int_4_int_4_int_8 | 300 MB

3.5 什么时候开始担心

朋友不会让朋友创建不对齐的表。但你不一定想成为公司公关评论的一致性警察！

初创公司，特别是早期的初创公司，通常有一个狂热的开发过程，节省10-20%的磁盘空间通常不是最优先考虑的事情。通常，这些节省在这个阶段是完全无关紧要的。那么，您的团队应该在什么时候担心表对齐呢？

正确的答案在很大程度上取决于产品背后的环境，但最终您会注意到与基础设施相关的巨大成本（特定于您的数据库），这可能是一个很好的起点。

然而，我认为一个称职的软件工程师应该始终牢记这一事实，并且在开发功能时尽可能进行调整。没有必要为此烦恼，但要尽量从一开始就保持你的表对齐。

这将大大节省未来的时间。重构数据模型并不容易。这不仅是重新排序表列的问题，也是重新排序索引的问题（这也意味着在应用层重新排序查询模式）。

事实上，对于处于早期阶段的初创公司，我的建议是时刻关注你的索引是否对齐得很好。您可以稍后重新排序实际的表列，但是要密切注意您的索引。我认为这是一个很好的建议，因为：

• 如上所述，索引很难重新排序。您还需要确保访问模式被重新排序，因为对（a, b）的查询不会使用对（b, a）的索引。
• 在开发过程中很容易更改索引的顺序，因为它们是单独添加的，不依赖于以前的迁移。
• 但是，在开发期间更改列的顺序是很困难的，因为它们通常建立在以前的迁移之上。
• 重构数据模型以重新排序表列，虽然不是微不足道，但不需要应用程序级别的更改（除非您正在做一些次要的事情），因此可以推迟到稍后的阶段。

3.6 经验法则

过去几年我一直使用的一般经验法则是，只要有可能，就根据数据类型大小的降序定义列。

换句话说：从较大的数据类型（如int8、float8和timestamp）开始，并将较小的类型放在末尾。这样可以很自然地对齐你的桌子。

请记住，这是一个“其他条件都相同”的规则。其他因素，如基数甚至可读性，可能比数据对齐具有更高的优先级，特别是在涉及索引时。

3.7 关于TOASTed值的说明

有些数据类型的长度是可变的，如果它们的值太大，以至于一行不能放在一个页面中，那么它们的值可能存储在其他地方。当发生这种情况时，我们称值为TOASTed。在这个场景中，该行将包含一个指向底层数据的指针。

根据数据的大小，可能存在不同类型的指针。对于单字节指针，不需要对齐，而对4字节指针应用4字节对齐。来自文档^[2]：

具有单字节标头的值不会在任何特定边界上对齐，而具有四字节标头的值至少在四字节边界上对齐；与短值相比，省略对齐填充能节省额外的空间。

4. 上述原则适用于别的数据库吗？

当使用Postgres以外的数据库时，我们需要考虑数据对齐吗？以下是我的发现：

4.1 SQLite

不适用。以下是来自Richard Hipp^[3]的注释：

1. SQLite不填充或对齐一行中的列。所有的东西都紧紧地挤在一起，使用最小的空间。这种设计的两个结果：

2. 一旦将一行放入内存，SQLite必须更加努力地工作（使用更多的CPU周期）来访问该行中的数据。 SQLite在磁盘上使用更少的字节，更少的内存，并且花费更少的时间来移动内容，因为要移动的字节更少。

我们认为，#2的优点大于#1的缺点，特别是在现代硬件上，#1在L1缓存外操作，而#2使用主存传输。但是，根据您在数据库文件中存储的内容，您的效果可能会有所不同。

对于那些熟悉SQLite架构的人来说，我想说这并不奇怪，而且很好地符合SQLite的理念。

4.2 MySQL

MySQL不是我的强项，因此您需要深入研究以证实我的发现。我只是引用文档，并添加了一些我自己的想法，我没有验证。你可以使用下面的链接作为你自己研究的起点。

4.2.1NDB

来自文档^[4]：

NDB表使用4字节对齐；所有NDB数据存储都是4字节的倍数。因此，通常占用15个字节的列值在NDB表中需要占用16个字节。例如，在NDB表中，由于对齐因素，TINYINT、SMALLINT、MEDIUMINT和INTEGER （INT）列类型每条记录都需要4字节的存储空间。

基于以上，我认为应该在小于4字节的列中添加填充。

4.2.2InnoDB

我没能在MySQL文档的第17章中看到对齐和填充的提及，除了这一点：

[innodb_compression_failure_threshold_pct] 以百分比的形式定义表的压缩失败率阈值，此时MySQL开始在压缩页面中添加填充，以避免代价高昂的压缩失败。

没太大帮助。它提示在页面级别填充，但我们想知道列是否单独填充。MySQL是oracle开放源码的，所以我们可以通过查看代码来获得一些提示。下面是来自storage/innobase/row/row0mysql.cc的有趣内容：

/** Pad a column with spaces.
@param[in] mbminlen Minimum size of a character, in bytes
@param[out] pad Padded buffer
@param[in] len Number of bytes to pad */
void row_mysql_pad_col(ulint mbminlen, byte *pad, ulint len) {
  const byte *pad_end;

  switch (UNIV_EXPECT(mbminlen, 1)) {
    default:
      ut_error;
    case 1:
      /* space=0x20 */
      memset(pad, 0x20, len);
      break;
    case 2:
      /* space=0x0020 */
      pad_end = pad + len;
      ut_a(!(len % 2));
      while (pad < pad_end) {
        *pad++ = 0x00;
        *pad++ = 0x20;
      };
      break;
    case 4:
      /* space=0x00000020 */
      pad_end = pad + len;
      ut_a(!(len % 4));
      while (pad < pad_end) {
        *pad++ = 0x00;
        *pad++ = 0x00;
        *pad++ = 0x00;
        *pad++ = 0x20;
      }
      break;
  }
}

很明显，在InnoDB存储引擎的某个地方填充了列。然而，当我为这个函数的用户grep MySQL代码库时，我得到的印象是它只在一些特殊情况下使用（而不是像Postgres代码库那样，在必要时总是检查对齐和填充）。例如，它似乎只用于填充固定长度的CHAR列（参见row0ins.cc的用法）。

无论如何，我强烈建议读者在MySQL中尝试一下“它在实践中看起来像什么”一节中的代码片段，并分析结果。有些东西告诉我，表不对齐在MySQL中不会像在Postgres中那样是一个大问题。

可以阅读下Hacker News上的讨论^[5]。

5. 注意

[0] -最初读为“当从磁盘检索页面时”。正如branko_d在Hacker News上指出的那样^[6]，这可能会产生误导：正确对齐带来的加速发生在数据被检索后的处理过程中，而不是在从磁盘检索数据时。这是一个微妙但重要的区别，值得强调。

参考资料