时间戳

首页 > 解决方案 > 在字符串数组（带有 GIN 索引）与拆分行（B-Tree 索引）之间进行选择

database - 在字符串数组（带有 GIN 索引）与拆分行（B-Tree 索引）之间进行选择

问题描述

我有一个存储一列的数据库receiver，以表示与数据相关的帐户（例如“查理”）。然而，这导致了大量的数据重复，因为一组数据可能会创建 3 个单独的行，其中唯一的区别是receiver列。

|---------------------|------------------||---------------------|------------------|
|      Receiver       |       Event      ||         Date        |     Location    |
|---------------------|------------------||---------------------|------------------|
|       Alpha         |         3        ||          12         |         USA       |
|---------------------|------------------||---------------------|------------------|
|       Bravo         |         3        ||          12         |         USA       |
|---------------------|------------------||---------------------|------------------|
|       Charlie       |         3        ||          12         |         USA       |
|---------------------|------------------||---------------------|------------------|

在重新设计数据库时，我考虑过使用带有 GIN 索引的数组，而不是接收器上的当前 B-Tree 索引。我提议的新表如下所示：

|-------------------------------|------------------||------------------|-------------------|
|           Receivers           |       Event      ||      Date        |     Location      |
|-------------------------------|------------------||------------------|-------------------|
| ["Alpha", "Bravo", "Charlie"] |         3        ||       12         |         USA       |
|-------------------------------|------------------||------------------|-------------------|

目前所有查询中有 95% 的形式为：SELECT * FROM table WHERE Receiver = Alpha

此外，该表目前包含超过 40 亿行，这会将其减少到 20 亿行以下。

哪个选项更有效？

标签： databasepostgresqldatabase-designdata-modeling

解决方案

您不应该使用数组，而应该使用规范化的数据模型，其中event和receiver是两个不同的表。表之间的关系应该通过外键约束来实现receiver。

这些表看起来像这样：

CREATE TABLE occurrence (
   occurrence_id bigint PRIMARY KEY,
   event integer NOT NULL,
   date integer NOT NULL,
   location text NOT NULL
);

CREATE TABLE receiver (
   receiver_id bigint PRIMARY KEY,
   receiver_name text NOT NULL
);

CREATE TABLE log_entry (
   occurrence_id bigint NOT NULL REFERENCES occurrence,
   receiver_id   bigint NOT NULL REFERENCES receiver,
   PRIMARY KEY (occurrence_id, receiver_id)
);

日志条目引用事件发生和接收者。

你会像这样查询

SELECT r.receiver_name,
       o.event,
       o.date,
       o.location
FROM occurrence AS o
   JOIN log_entry AS l USING (occurrence_id)
   JOIN receiver AS r USING (receiver_id)
WHERE /* your conditions */;

推荐阅读