CODEWITHSUNDEEP
postgresqldblinkpostgres-fdw
Fral
Fral

Reputation: 1

Low performance of postgres_fdw extension

I need to periodically copy data from the TMP database to the remote PROD database with some data modifications in columns. When I use the postgres_fdw extension from the PROD database (with mapping foreign schema), the process of copying a million records lasts 6 minutes.

insert into prod.foreign_schema.foreign_table 
(select * from tmp.public.table limit 1000000);

However, when I use the dblink to copy the same table from the PROD database (SQL is running on the PROD database, not on the TEMP), the process lasts 20 seconds.

insert into prod.public.table 
(select * from dblink('host=192.1... port=5432 dbname=... user=… password=…. connect_timeout=2', 'select * from tmp.production.table limit 1000000') as tab (id integer…..)
);

How can I optimize and shorten the process of copying data from the TEMP database?

I have to run SQL commands on the TMP database. TMP and PROD database are in this same versions (10).

Upvotes: 0

Views: 1111

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247485

The first statement will effectively run many small inserts, albeit with a prepared statement, so you don't have the planning overhead each time. So you'll have more round trips between the two servers, which probably is the reason for the difference.

Upvotes: 2

Related Questions