4
  • 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

Question: 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. Idea of insert select from fdw instead of running bash commands looked so sweet)

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

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

6
  • What is the ping time between the servers?
    – jjanes
    Feb 23 '17 at 18:07
  • I cant check ping atm. but how could it be relevant?.. psql gets data with \copy even faster then scp
    – Vao Tsun
    Feb 23 '17 at 20:03
  • For every 100 (or whatever fetch_size is set to) rows, it has to acknowledge it received them and ask for the next batch. The max speed at which that can happen depends on the ping time.
    – jjanes
    Feb 23 '17 at 21:57
  • fetch_size comes with 9.6?..
    – Vao Tsun
    Feb 23 '17 at 22:18
  • Yes, before 9.6 it was hard-coded and couldn't be changed. (Unless you can recompile postgres_fdw)
    – jjanes
    Feb 24 '17 at 15:46
3

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.

2
  • thank you for your answer. I know that 9.6 has several improvements for pg_fdw - remote join, delete and some performanc improvements. I even heard somewhere those improvements are ready in minor versions of 9.5. Aparently I can't change version
    – Vao Tsun
    Feb 23 '17 at 20:05
  • I accepted your answer. Thanks for your efforts! I tried to mock up fetch_size change with dblinks and it indeed effected the speed - by 40 times
    – Vao Tsun
    Feb 28 '17 at 16:28
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.