PostgreSQL - 利用citus处理地理大数据

GitHub地址： https://github.com/QingyaFan/effective-backend

实际问题

实际业务中，会有非常大的地理数据集的存储需求，比如全世界的点状POI，数据量级已达亿级别，存储在单一的PostgreSQL数据表中，查询性能会差很多，如果对这些数据进行空间分析，复杂的计算，动不动就耗时十几二十分钟，不能满足实时的要求，因此，我们开始寻找一个可以解决这个问题的方案。

要找方案，我们首先要弄清楚原来的瓶颈在哪里。为什么存储在单表中性能差？

记录多，扫描时间过长；
写入和更新时会产生表级的锁，其他的写入和更新操作只能等待；
单磁盘I/O性能有限。

单表存储可能存在的问题？

单表数据是单文件存在的，数据量过大可能会达到磁盘存储单文件的大小瓶颈。

Citus是什么

首先，Citus是一个PostgreSQL的扩展，类似于PostGIS、pgRouting，它主要的作用是将一张大表分布到多台机器的数据库中，并且可已经将查询分布到不同节点并行执行，最后汇总结果。Citus是单coordinator，多worker结构，coordinator统筹协调，worker是真正干活的壮丁。

Citus数据分布及查询执行示意图

从Citus官网盗了一张图来，这张图说的很清楚，当存储数据时，Coordinator节点会把当前表分为多个分片（shard），并将分片安装某种算法分布到各个worker节点，并记录一份元数据，如果开启了“分片复制”（Shard Replication），两个不同的节点中可能会存在同一分片的不同副本，为了防止单点故障造成的数据丢失。当一个针对分布式表的查询进来，Coordinator会根据数据的分布，将查询分别分配到各个worker节点，最后汇总结果。安排的明明白白。

什么时候用Citus！

数据分布式存储的应用（Multi-tenant Application），实测单节点vs四台citus集群入库20G数据有7倍的差距；
实时分析与统计（Real-Time Dashboards），只适合节点之间IO非常小的场景；
时间序列数据的查询分析（Timeseries Data），不甚了解。

Citus不是万金油，啥时候不要用？

各个worker节点间有大量的数据流动，I/O过大，会将并行执行节省的优势抵消掉，反倒不如单节点高配机器

Citus的特点

从单节点数据库到Citus集群无需更改应用逻辑，无缝集成。

当你从单节点切换到Citus集群时，你会发现这是一件多么容易的事情，不需要更改任何应用逻辑，之前你面对的是一个PostgreSQL，之后你还是面对的一个PostgreSQL，不同的是，之后你有了强大的存储和算力，而且这都是由citus的coordinate节点管理的，不需要我们维护。

并行执行查询提高效率，且性能随着节点数量增加线性增加。但不适用于需要各节点汇总数据，涉及大量IO的情景，这样反而会抵消并行执行省下的时间。

Citus通过使用钩子和扩展API来实现PostgreSQL的分布式存储和并行计算，因此很多PostgreSQL扩展也能使用citus提供的能力，包括PostGIS，这就为我们使用Citus集群来管理和分析地理大数据提供了可能。但是需要注意，要配合Citus使用其它扩展，Citus必须是第一个启用的扩展，其次，必须在coordinator和worker节点都安装相应扩展。所以我们需要在所有机器节点上安装Citus和PostGIS。

尝试

本示例使用了6台机器做集群，1台coordinator，5台worker

首先在各个节点上安装PostgreSQL，安装Citus扩展，安装PostGIS扩展；
修改PostgreSQL的配置，启动时预加载citus，并配置各个节点的PostgreSQL互相联通，可互相访问；
启动数据库，启用扩展。

systemctl start postgresql-10.service
systemctl enable postgresql-10.service
psql -U postgers -c "create extension citus;"
psql -U postgers -c "create extension postgis;"

添加worker

在coordinator节点添加所有worker节点，

psql -U postgres -c "select * from master_add_node('ip-or-name', port);"

最后列出所有节点检查是否添加成功，

psql -U postgres -c "SELECT * FROM master_get_active_worker_nodes();"

迁移数据

迁移现有的数据到citus集群

允许服务间断的场景：

首先将原来的数据备份，将一张表由单表调整为distributed，需要在灌数据之前声明，所以我们需要三个步骤：首先备份并恢复表结构，然后声明表为distributed，最后备份并恢复数据，此时数据会分布到各个worker。

备份表结构

pg_dump -Fc --no-owner --schema-only --dbname db_name > db_name_schema.back

pg_restore --dbname db_name db_name_schema.back

声明distributed

select * from create_distributed_table('table_name', 'id')

灌数据

pg_dump -Fc --no-owner --data-only --dbname db_name > db_name_data.back

pg_restore --dbname db_name db_name_data.back

不允许服务间断

可以使用PostgreSQL的logical replication，将在用的老数据库指向Citus主节点，这样新老数据库处于同步状态，然后统一将服务的数据库连接地址切换到Citus。

测试性能

All things done！接下来进行一些简单的测试。

导入数据与数据分布

数据量： 122608100 个多边形，恢复数据耗时7分钟，每个节点 9.1 G 数据；这些数据恢复到一台相同配置单主机的 PostgreSQL 数据库数据量是，耗时 55分钟，28 G 数据。将近8倍的差距，但是我们注意到数据是有一定程度的冗余的，因此citus可以承受单节点的数据丢失。

缓冲区操作处理时间

对1.2亿的数据进行6米半径的缓冲区分析，并将结果存入数据库

insert into buffer_result (id, the_geom) select id, st_buffer(the_geom, 6) as the_geom from table_name;

citus集群，56分钟；单机，120分钟，差距并不明显，原因其实我们上面提到过，计算结果需要汇总，且涉及到了大量的IO，观察处理过程注意到，15分钟计算完成，25分钟汇总数据，16分钟写入结果，而单机不需要汇总数据。

不涉及汇总的操作

对1.2亿多边形统计总节点数：

select sum(st_npoints(the_geom)) from table_name;

citus集群耗时1.7秒，而单节点耗时50秒，差距很明显。

总结

以上的测试中，数据库并没有进行很好的调优，是比较粗糙的结果，但也足以说明问题。

数据分布式存储的应用（Multi-tenant Application），实测单节点vs四台citus集群入库20G数据有7倍的差距；
实时分析（Real-Time Dashboards），只适合节点之间IO非常小的场景。