Batch rewriting logarithmic logic

[olivierdepriester]

When using the reWriteBatchedInserts flag, the way the batched queries are rewritten seems a bit awkward.

Example: if I batch 500 INSERT, it ends with submitting to postres

• 3 batches of 128
• 1 batch of 64
• 1 batch of 32
• 1 batch of 16
• 1 batch of 4

Total is 500 so all rows are sent but I would have expected ;

• 3 batches of 128
• 1 batch of 116

Is there any performance reason for this splitting ?

[davecramer]

The relevant code is

pgjdbc/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java

Line 1774 in 5c0dbf0

protected void transformQueriesAndParameters() throws SQLException {

I think you might be correct.

[bokken]

The performance reason is to limit the number of distinct sql statements in use. Each batch size is a unique sql statement.

…

On Thu, May 2, 2024 at 12:14 PM Olivier Depriester ***@***.***> wrote: When using the reWriteBatchedInserts flag, the way the batched queries are rewritten seems a bit awkward. Example: if I batch 500 INSERT, it ends with submitting to postres - 3 batches of 128 - 1 batch of 64 - 1 batch of 32 - 1 batch of 16 - 1 batch of 4 Total is 500 so all rows are sent but I would have expected ; - 3 batches of 128 - *1 batch of 116* Is there any performance reason for this splitting ? — Reply to this email directly, view it on GitHub <#3237>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAW3U3JCTTRC2YKRCCBEJS3ZAJX5XAVCNFSM6AAAAABHEDQSIWVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZWGU4TOMRXGE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[vlsi]

#3195 might be helpful to reduce the number of distinct sqls

[olivierdepriester]

Thanks, I got it. With the current algorithm we end with at most 8 query patterns (128 parameters, 64, 32, 16, 8, 4, 2 and 1). Otherwise we would have a 128 parameters pattern and some random [1 to 127 parameters] pattern depending on the total amount of parameters

[svendiedrichsen]

This guy was timing performance for the multirow insert and his finding looks pretty linear and might suggest that the 128 limit does not really exist.
https://dev.to/dm8ry/postgresql-database-insert-multiple-rows-how-the-number-of-rows-impacts-timing-hnn

[davecramer]

That doesn't account for network time.

[svendiedrichsen]

But shouldn't network time be an argument for larger batches? The batch limit 128 currently inceases overall time compared to say 256, doesn't it? So larger batches should decrease the no. of queries and thus improve overall query time if the net query on the server scales linearly.

[davecramer]

Possibly, but having huge payloads may be detrimental. I'd just like to see the experiment done the way the driver does it.

[davecramer]

So looking at his results again I think it proves exactly what we think.
What his code does is insert a constant number of records for varying chunk sizes. As chunk size goes up so does the time. Unfortunately, he started at 5000 so we have no idea based on this what smaller numbers look like.
Also not happy that he chose a random length for the text but that's a bit nit picky.

[davecramer]

OK, I used exactly the same function as the article referred to and here are the results

$do$
declare  
 n_rows_in_a_single_insert smallint;
 n_chunk_size smallint := 20000;  
args int[]:= ARRAY[16,36,64,128,256,512,1024,2048,4096,8192]; 
begin   
  foreach n_rows_in_a_single_insert  in ARRAY args loop  
  truncate table my_table;                                                                                                                                                                                                                                                                                                                            with n_of_recs as (select 200000::bigint val), 
chunk_size as (select n_rows_in_a_single_insert val),
 timing_ms as (select populate_table(n_of_recs.val::bigint, chunk_size.val::smallint)::bigint val from n_of_recs, chunk_size)                                           insert into check_the_timing select n_of_recs.val, chunk_size.val, timing_ms.val                                                                                          from n_of_recs, chunk_size, timing_ms;
end loop; 
 end;  
 $do$;

select * from  check_the_timing order by 2 ;
 n_of_recs | chunk_size | timing_ms
-----------+------------+-----------
    200000 |         16 |      3572
    200000 |         36 |      3662
    200000 |         64 |      3646
    200000 |        128 |      3838
    200000 |        256 |      3788
    200000 |        512 |      4428
    200000 |       1024 |      4851
    200000 |       2048 |      5614
    200000 |       4096 |      6852
    200000 |       8192 |      9184
(10 rows)

[davecramer]

Clearly this shows that larger inserts take longer. The optimal size seems to be around 128 or 256

[davecramer]

That's not clear to me. If you compare 512 with 1024 and you want to transfer 1024 rows you will get With chunk_size 512: 2 x 4428 = 8856 ( + 2x network traffic) With chunk_size 1024: 1 x 4851 = 4851 ( + 1x network traffic) Addmitted that the volume transferred in the second is larger thus the network traffic takes some millis longer.

As I understand it the timing numbers are for the total 20,000 records to be inserted so I'm unclear where the 2 x 4428 comes from?

[svendiedrichsen]

Ok, I see. Sorry for my confusion.

[svendiedrichsen]

And then it should be 16, shouldn't it?

[davecramer]

Could be. The test isn't ideal as there is no network traffic.
Ideally we redo the test with network traffic. However that is also fraught with ambiguity as it may depend on the round trip time.
It also depends on width of columns, etc.
Certainly worth doing some research.

[svendiedrichsen]

Maybe this is worth some config option to allow for tuning the chunk size from the application side?