PostgreSQL 实时位置跟踪+轨迹分析系统实践 - 单机顶千亿轨迹/天

背景

随着移动设备的普及,越来越多的业务具备了时空属性,例如快递,试试跟踪包裹、快递员位置。例如实体,具备了空间属性。

例如餐饮配送,送货员位置属性。例如车辆,实时位置。等等。

其中两大需求包括:

1、对象位置实时跟踪,例如实时查询某个位点附近、或某个多边形区域内的送货员。

2、对象位置轨迹记录和分析。结合地图,分析轨迹,结合路由算法,预测、生成最佳路径等。

DEMO

以快递配送为例,GPS设备实时上报快递员轨迹,写入位置跟踪系统,同时将轨迹记录永久保存到轨迹分析系统。

由于快递员可能在配送过程中停留时间较长(比如在某个小区配送时),上报的多条位置可能变化并不大,同时考虑到数据库更新消耗,以及位置的时效性,可以避免一些点的更新(打个比方,上一次位置和当前位置变化量在50米时,不更新)。

动态更新可以减少数据库的更新量,提高整体吞吐能力。

设计

实时位置更新

1、建表

create table t_pos (  
  uid int primary key,   -- 传感器、快递员、车辆、。。。对象ID  
  pos point,             -- 位置  
  mod_time timestamp     -- 最后修改时间  
);  
  
create index idx_t_pos_1 on t_pos using gist (pos);  

真实环境中,我们可以使用PostGIS空间数据库插件,使用geometry数据类型来存储经纬度点。

create extension postgis;  
  
create table t_pos (  
  uid int primary key,   -- 传感器、快递员、车辆、。。。对象ID  
  pos geometry,          -- 位置  
  mod_time timestamp     -- 最后修改时间  
);  
  
create index idx_t_pos_1 on t_pos using gist (pos);  

2、上报位置,自动根据移动范围,更新位置。

例如,移动距离50米以内,不更新。

 
insert into t_pos values (?, st_setsrid(st_makepoint($lat, $lon), ), now())  
on conflict (uid)  
do update set pos=excluded.pos, mod_time=excluded.mod_time  
where st_distancespheroid(t_pos.pos, excluded.pos, 'SPHEROID["WGS84",6378137,298.257223563]') > ?;  -- 超过多少米不更新  

历史轨迹保存

通常终端会批量上报数据,例如每隔10秒上报10秒内采集的点,一次上报的数据可能包含多个点,在PostgreSQL中可以以数组存储。

create table t_pos_hist (  
  uid int,                  -- 传感器、快递员、车辆、。。。对象ID  
  pos point[],              -- 批量上报的位置  
  crt_time timestamp[]      -- 批量上报的时间点  
);   
  
create index idx_t_pos_hist_uid on t_pos_hist (uid);                 -- 对象ID  
create index idx_t_pos_hist_crt_time on t_pos_hist ((crt_time[]));    -- 对每批数据的起始时间创建索引  

有必要的话,可以多存一个时间字段,用于分区。

历史轨迹分析

动态位置变更压测

写入并合并,同时判断当距离大于50时,才更新,否则不更新。

(测试)如果使用point类型,则使用如下SQL

insert into t_pos values (, point(,), now())  
on conflict (uid)  
do update set pos=excluded.pos, mod_time=excluded.mod_time  
where t_pos.pos <-> excluded.pos > ;  

(实际生产)如果使用PostGIS的geometry类型,则使用如下SQL

insert into t_pos values (, st_setsrid(st_makepoint(, ), ), now())  
on conflict (uid)  
do update set pos=excluded.pos, mod_time=excluded.mod_time  
where st_distancespheroid(t_pos.pos, excluded.pos, 'SPHEROID["WGS84",6378137,298.257223563]') > ;  

压测

首先生成1亿随机空间对象数据。

postgres=# insert into t_pos select generate_series(,), point(random()*, random()*), now();  
INSERT    
Time: 250039.193 ms (:10.039)  

压测脚本如下,1亿空间对象,测试动态更新性能(距离50以内,不更新)。

vi test.sql    
  
\set uid random(,)    
insert into t_pos    
select uid, point(pos[]+random()*-, pos[]+random()*-), now() from t_pos where uid=:uid   
on conflict (uid)   
do update set pos=excluded.pos, mod_time=excluded.mod_time   
where t_pos.pos <-> excluded.pos > ;   

压测结果,动态更新 21.6万点/s,187亿点/天。

pgbench -M prepared -n -r -P  -f ./test.sql -c  -j  -T    
  
number of transactions actually processed: 
latency average = 0.295 ms
latency stddev = 0.163 ms
tps = 216767.645838 (including connections establishing)
tps = 216786.403543 (excluding connections establishing)

轨迹写入压测

每个UID,每批写入50条:写入速度约 467.5万点/s,4039亿点/天。

压测时,写多表,压测使用动态SQL。

do language plpgsql $$  
declare  
begin  
  for i in .. loop  
    execute 'create table t_pos_hist'||i||' (like t_pos_hist including all)';  
  end loop;  
end;  
$$;  
create or replace function import_test(int) returns void as $$  
declare  
begin  
  execute format('insert into t_pos_hist%s values (%s, %L, %L)', mod($, ), $,   
  array[point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1)] ,  
  array['2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10']);  
end;  
$$ language plpgsql strict;  
vi test1.sql  
  
\set uid random(,)  
select import_test(:uid);  
pgbench -M prepared -n -r -P  -f ./test1.sql -c  -j  -T    
  
  
number of transactions actually processed:   
latency average = 0.599 ms  
latency stddev = 5.452 ms  
tps = 93504.532256 (including connections establishing)  
tps = 93512.274135 (excluding connections establishing)  

黑科技

1、块级索引(BRIN),在时序属性字段上,建立块级索引,既能达到高效检索目的,又能节约索引空间,还能加速写入。

《PostgreSQL BRIN索引的pages_per_range选项优化与内核代码优化思考》

《万亿级电商广告 - brin黑科技带你(最低成本)玩转毫秒级圈人(视觉挖掘姊妹篇) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践》

《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》

《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》

《PostgreSQL 并行写入堆表,如何保证时序线性存储 - BRIN索引优化》

《PostgreSQL 9种索引的原理和应用场景》

2、阿里云HDB PG特性:sort key , metascan

与BRIN类似,适合线性数据,自动建立块级元数据(取值范围、平均值、CNT、SUM等)进行过滤。

3、空间索引

GiST, SP-GiST空间索引,适合空间数据、以及其他异构数据。

4、动态合并写,根据位置变化量,自动判断是否需要合并更新。

insert on conflict语法,在do update里面,可以进行条件过滤,当位置变化超过N米时,才进行更新。

5、数组、JSON、KV等多值类型。

特别适合多值属性,例如批量上传的轨迹,通常GPS终端上报位置并不是实时的,可能存在一定的 延迟(例如批量上报)。使用数组、JSON都可以存储。

如果使用数组存储,将来分析轨迹时,依旧可以unnest解开,绘制轨迹。

性能

1、动态位置变更:1亿被跟踪对象,TPS:21.6万,动态更新21.6万点/s,187亿点/天。

2、轨迹写入:tps约10万,写入467.5万点/s,4039亿点/天。

参考

《PostGIS 空间数据学习建议》

《PostgreSQL + PostGIS + SFCGAL 优雅的处理3D数据》

《PostGIS 距离计算建议 - 投影 与 球 坐标系, geometry 与 geography 类型》

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户)》

《PostGIS 空间索引(GiST、BRIN、R-Tree)选择、优化 - 阿里云RDS PostgreSQL最佳实践》

《PostGIS 坐标转换(SRID)的边界问题引发的专业知识 - ST_Transform》

《无人驾驶背后的技术 - PostGIS点云(pointcloud)应用 - 2》

《无人驾驶背后的技术 - PostGIS点云(pointcloud)应用 - 1》

《geohash vs PostGIS》

《视觉挖掘与PostGIS空间数据库的完美邂逅 - 广告营销\圈人》

《PostGIS 点面叠加视觉判断输出》

《PostGIS 多点几何类型 空字符构造异常CASE》

《开放地图OpenStreetMap与PostGIS的三生三世十里桃花》

《PostGIS 地理信息、栅格数据 多核并行处理(st_memunion, st_union)》

《蜂巢的艺术与技术价值 - PostgreSQL PostGIS's hex-grid》

《如何建立GIS测试环境 - 将openstreetmap的样本数据导入PostgreSQL PostGIS库》

《GIS附近查找性能优化 - PostGIS long lat geometry distance search tuning using gist knn function》

   作者丨digoal
0 条评论
请不要发布违法违规有害信息,如发现请及时举报或反馈
还没有人评论呢,速度抢占沙发!
相关文章
  • 背景 在物联网、监控、传感器、金融等应用领域,数据在时间维度上流式的产生,而且数据量非常庞大。 例如我们经常看到的性能监控视图,就是很多点在时间维度上描绘的曲线。 又比如金融行业的走势数据等等。 我...

  • 背景 监控、优化、诊断 cpu, io消耗监控https://github.com/powa-team/pg_stat_kcache where条件过滤性统计https://github.com/po...

  • 一、查询表、模式及字段信息 1、查询指定模式下的所有表 select tablename,* from pg_tables where schemaname = 'ods'; 2、查询指定模式下的表名...

  • 背景 电子围栏,这个在GIS应用中非常常见的词。在很多业务场景中都可以使用: 电子围栏的常见手段是圈出一块,或者一些多边形。当被监控对象在多边形内或者多边形外时(根据业务模型),作为一个判断条件,触发...

  • Babelfish for PostgreSQL开源已快一月,不过全网还没有实践者总结。今天我们就测试看看,Babelfish到底是如何部署与使用的! Babelfish for PostgreSQ...

  • 序言Greenplum(以下简称 GP)是一种基于开源PostgreSQL基础上采用MPP架构的关系型分布式数据库,具有强大的大规模数据分析处理能力。GP有两种存储格式:Heap表和AO表。其中,AO...

  • 前言PostgreSQL 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time下文中 create_at 等价于 cre...

  • PostgreSQL技术内幕系列直播自推出以来,获得众多用户欢迎和认可。12月8日,我们将开启PostgreSQL技术内幕第四场直播,邀请光城跟大家讨论交流执行引擎的话题。本此直播中,将会引入Port...

  • 聚集算子 (Aggregation) 是数据库最常用的功能之一。在上次的直播中,我们主要介绍了Parallel HashAggregation 的基本实现细节。本文根据直播文字整理,作者现任HashD...

  • 背景 https://github.com/MobilityDB/MobilityDB https://www.citusdata.com/download/ https://www.postgres...

  • 简述 本文主要介绍如何使用 CloudCanal 构建一条 MySQL 到 Greenplum / PostgreSQL 的数据同步链路。 支持版本 源端 MySQL 支持的版本为:5.6、5.7、8...

  • 近年来大家可能都有这样一种感受:与编程语言市场不同,数据库市场的竞争激烈异常——一线的在停滞甚至下坠,二线的正在反超。有种种迹象表明:MySQL 这个流行榜上的榜一大哥,正在逐渐淡出专业开发者的视野。...

  • 编译目的本文章通过代码方式讲解Postgres数据库备份与还原。常用备份方式流复制备份归档备份SQL转储备份热备份流复制备份注意事项需要准备2台或2台以上服务器。应用程序写入数据是从主数据库写入。主数...

  • 编写目的根据(postgres12数据库备份方式一)上一章节和配置,可以实现主从流复制,在这章讲解流复制中常用的一些命令集。常用的命令#启用同步 ALTER SYSTEM SET synchronou...

  • 编写目的根据(postgres12数据库备份方式二)上一章节和配置,可以查看主从服务流复制信息,在这章讲解如何实现主备切换。主备切换#一、先停原主数据节点数据库 systemctl stop post...

  • 编写目的根据(postgres12数据库备份方式三)上一章节和配置,讲解如何实现主备切换,在这章讲解如何实现主服务归档还原。归档还原#1.在主服务器停止到postgres服务 systemctl st...

  • 编写目的根据(postgres12数据库备份方式四)上一章节配置,讲解如何实现归档还原,在这章讲解如如何备份SQL脚本。备份SQL脚本与还原#备份sql脚本,test是数据库名称,根据你的需求改变。 ...