标签
PostgreSQL , 实时轨迹 , IoT , 车联网 , GIS
背景
车联网，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。
所以行程表如果很大，并且需要频繁垃圾回收时，为了避免垃圾回收速度赶不上垃圾产生速度，同样也可以使用分区。
与点表分区类似，最好使用一样的分区键。
