Batch update million records in prd DB

[Yi Sun <yinan81@xxxxxxxxx>]

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