计算并节省PostgreSQL中的空间 [英] Calculating and saving space in PostgreSQL

问题描述

我有一张这样的表格：

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字节。我将在表中有很多行，可能上升了145亿行，所以表将要推13-14亿字节。我可以用什么技巧来压缩这张表？我可能的想法在下面...

The above adds up to 50 bytes per row. My experience is that I need another 40% to 50% for system overhead, without even any user-created indexes to the above. So, about 75 bytes per row. I will have many, many rows in the table, potentially upward of 145 billion rows, so the table is going to be pushing 13-14 terabytes. What tricks, if any, could I use to compact this table? My possible ideas below ...

将真实值转换为 integer 。如果它们可以作为 smallint 存储，那么每个字段保存2个字节。

Convert the real values to integer. If they can stored as smallint, that is a saving of 2 bytes per field.

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

Convert the columns b .. m into an array. I don't need to search on those columns, but I do need to be able to return one column's value at a time. So, if I need column g, I could do something like

SELECT a, arr[5] FROM t;

我可以使用数组选项节省空间吗？会有罚款吗？

Would I save space with the array option? Would there be a speed penalty?

任何其他想法？

推荐答案

在数组中存储几个数字字段时，我没有看到任何东西可以获得（而且会丢失）。

I see nothing to gain (and something to lose) in storing several numeric fields in an array.

大小每个数值类型的清楚记录，您应该简单地使用最小尺寸的类型与您期望的分辨率相兼容;这就是你能做的所有事情。

The size of each numerical type is clearly documented, you should simply use the smallest sized type compatible with your desired range-resolution; and that's about all you can do.

我不认为（但我不知道）是否有一些字符串对齐要求的列的一行，在这种情况下重新排序列可能会改变使用的空间 - 但我不这么认为。

I don't think (but I'm not sure) if there is some byte alignment requirement for the columns along a row, in that case a reordering of the columns could alter the space used - but I don't think so.

BTW，每行有一个修复开销，关于 23个字节。

BTW, there is a fix overhead per row, about 23 bytes.

这篇关于计算并节省PostgreSQL中的空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！