Re: Batch update million records in prd DB

Hi Yi,

I found that in postgres the memory is slowly eaten away when doing
updates within plsql loop. It only gets released once the whole block
completes. While it is ok for small tables you will eventually run out
of memory for really big ones. The working approach was to do the loop
in e.g a python script that called the DML statements and also called
commit. Several million rows is fortunately relatively small number to
update but once you get to billions this approach would not likely
work. Note that after each batch you also should call VACUUM before
starting a new one to avoid significant table bloat.

BR,

Kristjan

On Wed, Feb 24, 2021 at 3:01 PM Yi Sun <yinan81(at)gmail(dot)com> wrote:
>
> Hello,
>
> Now need to update several million records in a table in prd DB, if can use batch update 1000 records and commit each time, if it will affect prd application like below sample script please?
>
> Sample script:
>
> DO $MAIN$
> DECLARE
> affect_count integer;
> chunk_size CONSTANT integer :=1000;
> sleep_sec CONSTANT numeric :=0.1;
> BEGIN
>
> loop
>
> exit when affect_count=0;
>
> UPDATE tbl a
> SET name = ''
> WHERE a.id IN (SELECT id
> FROM tbl b
> WHERE name IS NULL
> LIMIT chunk_size);
>
> GET DIAGNOSTICS affect_count = ROW_COUNT;
>
> commit;
>
> PERFORM pg_sleep(sleep_sec);
>
> end loop;
> END;
> $MAIN$;
>
> Thanks and best regards

--
Kristjan Mustkivi

Email: kristjan(dot)mustkivi(at)gmail(dot)com