TL;DR

UNIQUE INDEXによってDBレコードの一意性が保たれている場合、
そのINDEXを削除すると一意性の制約が失われる。
＝意図しない重複が発生し、不整合を起こす危険がある。

PgHeroは書き込み速度向上のため、利用のないインデックスや
他のインデックスによってカバーされている(Duplicate)インデックスについて
削除を促すが、仮にUNIQUE INDEXを削除すると上記の理由で不整合が起き得る。

上記を踏まえ
Mastodonの運用上、これらのPgHeroからのアドバイスは安心して無視していい。

伝えたいことは以上だが、情報共有のため
・私がDuplicate / UNUSEDなINDEXを削除するに至った経緯(ポエム)
・INDEX削除手順
・INDEX再作成手順
を記載する。

環境

Mastodonサーバーをかれこれ3年ほど運用している。
ほぼおひとり様状態だが、振り返ってみると色んな事が起きた。
Docker利用時にDBを吹き飛ばしたり、サーバーの引っ越しも2回ほどやった。
たしか、さくら:CentOS -> CloudGarage:Ubuntu -> AWS Lightsail:Debian　という流れだ。
その間にMastodon自体も大きく変わった。v1 -> v2のメジャーバージョンアップも経験し、
Ostatusからの脱却、ActivityPubへの遷移などなど、振り返ると感慨深い。

Mastodon : v3.2.0
ruby : 2.6.6p146

経緯

PgHeroからのメッセージ

MastodonにはPgHeroが入っており、DBの状況をWebから確認することが出来る。
これは非常に便利で、DBのサイズや増加傾向、コネクション数などを気軽に確認できる。

PgHeroのOverviewページはDB全体の概況を知らせてくれるが、
duplicate indexesとslow queryについて黄色で警告が出ている。
DB素人の私には、スロークエリの改善は見るからに荷が重いから無視するとして、
duplicate indexesについては以下のように書いてある。

These indexes exist, but aren’t needed. Remove them with a migration for faster writes.

PgHero「このIndex、必要ないから消した方が書き込みが早くなるお！」

また、Spaceページにはこう書いてある。

16 unused index. Remove them with a migration for faster writes.

PgHero「使ってないIndex、migrationで消した方が書き込みが早くなるお！」

DB素人の私からすると、インデックスというのはクエリを高速化するために付けるもので、
それを消したところでレコードに影響は与えないと思っていた。
PgHeroがわざわざ警告するのだし、消すと書き込みが早くなる？らしいので
それじゃあ消してしまおうと考えたのである。

また、これは経験則だが、長期間運用し続けて様々な環境を経験してきた
サーバーには、ジワジワと過去の【遺産】が溜まるものだ。
かつては許された設定値が、遠い未来となった現在にひょっこり顔を出してエラーを起こしたり。
私の環境もずいぶん古いから、何かの手違いでDBに要らないIndexが張りっぱなしになっていて
こういう警告が出てるんじゃないかな？という考えもあったのである。

ちなみに補足だが、PgHeroのメッセージ自体は何の嘘も誤りもない。
インデックスが貼られているところへ書き込むと、インデックスの更新が走るので、
理屈上はその分書き込みが遅くなるらしい。
むやみやたらに不要なインデックスを貼ると、かえって遅くなるのはこのためだ。
しかし、一意性制約のために作られたインデックスは、
クエリに利用されていなくても存在意味がある…というのが本件のミソである。
また、UNUSEDについては環境の大きさによっても差異があるようだ。
この辺の理解はかなり曖昧なので間違っているかもしれないが、
いずれにせよ、Mastodonの運用上、これらのアドバイスは安心して無視していい、と言えるだろう。

INDEX削除手順

psqlで直接入ってちょちょいとINDEXをDROP！
…という思いっきりの良さは持ち合わせていなかったため、
PgHeroが勧める通りdb:migrateを使ってINDEXを落とした。
※記録目的で記載してます。実行は自己責任で。

RAILS_ENV=production bundle exec rails generate migration remove_unneeded_indexes

db/migrate/20200713024830_remove_unused_indexes.rb

class RemoveUnusedIndexes2 < ActiveRecord::Migration[5.2]
  def change
    remove_index :account_conversations, name: "index_account_conversations_on_account_id", column: :account_id
    remove_index :account_identity_proofs, name: "index_account_identity_proofs_on_account_id", column: :account_id
  end
end

コマンドを実行すると、mastodonのlive/db/migrate配下に上記のようなファイルが生成される。
def change ... end の間は空白となっているため、PgHeroの表示(And Paste)内容を張り付ける。

あとは通常のMastodonアップデート時と同様
RAILS_ENV=production bundle exec rails db:migrate
でインデックスが消える。

INDEX再作成手順

正しい状態を知る

https://github.com/tootsuite/mastodon/blob/v3.2.0/db/schema.rb
v3.2.0(執筆時最新)の正しい状態のDB構造を示しているのが上記のファイルである。
インデックス再生成では、上記ファイルの状態を目指す。

インデックス情報については、各テーブルの中でt.indexから始まる行に書かれている。

例示すると

schema.rb

  create_table "account_conversations", force: :cascade do |t|
    ...
    t.index ["account_id", "conversation_id", "participant_account_ids"], name: "index_unique_conversations", unique: true
    ...

上記の場合、
テーブル：account_conversations
インデックス名：index_unique_conversations
対象カラム：account_id, conversation_id, participant_account_ids
ユニークインデックス：true
であることが分かる。

現在の状態を知る

sudo -u postgres psql
\c <MastodonDB名>
postgres=# COPY (SELECT * FROM pg_indexes) TO '/tmp/index_list.csv' With CSV DELIMITER ',';
postgres=# quit

※DB名が分からないときはpsqlに入ってから\lのDB一覧から確認可能。

上記のコマンドで、/tmp/index_list.csvに現状のインデックス一覧が出力される。
これとschema.rbのインデックスを突き合わせることで、
あるべき姿との差異を知ることが出来る。

db:rollback

PgHeroの示す手順に沿ってインデックスを落とした場合、
rakeタスクによるロールバックが可能である。

RAILS_ENV=production bundle exec rails db:migrate:status
上記コマンドで、これまでに行ってきたdb:migrateの一覧が表示される。
※具体的にはdb/migrate配下に、この一覧に対応するファイルが存在している。

私の場合、最新の2つのmigrateがindexを落とすものだったため、
RAILS_ENV=production bundle exec rails db:rollback STEP=2
を実行した。

片方は無事ロールバックしたが、もう一方は
以下のようなエラーメッセージでロールバックに失敗した。

StandardError: An error has occurred, all later migrations canceled:
 remove_index is only reversible if given a :column option.

インデックスを落とすのは名前指定だけで十分だが、
元に戻すためにはカラム指定が必要、ということだ。

ということで、他記事を参考にしつつ、
schema.rbを元に正しいカラムをmigrate時に利用したファイルに加筆した。

db/migrate/<>_remove_unused_indexes.rb

  def change
    #remove_index :accounts, name: "index_accounts_on_url"
    #schema.rbを元にcolumnを加筆
    remove_index :accounts, name: "index_accounts_on_url", column: :url
    ...
    ...
  end

なお、schema.rbに記載がなかったインデックスについては不要と判断し、
そもそもロールバックから除外している。

migrate用ファイルの編集後、改めて以下を実行。
RAILS_ENV=production bundle exec rails db:rollback

今度はエラーなく、無事にインデックスが生成された。

直接INDEX落としちゃった場合

私は経験していないので詳細は不明だが、ここで記載しているように
psqlで直接コンソールに入り、schema.rb上でuniqueとなっているものを
CREATE UNIQUE INDEXしていけばいいようだ。

不正な重複レコードが存在するとUNIQUE INDEXは貼り直せないため、
重複を解消する必要がある。(参考)

どのように重複排除するのが正しいか断言できないが、
それぞれのデータを見比べ、利用されていそうな方を残すことになるのではなかろうか。

おわりに

「mastodon pghero index unused」のような単語で検索しても
パッとした情報が見つからなかったので、
ぼんやりと「PgHeroも言ってるし、たかがINDEX、落としちゃえ」とやった結果
それなりに面倒な目に遭遇してしまった。

本記事が私のようなふわっふわした鯖缶さんにとって
転ばぬ先の杖になったなら、それ以上に幸いなことはない。

記事内に誤り等あれば、コメントで指摘ください。