混合“类索引” btree 结构 - PostgreSQL 可以做到这一点吗？

Question

我是 PostgreSQL 新手。我对需要构建的混合数据库有一个非常不寻常的要求。从我见过的模块来看，在我看来，以下情况是可能的。

我需要能够将键 - [值] 添加到索引中，而无需实际将数据添加到表中。简而言之，我需要一个键[值]存储，最好是 btree（查找速度）。索引结构是理想的。也许另一个结构可以做到这一点。

非常具体地说，我希望存储类似的内容：

KEY     [IDs]
Blue    10, 20, 23, 47
Green   5, 12, 40

我不想要存储这些数据并为其建立索引的开销。可以这么说，我只需要“索引但不存储”的数据。

同样重要的是能够查询这些结构并获取数据 (ID)，并且能够对 ID 执行 INTERSECTS 等操作，以及对键执行 IN、BETWEEN、= 等操作。

正如您可能猜到的那样，最终目标是最终的 ID 列表，然后将其发送给客户端并随意查找。

编辑

我不想记录每个值的键。使用上面的示例，我不想存储 {Blue, 10}、{Blue, 20} 等。我想存储 {Blue, [10, 20, 23, 47]}。

如果我将其存储为传统表，我无法找到解决此重复问题的方法。

再看看 Blue, [10, 20, 23, 47]}，从技术上讲，这只不过是一个 btree，其中 ID (10, 20, 23, 47) 被标记为值，父键“Blue”被标记为密钥。

由于这种数据类型不匹配在单个树中可能会很混乱，所以我相信理想的解决方案是“btree 中的 [btrees]”，其中“btree”是关键，而 [btrees] 是 a 的每组值的 btree钥匙。

Answer 1

如果您确实坚持这样做，您可以将值存储为数组，并且 intarray 模块提供了操作符来操作它们。也就是说：

create table data(key text primary key, values int[] not null);
insert into data
  values('Blue', '{10,20,23,47}'),('Green','{5,12,40}'),('Red', '{5,10,28}');

用这个你可以写：

select unnest(values) from data where key = 'Blue'
  intersect
  select unnest(values) from data where key = 'Red';

理想情况下，你需要一个聚合函数来将 int[] 转换为集合并计算交集等，但它们似乎没有提供。

实际上，这只是更典型结构的稍微更紧凑的存储：

select key, unnest(values) as value from data;
  key  | value
-------+-------
 Blue  |    10
 Blue  |    20
 Blue  |    23
[...]

事实上，您可以简单地将视图定义为上面的查询。

一种更规范化的方法是有两个表：一个用于描述键，一个用于将它们与值关联：

create table key_dimension(key_id serial primary key, key text not null unique);
insert into key_dimension(key) values('Blue'),('Green'),('Red');
create table key_value(key_id int not null references key_dimension(key_id), value int not null, primary key(key_id, value));
insert into key_value(key_id, value)
  select key_id, unnest(values) from key_dimension join data using (key);

现在：

select value from key_value
  where key_id = (select key_id from key_dimension where key = 'Red')
intersect
select value from key_value
  where key_id = (select key_id from key_dimension where key = 'Blue')

因此，任何选择键值的查询都只需要针对键集（key_dimension）运行，然后是一个最小的合成key (key_id) 用于将它们转换为实际的数据值集（来自 key_value）。