postgresql - 在 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。如果有的话，我可以使用什么技巧来压缩这张表？以下是我可能的想法......

将实数值转换为整数。如果它们可以存储为 smallint，则每个字段可节省 2 个字节。

将列 b .. m 转换为数组。我不需要搜索这些列，但我确实需要能够一次返回一列的值。所以，如果我需要 g 列，我可以做类似的事情

SELECT a, arr[5] FROM t;

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

还有其他想法吗？

Answer 1

“俄罗斯方 block 列”

实际上，您可以做点什么，但这需要更深入的理解。关键字是对齐填充。 Every data type has specific alignment requirements .

您可以通过有利地对列进行排序，从而最大限度地减少因列之间 填充而损失的空间。以下(极端)示例会浪费大量物理磁盘空间:

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 不同)。最终，我在 note in the source code: 中找到了解释。

Note also that we allow the nominal alignment to be violated when storing "packed" varlenas; the TOAST mechanism takes care of hiding that from most code.

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

通常，您最多可以在播放“列俄罗斯方 block ” 时每行节省几个字节。在大多数情况下，这些都不是必需的。但是，如果有数十亿行，它可能很容易意味着几千兆字节。

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

最后，有些类型可以是compressed or "toasted" (离线存储)或两者兼而有之。

如果可能，将NOT NULL 列移到前面，将具有许多NULL 值的列移到后面。 NULL 值直接从 null 位图提供，因此它们在行中的位置与 NULL 值的访问成本无关，但它们会增加计算位于右侧的列的偏移量(在行中更靠后)。

每个元组(行)的开销

项目标识符每行 4 个字节 - 不受上述注意事项的约束。
元组头至少有 24 个字节(23 + 填充)。 The manual on Database Page Layout:

There is a fixed-size header (occupying 23 bytes on most machines),followed by an optional null bitmap, an optional object ID field, andthe user data.

对于 header 和用户数据之间的填充，您需要知道服务器上的 MAXALIGN - 在 64 位操作系统上通常为 8 个字节(或在 32 位操作系统上为 4 个字节)。如果您不确定，请查看 pg_controldata .

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

./pg_controldata /path/to/my/dbcluster

The manual:

The actual user data (columns of the row) begins at the offsetindicated by t_hoff, which must always be a multiple of the MAXALIGNdistance for the platform.

因此，您通常通过将数据打包为 8 字节的倍数来获得最佳存储。

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

每个数据页的开销

数据页大小通常为 8 KB。在这个级别上也有一些开销/膨胀:剩余量不足以容纳另一个元组，更重要的是死行或保留给 FILLFACTOR setting 的百分比。 .

磁盘大小还有几个其他因素需要考虑:

• How many records can I store in 5 MB of PostgreSQL on Heroku?
• Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
• Configuring PostgreSQL for read performance

数组类型？

对于您正在评估的数组 类型，您将为该类型添加24 字节的开销。另外，数组元素像往常一样占用空间。那里没有任何收获。