Derpibooru
Filters (Default) SettingsRegisterLogin

Posts

For more information, see the search syntax documentation. Search results are sorted by creation date.

Search Results

Site and Policy » "The Largest Database Migration I Have Ever Written" » Post 3

byte[]
Solar Supporter - Fought against the New Lunar Republic rebellion on the side of the Solar Deity (April Fools 2023).
Non-Fungible Trixie -
Verified Pegasus - Show us your gorgeous wings!
Preenhub - We all know what you were up to this evening~
An Artist Who Rocks - 100+ images under their artist tag
Artist -

Philomena Contributor
To answer another question:
 
The dates that show up in the changelog have no real connection to the date that code actually makes it live (though they may be well-correlated). They are just commit dates.
Posted Report

Site and Policy » "The Largest Database Migration I Have Ever Written" » Post 2

Site and Policy » "The Largest Database Migration I Have Ever Written" » Post 1

byte[]
Solar Supporter - Fought against the New Lunar Republic rebellion on the side of the Solar Deity (April Fools 2023).
Non-Fungible Trixie -
Verified Pegasus - Show us your gorgeous wings!
Preenhub - We all know what you were up to this evening~
An Artist Who Rocks - 100+ images under their artist tag
Artist -

Philomena Contributor
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 references** 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.
Posted Report
Edited

Site and Policy » "The Largest Database Migration I Have Ever Written" » Topic Opener

GenericArchangel
Alicorn Squadron - For supporting the site
Pixel Perfection - I still call her Lightning Bolt
Solar Supporter - Fought against the New Lunar Republic rebellion on the side of the Solar Deity (April Fools 2023).
Princess of Love - Extra special version for those who participated in the Canterlot Wedding 10th anniversary event by contributing art.
Artist -
Elements of Harmony - Had an OC in the 2022 Community Collab
Non-Fungible Trixie -
Preenhub - We all know what you were up to this evening~
Twinkling Balloon - Took part in the 2021 community collab.
Bronze Supporter - Bronze Supporter
My Little Pony - 1992 Edition
Friendship, Art, and Magic (2020) - Took part in the 2020 Community Collab
A Perfectly Normal Pony - I will get that badge one day even if it's the death of me.
Magical Inkwell - Wrote MLP fanfiction consisting of at least around 1.5k words, and has a verified link to the platform of their choice
Wallet After Summer Sale -

Moderator
Free the Tantabus
So I was looking through the changelog to see if I could figure out the exact reason the site was updated, and noticed this gem with no additional context. I assume this is something fairly important to how the site works, even though there have been no significant end-user changes or issues (congrats to the dev team on that, btw). So, out of curiosity: what was that update, and what is the whole database migration thing about?
 
(yes I see that was two days ago, but I presume the change “deployed” is related)
Posted Report
Edited
Showing results 1 - 4 of 4 total

Default search

If you do not specify a field to search over, the search engine will search for posts with a body that is similar to the query's word stems. For example, posts containing the words winged humanization, wings, and spread wings would all be found by a search for wing, but sewing would not be.

Allowed fields

Field SelectorTypeDescriptionExample
authorLiteralMatches the author of this post. Anonymous authors will never match this term.author:Joey
bodyFull TextMatches the body of this post. This is the default field.body:test
created_atDate/Time RangeMatches the creation time of this post.created_at:2015
idNumeric RangeMatches the numeric surrogate key for this post.id:1000000
myMetamy:posts matches posts you have posted if you are signed in. my:posts
subjectFull TextMatches the title of the topic.subject:time wasting thread
topic_idLiteralMatches the numeric surrogate key for the topic this post belongs to.topic_id:7000
topic_positionNumeric RangeMatches the offset from the beginning of the topic of this post. Positions begin at 0.topic_position:0
updated_atDate/Time RangeMatches the creation or last edit time of this post.updated_at.gte:2 weeks ago
user_idLiteralMatches posts with the specified user_id. Anonymous users will never match this term.user_id:211190
forumLiteralMatches the short name for the forum this post belongs to.forum:meta