优化关系数据库中的日常数据存储

Question

更新：有评论说问题不清楚，我在逻辑上做了一个飞跃，声称我将拥有 1180 亿行。我编辑了下面的文字以澄清问题。请参阅下面的斜体文本，

我已经为此苦苦挣扎了一段时间，甚至走了几条路，但现在我向社区寻求想法。首先，问题是为网格中的单元格存储 6 个日常变量大约 25 年。

• 变量数量 = 6
• 单元格数量 ~ 1300 万
• 天数 ~ 9125 (25 * 365)

针对两种不同类型的查询优化存储 —

查询一：检索所有或部分单元格的单个变量值一天。这类似于图像中每个像素都是单个变量的值。

查询二：检索单个单元格的单个变量的所有天数或天数持续时间的值。这就像从表中抓取一列，其中每行保存一天的所有变量。

因此，我开始设计数据库。单个表其中每一行都是一个单元格一天的值会这样

CREATE TABLE d (
    yr      SMALLINT,
    yday    SMALLINT,
    a       SMALLINT,
    b       SMALLINT,
    d       SMALLINT,
    e       SMALLINT,
    f       SMALLINT,
    g       SMALLINT,
    cell_id INTEGER
)
WITH (
    OIDS=FALSE
)

数据看起来像这样

yr      yday    a   b   c   d   e   f   g   cell_id
----------------------------------------------------
1980    1       x   x   x   x   x   x   x   1
1980    2       x   x   x   x   x   x   x   1
1980    3       x   x   x   x   x   x   x   1
..
1980    365     x   x   x   x   x   x   x   1
...
1981    1       x   x   x   x   x   x   x   1
1981    2       x   x   x   x   x   x   x   1
1981    3       x   x   x   x   x   x   x   1
..
1981    365     x   x   x   x   x   x   x   1

问题：上面的表将有 13 m * 9125 行 ~ 1180 亿行。巨大的索引、缓慢的查询、加载数据的主要问题等等。

所以，我将表划分为年份，就像这样

CREATE TABLE d_<yyyy> (
    CHECK ( yr = <yyyy> )
) INHERITS (d)

嗯...仍然不满意。我最终得到了 1 + 25 个表，但每个年份表现在都有大约 47.5 亿行，并且查询仍然非常慢。

因此，我按年和日将其全部分区，如下所示

CREATE TABLE d_<yyyy>_<yday> (
    CHECK ( yr = <yyyy> AND yday = <yday> )
) INHERITS (d)

每个表现在都有 1300 万行，并且速度相当快（尽管仍然不够快），但现在我有 9K 个表。这有其自身的问题。我无法再查询主表，因为 Pg 尝试锁定所有表并耗尽内存。此外，我无法再进行上面的第二个查询。我可以做类似的事情

SELECT a FROM d_1980_1 WHERE cell_id = 1 
UNION 
SELECT a FROM d_1980_2 WHERE cell_id = 1 
UNION 
SELECT a FROM d_1980_3 WHERE cell_id = 1 
UNION 
SELECT a FROM d_1980_4 WHERE cell_id = 1 
UNION 
...

但是上面的方法并不是最佳的。

任何建议、想法、集思广益将不胜感激。也许 Pg，甚至是 RDBMS，都不是解决这个问题的正确工具，在这种情况下，也欢迎提出替代方案的建议。

Answer 1

如果不诉诸大量索引或数据重复，我认为很难找到对两个查询都最佳的单一模式设计。

通过按日期或传感器对数据进行聚类，可以使这些条件之一的检索运行得更快，但不能同时运行这两种条件。

假设按日期访问是最重要的，您可以按如下方式布局表格：

CREATE TABLE d (
    day      DATE,
    a       SMALLINT[],
    b       SMALLINT[],
    ...
);

观察现在每天只有一行，并且单元格字段已变成数组，其中每个单元格都有自己的索引。如果单元编号不是从零开始，则可以使用从单元 id 到数组索引的映射来拟合表格。

查询1，

检索单个 var 的值
全部或部分单元格
一天。

完成

SELECT a FROM d WHERE day = '1981-01-01'

例如，通过查询 2

，

检索单个单元格的单个变量的所有天数或天数持续时间的值。

情况

SELECT a[1000] FROM d WHERE day BETWEEN '1981' AND '1982'

我相信 PostgreSQL 中的大型数组可以在不实际加载整个数据结构的 下进行访问。我知道 BLOB 是。如果是这种情况，这个解决方案对您来说可能足够快。否则，我建议对数据进行另一个视图，以优化单元格的访问。