PostgreSQL – 利用 citus 支持地理大数据
目录
目录
1. 实际问题
实际业务中，会有非常大的地理数据集的存储需求，比如全世界的点状POI，数据量级已达亿级别，存储在单一的PostgreSQL数据表中，若查询涉及到全表扫描，性能会差很多，如果对这些数据进行空间分析，复杂的计算，动不动就耗时十几二十分钟，不能满足实时的要求，因此，我们开始寻找一个可以解决这个问题的方案。
要找方案，我们首先要弄清楚原来的瓶颈在哪里。为什么存储在单表中性能差？
– 记录量太大，扫描记录时间过长；
– 写入和更新时会产生表级的锁，其他的写入和更新操作只能等待；
– 单磁盘I/O性能有限，若数据分布式存储在不同的磁盘，则I/O上限增加。
可能的解决方案
水平分表，是比较传统的处理数据量较大的单表的方法，可以有效的减小索引的大小，但分表一般存储在同一个数据库服务器，仍然受制于单磁盘I/O。因此，较好的方案是将分表分布到不同的数据库服务器，这就给了我们使用多个数据库并行处理的可能，还可以对单个分表做副本，冗余存放实现高可用。
但这两种方法都有一些问题，而这些问题我们自己来处理就太麻烦了：
- 需要更复杂的SQL，需要在SQL中加入shard或分表的逻辑；
- 维护分表的分布逻辑；
- 数据的导入和备份困难；
- 单点的故障可能导致整个集群不可用。
有没有现成的方案，先借鉴借鉴。
2. Citus是什么
首先，`Citus`是一个PostgreSQL的扩展，类似于PostGIS、pgRouting，它主要的作用是将一张大表进行水平分表，每个分表称为“分片（Shard）”，按照一定的规则分布(?)到多台机器上，并且将查询分布到不同节点并行执行，最后汇总结果。
Citus是单coordinator，多worker结构，coordinator统筹协调，worker节点是真正存储数据和执行查询的地方。如下图 👇
从Citus官网盗了一张图来，这张图说的很清楚，当存储数据时，Coordinator节点会把当前表分为多个分片（shard），并将分片安装某种算法分布到各个worker节点，并记录一份元数据，如果开启了“分片复制”（Shard Replication），两个不同的节点中可能会存在同一分片的不同副本，为了防止单点故障造成的数据丢失。当一个针对分布式表的查询进来，Coordinator会根据数据的分布，将查询分别分配到各个worker节点，最后汇总结果。安排的明明白白。
什么时候用Citus！
– 数据分布式存储的应用（`Multi-tenant Application`），实测单节点vs四台citus集群入库20G数据有7倍的差距；
– 实时分析与统计（`Real-Time Dashboards`），只适合节点之间IO非常小的场景。
Citus不是万金油，啥时候不要用？
– 各个worker节点间有大量的数据流动，I/O过大，会将并行执行节省的优势抵消掉，反倒不如单节点高配机器
Citus的特点
1. 从单节点数据库到Citus集群无需更改应用逻辑，无缝集成。
当你从单节点切换到Citus集群时，你会发现这是一件多么容易的事情，不需要更改任何应用逻辑，之前你面对的是一个PostgreSQL，之后你还是面对的一个PostgreSQL，不同的是，之后你有了强大的存储和算力，而且这都是由citus的coordinate节点管理的，不需要我们维护。
2. 并行执行查询提高效率，且性能随着节点数量增加线性增加。但不适用于需要各节点汇总数据，涉及大量IO的情景，这样反而会抵消并行执行省下的时间。
Citus通过使用钩子和扩展API来实现PostgreSQL的分布式存储和并行计算，因此很多PostgreSQL扩展也能使用citus提供的能力，包括PostGIS，这就为我们使用Citus集群来管理和分析地理大数据提供了可能。但是需要注意，要配合Citus使用其它扩展，Citus必须是第一个启用的扩展，其次，必须在coordinator和worker节点都安装相应扩展。所以我们需要在所有机器节点上安装Citus和PostGIS。
3. 建立集群
> 本示例使用了6台机器做集群，1台coordinator，5台worker
1. 首先在各个节点上安装PostgreSQL，安装Citus扩展，安装PostGIS扩展；
2. 修改PostgreSQL的配置，启动时预加载citus，并配置各个节点的PostgreSQL互相联通，可互相访问；
3. 启动数据库，启用扩展。
“`sh
systemctl start postgresql-10.service
systemctl enable postgresql-10.service
psql -U postgers -c “create extension citus;”
psql -U postgers -c “create extension postgis;”
“`
3.1 让Coordinator知道有Worker的存在
使用Citus的扩展函数“master_add_node”，在coordinator节点添加所有worker节点信息，Coordinator会将worker信息存储到元数据表中，以备不时之需。添加完成后，使用“master_get_active_worker_nodes”列出所有节点，检查是否添加成功。
“`sh
psql -U postgres -c “select master_add_node(‘ip-or-name’, port);”
psql -U postgres -c “select master_get_active_worker_nodes();”
“`
3.2 迁移数据
首先备份数据，将单表声明为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`
4. 测试性能
All things done！接下来进行一些简单的测试。
集群硬件为4台8核16G内存虚拟主机组成的集群，单节点为一台8和16G虚拟机。
导入数据与数据分布
数据量： 1.2亿面数据，恢复数据耗时7分钟，每个节点 9.1 G 数据；这些数据恢复到一台相同配置单主机的 PostgreSQL 数据库数据量是，耗时 55分钟，28 G 数据。将近8倍的差距！当然我们用了更多的机器。
缓冲区操作处理时间
对1.2亿的数据进行6米半径的缓冲区分析，并将结果存入数据库
“`sql
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亿多边形统计总节点数：
“`sql
select sum(st_npoints(the_geom)) from table_name;
“`
citus集群耗时1.7秒，而单节点耗时50秒，差距很明显。
3. 总结
以上的测试中，数据库没有调优，是比较粗糙的结果，但也足以说明问题。
– 数据分布式存储的应用（`Multi-tenant Application`），实测“单节点 vs 四台citus集群”入库20G数据有7倍的差距，四倍的机器，但性能有7倍的提升，是值得的；
– 实时分析（`Real-Time Dashboards`），只适合节点之间IO非常小的场景。
转载自：https://blog.csdn.net/qingyafan/article/details/86503076
