【PGCCC】优化 Postgres 表布局以实现最高效率-优快云博客

前言

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

在这篇文章中，我将探讨 Postgres 中的列对齐工作原理、它的重要性以及如何优化表以提高效率。通过几个真实示例，您将看到即使列顺序的微小变化也能带来可衡量的改进。

称量一行

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

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

然后，对于在行中添加的每个新列，它将占用更多的空间：

-- One column of type integer: 24 + 4 = 28 bytes
SELECT pg_column_size(ROW(1::int));
 pg_column_size
----------------
             28

-- Integer + smallint columns: 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)比一行多使用 21％的磁盘空间(bigint, boolean)！

这里发生了什么事？

数据对齐

答案是数据对齐。

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

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

视觉表现

让我们试着想象一下数据在磁盘上的样子。下面是正确对齐的(integer, smallint)行：
（整数，smallint）= 每行 30 个字节
将其与未对齐的(smallint, integer)行进行对比：
在这里插入图片描述
注意 Postgres 如何填充smallint列以强制执行必要的 4 个字节对齐。

下面是另一个例子，现在(bigint, smallint, boolean)产生了一行 35 个字节。
在这里插入图片描述
并且同一行，具有不同的顺序，导致每行 40 个字节：

计算对齐边界

什么决定了 Postgres 使用的对齐方式？来自文档：
typalign是存储此类型的值时所需的对齐方式。它适用于磁盘上的存储以及 PostgreSQL 内部值的大多数表示。当连续存储多个值时（例如在磁盘上表示整行时），会在这种类型的数据前插入填充，以便它从指定的边界开始。对齐参考是序列中第一个数据的开头。可能的值包括：

c = 字符对齐，即不需要对齐。
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

例如，您可以看到，这将需要（双倍或 8 个字节）int8的对齐，而需要一半的空间。dint4

varchar和的text工作方式不同。尽管它们的对齐方式为i，但它们的typlen对齐方式为负。这是为什么？因为它们的大小可变，即它们使用varlena结构。

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

还值得指出的是，uuid类型不同。它有typlen16 个字节，但它具有对齐（意味着它不需要事先对齐）。因此，例如，如果您在之前c有一列，则无需担心。booleanuuid

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

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

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