Re: UPDATE on 20 Million Records Transaction or not?

On 6/23/20 6: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.

Unless this is an UNLOGGED table WALs will be written.

> 
> Would it make sense to make a back up of the table then execute update 
> without a transaction? How would you guys do it?

You could break it down into multiple transactions if there is way to 
specify ranges of records.

> 
> Thanks,
> 
> *Jason Ralph*
> 
> This message contains confidential information and is intended only for 
> the individual named. If you are not the named addressee you should not 
> disseminate, distribute or copy this e-mail. Please notify the sender 
> immediately by e-mail if you have received this e-mail by mistake and 
> delete this e-mail from your system. E-mail transmission cannot be 
> guaranteed to be secure or error-free as information could be 
> intercepted, corrupted, lost, destroyed, arrive late or incomplete, or 
> contain viruses. The sender therefore does not accept liability for any 
> errors or omissions in the contents of this message, which arise as a 
> result of e-mail transmission. If verification is required please 
> request a hard-copy version.


-- 
Adrian Klaver
adrian.klaver@aklaver.com