I have an existing table with all the data, around 1 million records.
I needed to add GSI to the table to better query some data. However, with app logic the GSI is being populated only for new records. The partition key and sort key for GSI can be populated using other attributes of the record.
What is the most efficient/ recommended way to back fill the GSI PK and SK for the existing records in the table?
How you would approach backfilling the table would ultimately depend on the data size.
If your data size is in low GB's, then you can achieve it quite easily using Lambda/EC2/Local Machine etc....
You would need to Scan or Parallel Scan all the items in the table, filter out the items which do not require updating and then proceed to call UpdateItem on each item in the result set and append the GSI keys.
If however, you have a large amount of data you may want to use a distributed system such as AWS Glue and Spark. Here, you would either read all the items directly from the table or read from an S3 Export and again obtain the keys which require updating. Then using Sparks forEachPartition to distribute UpdateItem across executors to update items.
As with most services, there is a performance vs cost trade off. If you want to go fast, you'll likely incur more cost. If cost is an important factor for you, then you may want to do things slower. That would entail using a rate limited Scan rather than using Scan/Parallel Scan or even the Glue approach.
Rate limiting can be achieved using the Guava library in Java for example. This would ensure you don't consume too much capacity too quickly.
For larger tables its a little more difficult, as long running processes can die for various reasons, for that reason I would continually checkpoint which data you have read to avoid duplicate work when your restart your process.
Another important cost component is to use provisioned capacity mode when doing any sort of backfilling or data loading. On-demand mode is pay-per-request and for that reason it would not matter how fast or slow you go as you pay for each item read and written. Provisioned capacity mode offers significant cost savings over the alternative.
