PostgreSQL IoT，车联网 - 实时轨迹、行程实践 1

 

---

背景

车联网，IoT场景中，终端为传感器，采集各个指标的数据（同时包括时间、GIS位置信息，速度，油耗，温度，EDU采集指标），在运动过程中，通过GPS准实时上报到服务端。

服务端则通常根据设备（比如车辆）、时间范围，查询指定设备在某个时间区间的行程。

例如：

1、设备轨迹点表

create table tbl (  
  id int primary key,  -- 主键  
  sid int,  -- 传感器（车辆）ID  
  xxx int,  -- 行程ID  
  geo geometry,  -- 位置  
  att jsonb,  -- 属性  
  crt_time timestamp  -- 时间  
);  

2、查询

select * from tbl where sid=? and crt_time between x and y;  

这种方法的问题（一个点一条记录）：

1、查询性能问题，有IO放大（因为传感器都活跃），一个行程的每个点都落在不同的BLOCK里面，查询有IO放大。

2、空间占用，一个点一条记录，压缩比低。

3、行程运算，行程的所有点没有合并，运算效率差。

行程合并问题

为了解决以上问题，可以新建行程表，并将点的数据合并到行程。

create table tbl_agg (  
  xxx int,  -- 行程ID  
  geo 轨迹类型, -- 轨迹  
  agg jsonb[]  -- 其他属性聚合  
)  

例如，每隔N秒，将点表的数据，按行程ID为主键更新到行程表。

insert into tbl_agg on conflict (geo) do ?   
select xxx,geo_agg(geo),jsonb_agg(jsonb) from tbl where crt_time between ? and ?;  

这种做法有性能问题：

1、锁

如果并发聚合的话，很显然可能多个会话中会出现同样的xxx行程ID字段，所以会有锁冲突。

2、IO放大

如果要解决锁的问题，我们可以用HASH，每个会话算其中的一个HASH value，但是这样就会导致扫描时IO放大，例如8个并行，则有效数据仅八分之一。相当于IO多扫描了7次。

3、CPU只能用一核

为了解决第一个问题，也可以使用串行方法，串行就只能用一核。

4、GAP，由于时间差的问题（例如INSERT到达的数据有错乱，那么可能导致中间出现GAP，聚合的行程缺少一些点）

5、实时性，异步合并到行程表，显然，查询行程表时，可能还有一些POINT没有合并进来，那么就会导致即刻查询行程缺少最近没有合并的点（延迟）。

行程合并优化

为了解决前面提到的5个问题。行程合并的流程可以优化。

1、点表分区，对点表进行分区。按行程ID HASH。

create table tbl (like old_tbl including defaults) partition by list (abs(mod(hashtext(行程字段),16)));   
  
do language plpgsql $$  
declare  
begin  
  for i in 0..15 loop  
    execute 'create table tbl_'||i||' partition of tbl for values in ('||i||')';  
    execute 'create index idx_tbl_'||i||'_1 on tbl_'||i||' (id)';  
    execute 'create index idx_tbl_'||i||'_2 on tbl_'||i||' (crt_time)';  
  end loop;  
end;  
$$;  

2、由于点表分区了，而且行程ID HASH分区，每个分区一个行程合并处理进程（没有锁的问题），总共就可以开多个并行来提高合并行程的处理并行度。提高整体合并行程的性能。

3、行程表，分区。解决行程表垃圾回收的问题。

行程是UPDATE（APPEND POINT到行程类型中）的形式，所以UPDATE会很多，会经常需要对行程表进行VACUUM。

如果行程表不分区，行程表就会很大，目前PG的VACUUM，对于单个表来说，同一时间只能一个核来进行垃圾回收，还没有支持单表并行VACUUM。

所以行程表如果很大，并且需要频繁垃圾回收时，为了避免垃圾回收速度赶不上垃圾产生速度，同样也可以使用分区。

与点表分区类似，最好使用一样的分区键。  

参考

《PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合》

《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践》