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@xxxxxxxxx> 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.mustkivi@xxxxxxxxx
