diff options
author | Eugen Rochko <eugen@zeonfederated.com> | 2018-11-19 00:43:52 +0100 |
---|---|---|
committer | GitHub <noreply@github.com> | 2018-11-19 00:43:52 +0100 |
commit | d6b9a62e0a13497b8cc40eb1db56d1ec2b3760ea (patch) | |
tree | f8627888dd9dce43f66427261a90a3e9886b4df7 /db/migrate | |
parent | 4fdefffb9906ffc3e5fde7af652674bebffd6e15 (diff) |
Extract counters from accounts table to account_stats table (#9295)
Diffstat (limited to 'db/migrate')
-rw-r--r-- | db/migrate/20181116165755_create_account_stats.rb | 12 | ||||
-rw-r--r-- | db/migrate/20181116173541_copy_account_stats.rb | 54 |
2 files changed, 66 insertions, 0 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 |