我在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;
使用数组选项是否可以节省空间?是否会有速度损失?
还有别的主意吗?
5条答案
按热度按时间7vhp5slm1#
"圆柱俄罗斯方块"
实际上,你可以做***某事***,但这需要更深入的理解。关键字是***对齐填充***. Every data type has specific alignment requirements。
您可以通过对列***之间的填充***进行适当排序来最大限度地减少空间损失。以下(极端)示例将浪费大量物理磁盘空间:
要节省每行24字节,请改用:
老SQLF
根据经验,如果您将8字节列放在第一位,然后将4字节、2字节和1字节列放在最后,您不会出错。
boolean
、uuid
(!)和其他一些类型不需要对齐填充。text
、varchar
和其他"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二进制目录 * 中运行以下命令以获得明确答案:
The manual:
实际用户数据(行的列)从
t_hoff
指示的偏移开始,对于平台,该偏移必须始终是MAXALIGN
距离的倍数。因此,通常通过将数据打包为8字节的倍数来获得最佳存储。
在你发布的 * 示例中没有任何收获。它已经被紧密地打包了。在最后一个
int2
之后有2个字节的填充,在最后4个字节。你可以在最后将填充合并为6个字节,这不会改变任何东西。每个数据页的开销
数据页大小通常为8 KB。此级别也会产生一些开销/膨胀:剩余部分不够大,无法容纳另一个元组,更重要的是死行或使用
FILLFACTOR
设置保留的百分比。对于磁盘上的大小,还有其他一些因素需要考虑:
数组类型?
对于一个array类型,你会为这个类型增加**24字节的开销。另外,数组元素像往常一样占用空间。没有任何好处。
sy5wg1nm2#
我看不出在数组中存储多个数字字段有什么好处(也有什么损失)。
size of each numerical type有明确的文档记录,您应该简单地使用与您所需的距离分辨率兼容的最小尺寸类型;这就是你能做的一切。
我不认为(但我不确定)一行沿着的列是否有一些字节对齐要求,在这种情况下,列的重新排序可能会改变所使用的空间-但我不这样认为。
顺便说一句,每行有一个固定的开销,大约为23 bytes。
eimct9ow3#
从这个伟大的文档:https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
对于已经存在的表,或者正在开发中创建的名为
my_table
的表,此查询将给予从左到右的最佳顺序。goqiplq24#
下面是一个关于Erwin的列重新排序建议的很酷的工具:https://github.com/NikolayS/postgres_dba
它有确切的命令-- p1:
然后,它会自动显示所有表中列重新排序的真实的可能性:
flvtvl505#
在阅读了Erwin Brandstetter和jboxxx的答案以及后者中链接的文档之后,我对查询进行了一些改进,使其更加通用:
像这样使用:
但是您可以在
nspname
(表所在的模式)上添加过滤器。我还添加了storage kind,这是一个有用的信息,有助于确定哪些
-1
列要内联和/或排序在哪里,并保持现有列的相对顺序,否则使用相同的排序键。