Viewing last 25 versions of post by byte[] in topic "The Largest Database Migration I Have Ever Written"

This migration didn't actually change anything fundamental about the way the application code works. This migration is huge because six things were accomplished at once, and it required a fair amount of testing beforehand to ensure it would apply correctly.



 
 
1. Delete 9 unnecessary / unused indices on 8 tables
 
2. Create 32 necessary indices for cascaded updates and deletes in step 6
 
3. Delete data with inappropriate SQL @`NULL@` values==\*== as a prerequisite for step 5
 
4. Delete data with broken referential conestraints==\*\*== as a prerequisite for step 6
 
5. Add @`NOT NULL@` constraints to 218 columns across 30 tables
 
6. Add @`FOREIGN KEY@` referential constraints to 71 columns across 37 tables



 
 
In total, the completed run of this migration took exactly 548 seconds to apply from start to finish. There was about a 20 minute delay at the beginning while I waited for @`pg_dump@` to complete. I set the parallelism as high as I could get it, but I do not think I can get it to dump any faster, unfortunately.



 
 
Some hitches that I ran into along the way that required me to restart the migration:


 
- One post had a @`topic_position@` that was @`NULL@`, and my code to set a meaningful value there was broken. I fixed this with 2bf1b1dc57 and restarted the migration.
 
- A few Rails-generated foreign key names were different in development than in production, which threw a wrench into the works several times while I had to drop those constraints. I knew this was going to happen before I started, and tried to account for it in 95d509ed4b, but it did not break the way I expected it to.



 
 
Overall, that's pretty much it.


==
 
 
\*== A row in source_changes with a @`NULL@` @`image_id@` is inappropriate because it is meaningless.
==  
\*\*== A row in source_changes with an @`image_id@` that exists, but does not point to any known ID number, is inappropriate because it is meaningless.

This migration didn't actually change anything fundamental about the way the application code works. This migration is huge because six things were accomplished at once, and it required a fair amount of testing beforehand to ensure it would apply correctly.


1. Delete 9 unnecessary / unused indices on 8 tables
2. Create 32 necessary indices for cascaded updates and deletes in step 6
3. Delete data with inappropriate SQL @NULL@ values==*== as a prerequisite for step 5
4. Delete data with referential constraints==**== as a prerequisite for step 6
5. Add @NOT NULL@ constraints to 218 columns across 30 tables
6. Add @FOREIGN KEY@ referential constraints to 71 columns across 37 tables


In total, the completed run of this migration took exactly 548 seconds to apply from start to finish. There was about a 20 minute delay at the beginning while I waited for @pg_dump@ to complete. I set the parallelism as high as I could get it, but I do not think I can get it to dump any faster, unfortunately.


Some hitches that I ran into along the way that required me to restart the migration:

- One post had a @topic_position@ that was @NULL@, and my code to set a meaningful value there was broken. I fixed this with 2bf1b1dc57 and restarted the migration.
- A few Rails-generated foreign key names were different in development than in production, which threw a wrench into the works several times while I had to drop those constraints. I knew this was going to happen before I started, and tried to account for it in 95d509ed4b, but it did not break the way I expected it to.


Overall, that's pretty much it.


==*== A row in source_changes with a @NULL@ @image_id@ is inappropriate because it is meaningless.
==**== A row in source_changes with an @image_id@ that exists, but does not point to any known ID number, is inappropriate because it is meaningless.