From 97c02c3389b31b2459ffa157c91b7515ee1f626b Mon Sep 17 00:00:00 2001 From: aschmitz Date: Mon, 2 Oct 2017 14:28:59 -0500 Subject: Make IdsToBigints (mostly!) non-blocking (#5088) * Make IdsToBigints (mostly!) non-blocking This pulls in GitLab's MigrationHelpers, which include code to make column changes in ways that Postgres can do without locking. In general, this involves creating a new column, adding an index and any foreign keys as appropriate, adding a trigger to keep it populated alongside the old column, and then progressively copying data over to the new column, before removing the old column and replacing it with the new one. A few changes to GitLab's MigrationHelpers were necessary: * Some changes were made to remove dependencies on other GitLab code. * We explicitly wait for index creation before forging ahead on column replacements. * We use different temporary column names, to avoid running into index name length limits. * We rename the generated indices back to what they "should" be after replacing columns. * We rename the generated foreign keys to use the new column names when we had to create them. (This allows the migration to be rolled back without incident.) # Big Scary Warning There are two things here that may trip up large instances: 1. The change for tables' "id" columns is not concurrent. In particular, the stream_entries table may be big, and does not concurrently migrate its id column. (On the other hand, x_id type columns are all concurrent.) 2. This migration will take a long time to run, *but it should not lock tables during that time* (with the exception of the "id" columns as described above). That means this should probably be run in `screen` or some other session that can be run for a long time. Notably, the migration will take *longer* than it would without these changes, but the website will still be responsive during that time. These changes were tested on a relatively large statuses table (256k entries), and the service remained responsive during the migration. Migrations both forward and backward were tested. * Rubocop fixes * MigrationHelpers: Support ID columns in some cases This doesn't work in cases where the ID column is referred to as a foreign key by another table. * MigrationHelpers: support foreign keys for ID cols Note that this does not yet support foreign keys on non-primary-key columns, but Mastodon also doesn't yet have any that we've needed to migrate. This means we can perform fully "concurrent" migrations to change ID column types, and the IdsToBigints migration can happen with effectively no downtime. (A few operations require a transaction, such as renaming columns or deleting them, but these transactions should not block for noticeable amounts of time.) The algorithm for generating foreign key names has changed with this, and therefore all of those changed in schema.rb. * Provide status, allow for interruptions The MigrationHelpers now allow restarting the rename of a column if it was interrupted, by removing the old "new column" and re-starting the process. Along with this, they now provide status updates on the changes which are happening, as well as indications about when the changes can be safely interrupted (when there are at least 10 seconds estimated to be left before copying data is complete). The IdsToBigints migration now also sorts the columns it migrates by size, starting with the largest tables. This should provide administrators a worst-case scenario estimate for the length of migrations: each successive change will get faster, giving admins a chance to abort early on if they need to run the migration later. The idea is that this does not force them to try to time interruptions between smaller migrations. * Fix column sorting in IdsToBigints Not a significant change, but it impacts the order of columns in the database and db/schema.rb. * Actually pause before IdsToBigints --- app/models/account_domain_block.rb | 4 ++-- app/models/block.rb | 6 +++--- app/models/conversation_mute.rb | 4 ++-- app/models/domain_block.rb | 2 +- app/models/favourite.rb | 6 +++--- app/models/follow.rb | 6 +++--- app/models/follow_request.rb | 6 +++--- app/models/import.rb | 4 ++-- app/models/mention.rb | 4 ++-- app/models/mute.rb | 6 +++--- app/models/report.rb | 6 +++--- app/models/setting.rb | 4 ++-- app/models/stream_entry.rb | 5 ++--- app/models/subscription.rb | 4 ++-- app/models/web/setting.rb | 4 ++-- 15 files changed, 35 insertions(+), 36 deletions(-) (limited to 'app/models') diff --git a/app/models/account_domain_block.rb b/app/models/account_domain_block.rb index bdd64c01a..fb695e473 100644 --- a/app/models/account_domain_block.rb +++ b/app/models/account_domain_block.rb @@ -3,11 +3,11 @@ # # Table name: account_domain_blocks # -# id :integer not null, primary key -# account_id :integer # domain :string # created_at :datetime not null # updated_at :datetime not null +# account_id :integer +# id :integer not null, primary key # class AccountDomainBlock < ApplicationRecord diff --git a/app/models/block.rb b/app/models/block.rb index edb0d2d11..a913782ed 100644 --- a/app/models/block.rb +++ b/app/models/block.rb @@ -3,11 +3,11 @@ # # Table name: blocks # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class Block < ApplicationRecord diff --git a/app/models/conversation_mute.rb b/app/models/conversation_mute.rb index 79299b995..8d2399adf 100644 --- a/app/models/conversation_mute.rb +++ b/app/models/conversation_mute.rb @@ -3,9 +3,9 @@ # # Table name: conversation_mutes # -# id :integer not null, primary key -# account_id :integer not null # conversation_id :integer not null +# account_id :integer not null +# id :integer not null, primary key # class ConversationMute < ApplicationRecord diff --git a/app/models/domain_block.rb b/app/models/domain_block.rb index aea8919af..1268290bc 100644 --- a/app/models/domain_block.rb +++ b/app/models/domain_block.rb @@ -3,12 +3,12 @@ # # Table name: domain_blocks # -# id :integer not null, primary key # domain :string default(""), not null # created_at :datetime not null # updated_at :datetime not null # severity :integer default("silence") # reject_media :boolean default(FALSE), not null +# id :integer not null, primary key # class DomainBlock < ApplicationRecord diff --git a/app/models/favourite.rb b/app/models/favourite.rb index 53c79ccea..d28d5c05b 100644 --- a/app/models/favourite.rb +++ b/app/models/favourite.rb @@ -3,11 +3,11 @@ # # Table name: favourites # -# id :integer not null, primary key -# account_id :integer not null -# status_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# status_id :integer not null # class Favourite < ApplicationRecord diff --git a/app/models/follow.rb b/app/models/follow.rb index 62f6fb670..667720a88 100644 --- a/app/models/follow.rb +++ b/app/models/follow.rb @@ -3,11 +3,11 @@ # # Table name: follows # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class Follow < ApplicationRecord diff --git a/app/models/follow_request.rb b/app/models/follow_request.rb index 458c3a2cd..60036d903 100644 --- a/app/models/follow_request.rb +++ b/app/models/follow_request.rb @@ -3,11 +3,11 @@ # # Table name: follow_requests # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class FollowRequest < ApplicationRecord diff --git a/app/models/import.rb b/app/models/import.rb index 4656c3af6..8ae7e3a46 100644 --- a/app/models/import.rb +++ b/app/models/import.rb @@ -3,8 +3,6 @@ # # Table name: imports # -# id :integer not null, primary key -# account_id :integer not null # type :integer not null # approved :boolean default(FALSE), not null # created_at :datetime not null @@ -13,6 +11,8 @@ # data_content_type :string # data_file_size :integer # data_updated_at :datetime +# account_id :integer not null +# id :integer not null, primary key # class Import < ApplicationRecord diff --git a/app/models/mention.rb b/app/models/mention.rb index 7450b1b85..3700c781c 100644 --- a/app/models/mention.rb +++ b/app/models/mention.rb @@ -3,11 +3,11 @@ # # Table name: mentions # -# id :integer not null, primary key -# account_id :integer # status_id :integer # created_at :datetime not null # updated_at :datetime not null +# account_id :integer +# id :integer not null, primary key # class Mention < ApplicationRecord diff --git a/app/models/mute.rb b/app/models/mute.rb index 00e5661a7..6e64848c7 100644 --- a/app/models/mute.rb +++ b/app/models/mute.rb @@ -3,11 +3,11 @@ # # Table name: mutes # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null +# id :integer not null, primary key +# target_account_id :integer not null # class Mute < ApplicationRecord diff --git a/app/models/report.rb b/app/models/report.rb index 479aa17bb..bffb42b48 100644 --- a/app/models/report.rb +++ b/app/models/report.rb @@ -3,15 +3,15 @@ # # Table name: reports # -# id :integer not null, primary key -# account_id :integer not null -# target_account_id :integer not null # status_ids :integer default([]), not null, is an Array # comment :text default(""), not null # action_taken :boolean default(FALSE), not null # created_at :datetime not null # updated_at :datetime not null +# account_id :integer not null # action_taken_by_account_id :integer +# id :integer not null, primary key +# target_account_id :integer not null # class Report < ApplicationRecord diff --git a/app/models/setting.rb b/app/models/setting.rb index 340552581..a14f156a1 100644 --- a/app/models/setting.rb +++ b/app/models/setting.rb @@ -3,13 +3,13 @@ # # Table name: settings # -# id :integer not null, primary key # var :string not null # value :text # thing_type :string -# thing_id :integer # created_at :datetime # updated_at :datetime +# id :integer not null, primary key +# thing_id :integer # class Setting < RailsSettings::Base diff --git a/app/models/stream_entry.rb b/app/models/stream_entry.rb index 44aac39b3..b51fe9ad7 100644 --- a/app/models/stream_entry.rb +++ b/app/models/stream_entry.rb @@ -1,16 +1,15 @@ # frozen_string_literal: true - # == Schema Information # # Table name: stream_entries # -# id :integer not null, primary key -# account_id :integer # activity_id :integer # activity_type :string # created_at :datetime not null # updated_at :datetime not null # hidden :boolean default(FALSE), not null +# account_id :integer +# id :integer not null, primary key # class StreamEntry < ApplicationRecord diff --git a/app/models/subscription.rb b/app/models/subscription.rb index 14f1a140c..39860196b 100644 --- a/app/models/subscription.rb +++ b/app/models/subscription.rb @@ -3,16 +3,16 @@ # # Table name: subscriptions # -# id :integer not null, primary key # callback_url :string default(""), not null # secret :string # expires_at :datetime # confirmed :boolean default(FALSE), not null -# account_id :integer not null # created_at :datetime not null # updated_at :datetime not null # last_successful_delivery_at :datetime # domain :string +# account_id :integer not null +# id :integer not null, primary key # class Subscription < ApplicationRecord diff --git a/app/models/web/setting.rb b/app/models/web/setting.rb index 04a049523..1b0bfb2b7 100644 --- a/app/models/web/setting.rb +++ b/app/models/web/setting.rb @@ -3,11 +3,11 @@ # # Table name: web_settings # -# id :integer not null, primary key -# user_id :integer # data :json # created_at :datetime not null # updated_at :datetime not null +# id :integer not null, primary key +# user_id :integer # class Web::Setting < ApplicationRecord -- cgit