Slow insert using postgres foreign data wrapper

Question

insert into my_foreign_table select * from my_local_table

My columns are two integers and two floats. The number of rows was >100M. I left it running for a few days, but killed it because it took forever.

Should this run quickly? Does Postgres do the inserts one at a time or something? Is there a way to speed inserts up via foreign data wrapper?

Maybe provide details of the machines involved and the network between them? Accompany that with timings for 100,000 rows and you stand a chance of getting a useful answer. — Richard Huxton
– Richard Huxton, Commented Jul 2, 2024 at 17:09

Frank Heikens · Accepted Answer · 2024-07-02 18:21:26Z

3

You can change the batch_size:

ALTER SERVER name_of_your_foreign_server OPTIONS (SET batch_size '1000');

batch_size (integer) This option specifies the number of rows postgres_fdw should insert in each insert operation. It can be specified for a foreign table or a foreign server. The option specified on a table overrides an option specified for the server. The default is 1.

answered Jul 2, 2024 at 18:21

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slow insert using postgres foreign data wrapper

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related