Update by query is very slow in elastic search

I am trying to copy the data of one field with object type to another field with nested type, using painless script and _update_by_query. It took my Elasticsearch cluster ca. 48 hours to copy over 8.4M worth of data, and I want to speed up this operation.

This is the URL:

localhost:9200/myindex/_update_by_query?conflicts=proceed&requests_per_second=50

and here is the body:

{
    "query": {
        "match_all": {}
    },
    "script": {
        "inline": "ctx._source.myfield_copy = ctx._source.myfield;"
    }
}

Any way to speed up this operation?

By specifying requests_per_second you're throttling the operation.

Since the default bath size is 1000, and admitting the write time for 50 records is 100ms (hypothesis, your mileage may vary), we have

target_time = 1000 / 50 per second = 20 seconds
wait_time = target_time - write_time = 20 seconds - 0.1 seconds = 19.9 seconds

So you're basically waiting 19.9 artificial seconds between each batch, which is probably why it takes so long. Just remove requests_per_second altogether and that should already help a lot.

If you're just copying a field into another, another performance improvement would be to not do it with a script but with an ingest pipeline

PUT _ingest/pipeline/copy-field
{
  "processors": [
    {
      "set": {
        "field": "myfield_copy",
        "copy_from": "myfield"
      }
    }
  ]
}

And then simply run update by query by referencing that pipeline:

POST myindex/_update_by_query?pipeline=copy-field&wait_for_completion=false