Feedback-ID: i76984098:Fastmail
Message-ID: <55697e68-ee97-4571-9b71-c91a9373d40b@aklaver.com>
Date: Sat, 21 Sep 2024 10:20:27 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: IO related waits
To: veem v <veema0000@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
 <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com>
 <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
 <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
 <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com>
 <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>
 <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com>
 <bc8363b5-28d0-4218-b3d6-73c7d78717da@aklaver.com>
 <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
 <4cfe720e-4283-43fc-8d72-8fa11e2f741a@aklaver.com>
 <20240920221718.ctq5whqfyqlrjwr2@hjp.at>
 <CAB+=1TU7FcET2RDV0W969B3q0UB3xwiEVdPmqLybBmfqU2VC8w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TU7FcET2RDV0W969B3q0UB3xwiEVdPmqLybBmfqU2VC8w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/55697e68-ee97-4571-9b71-c91a9373d40b%40aklaver.com>
Precedence: bulk

On 9/21/24 02:36, veem v wrote:
> 
> 

> 
> Actually here the application is using kafka and  flink stream and is 
> using one of existing code in which it was doing row by row commit which 
> is now changed to Batch commit i.e. the commit point is shifted from row 
> by row to batch now. There are multiple sessions spawning at the same 
> time to process the incoming messages 24/7. And also as described in 
> another ongoing thread and also we saw in the past we did not see much 
> difference between "batch commit" and "Batch insert" performance. We 
> only realized the row by row commit is having worse performance.

The bottom line is that to solve this a cost is going to have to be paid 
somewhere. Previously  it was done with autocommit in the form of slow 
insert performance. You improved the speed of the inserts by wrapping 
multiple inserts in transactions and that led you to this problem, where 
open transactions across sessions is leading to deadlock issues due to 
the same id being inserted in concurrent open sessions. Per my and Greg 
Sabino Mullane comments the solution is going to need planning. Right 
now you are playing a round of Whack-a-Mole by making ad-hoc changes of 
portions of the process without reference to the process as a whole.At 
some point the parallel duplicate ids(records) need to be straightened 
out into a serial application of data. You and the other people involved 
need to come up with a coherent view of the process as whole with a goal 
to achieving that. Then you can start planning on where that cost is 
best paid: 1) In the data before the streaming. 2) In the streaming 
process itself 3) In the database or 4) Spread out across 1-4.

> 
> Now, in such a scenario when the messages are coming from a streaming 
> framework using kafka +flink and also the insert is happening using row 
> by row only (but just the commit is shifted to batch), i don't see any 
> way to sort the ID columns in this streaming process, so that they 
> won't overlap across session.
> 
> In such a situation , the only way seems to have the messages replayed 
> for which the deadlock error happens , as I think during a deadlock 
> error, one session gets terminated by postgres and that messages perhap 
> we can save in some exception table and then replay?
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com