patternsqlMinor
postgres_fdw slower then copy + scp + copy (~12 times)
Viewed 0 times
postgres_fdwscpslowerthentimescopy
Problem
-
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:
-
postgres_fdw:
Did I miss something in config (meaning I can improve it), or
(I use it for small data amount reconcile and it works fine. The idea of
I tried psql to remote server from local server and
The
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:
- copy to - 2:36
- scp - 08:17
- copy from - 10:11
-
postgres_fdw:
- 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.Solution
postgres_fdw is certainly not as optimized for bulk transfer as
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.
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.
Context
StackExchange Database Administrators Q#165329, answer score: 4
Revisions (0)
No revisions yet.