PostgreSQL 海量时序数据(任意滑动窗口实时统计分析) - 传感器、人群、物体等对象跟踪

postgresql , 物联网 , feed , 网游 , 热力 , 商场驻留 , 人群分析 , 实时热力图 , 实时在线图 , 实时分段最大最小区间图 , 任意滑动窗口实时最高、最低在线数

在现实生活中，经常有聚集分析的需求。

例如：

某个商场，每个时间点，商场的每个商铺位置的人群驻留数量。（有技术手段可以感知人的驻留位置，当走进某个区域时，将写入一条记录，表示你进入了这个区域，离开时记录一条离开的记录，如果长时间不动，则定时写心跳记录）。

某个网游，每个时间点，在线人数。（上线时写一条上线记录，下线时写一条下线记录。）

某个共享单车公司，每个时间点，在线和不在线的车辆数量。（借车时写一条上线记录，还车时写一条下线记录。同时每隔一段时间询问车辆状态。）

某个物联网企业，每个分钟单位内，最小、最大在线传感器的数量。（传感器上线时写一条上线记录，下线时写一条下线记录，同时每隔一段时间询问传感器状态。）

这种属于非常典型的feed应用，要求输出每个时间点这个世界（系统）的在线数。（如果按时间段输出，则输出每个时间段内的最大，最小在线数，实际上就是取range的边界）。

场景：

某个物联网企业，有一些传感器，传感器上线时写一条上线记录，下线时写一条下线记录，同时每隔小时询问传感器状态，也就是说1小时内没有记录的传感器视为不在线。

企业需要统计每个分钟单位内，最小、最大在线传感器的数量。

1、表结构

2、索引

写入1.101亿测试数据（我们假设这是1小时的数据写入量，全天写入26.424亿记录），1001个传感器id。

3、数据ttl，确保表比较瘦，只包含心跳时间范围内的数据。

由于每小时接收心跳，所以1小时内，必有数据，没有数据的传感器不计状态。因此我们保留1小时内的状态即可。

一种保留方法是pipelinedb，用法如下。

<a href="https://github.com/digoal/blog/blob/master/201706/20170612_03.md">《数据保留时间窗口的使用》</a>

另一种保留方法，使用两张表，轮询使用即可。

类似的用法如下

<a href="https://github.com/digoal/blog/blob/master/201703/20170321_02.md">《postgresql 数据rotate用法介绍 - 按时间覆盖历史数据》</a>

4、使用递归查询，高效查询传感器的最终状态

执行计划如下

样例

效率很高，1.101亿数据，11毫秒获取最终在线状态。

在线的设备为state=t的。

5、统计任意时间点的传感器在线数量，如果每个设备上线的时间精确到秒(crt_time精确到秒)，那么不管有多少条记录，一天最多需要统计86400个时间点的传感器在线数量。

例如统计 <code>2017-07-05 10:29:09</code> 时间点的传感器在线数量，加一个时间限制即可。

新增这个时间限制，会带来一定的性能影响，特别是如果这个时间是过去很久以前的时间，过滤会越多，性能下降越严重。

因此，建议实时，每秒发起一次查询请求，就不要加这个时间限制了。

6、一次性生成过去每一秒的在线数。

使用窗口查询的帧查询技术。（帧表示按时间排序，截止到当前记录的区间。）

7、统计每分钟内，最高在线数、最低在线数。

每秒查询一次，将数据写入结果表。

由于每次查询仅需12毫秒，每秒调用一次没有问题。

统计某一分钟内，最高在线数、最低在线数。

当传感器id达到10万级别时，查询性能会下降到250毫秒。

如果传感器id特别多，例如有百万以上，那么会下降到2.5秒。就不适合每秒查询一次了。

因此传感器数量特别多时，如何优化？

有一个比较好的方法是数据按传感器id进行哈希分布，例如每张分区表负责1万个传感器id。在查询在线数时，并发的查询所有的分区表，从而降低rt。

使用本文提到的方法(递归查询)，我们可以实现非常细粒度的，大量被跟踪物的状态实时统计。

用于绘制被跟踪物的实时状态图，例如：

1、实时热力图

2、实时传感器(或用户)在线、离线数，任意滑动窗口的最大最小在线、离线值。