What is the best way to make big changes to the database structure?

Question

Yii

There is a project on yii 1.x, there was a need to make quite a few changes to the database: earlier, some of the properties of objects were stored as rows in the same table, but now they have been transferred to related tables, so all objects need to be updated. So far, there are two ways:
1) Standard migrations - you can stuff everything there, but (a) update a couple of thousand objects in this way, IMHO, somehow wrong (or is it still right?) and (b) there will be too much garbage in the console ( sql queries), which are of no interest, but are a great hindrance (although you can override $this->execute()other methods and thereby get rid of garbage)
2) Write a console command to update.
What to choose or are there other options?

Answer 1

That's what migrations are for. And they are essentially executed as console commands. Regarding the garbage in the console - what's the difference to you? Migrations should just roll correctly, for me they generally roll in the ci server and I don’t see what’s going on there at all. Only if something goes wrong, the log will be thrown off by email.
If you write a console command, then after a while, if you need to update something else, you will need to add support for versioning the structure and, in fact, you will write another migrations component. So why?

Answer 2

Theoretically correct 1. Practically - the second option.
The choice must be made depending on whether the site is in production or not. What will provide a smaller downtime - then choose.
Once I had to change a bunch of 1 to 1 to 1 to many, on tables with tens of millions of records. It was implemented:
1. step - created new tables
2. update trigger (to change both old and new data).
3. launching the background update console script (it took 7 hours, but it did not load the database much)
4. replacing the code that worked with the old structure with the new one.
5. deleting old data.
Total downtime = 0 seconds.
Total: update time 10 hours. No downtime
On development machines (with almost the same data)
1. step - create new tables
2. run the update console script (2 hours were executed, while loading the database, locking the tables, and nothing worked at all)
3. replacing the code that worked from the old structure to the new one.
4. deleting old data.
Total: update time 2 hours hours. Downtime 2 hours