PostgreSQL之dblink使用

选择PostgreSQL数据库不是因为它是世界上最高级的开源数据库，而是业务使然，用到了空间计算的概念，这一块PostgreSQL确实是最强大的。dblink是PostgreSQL中重要的一个扩展，也是PostgreSQL自带的一个扩展之一；它创建了一个通道，使得我们访问远程数据库就像访问本地数据库一样方便。
一次程序性能的优化，让我开始认识和使用dblink，并使性能有个非常大的提升，从单位计算需要60分钟，优化后只有2-3分钟，改进非常明显，好的东西一定要分享。
首先大概描述下业务模型，不一定具有普遍性；数据类型非常多，有50多种，每种数据类型对应一个数据库，每个数据库包含按城市划分的400多个表，每个表中的数据量都在100万+以上，最大的可达亿级；一些数据母表采用了流式热备，同时支持数据库负载均衡，反正一句话，数据量很大。
要处理这些数据，就存在跨数据库查询数据的问题，JOIN是肯定用不了了，只能在程序内部对数据进行处理；程序的优化主要经历了下面三个阶段：
一、HashMap: 将需要的数据抽取到HashMap中，计算的时候在内存中进行比较、查询；问题来了，对于数据量比较大的表，直接就内存就溢出了；这种方式只能计算一些数据量不大的城市，稍大一些就运行出错；这种情况下成功处理一个单位的时间>1hr.
二、中间数据库：使用一个独立的数据库保存计算过程中需要的数据，类似第一种方法，存储方式不同，这里是保存到表中；整个过程需要根据需要动态创建和删除表；使用这种方式，内存不足的问题解决了，数据的查询和比较等操作的性能有了很大提高, 单位处理时间为>40min，但是耗时还很长，因为主要时间消耗在数据抽取和插入上，数据量小的时候感觉不到问题的存在，但是对于超过1000万记录的表，时间消耗非常明显，这个性能损耗就成了主要的问题；
三、混合方式：优化还是要进行，采用多种方式优化：
1、将数据分类，按缓存的28法则，将使用频繁或则数据量不是很大的一些数据继续放在HashMap中，在内存不溢出的情况下尽量少些数据库插入操作；
2、采用数据库的批量操作提升数据插入效率；
3、使用存储过程对数据进行预处理，这样可以减少返回数据，减少程序处理的数据量。

经过上面的优化，单位计算时间提升到30Min内；但是对于400多个城市计算量，即使使用并行计算还是不理想。使用了10台机器，这样平均下来每天差不多每个城市可以运行一遍。但是业务方的要求是最好在每个批次数据更新后2个小时内看到结果，当然如果每次更新少量的城市，比如20个，那现在的方案基本可以满足要求，问题在于每次的修改量是全量修改，下一次修改还要依赖这次的计算结果，对于质检人员来说这非常影响他们的效率，每天处理一类数据是不可接受的；所以优化还是要继续。

四、dblink: 偶尔看到PostgreSQL可以像Oracle那样支持dblink, 可以很方便的从其他数据库中提取数据，并且性能很好；我没用过Oracle的dblink, 但是我听到可以从其他数据库提取数据，并且性能很好，感觉机会来了，就赶紧找资料了解下，然后开始继续优化;
首先需要配置安装dblink扩展，因为是PostgreSQL自带的，安装非常简单：

Create extensions dblink;

然后就写了一堆存储过程用于抽取数据到指定的表中, 并对应用程序进行了优化：

SET client_min_messages TO WARNING;
create or replace function copy_track_data(
    p_city varchar,
    p_track_host varchar,
    p_track_dbname varchar,
    p_track_port varchar,
    p_track_user varchar) RETURNS void
AS $$

begin
    drop table if exists gen_track_all;
    create table gen_track_all as 
        select * 
        from 
        dblink('host='||p_track_host||' dbname='||p_track_dbname||' port='||p_track_port||' user='||p_track_user || ' password=password',
        'select track_id,geom,track_time,north_angle,speed,task_id,mesh_id,status,slope_angle
            from pano_track_'||p_city) 
        as t(track_id character varying(128),
            geom GEOMETRY(GEOMETRY,4326),
            track_time text,
            north_angle integer,
            speed double precision,
            task_id character varying(128),
            mesh_id character varying(6),
            status integer, slope_angle integer);

    drop table if exists gen_untrack;
    create table gen_untrack as 
        select * 
        from      
        dblink('host=182.168.1.143 dbname=result port=8435 user=postgres password=password',
        'select track_id from result_pano_link_'||p_city) 
        as t(track_id character varying(128));
end;    
$$ LANGUAGE plpgsql;

运行结果让我震惊，数据非常快，数据量在100万左右、并且字段不是很多的表的抽取时间在2-3秒，太不可思议了，我很想知道dblink的底层是如何实现的？
改造后的程序的单位运行时间缩短为3分钟，10台计算并行计算，完全满足2小时全量更新的要求。