2

I'm currently obsessing over bulk inserts via prepared statements with libpqxx.

I am far from a database expert and even further when it comes to Postgres, but just thinking superficially, it doesn't seem like it would make a difference.

In my MySQL days, it made a huge difference, but it strikes me that MySQL's clustered index was the bottleneck, that it would be better to prepare it for many rows at once rather than one at a time.

Since Postgres doesn't do that, happily I might add, is it correct that there would be a near negligible performance boost to bulk inserts? Please explain why or why not.

1 Answer 1

2

There is a big difference. Insert as many rows as possible at once. Best in a single INSERT statement, but at least in a single transaction. Inserting individual rows takes much longer. Quoting the chapter Populating a Database:

If you allow each insertion to be committed separately, PostgreSQL is doing a lot of work for each row that is added. An additional benefit of doing all insertions in one transaction is that if the insertion of one row were to fail then the insertion of all rows inserted up to that point would be rolled back, so you won't be stuck with partially loaded data.

If you insert a large number of rows, consider COPY, which is much faster, yet. And read that chapter in the manual. It has all you need.

4
  • Thanks for the answer. TWhat considers as large number of rows?
    – user
    May 15, 2021 at 16:55
  • 1
    @user: That depends on many factors. Row size, DB setup, connection, etc. You'll have to test to find your sweet spot. If in doubt, COPY (or the corresponding \copy meta-command of psql) is almost always fastest. INSERT is mostly for operations within the database. May 15, 2021 at 17:12
  • Thank you. Is there anything equivalent if I am trying ot insert large volume of data from python?
    – user
    May 15, 2021 at 20:16
  • 1
    @user: I am no Python expert. But from what I remember, Python has copy_from to make use of this. A quick search based on this turned up this related question on SO and this related blog post. For anything more, please create a new question. Comments are not the place. May 15, 2021 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.