在PostgreSQL中存储字符串的最紧凑和最快速的方法是什么？

Question

我有一个大的十六进制（16字节，32个十六进制数字）数据项，始终具有以下格式：

00d980113901429fa6de7fb7e2da705a

这是从我的资源以ASCII字符串的形式传入的（即，上面的零是字符零0x30，而不是0x00），我想知道人们对在PostgreSQL中存储它的最佳方法（关于存储和速度）的意见。

显然的方法是将其存储为varchar，但将其以二进制形式存储肯定会节省空间。如果以二进制形式存储，我会从选择和插入中获得性能提升吗？bytea还是bit更好？这两者在内部表示方面有什么区别？

另一个想法是将其存储为两个bigint/int8或四个integer/int4，拆分成多个列。

由于我有很多这些（超过一万亿），所以空间和时间都是问题。

- Donald Miner

Answer 1

比较这两个包含1000万条记录的表：

create table test (a int8 not null, b int8 not null, primary key(a,b));
insert into test
  select generate_series(1,10000000), generate_series(1,10000000);
select pg_size_pretty(pg_total_relation_size('test'));

723 MB

create table test_bytea (a bytea not null);
insert into test_bytea
  select decode(lpad(to_hex(a),16,'0')||lpad(to_hex(b),16,'0'),'hex') from test;
alter table test_bytea add primary key (a);
select pg_size_pretty(pg_total_relation_size('test_bytea'));

804兆字节

带索引的 bytea 比 2*int8 大11％。这不算太多，但意味着缓存中将有11％较少的行。顺序扫描将慢11％等等。

如果您的数据不会改变，也许考虑使用排序值的平面文件存储，而不是数据库 - 这将是每10M记录152MB，搜索将是O(log(n))。

- Tometzky

---

即使有一万亿行，我也不会这样做。你为了避免BYTEA的长度标头而使用两个固定宽度的INT8，但是所有查询和外键变得更加复杂。就我个人而言，我愿意支付11%的税费来保持事情简单。或者你可以切换到MySQL，在那里BINARY（16）将具有与两个INT8字段相同的性能。 - Julius Musseau

Bytea并不容易处理，因为它需要转义或准备查询。与2*int8相比，bytea的比较可能也会更慢，因为它可能逐字节检查而不是以8字节为单位进行检查，并且还需要检查大小。 - Tometzky

Answer 2

你需要确定数据的最常见用途，以确定适当的数据类型。转换数据类型意味着引用该列的索引无效。

- OMG Ponies

---

我可以调整我们使用/查询数据的方式以适应所使用的表示。提高空间/时间效率是值得的。 - Donald Miner

如果你在转换上创建一个函数索引，那么它就不是无用的。 - rfusca

@rfusca：没错，但以我的经验来看，数据库管理员厌恶基于函数的索引。 - OMG Ponies

Answer 3

我怀疑在节省空间方面，使用BYTEA相比VARCHAR表示法可以减少一半的空间，并且在比较（>，<，=）方面比VARCHAR表示法快两倍。

在其他数据库引擎中，您甚至可以避免长度头开销。例如：

MS-SQL: BINARY(16)
Oracle: RAW(16)
MySQL: BINARY(16)

或者，如果您喜欢长度头：

MS-SQL: VARBINARY(16)
Oracle: BLOB
MySQL: VARBINARY(16)

PostgreSQL仅支持BYTEA，因此您始终需要支付长度头的费用，但在这种情况下，我仍然选择使用BYTEA。

- Julius Musseau