I have a SQL database (postgres using amazon RDS) on production with several records, some tables go up to a few millions rows and need to have some structure changes, in my particular case I just want to copy a field from a JSONb field to a column of its own using an UPDATE query.
The issue is that we added a migration file (actually using sequelize, but a common SQL file would be the same in this case) to the deployment pipeline. In this particular case it is applied to 35,000 rows and this keeps giving us timeout.
What would be the best solution to apply this migration without hitting this timeout?
- Just run it as part of the deployment (this didn't work)
- Scheduled downtime (would prefer to avoid this option)
- Do batches on runtime (eg. using a cron job/an endpoint/... to trigger X amount)
- Create a replica, run the migration there and then promote it (I would need to look up more info on how to do this)
- Other options
Thanks