We have come across a situation where we need to separate one column (old_col) into 3 different columns (new_col1, new_col2, new_col3) in the SQL db.
We have already come up with a business logic of separating the value inside old_col into the new_cols.
Current Data Migration Strategy:-
Use existing REST API to update(PUT) all the rows. The update API has the business logic built in it.
Cons:
taking too long
unnecessary load on the server
Proposed Strategy:-
Directly update the DB values using a script
Cons:
- Need to invalidate the cache
- Multiple services reading from the DB
I know this is very little information, but in general what is the best strategy to go ahead with the problem statement and what else can we do/explore to reduce our risks?
Backend: GoLang
DB: SQL
No of Records: 5 million
