数十亿行数据的最佳数据库和表设计

#1 楼

这正是我每天要做的事情，除了使用5分钟数据，而不是使用小时数据。我每天下载大约2亿条记录，因此您在这里谈论的数量不是问题。 5分钟的数据大约为2 TB，我的天气数据可以按位置每小时一次地追溯到50年前。因此，让我根据我的经验回答您的问题：


不要为此使用NoSQL。数据是高度结构化的，并且非常适合
关系数据库。
我个人使用SQL Server 2016，并且在跨该数据量进行计算时没有问题。当我开始工作时，它最初是在
PostgreSQL实例上，它不能像在小型AWS实例上那样处理
数据量。
我强烈建议提取小时数日期的一部分并将其与日期本身分开存储。相信我，从我的错误中学习！
我以列表方式存储大多数数据（DATE，TIME，DATAPOINT_ID，VALUE），但这不是人们想要解释数据的方式。准备好对数据和大量数据透视表进行可怕的查询。不要害怕为结果集创建非标准化的表，这些结果集太大而无法即时计算。

一般提示：我将大多数数据存储在两个数据库之间，第一个是整理时间序列数据并进行归一化。我的第二个数据库非常不规范，并包含预先聚合的数据。尽管我的系统速度如此之快，但是我什至对用户甚至不想等待30秒来加载报告这一事实也没有视而不见-即使我个人认为30秒来处理2 TB数据也非常快。 />
要详细说明为什么我建议将小时与日期分开存储，以下是我这样做的一些原因：


电气数据的显示方式是按小时数表示的-因此，01：00实际上是前一小时的平均电能，而00:00是24小时制。（这很重要，因为您实际上必须搜索两个日期包括24小时值-您要寻找的日期加上第二天的第一个标记。）但是，天气数据实际上是以向前的方式显示的（实际和下一个小时的预报）。根据我对这些数据的经验，消费者希望分析天气对电力价格/需求的影响。如果要使用直接日期比较，则实际上是在比较前一个小时的平均价格与下一个小时的平均温度，即使时间戳相同。将小时与日期分开存储，使您可以将转换应用于时间，而对性能的影响要小于对DATETIME列进行计算所看到的性能。
性能。我要说的是，我生成的报告中至少有90％是图表，通常是针对单个日期或一系列日期将价格相对于小时作图。必须从日期中分出时间，这可能会降低要生成报表的查询的速度，具体取决于您要查看的日期范围。消费者通常希望看到过去30年的单个日期（按年）（事实上，要生成30年的常态需要天气），这很慢。当然，您可以优化查询并添加索引，并且相信我，我有一些我不希望拥有的疯狂索引，但是它使系统运行得很快。
生产率。我讨厌不得不多次编写同一段代码。我曾经将日期和时间存储在同一列中，直到不得不一遍又一遍地写相同的查询来提取时间部分。一段时间后，我厌倦了必须执行此操作并将其提取到自己的专栏中。您编写的代码越少，出现错误的机会就越少。同样，不必编写太多代码，意味着您可以更快地生成报告，没有人愿意整天等待报告。
最终用户。并非所有最终用户都是超级用户（即知道如何编写SQL）。数据已经以可以轻松导入Excel（或其他类似工具）的格式存储，将使您成为办公室的英雄。如果用户无法轻松访问或操作数据，则他们将不会使用您的系统。相信我，几年前我设计了完美的系统，因此没有人使用它。数据库设计不仅要遵循一组预定义的规则/准则，还要使系统可用。

正如我上面所说，这一切都是基于我的个人经验，我告诉你，要到达现在的位置已经经历了几年的艰辛和大量的重新设计。不要做我做的事情，从我的错误中吸取教训，并确保在做出有关数据库的决策时让系统的最终用户（或开发人员，报告作者等）参与进来。

#2 楼

PostgreSQL和BRIN索引

自己进行测试。在装有ssd的5年旧笔记本电脑上，这不是问题。

EXPLAIN ANALYZE
CREATE TABLE electrothingy
AS
  SELECT
    x::int AS id,
    (x::int % 20000)::int AS locid,  -- fake location ids in the range of 1-20000
    now() AS tsin,                   -- static timestmap
    97.5::numeric(5,2) AS temp,      -- static temp
    x::int AS usage                  -- usage the same as id not sure what we want here.
  FROM generate_series(1,1728000000) -- for 1.7 billion rows
    AS gs(x);

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..15.00 rows=1000 width=4) (actual time=173119.796..750391.668 rows=1728000000 loops=1)
 Planning time: 0.099 ms
 Execution time: 1343954.446 ms
(3 rows)

因此创建表花费了22分钟。很大程度上是因为该表只有97GB。接下来，我们创建索引，

CREATE INDEX ON electrothingy USING brin (tsin);
CREATE INDEX ON electrothingy USING brin (id);    
VACUUM ANALYZE electrothingy;

创建索引也花了很长时间。尽管因为它们是BRIN，所以它们只有2-3 MB，并且可以轻松地存储在ram中。读取96 GB并不是即时的，但对于我的笔记本电脑来说，这并不是一个真正的问题。

现在我们对其进行查询。

explain analyze
SELECT max(temp)
FROM electrothingy
WHERE id BETWEEN 1000000 AND 1001000;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=5245.22..5245.23 rows=1 width=7) (actual time=42.317..42.317 rows=1 loops=1)
   ->  Bitmap Heap Scan on electrothingy  (cost=1282.17..5242.73 rows=993 width=7) (actual time=40.619..42.158 rows=1001 loops=1)
         Recheck Cond: ((id >= 1000000) AND (id <= 1001000))
         Rows Removed by Index Recheck: 16407
         Heap Blocks: lossy=128
         ->  Bitmap Index Scan on electrothingy_id_idx  (cost=0.00..1281.93 rows=993 width=0) (actual time=39.769..39.769 rows=1280 loops=1)
               Index Cond: ((id >= 1000000) AND (id <= 1001000))
 Planning time: 0.238 ms
 Execution time: 42.373 ms
(9 rows)

使用时间戳进行更新

在这里，我们生成具有不同时间戳的表以满足索引索引和在时间戳列上进行搜索的请求，因此创建时间要长一些，因为to_timestamp(int)比now()慢得多（已为事务缓存）

EXPLAIN ANALYZE
CREATE TABLE electrothingy
AS
  SELECT
    x::int AS id,
    (x::int % 20000)::int AS locid,
    -- here we use to_timestamp rather than now(), we
    -- this calculates seconds since epoch using the gs(x) as the offset
    to_timestamp(x::int) AS tsin,
    97.5::numeric(5,2) AS temp,
    x::int AS usage
  FROM generate_series(1,1728000000)
    AS gs(x);

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..17.50 rows=1000 width=4) (actual time=176163.107..5891430.759 rows=1728000000 loops=1)
 Planning time: 0.607 ms
 Execution time: 7147449.908 ms
(3 rows)

现在我们可以对时间戳值运行查询，

explain analyze
SELECT count(*), min(temp), max(temp)
FROM electrothingy WHERE tsin BETWEEN '1974-01-01' AND '1974-01-02';
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=296073.83..296073.84 rows=1 width=7) (actual time=83.243..83.243 rows=1 loops=1)
   ->  Bitmap Heap Scan on electrothingy  (cost=2460.86..295490.76 rows=77743 width=7) (actual time=41.466..59.442 rows=86401 loops=1)
         Recheck Cond: ((tsin >= '1974-01-01 00:00:00-06'::timestamp with time zone) AND (tsin <= '1974-01-02 00:00:00-06'::timestamp with time zone))
         Rows Removed by Index Recheck: 18047
         Heap Blocks: lossy=768
         ->  Bitmap Index Scan on electrothingy_tsin_idx  (cost=0.00..2441.43 rows=77743 width=0) (actual time=40.217..40.217 rows=7680 loops=1)
               Index Cond: ((tsin >= '1974-01-01 00:00:00-06'::timestamp with time zone) AND (tsin <= '1974-01-02 00:00:00-06'::timestamp with time zone))
 Planning time: 0.140 ms
 Execution time: 83.321 ms
(9 rows)

结果：

 count |  min  |  max  
-------+-------+-------
 86401 | 97.50 | 97.50
(1 row)

因此，在83.321毫秒内，我们可以在具有17亿行的表中聚合86401条记录。这应该是合理的。

小时结局

计算小时结局也很容易，将时间戳记截短然后再增加一个小时。

SELECT date_trunc('hour', tsin) + '1 hour' AS tsin,
  count(*),
  min(temp),
  max(temp)
FROM electrothingy
WHERE tsin >= '1974-01-01'
  AND tsin < '1974-01-02'
GROUP BY date_trunc('hour', tsin)
ORDER BY 1;
          tsin          | count |  min  |  max  
------------------------+-------+-------+-------
 1974-01-01 01:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 02:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 03:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 04:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 05:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 06:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 07:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 08:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 09:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 10:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 11:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 12:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 13:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 14:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 15:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 16:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 17:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 18:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 19:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 20:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 21:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 22:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-01 23:00:00-06 |  3600 | 97.50 | 97.50
 1974-01-02 00:00:00-06 |  3600 | 97.50 | 97.50
(24 rows)

Time: 116.695 ms

需要注意的是，尽管可以，但它并未在聚合中使用索引。如果这是您通常的查询，则可能要在date_trunc('hour', tsin)上放置BRIN，这是一个小问题，因为date_trunc并非一成不变的，因此您必须先对其进行包装才能做到这一点。

分区

关于PostgreSQL的另一个重要信息是PG 10带了分区DDL。因此，例如，您可以轻松地每年创建分区。将适度的数据库分解为较小的较小数据库。这样，您应该能够使用和维护btree索引，而不是BRIN索引，因为BRIN索引会更快。

CREATE TABLE electrothingy_y2016 PARTITION OF electrothingy
    FOR VALUES FROM ('2016-01-01') TO ('2017-01-01');

等等

#3 楼

令我惊讶的是，这里没有人提到基准测试-直到@EvanCarroll做出了他的杰出贡献！

如果我是你，我会花一些时间（是的，我知道这是很宝贵的商品！）来设置系统，运行您想像的内容（在此处获得最终用户的输入！），例如10个最常见的查询。

我自己的想法：

NoSQL解决方案在特定的用例中可以很好地工作，但对于即席查询通常不灵活。有关MySQL前首席架构师Brian Aker对NoSQL的有趣看法，请参见此处！

我同意@ Mr.Brownstone的观点，即您的数据非常适合关系解决方案（这种观点已经

如果我要承担任何支出，那将关乎我的磁盘技术！我将花我所有的钱花在NAS或SAN上，或者花些SSD磁盘来保存我很少写入的聚合数据！

首先，我将看看我现在所拥有的。运行一些测试，并将结果显示给决策者。您已经以EC的工作形式获得了代理！但是，在您自己的硬件上进行一两次快速测试会更有说服力！

然后考虑花钱！如果您要花钱，请先关注硬件而不是软件。 AFAIK，您可以在试用期内租用磁盘技术，或者更好的是，在云上启动几个概念验证。

我自己的第一个类似项目的第一联系人这将是PostgreSQL。这并不是说我会排除专有解决方案，但是物理定律和磁盘定律对于每个人都是相同的！ “ Yae cannae甜菜的物理定律” ：-)

#4 楼

如果还没有，请看一下时间序列DBMS，因为它是为存储和查询主要针对日期/时间类型的数据而优化的。通常，时间序列数据库用于记录分钟/秒/亚秒范围内的数据，因此我不确定它是否仍然适合小时增量。也就是说，这种类型的DBMS似乎值得研究。当前，InfluxDB似乎是最成熟且使用最广泛的时间序列数据库。

#5 楼

显然，这不是NoSQL问题，但我建议，尽管RDBMS解决方案可以工作，但我认为OLAP方法会更好，并且鉴于涉及的数据范围非常有限，我强烈建议调查基于列的数据库的使用而不是基于行。以这种方式考虑，您可能拥有17亿条数据，但是您仍然只需要5位就可以索引小时或月中的每个可能值。

我在类似的问题域中也有经验，其中Sybase IQ（现为SAP IQ）用于每小时存储多达3亿个计数器的电信设备性能管理数据，但我怀疑您是否有预算对于那种解决方案。在开放源代码领域，MariaDB ColumnStore是一个非常有前途的候选对象，但是我建议您还应研究MonetDB。由于查询性能是您的主要驱动力，因此请考虑如何对查询进行措辞。这是OLAP和RDBMS表现出最大差异的地方：-使用OLAP，您可以标准化查询性能，而不是减少重复，减少存储甚至增强一致性。因此，除了原始时间戳（您确实记得要捕获其时区？）之外，还有一个单独的字段用于UTC时间戳，其他字段用于日期和时间，还有更多字段用于年，月，日，小时，分钟。和UTC偏移量。如果您还有其他有关位置的信息，可以随时将其保存在一个可以按需查找的单独位置表中，并可以将该表的关键字保留在主记录中，但可以将完整的位置名保留在主表中，例如好吧，毕竟，所有可能的位置仍然只需要10位索引，而不必为了获取要报告的数据而必须遵循的每个引用都节省了查询时间。

作为最后的建议，对流行的聚合数据使用单独的表，并使用批处理作业来填充它们，这样您就不必为每个使用聚合值并进行将当前值与历史值或历史值进行比较的查询的报告重复该练习。从历史到历史要容易得多，而且要快得多。