在PostgreSQL中计算和节省空间

wpx232ag  于 2023-01-08  发布在  PostgreSQL
关注(0)|答案(5)|浏览(164)

我在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。如果有什么技巧,我可以用它来压缩这张table吗?我的想法如下...
real值转换为integer。如果它们可以存储为smallint,则每个字段节省2个字节。
将列b .. m转换为数组,我不需要搜索这些列,但我需要一次返回一列的值,因此,如果我需要g列,我可以执行如下操作

SELECT a, arr[5] FROM t;

使用数组选项是否可以节省空间?是否会有速度损失?
还有别的主意吗?

7vhp5slm

7vhp5slm1#

"圆柱俄罗斯方块"

实际上,你可以做***某事***,但这需要更深入的理解。关键字是***对齐填充***. 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〈〉小提琴here *

老SQLF
根据经验,如果您将8字节列放在第一位,然后将4字节、2字节和1字节列放在最后,您不会出错。
booleanuuid(!)和其他一些类型不需要对齐填充。textvarchar和其他"varlena"(可变长度)类型 * 名义上 * 需要"int"对齐(在大多数机器上为4字节)。但我发现磁盘格式中没有对齐填充(与RAM不同)。最终,我在源代码的注解中找到了解释:
还应注意,当存储"压缩的" varlenas时,我们允许违反标称对齐; TOAST机制负责对大多数代码隐藏这些信息。
因此,只有当包含单个前导长度字节的数据(可能经过压缩)超过127字节时,才会强制执行"int"对齐,然后varlena storage会切换到四个前导字节,并要求"int"对齐。
通常,你玩"列俄罗斯方块"时每行最多可以节省几个字节,但在大多数情况下,这些都是不必要的,但对于数十亿行来说,这意味着几个千兆字节很容易。
可以使用函数**pg_column_size()**测试实际的列/行大小。
某些类型在RAM中占用的空间比在磁盘上占用的空间多(压缩或"打包"格式)。当使用pg_column_size()测试相同的值(或值行与表行)时,常量(RAM格式)的结果比表列的结果大。
最后,一些类型可以是compressed or "toasted"(行外存储)或两者兼有。
在可能的情况下,将NOT NULL列移到前面,将具有许多NULL值的列移到后面。NULL值直接从空位图提供,因此它们在行中的位置与NULL值的访问成本没有关系,但是它们增加了计算位于右侧(行中更靠后)的列的偏移量的微小成本。

每个元组(行)的开销

项目标识符每行4个字节-不受上述考虑因素影响。
以及至少24字节(23+填充)用于元组报头。The manual on Database Page Layout:
有一个固定大小的标头(在大多数计算机上占用23个字节),后跟一个可选的空位图、一个可选的对象ID字段和用户数据。
对于标头和用户数据之间的填充,您需要知道服务器上的MAXALIGN-通常在64位操作系统上为8字节(在32位操作系统上为4字节)。
在您的 * Postgres二进制目录 * 中运行以下命令以获得明确答案:

./pg_controldata /path/to/my/dbcluster

The manual:
实际用户数据(行的列)从t_hoff指示的偏移开始,对于平台,该偏移必须始终是MAXALIGN距离的倍数。
因此,通常通过将数据打包为8字节的倍数来获得最佳存储。
在你发布的 * 示例中没有任何收获。它已经被紧密地打包了。在最后一个int2之后有2个字节的填充,在最后4个字节。你可以在最后将填充合并为6个字节,这不会改变任何东西。

每个数据页的开销

数据页大小通常为8 KB。此级别也会产生一些开销/膨胀:剩余部分不够大,无法容纳另一个元组,更重要的是死行或使用FILLFACTOR设置保留的百分比。
对于磁盘上的大小,还有其他一些因素需要考虑:

数组类型?

对于一个array类型,你会为这个类型增加**24字节的开销。另外,数组元素像往常一样占用空间。没有任何好处。

sy5wg1nm

sy5wg1nm2#

我看不出在数组中存储多个数字字段有什么好处(也有什么损失)。
size of each numerical type有明确的文档记录,您应该简单地使用与您所需的距离分辨率兼容的最小尺寸类型;这就是你能做的一切。
我不认为(但我不确定)一行沿着的列是否有一些字节对齐要求,在这种情况下,列的重新排序可能会改变所使用的空间-但我不这样认为。
顺便说一句,每行有一个固定的开销,大约为23 bytes

eimct9ow

eimct9ow3#

从这个伟大的文档: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
goqiplq2

goqiplq24#

下面是一个关于Erwin的列重新排序建议的很酷的工具:https://github.com/NikolayS/postgres_dba
它有确切的命令-- p1:

然后,它会自动显示所有表中列重新排序的真实的可能性:

flvtvl50

flvtvl505#

在阅读了Erwin Brandstetterjboxxx的答案以及后者中链接的文档之后,我对查询进行了一些改进,使其更加通用:

-- 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(表所在的模式)上添加过滤器。
我还添加了storage kind,这是一个有用的信息,有助于确定哪些-1列要内联和/或排序在哪里,并保持现有列的相对顺序,否则使用相同的排序键。

相关问题