Message-ID: <1955d0e6cafd643520d282a74d9956340983074e.camel@cybertec.at>
Subject: Re: update faster way
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: yudhi s <learnerdatabase99@gmail.com>, pgsql-general
	 <pgsql-general@lists.postgresql.org>
Date: Sat, 14 Sep 2024 09:39:14 +0200
In-Reply-To: <CAEzWdqd-22B-bpVdT3yzegiOig9zvJnfJvi=GOMFfHT-Jg8CgQ@mail.gmail.com>
References: 
	<CAEzWdqd-22B-bpVdT3yzegiOig9zvJnfJvi=GOMFfHT-Jg8CgQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/1955d0e6cafd643520d282a74d9956340983074e.camel%40cybertec.at>
Precedence: bulk

On Sat, 2024-09-14 at 08:43 +0530, yudhi s wrote:
> We have to update a column value(from numbers like '123' to codes like 'a=
bc'
> by looking into a reference table data) in a partitioned table with billi=
ons
> of rows in it, with each partition having 100's millions rows. As we test=
ed

> 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
>=20
> 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. fi=
ve
>    partitions will have the updates done at same time from 5 different
>    sessions)? And will it have any locking effect or we can just=C2=A0sta=
rt the
>    sessions and let them run without impacting our live transactions?

Option 1 doesn't exist.
Option 2 is possible, and you can even have more than one session workingr
on a single partition.

However, the strain on your system's resources and particularly the row
locks will impair normal database work.

Essentially, you can either take an extended down time or perform the updat=
es
in very small chunks with a very low "lock_timeout" over a very long period
of time.  If any of the batches fails because of locking conflicts, it has
to be retried.

Investigate with EXPLAIN (ANALYZE) why the updates take that long.  It coul=
d
be a lame disk, tons of (unnecessary?) indexes or triggers, but it might as
well be the join with the lookup table, so perhaps there is room for
improvement (more "work_mem" for a hash join?).

Yours,
Laurenz Albe