diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/migrate/20181116165755_create_account_stats.rb | 12 | ||||
| -rw-r--r-- | db/migrate/20181116173541_copy_account_stats.rb | 54 | ||||
| -rw-r--r-- | db/post_migrate/20181116184611_copy_account_stats_cleanup.rb | 13 | ||||
| -rw-r--r-- | db/schema.rb | 16 |
4 files changed, 91 insertions, 4 deletions
diff --git a/db/migrate/20181116165755_create_account_stats.rb b/db/migrate/20181116165755_create_account_stats.rb new file mode 100644 index 000000000..a798e8166 --- /dev/null +++ b/db/migrate/20181116165755_create_account_stats.rb @@ -0,0 +1,12 @@ +class CreateAccountStats < ActiveRecord::Migration[5.2] + def change + create_table :account_stats do |t| + t.belongs_to :account, null: false, foreign_key: { on_delete: :cascade }, index: { unique: true } + t.bigint :statuses_count, null: false, default: 0 + t.bigint :following_count, null: false, default: 0 + t.bigint :followers_count, null: false, default: 0 + + t.timestamps + end + end +end diff --git a/db/migrate/20181116173541_copy_account_stats.rb b/db/migrate/20181116173541_copy_account_stats.rb new file mode 100644 index 000000000..bb523fbbd --- /dev/null +++ b/db/migrate/20181116173541_copy_account_stats.rb @@ -0,0 +1,54 @@ +class CopyAccountStats < ActiveRecord::Migration[5.2] + disable_ddl_transaction! + + def up + safety_assured do + if supports_upsert? + up_fast + else + up_slow + end + end + end + + def down + # Nothing + end + + private + + def supports_upsert? + version = select_one("SELECT current_setting('server_version_num') AS v")['v'].to_i + version >= 90500 + end + + def up_fast + say 'Upsert is available, importing counters using the fast method' + + Account.unscoped.select('id').find_in_batches(batch_size: 5_000) do |accounts| + execute <<-SQL.squish + INSERT INTO account_stats (account_id, statuses_count, following_count, followers_count, created_at, updated_at) + SELECT id, statuses_count, following_count, followers_count, created_at, updated_at + FROM accounts + WHERE id IN (#{accounts.map(&:id).join(', ')}) + ON CONFLICT (account_id) DO UPDATE + SET statuses_count = EXCLUDED.statuses_count, following_count = EXCLUDED.following_count, followers_count = EXCLUDED.followers_count + SQL + end + end + + def up_slow + say 'Upsert is not available in PostgreSQL below 9.5, falling back to slow import of counters' + + # We cannot use bulk INSERT or overarching transactions here because of possible + # uniqueness violations that we need to skip over + Account.unscoped.select('id, statuses_count, following_count, followers_count, created_at, updated_at').find_each do |account| + begin + params = [[nil, account.id], [nil, account.statuses_count], [nil, account.following_count], [nil, account.followers_count], [nil, account.created_at], [nil, account.updated_at]] + exec_insert('INSERT INTO account_stats (account_id, statuses_count, following_count, followers_count, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6)', nil, params) + rescue ActiveRecord::RecordNotUnique + next + end + end + end +end diff --git a/db/post_migrate/20181116184611_copy_account_stats_cleanup.rb b/db/post_migrate/20181116184611_copy_account_stats_cleanup.rb new file mode 100644 index 000000000..9267e9b2c --- /dev/null +++ b/db/post_migrate/20181116184611_copy_account_stats_cleanup.rb @@ -0,0 +1,13 @@ +# frozen_string_literal: true + +class CopyAccountStatsCleanup < ActiveRecord::Migration[5.2] + disable_ddl_transaction! + + def change + safety_assured do + remove_column :accounts, :statuses_count, :integer, default: 0, null: false + remove_column :accounts, :following_count, :integer, default: 0, null: false + remove_column :accounts, :followers_count, :integer, default: 0, null: false + end + end +end diff --git a/db/schema.rb b/db/schema.rb index 9e718615f..f39b74a78 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -10,7 +10,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 2018_10_26_034033) do +ActiveRecord::Schema.define(version: 2018_11_16_184611) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -56,6 +56,16 @@ ActiveRecord::Schema.define(version: 2018_10_26_034033) do t.index ["target_account_id"], name: "index_account_pins_on_target_account_id" end + create_table "account_stats", force: :cascade do |t| + t.bigint "account_id", null: false + t.bigint "statuses_count", default: 0, null: false + t.bigint "following_count", default: 0, null: false + t.bigint "followers_count", default: 0, null: false + t.datetime "created_at", null: false + t.datetime "updated_at", null: false + t.index ["account_id"], name: "index_account_stats_on_account_id", unique: true + end + create_table "accounts", force: :cascade do |t| t.string "username", default: "", null: false t.string "domain" @@ -85,9 +95,6 @@ ActiveRecord::Schema.define(version: 2018_10_26_034033) do t.boolean "suspended", default: false, null: false t.boolean "locked", default: false, null: false t.string "header_remote_url", default: "", null: false - t.integer "statuses_count", default: 0, null: false - t.integer "followers_count", default: 0, null: false - t.integer "following_count", default: 0, null: false t.datetime "last_webfingered_at" t.string "inbox_url", default: "", null: false t.string "outbox_url", default: "", null: false @@ -640,6 +647,7 @@ ActiveRecord::Schema.define(version: 2018_10_26_034033) do add_foreign_key "account_moderation_notes", "accounts", column: "target_account_id" add_foreign_key "account_pins", "accounts", column: "target_account_id", on_delete: :cascade add_foreign_key "account_pins", "accounts", on_delete: :cascade + add_foreign_key "account_stats", "accounts", on_delete: :cascade add_foreign_key "accounts", "accounts", column: "moved_to_account_id", on_delete: :nullify add_foreign_key "admin_action_logs", "accounts", on_delete: :cascade add_foreign_key "backups", "users", on_delete: :nullify |