轨迹系统 需求分析与DB设计

postgresql , postgis , 快递 , 包裹侠 , 地理位置 , 距离排序 , knn

物流行业对地理位置信息数据的处理有非常强烈的需求，例如

1. 实时跟踪快递员、货车的位置信息。对数据库的写入性能要求较高。

2. 对于当日件，需要按发货位置，实时召回附近的快递员。

3. 实时的位置信息非常庞大，为了数据分析的需求，需要保留数据，所以需要廉价的存储。例如对象存储。同时还需要和数据库或分析型的数据库产品实现联动。

阿里云的 postgresql + hybriddb for postgresql + oss 对象存储可以很好的满足这个需求，详细的方案如下。

以物流配送场景为例，介绍阿里云的解决方案。

快递员：百万级。

快递员的轨迹定位数据间隔：5秒。

一个快递员每天工作时间 7 ~ 19点 (12个小时)。

一个快递员一天产生8640条记录。

所有的快递员，全网一天产生86.4亿条记录。

1. 绘制快递员轨迹（实时）

2. 召回快递员（实时）

当天件的需求。

按快递员id哈希，128张表。

(如果不分区，单表存储86.4亿记录，问题也不大，只是导出到oss对象存储的过程可能比较长，如果oss出现故障，再次导出又要很久)

另一方面的好处是便于扩容。

每天1张子表，轮询使用，覆盖到周(便于维护, 导出到oss后直接truncate)。一共7张子表。

oss对象存储。

阿里云postgresql有oss_ext插件，可以将数据写入oss对象存储。同时也支持从oss对象存储读取数据（外部表的方式），对用户透明。

详见

<a href="https://help.aliyun.com/document_detail/44461.html">https://help.aliyun.com/document_detail/44461.html</a>

postgresql 10.0 内置了分区表，所以以上分区，可以直接读写主表。

<a href="https://github.com/digoal/blog/blob/master/201612/20161215_01.md">《postgresql 10.0 preview 功能增强 - 内置分区表》</a>

9.5以及以上版本，建议使用pg_pathman插件，一样可以达到分区表的目的。

<a href="https://github.com/digoal/blog/blob/master/201610/20161024_01.md">《postgresql 9.5+ 高效分区表实现 - pg_pathman》</a>

分区表例子

实时位置表，记录快递员的实时位置（最后一条记录的位置）。

由于快递员的位置数据会不停的汇报，因此实时位置表的数据不需要持久化，可以使用unlogged table。

注意

（假如快递员的位置不能实时上报，那么请使用非unlogged table。）

位置字段，创建gist空间索引。

为了实时更新快递员的位置，可以设置一个触发器，在快递员上传实时位置时，自动更新最后的位置。

（如果实时位置表cainiao_trace_realtime使用了非unlogged table，那么考虑到(写入+update)的rt会升高一些，建议不要使用触发器来更新位置。建议程序将 插入和update 作为异步调用进行处理。例如在收到快递员上报的批量位置轨迹后，拆分为batch insert以及update 一次。）

（batch insert: insert into cainiao values (),(),(),....; update 最终状态: update cainiao_trace_realtime set xx=xx where uid=xx;）(好处：1. 插入和更新异步, 2. 插入批量执行, 3. 整体rt更低)

对基表添加触发器

触发器示例如下

说明

1. 本文假设应用程序会根据 快递员uid ，时间字段 拼接出基表的表名。

否则就需要使用postgresql的分区表功能（分区表的性能比直接操作基表差一些）。

2. 本文使用point代替经纬度，因为point比较好造数据，方便测试。

实际上point和经纬度都是地理位置类型，可以实现的场景类似。性能指标也可以用于参考。

模拟快递员实时的上传轨迹，实时的更新快递员的最新位置。

pgbench的测试脚本如下

开始测试，持续300秒。

每秒写入17.4万，单次请求延迟0.18毫秒。

比如当日件达到一定数量、或者到达一定时间点时，需要召回附近的快递员取件。

或者当用户寄当日件时，需要召回附近的快递员取件。

压测用例

随机选择一个点，召回半径为20000范围内，距离最近的100名快递员。

sql样例

每秒处理召回请求 6万，单次请求延迟0.53毫秒。

备注，如果只召回一名快递员，可以达到28万 tps.

同时压测快递员轨迹插入、随机召回快递员。

插入tps: 12.5万，响应时间0.25毫秒

查询tps: 2.17万，响应时间1.47毫秒

如果要尽量的降低rt，快递员实时位置表可以与轨迹明细表剥离，由应用程序来更新快递员的实时位置。

至于这个实时位置表，你要把它放在明细表的数据库，还是另外一个数据库？

我的建议是放在另外一个数据库，因为这种表的应用非常的独立（更新，查询），都是小事务。

而明细轨迹，可能涉及到比较大的查询，以插入，范围分析，数据合并，日轨迹查询为主。

将明细和实时轨迹独立开来，也是有原因的。

剥离后，明细位置你可以继续使用unlogged table，也可以使用普通表。

下面测试一下剥离后的性能。

pgbench脚本，更新快递员位置，查询某个随机点的最近100个快递员。

前面对实时轨迹数据使用一周的分表，目的就是有时间可以将其写入到oss，方便维护。

每天可以将6天前的数据，写入oss对象存储。

单个快递员，一天产生的轨迹是8640条。

postgresql支持json、hstore(kv)、数组、复合数组 类型。每天将单个快递员的轨迹聚合为一条记录，可以大幅度提升按快递员查询轨迹的速度。

同样的场景可以参考：

<a href="https://github.com/digoal/blog/blob/master/201212/20121217_01.md">《performance tuning about multi-rows query aggregated to single-row query》</a>

聚合例子

查询某个快递员1天的轨迹，性能提升对比

聚合前(b-tree索引)，耗时8毫秒

聚合后，耗时0.033毫秒

1. 本文以物流轨迹系统为背景，对两个常见需求进行数据库的设计以及模型的压测：实时跟踪快递员轨迹，实时召回附近的快递员。

2. postgresql 结合 oss，实现了数据的冷热分离，历史轨迹写入oss保存，再通过oss可以共享给hybriddb for postgresql，进行实时的数据挖掘分析。

3. 单机满足了每秒18万的轨迹写入，按最近距离召回快递员(100名快递员)可以达到6万/s的速度，按最近距离召回快递员(1名快递员)可以达到28万/s的速度。

4. 使用postgresql的继承特性，可以更好的管理分区表，例如要查询礼拜一的所有分区，查询这些分区的主表。 如果要查某个模值的所有时间段数据，查询对应的主表即可。

<a href="https://github.com/digoal/blog/blob/master/201308/20130806_01.md">《postgis long lat geometry distance search tuning using gist knn function》</a>

<a href="https://github.com/digoal/blog/blob/master/201601/20160119_01.md">《postgresql 百亿地理位置数据 近邻查询性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201701/20170101_02.md">《apsaradb的左右互搏(pgsql+hybriddb+oss) - 解决oltp+olap混合需求》</a>