PostgreSQL: performance of large inserts without index, temp table

Question

I'm working with a table of 50+ million rows.

I'd like to know if I should expect a better performance if I first insert batches of ~50k rows in a temporary table, and then in one time insert all of them in the bulk table, instead of directly inserting the ~50k rows in the bulk table?

To be precise, which is better?

Many small inserts into the target table or
First insert into temp table, then all of them in the target table?

The table has no indices.

Any suggestions for performance increase are welcome, of course!

Insert all at once into the target table

user330315
– user330315

2019-02-26 20:20:37 +00:00
Commented Feb 26, 2019 at 20:20 — user330315
– user330315, Commented Feb 26, 2019 at 20:20
direct insert = superior? @a_horse_with_no_name

Willem
– Willem

2019-02-26 20:25:18 +00:00
Commented Feb 26, 2019 at 20:25 — Willem
– Willem, Commented Feb 26, 2019 at 20:25
@Willem yes, it should be.

Gnudiff
– Gnudiff

2019-02-26 21:40:01 +00:00
Commented Feb 26, 2019 at 21:40 — Gnudiff
– Gnudiff, Commented Feb 26, 2019 at 21:40

Laurenz Albe · Accepted Answer · 2019-02-26 22:26:14Z

1

With INSERT performance should not depend on the size of the batch.

The important thing is that you don't run each INSERT in its own transaction.

Additionally, it hurts if each tiny little INSERT has to be parsed separately, so use a prepared statement.

If you do that, it should be faster to insert the data directly rather than using an intermediary table.

answered Feb 26, 2019 at 22:26

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: performance of large inserts without index, temp table

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