Add field to existing documents over million records

Question

Scenario

We have over 5 million document in a bucket and all of it has nested JSON with a simple uuid key. We want to add one extra field to ALL of the documents.

Example

ee6ae656-6e07-4aa2-951e-ea788e24856a
{
   "field1":"data1",
   "field2":{
      "nested_field1":"data2"
   }
}

After adding extra field

ee6ae656-6e07-4aa2-951e-ea788e24856a
{
   "field1":"data1",
   "field3":"data3",
   "field2":{
      "nested_field1":"data2"
   }
}

It has only one Primary Index: CREATE PRIMARY INDEX idx FOR bucket.

Problem

It takes ages. We tried it with n1ql, UPDATE bucket SET field3 = data3. Also sub-document mutation. But all of it takes hours. It's written in Go so we could put it into a goroutine, but it's still too much time.

Question

Is there any solution to reduce that time?

Answer 1

As you need to add new field, not modifying any existing field it is better to use SDKs SUBDOC API vs N1QL UPDATE (It is whole document update and require fetch the document).

The Best option will be Use N1QL get the document keys then use SDK SUBDOC API to add the field you need. You can use reactive API(asynchronously)

You have 5M documents and have primary index use following

val = ""
In loop
    SELECT RAW META().id FROM mybucket WHERE META().id > $val  LIMIT 10000;
    SDK SUBDOC update
    val = last value from the SELECT

https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/