Re: UPDATE on 20 Million Records Transaction or not?

>
> >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)
>>
>>
Just update them and be done with it. Do the work in batches if it doesn't
matter that concurrent accesses to the table might see some rows that have
old value and some new. Given you are on PG11 and can commit within a
function, you could adapt something like this to just run until done. Oh,
if you have not tuned the autovacuum process much, depending on the total
number of rows in your table, it could be good to manually vacuum once or
twice during this process so that space is reused.

*the below was shared by Andreas Krogh on one of these mailing lists about
6 months back.

do $_$
declare
num_rows bigint;
begin
loop
delete from YourTable where id in
(select id from YourTable where id <
500 limit 100);
commit;
get diagnostics num_rows = row_count;
raise notice 'deleted % rows', num_rows;
exit when num_rows = 0;
end loop;
end;$_$;