PostgreSQL: Optimizing bulk INSERT INTO/SELECT FROM

Question

In my Postgresql schema I have a jobs table and an accounts table. Once a day I need to schedule a job for each account by inserting a row per account into the jobs table. This can done using a simple INSERT INTO.. SELECT FROM statement, but is there any empirical way I can know if I am straining my DB by this bulk insert and whether I should chunk the inserts instead?

Postgres often does miraculous work so I have no idea if bulk inserting 500k records at a time is better than 100 x 5k, for example. The bulk insert works today but can take minutes to complete.

One additional data point: the jobs table has a uniqueness constraint on account ID, so this statement includes an ON CONFLICT clause too.

Could your share the result from EXPLAIN(ANALYZE, VERBOSE, BUFFERS) for this insert statement? (Be aware that the insert will be executed!) — Frank Heikens
– Frank Heikens, Commented Sep 9, 2022 at 5:44
I hope this is what you're after: gist.github.com/rarkins/cc64ef6d61f6d807f8bfdaebfbbe67aa. It is a smaller scale insert done hourly with up to 50k rows. In this case most of them were already in the table so most conflicted and did nothing — Rhys Arkins
– Rhys Arkins, Commented Sep 9, 2022 at 7:20
A transaction inserting 1 million rows is no more "expensive" than a transaction inserting 1000 rows. — user330315
– user330315, Commented Sep 9, 2022 at 8:10
This is a query plan, but not the result from EXPLAIN(ANALYZE, VERBOSE, BUFFERS) . There are no timings, no-one can see where most of the time is spend and what could be optimised. — Frank Heikens
– Frank Heikens, Commented Sep 9, 2022 at 8:59

Laurenz Albe · Accepted Answer · 2022-09-09 05:40:56Z

1

In PostgreSQL, it doesn't matter how many rows you modify in a single transaction, so it is preferable to do everything in a single statement so that you don't end up with half the work done in case of a failure. The only consideration is that transactions should not take too long, but if that happens once a day, it is no problem.

answered Sep 9, 2022 at 5:40

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Optimizing bulk INSERT INTO/SELECT FROM

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related