MIME-Version: 1.0
References: <CAEzWdqd-22B-bpVdT3yzegiOig9zvJnfJvi=GOMFfHT-Jg8CgQ@mail.gmail.com>
In-Reply-To: <CAEzWdqd-22B-bpVdT3yzegiOig9zvJnfJvi=GOMFfHT-Jg8CgQ@mail.gmail.com>
From: Muhammad Usman Khan <usman.k@bitnine.net>
Date: Tue, 17 Sep 2024 10:36:50 +0500
Message-ID: <CAPnRvGs0VBL=2Huz4qChHdh+wKO-CuimrKzZt3xFiRHxkAc5vA@mail.gmail.com>
Subject: Re: update faster way
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000064b6d706224a14c5"
Archived-At: <https://www.postgresql.org/message-id/CAPnRvGs0VBL%3D2Huz4qChHdh%2BwKO-CuimrKzZt3xFiRHxkAc5vA%40mail.gmail.com>
Precedence: bulk

--00000000000064b6d706224a14c5
Content-Type: text/plain; charset="UTF-8"

Hi,

You can solve this problem using Citus in PostgreSQL, which is specifically
designed for parallelism

SELECT create_distributed_table('tab_part1', 'partition_key');
SELECT create_distributed_table('reference_tab', 'reference_key');

UPDATE tab_part1
SET column1 = reftab.code
FROM reference_tab reftab
WHERE tab_part1.column1 = reftab.column1;


On Sat, 14 Sept 2024 at 08:22, yudhi s <learnerdatabase99@gmail.com> wrote:

> Hello,
> We have to update a column value(from numbers like '123' to codes like
> 'abc' by looking into a reference table data) in a partitioned table with
> billions of rows in it, with each partition having 100's millions rows. As
> we tested for ~30million rows it's taking ~20minutes to update. So if we go
> by this calculation, it's going to take days for updating all the values.
> So my question is
>
> 1) If there is any inbuilt way of running the update query in parallel
> (e.g. using parallel hints etc) to make it run faster?
> 2) should we run each individual partition in a separate session (e.g.
> five partitions will have the updates done at same time from 5 different
> sessions)? And will it have any locking effect or we can just start the
> sessions and let them run without impacting our live transactions?
>
> UPDATE tab_part1
> SET column1 = reftab.code
> FROM reference_tab reftab
> WHERE tab_part1.column1 = subquery.column1;
>
> Regards
> Yudhi
>

--00000000000064b6d706224a14c5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div dir=3D"ltr" class=3D"gmail_signature" data-smart=
mail=3D"gmail_signature"><div dir=3D"ltr"><div>Hi,</div><div><br>You can so=
lve this problem using Citus in PostgreSQL, which is specifically designed =
for parallelism<br><br>SELECT create_distributed_table(&#39;tab_part1&#39;,=
 &#39;partition_key&#39;);<br>SELECT create_distributed_table(&#39;referenc=
e_tab&#39;, &#39;reference_key&#39;);<br><br>UPDATE tab_part1<br>SET column=
1 =3D reftab.code<br>FROM reference_tab reftab<br>WHERE tab_part1.column1 =
=3D reftab.column1;<br></div></div></div></div><br></div><br><div class=3D"=
gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 14 Sept 2024 at =
08:22, yudhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerda=
tabase99@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex"><div dir=3D"ltr">Hello,<br>We have to update a column val=
ue(from numbers like &#39;123&#39; to codes like &#39;abc&#39; by looking i=
nto a reference table data) in a partitioned table with billions of rows in=
 it, with each partition having 100&#39;s millions rows. As we tested for ~=
30million rows it&#39;s taking ~20minutes to update. So if we go by this ca=
lculation, it&#39;s going to take days for updating all the values. So my q=
uestion is<br><br>1) If there is any inbuilt way of running the update quer=
y in parallel (e.g. using parallel hints etc) to make it run faster?<br>2) =
should we run each individual partition in a separate session (e.g. five pa=
rtitions will have the updates done at same time from 5 different sessions)=
? And will it have any locking effect or we can just=C2=A0start the session=
s and let them run without impacting our live transactions?<br><br>UPDATE t=
ab_part1<br>SET column1 =3D reftab.code<br>FROM reference_tab reftab<br>WHE=
RE tab_part1.column1 =3D subquery.column1;<br><div><br></div><div>Regards</=
div><div>Yudhi</div></div>
</blockquote></div>

--00000000000064b6d706224a14c5--