是否可以在Postgres中存储一个1字节的数字？ [英] Is it possible to store a 1 byte number in Postgres?

问题描述

我要存储在Postgres中的每个记录有7个8位整数值。 Pg不提供单字节整数类型SMALLINT或2字节，这是最小的整数数据类型。反正我可以存储7个8位数字并节省空间吗？

I have 7 8-bit integer values per record that I want to store in Postgres. Pg doesn't offer a single byte integer type, SMALLINT, or 2 bytes, being the smallest integer datatype. Is there anyway I can store my 7 8-bit numbers and save on space?

具有7个元素的数组的数组类型是否更紧凑？还是我应该对7个数字进行二进制表示（例如，在Perl中使用pack）并将其存储在单个bytea字段中？

Would an array type with a 7 element array be more compact? Or, should I make a binary representation of my 7 numbers (for example, using pack in Perl) and store that in a single bytea field?

还有其他建议吗？

推荐答案

鉴于PostgreSQL中任何行的开销为 23个字节（HeapTupleHeaderData），如果您真的很在意少量空间，则可能是选择了错误的存储方式

Given that the overhead for any row in PostgreSQL is 23 bytes (HeapTupleHeaderData), if you really care about small amounts of space this much you've probably picked the wrong way to store your data.

无论如何，由于所有更复杂的类型都有其自身的开销（例如，bytea会增加四个字节的开销，例如位字符串5至8）。要完成您要查找的内容，请使用bigint（8个字节），对每个值进行数字移位，然后将结果进行或运算。您可以使用位字符串操作进行编码更容易-将其计算为位字符串，然后在存储之前转换为bigint；或者，如果您想提高速度，则只需手动乘/加即可。例如，以下是将两个字节一起存储为两个字节的结构，然后再次将其取回的方法：

Regardless, since all the more complicated types have their own overhead (bytea adds four bytes of overhead for example, bit strings 5 to 8), the only way to accomplish what you're looking for is to use a bigint (8 bytes), numerically shifting each value and OR-ing together the result. You can do this using the bit string operations to make the code easier--compute as bit string, then cast to bigint before storing--or just manually multiply/add if you want speed to be better. For example, here's how you store two bytes together into a two byte structure and then get them back again:

int2 = 256 * byte1 + byte2
byte1 = int2 / 256
byte2 = int2 % 256

您可以将相同的想法扩展为以这种方式存储7个想法。检索开销仍将是可怕的，但是实际上您已经在该过程中节省了一些空间。但是相对于行标题而言并没有太大关系。

You can extend the same idea into storing 7 of them that way. Retrieval overhead is still going to be terrible, but you will have actually saved some space in the process. But not very much relative to just the row header.

这篇关于是否可以在Postgres中存储一个1字节的数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！