我有一张这样的表格:
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中的空间
发布评论