代码之家 › 专栏 › 技术社区 › punkish

PostgreSQL中空间的计算与节省

bigdata storage database-design postgresql

punkish · 技术社区 · 16 年前

我有一张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-14TB。如果有的话,我可以用什么方法来压缩这张桌子?我可能的想法如下。。。

转换 real integer . 如果它们可以作为 smallint

转换列b。。我进入一个数组。我不需要搜索这些列,但我需要能够一次返回一列的值。所以,如果我需要g列,我可以这样做

SELECT a, arr[5] FROM t;

我可以用数组选项节省空间吗?会有超速处罚吗?

还有其他想法吗?

2 回复 | 直到 12 年前

leonbloy 16 年前

这个 size of each numerical type

我不认为(但我不确定)行中的列是否需要字节对齐,在这种情况下,列的重新排序可能会改变所用的空间,但我不这么认为。

顺便说一句,每行有固定开销,大约 23 bytes .

212

Erwin Brandstetter 5 年前

实际上,你能做到 ,但这需要更深入的理解。关键字是 对齐填充 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)

保存每行使用:

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

数据库<&燃气轮机;小提琴 here
_{旧的

sqlfiddle}

根据经验,如果先放置8字节列,然后再放置4字节、2字节和1字节列,则不会出错。

boolean , uuid (!) 其他一些类型不需要对齐填充。 text , varchar 名义上 需要“int”对齐(在大多数机器上为4字节)。但我观察到磁盘格式中没有对齐填充(与RAM不同)。最终,我在一份报告中找到了解释 note in the source code:

还请注意,我们允许在存储“打包”varlenas时违反标称对齐;TOAST机制负责对大多数代码隐藏这一点。

通常情况下,您最多每行可以节省几个字节 “俄罗斯方块” . 在大多数情况下,这些都不是必需的。但对于数十亿行来说,它很容易就意味着几千兆字节。

pg_column_size() .
有些类型在RAM中占用的空间比磁盘上的空间大(压缩或“打包”格式)。当使用测试相同的值(或值行与表行)时,常数(RAM格式)的结果可能比表列的结果更大 pg\列\大小() .

最后,一些类型可以 compressed or "toasted"

每元组(行)开销

项目标识符每行4字节-不受上述考虑。
以及至少24字节(23+填充)的元组头。 The manual on Database Page Layout:

后跟可选的空位图、可选的对象ID字段和用户数据。

对于标头和用户数据之间的填充,您需要知道 MAXALIGN pg_controldata .

在您的计算机中运行以下命令 Postgres二进制目录

./pg_controldata /path/to/my/dbcluster

The manual:

实际用户数据(行的列)从偏移量开始表示为 t_hoff 站台的距离。

因此,通常通过将数据打包成8字节的倍数来获得最佳存储空间。

在这场战争中没有任何收获 . 已经挤得很紧了。在最后一个 int2

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

带着一个数组你会加上

jboxxx 6 年前

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