Feedback-ID: i76984098:Fastmail
Message-ID: <1dec4905-f6b7-4366-bdc0-fe1ce22bdacd@aklaver.com>
Date: Fri, 4 Oct 2024 13:49:03 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How batch processing works
To: Lok P <loknath.73@gmail.com>, 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>
 <CAKna9VZA5x1g7-4j8cQrW1ByqX5jtPwY7sY2MHuRgrxrOu4LBg@mail.gmail.com>
 <20240922192321.kpbe3p4ixmoid3ma@hjp.at>
 <CAKna9Vb39v+83mrsCV02W0yCKusW033rcc6bAOGKZj6qZBfsxQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKna9Vb39v+83mrsCV02W0yCKusW033rcc6bAOGKZj6qZBfsxQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/1dec4905-f6b7-4366-bdc0-fe1ce22bdacd%40aklaver.com>
Precedence: bulk


On 10/4/24 1:05 PM, Lok P wrote:
> 
> 
> On Mon, Sep 23, 2024 at 12:53 AM Peter J. Holzer <hjp-pgsql@hjp.at 
> <mailto:hjp-pgsql@hjp.at>> wrote:
> 
> 
>      > Thank you so much.
>      > I was expecting method-3(batch insert) to be the fastest or
>     atleast as you said
>      > perform with similar speed as method-2 (row by row insert with
>     batch commit)
> 
>     Oops, sorry! I wrote that the wrong way around. Method 3 is the fastest.
>     I guess I meant to write "method2 takes about twice as long as method3"
>     or something like that.
> 
> 
> As in case of batch insert below is the fastest one as it inserts 
> multiple rows in one statement. Similarly I understand, Delete can be 
> batched as below. However, can you suggest how an Update can be batched 
> in a simple/generic fashion in JDBC for an input data stream with 
> multiple input values. As because for an update if we write as below , 
> it will just do one row update at a time?
> 
> Update <table_name> SET column1=?,   column2=?, column3=? where 
> <PK_Column>=? ;

UPDATE table_name SET column1 = vals.text_val, column2=int_val FROM 
(VALUES (1, 'dog', 23),(2, 'cat', 44)) AS vals (id, text_val, int_val) 
where table_name.id = vals.id;

> 
> 
> INSERT INTO <table_name> VALUES  (1, 'a'), (2, 'a'),(3,'a');
> Delete from <table_name> where column_name  in (<value1>, 
> <value2>,<value3>...);
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com