sql - 在 PostgreSQL 中存储我的字符串的最紧凑和最快的方法

Question

我有一个大的十六进制(16 字节，32 个十六进制数字)数据项，其格式始终为:

00d980113901429fa6de7fb7e2da705a

这是来 self 的来源的 ASCII 字符串(即上面的零是字符零 0x30，而不是 0x00)，我想知道人们对最佳存储方式(irt 存储和速度)的看法这在 PostgreSQL 中。

显而易见的做法是将其存储为 varchar，但以二进制形式存储肯定会节省空间。通过以二进制形式存储 select 和 insert，我会看到性能提升吗？ bytea 或 bit 会更好吗？这两者在内部表示上有区别吗？

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

空间和时间是个问题，因为我有很多(超过一万亿)。

Answer 1

比较这两个10M记录的表:

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 MB

带有索引的 bytea 比 2*int8 大 11%。这并不多，但它意味着缓存中的行将减少 11%。并且顺序扫描会慢 11% 等等。

如果您的数据没有改变，也许您应该考虑存储排序值的平面文件而不是数据库 - 每 1000 万条记录只有 152MB，搜索的时间复杂度为 O(log(n))。