在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;

我可以使用array选项节省空间吗？会有速度限制吗？

还有其他想法吗？

Answer 1

实际上，您可以做一些事情，但这需要更深入的了解。关键字是alignment padding。每种数据类型都有特定的对齐要求。

您可以通过对列进行有序排序来最大程度地减少列之间的填充丢失的空间。以下（极端）示例将浪费大量物理磁盘空间：

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 here

旧的sqlfiddle

根据经验，如果您先放置8字节的列，然后再放置4字节，2字节和1字节的列，那么您不会出错。

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

还请注意，在存储“打包”的varlenas时，我们会违反标称对齐方式；TOAST机制负责将其隐藏在大多数代码中。

因此，只有当包含单个前导长度字节的（可能是压缩的）数据超过127个字节时，才强制执行“ int”对齐。然后，varlena存储切换到四个前导字节，并要求“ int”对齐。

通常，最好在播放“ cote tetris”时每行可以节省几个字节。在大多数情况下，这些都不是必需的。但是，随着数十亿行的出现，它可能很容易意味着几千兆字节。

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

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

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

有一个固定大小的标头（在大多数计算机上占23个字节），后跟一个可选的空位图，一个可选的对象ID字段以及用户数据。

对于标头和用户数据之间的填充，您需要MAXALIGN在服务器上知道-在64位OS上通常为8个字节（在32位OS上为4个字节）。如果不确定，请签出pg_controldata。

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

./pg_controldata /path/to/my/dbcluster

手册：

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

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

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