Recently I have come across an issue while inserting data.
I have a rather small table with 23 columns and barely any size in columns. The biggest column has a size of 128 characters but the average is about 50~ char.
I was looking into optimizing performance for our table generation and thus found out about 'reWriteBatchedInserts'. So I set this parameter to 'true' and after that, we encounter a 'freeze/lock' on the table I'm trying to insert the data into.
We have a Postgres Version 12.5 (PostgreSQL 12.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit) and are running batches of 25.000 while committing every 100.000. The jdbc version we're running is: 42.2.20
When I was optimizing the performance in another project, I found out about the jdbc BUG https://github.com/pgjdbc/pgjdbc/issues/194 which had the exact same behaviour.
But there I was inserting much more data, a table with 55 Columns and 15 columns having 2000 Characters and most of them having 100+ Characters in column size.
I found out that manually setting the bufferSize of the pgStream in the connection, it was possible to avoid this issue. Now with much less data AND with this workaround incorporated, we still get this issue.
In the trace log of the server, I can see the statements being rewritten in batches of 128.
So it looks somewhat like this on database:
INSERT INTO tableplaceholder (col1 .. col22), (val1..val22), .. (val2795 .. 2816)x42
This runs a total of 42 times meaning there should be around 5376~ entries done.
In pgAdmin I can see the session with the insert being stuck on Client:ClientWrite all the time. When I kill the session, I see 5436 in the tuples IN graph but no actual data is inserted.
Any ideas on what else I could do?
As I said, this issue is gone once I deactivate reWriteBatchedInserts. But this results in a more than 2x times runtime of inserts.
Commit more often would be my recommendation.
Commitsize has had no effect only reducing batchSize resulted in it working again at around size = 2000.
Unfortunately it seems setting the BufferSize also doesn't change the behaviour. Not sure why it works on one system and not the other.
Am Mi., 19. Mai 2021 um 18:47 Uhr schrieb Dave Cramer <[hidden email]>:
On Fri, 21 May 2021 at 05:33, Semen Yefimenko <[hidden email]> wrote:
So yes, there is an issue with the size of the buffers being sent back and forth. pgjdbc/faq.md at f61fbfe7b72ccf2ca0ac2e2c366230fdb93260e5 · pgjdbc/pgjdbc (github.com)
|Free forum by Nabble||Edit this page|