标签
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.
所以行程表如果很大, 并且需要频繁垃圾回收时, 为了避免垃圾回收速度赶不上垃圾产生速度, 同样也可以使用分区.
与点表分区类似, 最好使用一样的分区键.
参考
《PostgreSQL pipelinedb 流计算插件 - IoT 应用 - 实时轨迹聚合》
《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云 RDS PostgreSQL 最佳实践》
来源: https://yq.aliyun.com/articles/700365