Feedback-ID: i76984098:Fastmail
Message-ID: <a51348e6-2da2-4c33-a060-d7d9c416e4be@aklaver.com>
Date: Sun, 22 Sep 2024 08:46:37 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How batch processing works
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
 <4178E73A-24F5-4E3C-92F6-1532D8102C3E@kleczek.org>
 <CAKna9Vbt1VJu7Oa8FTWasgby+-kJn7omOhbfmWzkdpVwBiqNzQ@mail.gmail.com>
 <20240921143629.t2x37xfczeeunpnf@hjp.at>
 <2fe0be15-f64e-465c-9dd2-b55c559ac7e2@aklaver.com>
 <CAKna9VYu5O5ZsdT9iFJZ3FwYJVo0ocyczYbge1rwYDSiRZ8Rmg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKna9VYu5O5ZsdT9iFJZ3FwYJVo0ocyczYbge1rwYDSiRZ8Rmg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a51348e6-2da2-4c33-a060-d7d9c416e4be%40aklaver.com>
Precedence: bulk

On 9/21/24 22:21, Lok P wrote:
> 
> 
> On Sun, Sep 22, 2024 at 12:46 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 

> 
> 
> Thank you. So if I get it correct, if the client app(from which the data 
> is getting streamed/inserted) is in the same data center/zone as the 
> database (which is most of the time the case) then the batch insert does 
> not appear to be much beneficial.

No, the point is that once the client and the database are not on the 
same machine the network that they communicate across becomes a 
consideration. In a contrived example the client could be in the same 
same data center as the database server and talking to the server via a 
dialup modem and the data transfer would be worse then the same client 
talking to a database server a 1000 miles away across a fiber optic 
connection. This gets back to plan --> test.
                               /|\        |
                                |   <--  \|/

> 
> Which also means , people here were afraid of having triggers in such a 
> high dml table as because this will make the "batch insert" 
> automatically  converted into "row by row" behind the scene, but 
> considering the above results, it looks fine to go with a row by row 
> approach (but just having batch commit in place in place of row by row 
> commit). And not to worry about implementing the true batch insert 
> approach as that is not making a big difference here in data load 
> performance.

This is getting ahead of the game. The immediate issue is the deadlock 
issues with the concurrent sessions and duplicate data.

-- 
Adrian Klaver
adrian.klaver@aklaver.com