在PostgreSQL中计算和节省空间

Question

我有一个pg中的表格，如下所示：

CREATE TABLE t (
    a BIGSERIAL NOT NULL,               -- 8 b
    b SMALLINT,                         -- 2 b
    c SMALLINT,                         -- 2 b
    d REAL,                             -- 4 b
    e REAL,                             -- 4 b
    f REAL,                             -- 4 b
    g INTEGER,                          -- 4 b
    h REAL,                             -- 4 b
    i REAL,                             -- 4 b
    j SMALLINT,                         -- 2 b
    k INTEGER,                          -- 4 b
    l INTEGER,                          -- 4 b
    m REAL,                             -- 4 b
    CONSTRAINT a_pkey PRIMARY KEY (a)
);

上面的每行总共需要50个字节。我的经验是，即使没有任何用户创建的索引，我需要另外40%到50%作为系统开销。因此，每行大约需要75个字节。由于表中可能会有很多行，潜在的行数高达1450亿行，因此该表将占用13-14 TB的空间。我能使用哪些技巧来压缩这个表呢？以下是我可能想到的一些想法...

将“real”值转换为“integer”。如果它们可以存储为“smallint”，那么每个字段可以节省2个字节。

将列b..m转换为数组。我不需要在这些列上进行搜索，但是我确实需要能够每次返回一列的值。因此，如果我需要g列，我可以这样做：

SELECT a, arr[5] FROM t;

使用数组选项会节省空间吗？会有速度惩罚吗？

还有其他想法吗？

- punkish

---

PostgreSQL文档建议避免使用real和double precision类型来表示精确浮点数（例如用于存储会计价值）。相反，应该使用numeric数据类型。这只是一个提示，以防那些real值被用于会计或精确结果操作。 - sɪʒɪhɪŋ βɪstɦa kxɐll

Answer 1

“列式俄罗斯方块”

实际上，你可以做某些事情，但这需要更深入的理解。关键词是对齐填充。每种数据类型都有特定的对齐要求。

您可以通过有利地排序来最小化在列之间浪费的填充空间。以下（极端）示例将浪费大量物理磁盘空间：

CREATE TABLE t (
    e int2    -- 6 bytes of padding after int2
  , a int8
  , f int2    -- 6 bytes of padding after int2
  , b int8
  , g int2    -- 6 bytes of padding after int2
  , c int8
  , h int2    -- 6 bytes of padding after int2
  , d int8)

为了每行节省24字节，请使用以下代码：

CREATE TABLE t (
    a int8
  , b int8
  , c int8
  , d int8
  , e int2
  , f int2
  , g int2
  , h int2)   -- 4 int2 occupy 8 byte (MAXALIGN), no padding at the end

db<>fiddle 这里
_{旧版sqlfiddle}

通常情况下，如果你将8字节列放在前面，然后是4字节、2字节和1字节列，你就不会出错。

boolean、uuid（！）和其他一些类型不需要对齐填充。 text、varchar 和其他“varlena”（可变长度）类型在名义上需要“int”对齐填充（大多数机器上为4字节）。 但我观察到磁盘格式中没有对齐填充（与 RAM 不同）。最终，我在源代码的注释中找到了解释：

请注意，在存储“紧凑型”变长类型时，我们允许违反名义对齐；TOAST机制会从大多数代码中隐藏这一点。

因此，“int”对齐仅在包括单个前导长度字节的（可能压缩的）数据超过127字节时强制执行。然后，varlena存储切换为四个前导字节，并需要“int”对齐。

通常情况下，最好通过玩“列俄罗斯方块”来每行节省几个字节。但在大量行的情况下，这可能意味着几个GB。

您可以使用函数pg_column_size()测试实际列/行大小。某些类型在RAM中占用的空间比磁盘上的空间更大（压缩或“紧凑”格式）。当使用pg_column_size()测试相同值（或值行与表行）时，常量（RAM格式）的结果可能比表列更大。

最后，一些类型可以被压缩或“烤”（存储在行外）或两者兼而有之。

尽可能将NOT NULL列移至前面，并将具有许多NULL值的列移到后面。 NULL值直接从null位图中提供，因此它们在行中的位置对NULL值的访问成本没有影响，但会为计算位于右侧（行末）的列的偏移量增加微小的成本。

每个元组（行）的开销

每行4个字节的项目标识符-不受上述考虑的限制。
至少需要24个字节（23个字节+填充）用于元组头。 数据库页面布局手册：

有一个固定大小的标题（在大多数机器上占用23个字节），然后是可选的空位图，可选的对象ID字段和用户数据。

对于标题和用户数据之间的填充，您需要了解服务器上的MAXALIGN - 通常在64位操作系统上为8字节（或在32位操作系统上为4字节）。如果您不确定，请查看pg_controldata。

在您的Postgres二进制目录中运行以下命令以获得明确的答案：

./pg_controldata /path/to/my/dbcluster

手册:

实际用户数据（行的列）从偏移量t_hoff开始，它必须始终是平台上MAXALIGN距离的倍数。

因此，通常通过以8字节的倍数打包数据来获得存储最佳性能。

在您发布的示例中没有任何可获得的好处。它已经紧密打包了。在最后一个int2之后有2个字节的填充，最后还有4个字节。您可以将填充合并到末尾的6个字节中，这不会改变任何内容。

每个数据页的开销

数据页大小通常为8 KB。在此级别也存在一些开销/膨胀：剩余空间不足以容纳另一个元组，更重要的是死行或保留了一定百分比的FILLFACTOR设置。

还有一些其他因素需要考虑磁盘大小：

• 我在Heroku上的PostgreSQL数据库中，5MB可以存储多少条记录？
• 在PostgreSQL中不使用NULL是否仍然在头部使用NULL位图？
• 为了获得读取性能，如何配置PostgreSQL？

数组类型？

对于像您正在评估的数组类型，您将添加24字节的开销以用作类型标识。此外，数组元素按照通常方式占用空间。在那里没有任何可获得的东西。

- Erwin Brandstetter

---

一般而言，如果您将8字节的列放在最前面，然后是4字节、2字节和1字节的列，就不会出错。这是一些真正酷的建议。 - Evan Carroll

这是一个很棒/有趣的回答，但有一件事我不明白，为什么在“CREATE TABLE”中列名的顺序很重要。我原本以为这并不重要。为什么Postgres不能自动进行俄罗斯方块式的优化？为什么列顺序被认为是足够重要而保持原定义？ - Russ

@Russ: 因为没有人实现逻辑和物理列顺序之间的分离。这是一个待办事项（https://wiki.postgresql.org/index.php?title=Alter_column_position&action=history），但不容易，因为它会在各个系统目录中造成混乱。到现在已经过了将近6年，但这仍然是真实的。自Postgres 9.3以来，VIEW可以用于简单情况下呈现不同的列顺序（写入也会自动传播）。 - Erwin Brandstetter

谢谢，@Erwin。然而，让用户自己处理似乎仍然有些奇怪。我明白在创建表后更改系统目录可能会很棘手，但如果重新排序在一开始就完成，似乎不会有问题。使用类似于存储参数的东西会有什么问题吗？比如CREATE TABLE WITH column_reorder_ok，它表示“我不关心逻辑列顺序，所以请随意调整以优化表格”。或者，按照您的术语，WITH column_tetris_ok。 :) - Russ

@Russ：听起来是一个有用的功能。在创建表之前重新排序列的工具，可以避免对系统目录进行操作而产生的复杂性。这也可以在任何客户端软件中实现，即为最小存储优化CREATE TABLE语句的工具... - Erwin Brandstetter

Answer 2

从这份出色的文档中：https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/ 对于一个已经存在或者正在开发中的表，名为my_table，以下查询将会给出最优左到右的顺序。

SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'my_table'
 AND a.attnum >= 0
ORDER BY t.typlen DESC

- jboxxx

---

这个查询非常灵活。有没有人知道有没有一种工具可以根据这个查询自动重新对列进行排列？https://wiki.postgresql.org/wiki/Alter_column_position - olidem

4 @olidem https://github.com/NikolayS/postgres_dba，请查看我的回答以获取示例。 - Алексей Лещук

Answer 3

我认为在数组中存储多个数字字段无法获得任何好处（而且可能会有损失）。

每种数值类型的大小都有明确的文档说明，您应该使用与所需范围 - 分辨率兼容的最小类型；这就是您能够做的全部。

我不确定是否存在一些列对齐要求，如果存在，则列的重新排序可能会改变使用的空间 - 但我认为不存在。

顺便说一句，每行有一个固定的开销，约为23字节。

- leonbloy

---

截至9.2版本，每行的行头占据24字节，页偏移量占据4字节（存储在页头中），即每行占据28字节。还有其他因素需要考虑，例如每8列支持NULL值，需要1个字节来存储（NULL值被存储为位掩码）。 - Sean

@Sean：这并不完全正确。根据手册此处，行头（HeapTupleHeader）占用23个字节，而不是24个字节。有一个固定大小的头部（在大多数机器上占用23个字节），后面跟着一个可选的空值位图，一个可选的对象ID字段。 这种差异很重要，最多有8列的表的NULL位掩码适合这个备用字节，使得这些表的NULL存储实际上是免费的。 - Erwin Brandstetter

正确，然而由于数据类型的对齐，在第23和24个字节之间几乎肯定存在一个空洞，并且从第25个字节开始是一个INT。因此，标头只有23个字节，但所占用的空间是24个字节。 - Sean

Answer 4

这里有一个关于Erwin列重新排序建议的很酷的工具：https://github.com/NikolayS/postgres_dba

它有确切的命令—— p1：

然后它会自动显示所有表格中列重新排序的真正潜力：

- Алексей Лещук

Answer 5

在阅读了Erwin Brandstetter和jboxxx的答案以及后者链接的文档之后，我稍微改进了查询以使其更加通用：

-- https://www.postgresql.org/docs/current/catalog-pg-type.html
CREATE OR REPLACE VIEW tabletetris
    AS SELECT n.nspname, c.relname,
        a.attname, t.typname, t.typstorage, t.typalign, t.typlen
    FROM pg_class c
    JOIN pg_namespace n ON (n.oid = c.relnamespace)
    JOIN pg_attribute a ON (a.attrelid = c.oid)
    JOIN pg_type t ON (t.oid = a.atttypid)
    WHERE a.attnum >= 0
    ORDER BY n.nspname ASC, c.relname ASC,
        t.typlen DESC, t.typalign DESC, a.attnum ASC;

使用方法如下：

SELECT * FROM tabletetris WHERE relname='mytablename';

但是你可以在nspname上添加过滤器（表所在的模式）。

我还添加了存储类型，这是有用的信息，可以确定哪些-1要内联和/或排序，以及保持具有相同排序键的现有列的相对顺序。

- mirabilos