0

I am currently running tests on how fast can JDBC with PostgreSQL insert bulk inserts.

It seems that there is an optimal number of inserts one bulk insert should be doing and it is not very high (about 200). I call it the optimal number, since inserting more (or less) takes more time to insert a million of rows.

Why is that the case and can I increase that optimal number?

If you are interested of how my test works:
I am creating a million of events that should be inserted into the table. I set a maximal bulk size to 1, let the program insert and measure the time as the number of events to insert hits 0. I then repeat this with a maximal bulk size of one more.

Shuumi
  • 66
  • 6
  • https://stackoverflow.com/q/12206600/32453 may be useful – rogerdpack Mar 23 '18 at 15:47
  • Works well, but it's about searching for reasons why it is so and not ways to work around it. I want to explain it in my bachelor, but can not find a clear answer. Thank you nontheless – Shuumi Mar 23 '18 at 17:31
  • There are so many factors that can have influence that no one here is able this question. You must profile your system, measure varoius parameters and find a bottleneck, no one will do it for you without access to your system. See this link, there are tips on what bottlenecks you can expect while tuning your system: [List Of 20 classical Common Bottlenecks](http://highscalability.com/blog/2012/5/16/big-list-of-20-common-bottlenecks.html) – krokodilko Mar 24 '18 at 08:40
  • That is actually an answer that can help me... I was thinking, whether it is possible to tweak my PostgreSQL to incrase that number. Your answer gave me things to look about, when tweaking. – Shuumi Mar 24 '18 at 11:24

1 Answers1

0

You will see dramatically faster inserts if they are performed in a transaction block with a single commit at the end, e.g.

begin;
insert 1;
insert 2;
commit;
Curt Evans
  • 326
  • 4
  • 4