标签
PostgreSQL , oss对象存储 , 阿里云RDS PG , 并行写 , dblink , 异步调用 , 异步任务监控 , OSS外部表 , 数据传输
背景
阿里云RDS PostgreSQL、HybridDB for PostgreSQL提供了一个非常强大的功能，OSS对象存储外部表。
阿里云的RDS PostgreSQL用户可以利用OSS存储冷数据（OSS外部表的形态呈现），实现冷热分离；也可以利用OSS作为数据的中转桥梁，打通其他云端业务，例如HDB FOR PostgreSQL分析型数据库。
oss外部表的用法文档如下。
https://help.aliyun.com/document_detail/44461.html
目前oss外部表支持文本\GZIP等格式。将来还会支持流行的列存格式（ORC，parquet等），扫描下推，并行读写OSS文件等，提升体验。
由于目前RDS PG的版本是9.4，9.4的版本目前不支持并行框架，单个写进程是15MB/s左右。采用gzip压缩格式，可能能提升到20MB/s。
采用并行框架的PostgreSQL 10，可以在写出到OSS时开启并行写，每个WORKER进程 20MB/s，单表导到OSS的速度将得到大幅度的提升（读取也一样支持并行）。
如果RDS PG 9.4的用户需要将大表快速的写出到OSS的话，有什么优化手段呢？
答案是通过PG DBLINK来实现异步并行。
业务背景
用户的订餐、购物、寄送包裹等操作，会产生订单，订单与业务逻辑挂钩，在各个业务系统流转会生成新的状态或属性（每个业务系统产生的数据字段可能都不一样）。
为了对订单数据进行统一管理、准实时数据分析、透视。需要实时的将订单数据在各个业务系统中生成的状态、属性进行合并，输送到分析型数据库HybridDB for PostgreSQL。
数据流
订单信息，从业务系统流入阿里云的流计算平台，从流计算平台实时写入RDS PG，从RDS PG批量写入OSS，从OSS批量合并到HybridDB PG(HybridDB PostgreSQL保存最完整的订单信息，提供分析透视)。
1、从流计算平台到RDS PG。
实时、批量，采用UPSERT的方式，PostgreSQL UPSERT的语法请参考：
《PostgreSQL upsert功能(insert on conflict do)的用法》
我们采用了其中FUNCTION批量upsert的方法。对于PostgreSQL 9.5以及以上版本，可以在function中使用insert into on conflict语法（因为insert into on conflict不支持values (),(),()...()的批量写法）。
2、从RDS PG写入OSS
由于RDS PG 9.4没有内置写OSS并行，当数据量很大的时候，单线程写速度很慢，容易成为瓶颈。
这个是本文的重点，RDS PG 9.4如何采用单表异步并行，写入OSS。（未来PG 10上线，内置了并行，不需要这么麻烦）
3、从OSS合并到HybridDB PostgreSQL
采用三步走的方法：
3.1 oss_tmp1 inner join big_table into tmp2 得到大表（总表）已有订单已有字段属性+订单新状态的数据tmp2。
3.2 delete from big_table using tmp2 删除总表中已剥离出来的tmp2。
3.3 insert into bit_table select * from oss_tmp1 left join tmp2 where tmp2.* is null (union all) tmp2。 将数据汇入总表。
RULE的方式，并不能提升效果
创建4个外部表（4个并行），表名不一样，其他外部参数(bucket, dir)一样，文件名会以表名来命名，所以不用担心写入OSS 同一目录的时候文件重名：
tbl_oss_ext0
tbl_oss_ext1
tbl_oss_ext2
tbl_oss_ext3
创建一张规则表，与外部表定义一致：
create table tbl_entry (like tbl_oss_ext0);
创建规则：
create rule r0 as on insert to tbl_entry where mod(order_id, 4)=0 do instead insert into tbl_oss_ext0 values (NEW.*);
create rule r1 as on insert to tbl_entry where mod(order_id, 4)=1 do instead insert into tbl_oss_ext1 values (NEW.*);
create rule r2 as on insert to tbl_entry where mod(order_id, 4)=2 do instead insert into tbl_oss_ext2 values (NEW.*);
create rule r3 as on insert to tbl_entry where mod(order_id, 4)=3 do instead insert into tbl_oss_ext3 values (NEW.*);
写入规则表，数据将重定向到4个外部表。
insert into tbl_entry select * from stream_table;
因为只使用了一个进程在做这件事情，所以这种方法并不是真正的并行。
所以采用DBLINK异步调用，实现真正的并行。
https://www.postgresql.org/docs/10/static/dblink.html
基于DBLINK的并行设计
1、前端写分区表（可选）
例如写入到16个分区，导出时，每个分区表对应一个OSS外部表，可以实现16的并行度。
分区表有两种写法：
PG内置分区（继承、触发器、规则）。
业务层逻辑分区，业务层确定数据写入哪个分区。
这两种方法，方法1更灵活，但是性能会受到一定的影响。
如果不写分区表，单表开启并行的话，可以使用取模的方法来并行，会带来一定的重复扫描本地表的成本（每个并行都需要扫描所有记录，而且不建议用索引来分割，因为索引扫描速度也好不到哪里去）。
2、建立本地DBLINK连接（并设置连接指纹）
使用application_name来设置连接指纹。
select dblink_connect('外部表名_1','dbname=postgres user=xxx password=pwd application_name=外部表名_1');
select dblink_connect('外部表名_2','dbname=postgres user=xxx password=pwd application_name=外部表名_2');
select dblink_connect('外部表名_3','dbname=postgres user=xxx password=pwd application_name=外部表名_3');
select dblink_connect('外部表名_4','dbname=postgres user=xxx password=pwd application_name=外部表名_4');
3、使用DBLINK异步调用接口发起写请求
同时将只需结果输出到结果表。
select dblink_send_query('外部表名_1','begin; insert into 外部表1 select * from tmp where mod(order_id,4)=0; insert into tbl_result values(1); end;');
select dblink_send_query('外部表名_2','begin; insert into 外部表2 select * from tmp where mod(order_id,4)=1; insert into tbl_result values(2); end;');
select dblink_send_query('外部表名_3','begin; insert into 外部表3 select * from tmp where mod(order_id,4)=2; insert into tbl_result values(3); end;');
select dblink_send_query('外部表名_4','begin; insert into 外部表4 select * from tmp where mod(order_id,4)=3; insert into tbl_result values(4); end;');
4、查看异步任务状态
select * from pg_stat_activity where application_name in ('外部表名_1','外部表名_2','外部表名_3','外部表名4') and state !~ 'idle';
-- 没有记录返回，说明任务跑完。
通过查询tbl_result，如果记录数不等于线程数，则说明有任务失败。
任务正常结束：清除tbl_result表。
任务异常结束：清除tbl_result表、清除oss dir，重跑任务。
5、关闭连接
开启了异步调用的连接，需要get异步调用的结果后，才能继续使用这个连接。或者关闭连接后，重新建立连接即可使用。
https://www.postgresql.org/docs/10/static/dblink.html
dblink_connect — opens a persistent connection to a remote database
dblink_connect_u — opens a persistent connection to a remote database, insecurely
dblink_disconnect — closes a persistent connection to a remote database
dblink — executes a query in a remote database
dblink_exec — executes a command in a remote database
dblink_open — opens a cursor in a remote database
dblink_fetch — returns rows from an open cursor in a remote database
dblink_close — closes a cursor in a remote database
dblink_get_connections — returns the names of all open named dblink connections
dblink_error_message — gets last error message on the named connection
dblink_send_query — sends an async query to a remote database
dblink_is_busy — checks if connection is busy with an async query
dblink_get_notify — retrieve async notifications on a connection
dblink_get_result — gets an async query result
dblink_cancel_query — cancels any active query on the named connection
dblink_get_pkey — returns the positions and field names of a relation's primary key fields
dblink_build_sql_insert — builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values
dblink_build_sql_delete — builds a DELETE statement using supplied values for primary key field values
dblink_build_sql_update — builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values
6、达到的效果
开启40个并行，26GB的数据，140秒，达到190MB/s的写出速度。
云端相关产品
相关案例
《打造云端流计算、在线业务、数据分析的业务数据闭环 - 阿里云RDS、HybridDB for PostgreSQL最佳实践》
小结
目前阿里云RDS PostgreSQL、HybridDB PostgreSQL oss外部表支持文本\GZIP等格式。将来还会支持流行的列存格式（ORC，parquet等），扫描下推，并行读写OSS文件等，提升体验。
由于目前RDS PG的版本是9.4，9.4的版本目前不支持并行框架，单个写进程是15MB/s左右。采用gzip压缩格式，可能能提升到20MB/s。
采用并行框架的PostgreSQL 10，可以在写出到OSS时开启并行写，每个WORKER进程 20MB/s，单表导到OSS的速度将得到大幅度的提升（读取也一样支持并行）。
如果RDS PG 9.4的用户需要将大表快速的写出到OSS的话，通过PG DBLINK来实现异步并行。
开启40个并行，26GB的数据，140秒，达到190MB/s的写出速度。
