Re: UPDATE on 20 Million Records Transaction or not?

On 6/23/20 8:32 AM, Jason Ralph wrote:
>
> Hello List,
>
> PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (R
>
> ed Hat 4.4.7-23), 64-bit
>
> I am planning an update on a table with 20Million records, I have been
> researching the best practices.  I will remove all indexes and foreign
> keys prior to the update, however I am not sure if I should use a
> transaction or not.
>
> My thought process is that a transaction would be easier to recover if
> something fails, however it would take more time to write to the WAL log
> in a transaction.
>

Are you updating *every* row in the table?

Are you updating indexed fields?  (If not, then leave the indexes and FKs,
since they won't be touched.)

> Would it make sense to make a back up of the table then execute update
> without a transaction?
>

Always make a backup.

> How would you guys do it?
>

It depends on what percentage of the rows are being updated, which columns
are being updated and how big the records are.

--
Angular momentum makes the world go 'round.