RE: UPDATE on 20 Million Records Transaction or not? - Mailing list pgsql-general
| From | Jason Ralph |
|---|---|
| Subject | RE: UPDATE on 20 Million Records Transaction or not? |
| Date | |
| Msg-id | BN7PR04MB3826DE611F9BCE758CFF79E9D0940@BN7PR04MB3826.namprd04.prod.outlook.com Whole thread Raw |
| In response to | Re: UPDATE on 20 Million Records Transaction or not? (Michael Lewis <mlewis@entrata.com>) |
| List | pgsql-general |
>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;$_$;
Thanks to all the suggestions, I really like the function, I will test this. I have autovacuum fully tuned for this table, so should be good.
Jason Ralph
From: Michael Lewis <mlewis@entrata.com>
Sent: Tuesday, June 23, 2020 1:43 PM
To: Ganesh Korde <ganeshakorde@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org
Subject: 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;$_$;
pgsql-general by date: