计算并节省PostgreSQL中的空间

本文介绍了计算并节省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.