tateisu/gist:3d98290f2b72d12ba5f1b977a0d5743c

## gistfile1.txt.md

      
    Raw
  

              gistfile1.txt.md
            
          
    使われてない古い投稿の削除

English => https://gist.github.com/tateisu/c3c13f2580961e86f79889a17f57260a
背景


マストドンのインスタンスを運営していると、記録データが年々蓄積されていく。
自分のインスタンスならローカルユーザ自身の投稿やローカルユーザが興味をもった外部の投稿を記録し続けないといけないのは理解できる。
しかし外部タンスの投稿について「もはやフォローされてない外部ユーザの投稿」「リレーで無条件に流れてくる外部ユーザの投稿」をもずっと記録し続ける理由はない。
古い不要な投稿データを削除することで、記録データの増加を軽減できるのではないだろうか。サーバ維持費の増加を抑えられる。
特にリレーについて「投稿データが増えすぎるのを避けるためリレーを利用しない」という判断をせずにすむ。

やること

FTLやリレーから流れてきたトゥートのうち、利用されなかったものを適当に削除する。
メモ：あくまでデータ増加の「軽減」であり、「一定のディスク容量で無限に運用できるようになる」とかではない。
前提条件


データベースはPostgreSQL 9.5 以上であること
tootctl media remove_remote を事前に済ませておくこと

インデックスの作成

とても遅いクエリなので事前にインデクスを作る
「ローカルユーザ」を示す部分インデックス

create index index_accounts_local on accounts(id) where domain is null;
作業が終わったら消してもよい
「pinされた投稿」を示すインデックス

create index index_status_pins_status_id on status_pins(status_id);
作業が終わったら消してもよい
添付メディアのremote_url

create index index_media_attachments_remote_url on media_attachments(remote_url) where remote_url is not null;
投稿を削除しまくると「SELECT "media_attachments".* FROM "media_attachments" WHERE "media_attachments"."status_id" IS NULL AND "media_attachments"."remote_url" = ? ORDER BY "media_attachments"."id" ASC LIMIT ?」がpgHeroのスロークエリに出ていたので、それを回避するためのインデックス。
調査不足で、このクエリがどんな条件で発生するのか分からず、いつインデックスを削除してよいかハッキリと言えない。
削除SQL

delete はテーブルロックする。
ロック時間が長くなりすぎるのを避けるため一度に処理する件数を制限する。
それでもサーバスペックによってはかなり重い処理だが…。
DELETE FROM statuses WHERE id in (
SELECT id FROM statuses
where not local 
and id < (EXTRACT(EPOCH FROM ((TIMESTAMP 'now')-interval '100 days')) * 65536000)::bigint
and reblog_of_id is null
and in_reply_to_id is null
and id not in ( select status_pins.status_id from status_pins where statuses.id = status_id)
and id not in ( select mentions.status_id from mentions where statuses.id = status_id)
and id not in ( select sb.in_reply_to_id from statuses as sb where statuses.id = sb.in_reply_to_id)
and id not in ( select favourites.status_id from favourites where statuses.id = status_id
  and favourites.account_id in ( select accounts.id from accounts where accounts.domain is null)
)
and id not in ( select sb.reblog_of_id from statuses as sb where statuses.id = sb.reblog_of_id
  and sb.account_id in ( select accounts.id from accounts where accounts.domain is null) 
)
and account_id not in ( select follows.target_account_id from follows where statuses.account_id = follows.target_account_id
  and follows.account_id in ( select accounts.id from accounts where accounts.domain is null)
)
order by id asc limit 2000
);
また、スクリプトなどで使う場合にはdelete文の末尾に returning id を付けることで「削除された投稿のIDのリスト」を得られる。
各条件の説明

以下、クエリ条件を個別に確認する。
メモ： inner join ～ where null や not exists ではなく not in が使われているのは、 PostgreSQL 9.6 でクエリの実行計画にそれなりの影響があったからだ。not exists を使った場合の実行計画は Merge Anti join ～ Materialize が多用され、それは投稿IDの指定範囲外に対してもサブクエリを動かすのだった。 not exists を not in で書き換えたらjoinではなく statuses テーブルの範囲スキャンの内部で Cond Check に SubPlan を処理するようになり、 costだけ見ると重くなってるが、サブプランを扱う行数が大幅に減っていた。
ローカル投稿ではない

not local に相当する。
ローカルユーザによる投稿やブーストは削除するべきではない。
十分に古い

id < (EXTRACT(EPOCH FROM ((TIMESTAMP 'now')-interval '100 days')) * 65536000)::bigint に相当する。
少なくともtootctl media remove_remote に指定した日数(デフォルト7日)より古い投稿に限定して削除するべきだ。
メモ：Mastodonの投稿IDはSnowflake ID なので、unix time に65536000 を掛け算すると日時範囲指定をID範囲指定に置き換えられる。
メモ： now() だと定数式にならないが (TIMESTAMP 'now') だと定数式になる。
pinされてない

id not in ( select status_pins.status_id from status_pins where statuses.id = status_id) に相当する。
pinされた投稿はユーザプロフに表示される「現在の状態」の一種なので、たとえ古くても削除するべきではない。
ブーストではない

reblog_of_id is null
に相当する。
リレーからはブーストは来ないが、フォロー外からもブースト通知は来る。「投稿をブーストした人」の表示に影響するので、消すべきではない。
メンションを含まない、返信ではない、返信されてない

in_reply_to_id is null
and id not in ( select mentions.status_id from mentions where statuses.id = status_id)
and id not in ( select sb.in_reply_to_id from statuses as sb where statuses.id = sb.in_reply_to_id)
に相当する。
会話ツリーのごく一部にだけローカルユーザが絡む場合があり、その場合でもローカルユーザは会話ツリーの全体を閲覧できるのが望ましい。よって削除するべきではない。
メモ：ローカルユーザが全く絡まない会話ツリーは削除しても構わないと思うが、今回のスクリプトではそんな複雑な判定はせずに「会話ツリーは一切削除しない」ようになっている。
ローカルユーザにフォローされてない

account_id not in ( select follows.target_account_id from follows where statuses.account_id = follows.target_account_id
  and follows.account_id in ( select accounts.id from accounts where accounts.domain is null)
)
に相当する。
ローカルユーザにお気に入りされてない

id not in ( select favourites.status_id from favourites where statuses.id = status_id
  and favourites.account_id in ( select accounts.id from accounts where accounts.domain is null)
)
に相当する。
ローカルユーザにブーストされてない

id not in ( select sb.reblog_of_id from statuses as sb where statuses.id = sb.reblog_of_id
  and sb.account_id in ( select accounts.id from accounts where accounts.domain is null) 
)
に相当する。
ローカルユーザにフォローされたリモートユーザにブーストされてない

これは上記クエリ例に含まれてない。
この条件を含まない場合、db/schema.rb には add_foreign_key "statuses", "statuses", column: "reblog_of_id", on_delete: :cascade とあるため、ブーストされた投稿が削除されるとブーストしたことを示す投稿も一緒に削除される。
結果としては、該当するリモートユーザのアカウントTLを深く辿った時に、そのブーストが消えたように見えるはずだ。
「フォロー済みユーザによるブースト」が見れなくなることは是も非もあるだろう。
もし条件を追加するなら、「ローカルユーザにブーストされてない」もあわせて以下のように書けばよいだろうか？
id not in ( select sb.reblog_of_id from statuses as sb where statuses.id = sb.reblog_of_id
  and ( sb.account_id in ( select accounts.id from accounts where accounts.domain is null ) 
     or sb.account_id in ( select follows.target_account_id from follows where sb.account_id = follows.target_account_id
       and follows.account_id in ( select accounts.id from accounts where accounts.domain is null)
     )
)
投稿が削除された後の影響


会話やメンションを含む投稿は今回の削除対象ではないので、 Statuses.unlink_from_conversations 相当の処理は必要ないはず。
他の外部キーは全て on_delete: :cascade なり on_delete: :nullify なり定義されてるはず？(要検証)
添付メディアはstatusとの関連が切れた状態になる。sidekiqにスケジュールされたMediaCleanupSchedulerにより破棄される
(sidekiqからMediaCleanupSchedulerが呼ばれてstatus_idと紐ついてないMediaAttachmentがdestroyされるとき、添付ファイルはどうなるんだろう…？)