postgres_fdw slower then copy + scp + copy (~12 times)

Question

• foreign server 9.2

• local server 9.5

• table is 10GB

• data transfer performed on same network interface as foreign server works

• no indexes set on data

• old way:

  1. copy to - 2:36
  2. scp - 08:17
  3. copy from - 10:11

• postgres_fdw:

  1. by the time old way finished it has done 800MB of insert into .. select * from foreign_table

Did I miss something in config (meaning I can improve it), or postgres_fdw is just not meant for bulk load (meaning I can't improve it)?

(I use it for small data amount reconcile and it works fine. The idea of insert select from fdw instead of running bash commands looked so sweet.)*

I tried psql to remote server from local server and \copy table - six minutes - faster then over ssh.

The fetch_size option, not available prior to 9.6, can be mocked up with dblink_fetch(CURSOR, fetch_size) - see my answer below.

Answer 1

postgres_fdw is certainly not as optimized for bulk transfer as copy to, copy from, and scp are. After all, bulk transfer is the main reason for the existence of those tools.

But that doesn't mean there is nothing you can do. If you were running 9.6 on the local server, you could try increasing the fetch_size.

Answer 2

prior to 9.6 fetch_size can't be set for server nor foreign table, yet we can mock up this option using dblinks for bulk operation. In the case below I speeded up ~1GB table bulk select from postgres_fdw from hour and half to two minutes by mocking up fetch_size change from 100 to 100K.

Thanks to @jjanes, I started looking into fetch_size available from 9.6. Alas I can't go for upgrade, so I had to implement a workaround. Watching pg_stat_activity on remote I noticed FETCH 100 FROM c1 from local server, so I thought fetch_size = 100 is probably hardcoded in prior versions. So I ran a little wrap-up to fetch data with dblink fetched by 100 rows:

truncate table bin.t1;
begin;
do
$$
declare
 _r record;
begin
  perform dblink_connect('past');
  perform dblink_exec('BEGIN;');
  perform dblink_open('cr', 'select * from rel');

  for _r in 0..130*1000 loop /* I know the number of rows is apxm 130*1000*100*/
    raise info '%',concat(lpad(_r::text,4,'0'),': ',clock_timestamp());
    insert into bin.t1
      SELECT * FROM dblink_fetch('cr', 100) 
        AS (a integer, b character varying(200), c double precision)
    ;
  end loop;

  perform dblink_close('cr');
  perform dblink_exec('END;');
  perform dblink_disconnect();
end;
$$
;
end;
DO
Time: 4235292.205 ms
db=# end;
COMMIT
Time: 89.988 ms
db=# select count(1) from bin.t1;
  count
----------
 12309947
(1 row)

So it took 4235 seconds... Next I increased fetch_size in my wrap-up from 100 to 100*1000:

truncate table bin.t2;
begin;
do
$$
declare
 _r record;
begin
  perform dblink_connect('past');
  perform dblink_exec('BEGIN;');
  perform dblink_open('cr', 'select * from rel');

  for _r in 0..130 loop
    raise info '%',concat(lpad(_r::text,4,'0'),': ',clock_timestamp());
    insert into bin.t2
      SELECT * FROM dblink_fetch('cr', 100*1000) 
        AS (a integer, b character varying(200), c double precision)
    ;
  end loop;

  perform dblink_close('cr');
  perform dblink_exec('END;');
  perform dblink_disconnect();
end;
$$
;
end;
DO
Time: 89606.764 ms
db=# end;
COMMIT
Time: 0.153 ms
db=# select count(1) from bin.t2;
-[ RECORD 1 ]---
count | 12309947

So I saw **FETCH 100000 FROM cr** as expected in pg_stat_activity and execution time changed from 4235 seconds to 90 seconds - which is 40 something times!

Last to mention that insert select from postgres_fdw takes more or less same time as dblink wrap up with fetch 100:

db=# insert into bin.t3 select * from remote.rel;
INSERT 0 12296752
Time: 5321428.694 ms