Re: UPDATE on 20 Million Records Transaction or not?

You can try this

First take backup of table then execute below statements.

create table members_temp
as
select <other_columns>,'2038-01-18' regdate from members where regdate =
'2020-07-07';

delete from members where regdate = '2020-07-07';

insert into members select * from members_temp ;

drop table members_temp;

Regards,
Ganesh Korde.

On Tue, Jun 23, 2020 at 9:06 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

>
> I'd make a copy of the table, and test how long the various methods take.
>
> On 6/23/20 10:17 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?
>
> No I am using an update like so: UPDATE members SET regdate='2038-01-18'
> WHERE regdate='2020-07-07'
>
> DB=# select count(*) from members where regdate = '2020-07-07';
>
> count
>
> ----------
>
> 17333090
>
> (1 row)
>
> >Are you updating indexed fields? (If not, then leave the indexes and
> FKs, since they won't be touched.)
>
> Just checked regdate is not indexed so I will leave them in place.
>
>
>
>
> Would it make sense to make a back up of the table then execute update
> without a transaction?
>
>
> >Always make a backup.
>
> Agreed
>
>
> 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.
>
> Please see above, thanks
>
>
>
> *Jason Ralph*
>
>
>
> *From:* Ron <ronljohnsonjr(at)gmail(dot)com> <ronljohnsonjr(at)gmail(dot)com>
> *Sent:* Tuesday, June 23, 2020 10:57 AM
> *To:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* 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.
> 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.
>
>
> --
> Angular momentum makes the world go 'round.
>