未使用リモートノートの削除についてのアイデア

[tamaina]

連合が多く数年単位で運営されているサーバーでは、リモートノートによるデータベースの肥大化が喫緊の課題となっている。p1.a9z.devではnoteテーブルだけで50GB近くの容量を消費しており、インデックスを含めるとさらに容量を消費する。受信後一定期間が過ぎた未利用のリモートノートは削除されるべきである。

Related Issue: #9972 #12278 #7342

「未利用のリモートノート」をどう定義・検出する？

ローカルユーザーによって返信, Renote, リアクション, クリップ, お気に入りなどがされていない？

ローカルユーザーによって反応されているリモートノートは削除するべきでない。

しかし、返信, Renote, リアクション, クリップ, お気に入りなどがローカルユーザーが反応しているかを検出するには、SQLにしろnodeで処理するにしろ計算が多くなりそうである。
(特にクリップやお気に入りの判定はとんでもないことになりそう)

参照された期間で決めよう

ローカルユーザーやAPIによって参照・操作された日時を記録し、参照から一定時間経過したノートを削除すれば良いのだ。計算量は軽いはず。

日時の記録(参照からの時間経過の検出)は、Redisでexpire指定でnoteIdを記録するとか、PostgreSQLにlastSeenAtを記録するとかが考えられる。

経過日時は設定ファイルで変えられると良さそう。

コンテンツだけ削除

削除はコンテンツの中身だけを消去するものとする。期間削除時にローカルユーザーからの反応を考慮しないので、idを確保したりデータベースの関連付けを保持しておくためだ。

• ノートの本文・CW・投票（・ファイル？）などの中身を削除し、もし必要になったらリモートから取得し表示する。
• リモートから取得できなかった時は、ユーザーには空のノートとして見せる。

[tamaina]

Redisでexpire指定でnoteIdを記録するとか、PostgreSQLにlastSeenAtを記録するとか

どっちがいいだろう
(後者の方がSQL一文で書けそうではある)

[1673beta]

ローカルユーザーやAPIによって参照・操作された日時を記録

TLに流れてきたりページを開いたりするたびに記録となると結構DB負荷高くなる気がする(特に大規模サーバーでなりそう)

[tamaina]

オフトピ: いっそのことエックスみたいにインプレ(アクセスカウンタ)でもつけるかみたいな考えがよぎった

チャートシステムを応用(というかチャート関連のクラスをextendしたりManagementServiceに載せたりする？)して20分に1回ポスグレに書き込むとか？

[1673beta]

リモートからの閲覧をインプレに含めるか含めないかで変わりそう

[tamaina]

リモートからの閲覧をインプレに含めるか含めないか

リモートサーバーからの参照回数(リモートノートの更新をとってくる時とか)なら記録できるけど、リモートサーバーが能動的に数字を送ったりするのは高負荷だしそういうプロトコルもなさそう

[1673beta]

一応Activity VocabularyのActivityにViewとReadがあるのでそのへんをうまいことやれればよさそう(負荷が高いのはそう)

[syuilo]

愚直にSQL書いて参照されていないノートを抽出 & ポスグレのVacuumよろしく定期的なメンテナンスという扱いでジョブキューを利用しちまちま消していけるようにする

[syuilo]

効率考えると削除しても安全かどうかのフラグを持っておきたいわね

[syuilo]

リモートの場合最初は true で、なんらかのアクションがあった際に false にする

[tamaina]

リアクションバッファの処理まわりをもうちょっと具体的にした

(リアクションバッファのbake直後にやる必要がありそうかも)

[tamaina]

効率考えると削除しても安全かどうかのフラグを持っておきたいわね
リモートの場合最初は true で、なんらかのアクションがあった際に false にする

それってでも、"ローカルユーザーやAPIによって参照・操作された日時を記録する"のとあんまり変わらない気がする

[tamaina]

どうしよう(割とすぐ実装する気でいる)

[tamaina]

lastActiveDateはあるけどストリーミング接続時にしか判定されないのね

[tamaina]

削除の仕方でpostgresのデータ消費量を調べてみた

DELETEの場合 https://www.db-fiddle.com/f/2Dpdqzonx9pk9GEs652Ved/3
textを''で埋める場合https://www.db-fiddle.com/f/58H9RyF3CbLsa7hsiKQFBy/3

	初期	INSERT後	削除操作直後	VACCUM後
DELETE	7417 kB	7465 kB	7465 kB	7441 kB
text = ''	7417 kB	7465 kB	7465 kB	7473 kB

[tamaina]

text = ''`、VACUUM後はデータ増えててダメそう

[anatawa12]

（FULLが指定されていない）通常のVACUUMは、単に領域を回収し、そこを再利用可能な状態に変更します。 この形式のコマンドでは排他的ロックを取得しないため、テーブルへの通常の読み書き操作と並行して実行することができます。 しかし余った領域はオペレーティングシステムには(ほとんどの場合)返されません。

https://www.postgresql.jp/docs/9.4/sql-vacuum.html

だからデフラグのためにコピーするための領域を確保した分だけ増えた可能性ある？

VACUUM FULL しないと物理使用量は減らず、pg_relation_size だけ減る可能性ありそう

[anatawa12]

調べたらちゃんと条件が書かれてました。

標準形式のVACUUMは、テーブルとインデックス内の不要な行を削除し、その領域を将来の再利用が可能であるものとして記録します。 しかし、その領域をオペレーティングシステムに返却することはありません。 例外として、テーブルの末尾に完全に空のページが存在し、かつそのテーブルの排他ロックが容易に獲得できるような特殊な場合には、その領域を返却します。

https://www.postgresql.jp/docs/9.4/routine-vacuuming.html 23.1.2

[tamaina]

削除の仕方でpostgresのデータ消費量を調べてみた VACUUM FULL版

DELETEの場合 https://www.db-fiddle.com/f/2Dpdqzonx9pk9GEs652Ved/6
textを''で埋める場合https://www.db-fiddle.com/f/58H9RyF3CbLsa7hsiKQFBy/7

	初期	INSERT後	削除操作直後	VACCUM後	VACCUM FULL後
DELETE	6433 kB	6481 kB	6481 kB	7497 kB	6369 kB
text = ''	6433 kB	6481 kB	6481 kB	7529 kB	6385 kB

[tamaina]

削除の仕方でpostgresのデータ消費量を調べてみた

VACUUM後に同じ量INSERT・さらにINSERTしたらどうなる？

DELETEの場合 https://www.db-fiddle.com/f/2Dpdqzonx9pk9GEs652Ved/9
textを''で埋める場合https://www.db-fiddle.com/f/58H9RyF3CbLsa7hsiKQFBy/10

	初期	INSERT後	削除操作直後	VACUUM後	第2次INSERT後	第3次INSERT後
DELETE	6433 kB	6481 kB	6481 kB	7497 kB	7521 kB	7529 kB
text = ''	6433 kB	6481 kB	6481 kB	7529 kB	7529 kB	7537 kB

[tamaina]

削除の仕方でpostgresのデータ消費量を調べてみた

削除後に同じ量INSERT・さらにINSERTしたらどうなる？

DELETEの場合 https://www.db-fiddle.com/f/2Dpdqzonx9pk9GEs652Ved/10
textを''で埋める場合https://www.db-fiddle.com/f/58H9RyF3CbLsa7hsiKQFBy/11

	初期	INSERT後	削除操作直後	第2次INSERT後	第3次INSERT後
DELETE	6433 kB	6481 kB	6481 kB	6489 kB	6497 kB
text = ''	6433 kB	6481 kB	6481 kB	6489 kB	6497 kB

[tamaina]

削除の仕方でpostgresのデータ消費量を調べてみた

• WHERE "id" <> '0001' AND "id" <= '0020';を削除する
• VACUUM後に同じ量INSERT・さらにINSERTしたらどうなる？

DELETEの場合 https://www.db-fiddle.com/f/2Dpdqzonx9pk9GEs652Ved/11
textを''で埋める場合https://www.db-fiddle.com/f/58H9RyF3CbLsa7hsiKQFBy/12

	初期	INSERT後	削除操作直後	VACUUM後	第2次INSERT後	第3次INSERT後
DELETE	6433 kB	6481 kB	6481 kB	7529 kB	7529 kB	7537 kB
text = ''	6433 kB	6481 kB	6481 kB	7529 kB	7529 kB	7537 kB

[tamaina]

現実に近づけたらだんだん同じくらいになってきた

もうちょっと大量のデータで確認したいけど別のプラットフォームを用意すべきだわね

[syuilo]

インデックスサイズ(メモリ負荷)が気になるわね

[tamaina]

#16232 (reply in thread)

text = ''は(当然ながら)インデックスの容量は大きくなる
(サーバーのメモリが大きいので個人的にはあんまり気にしてない…)

[tamaina]

https://github.com/tamaina/postgres-size-test/actions/runs/16351192657/job/46198131168#step:9:16

作った

[tamaina]

IDX_153536c67d05e9adb24e99fc2b、uriのインデックスらしくて大横転してる

[tamaina]

UNIQUEなインデックスは大きくなっちゃうっぽいな

[tamaina]

いや純粋に長くて複雑な文字列にインデックス貼ってるからか？

テストするか

[tamaina]

https://github.com/tamaina/postgres-size-test/actions/runs/16359444197/job/46224549828#step:9:117

変わらんな

[tamaina]

REINDEX TABLE CONCURRENTLY note; 後

だいぶ減った

              tablename              |                        indexname                        | relpages | pg_size_pretty 
-------------------------------------+---------------------------------------------------------+----------+----------------
 note                                | IDX_153536c67d05e9adb24e99fc2b                          |   622502 | 4864 MB
 note_reaction                       | IDX_ad0c221b25672daf2df320a817                          |   388869 | 3038 MB
 note                                | IDX_a6f649630f55af3888e5a42919                          |   337874 | 2640 MB
 note                                | PK_96d0c172a4fba276b1bbed43058                          |   262416 | 2050 MB
 note_reaction                       | PK_767ec729b108799b587a3fcc9cf                          |   203244 | 1588 MB
 drive_file                          | IDX_e5848eac4940934e23dbc17581                          |   160648 | 1255 MB
 user                                | IDX_80ca6e6ef65fb9ef34ea8c90f4                          |   150437 | 1175 MB
 note_reaction                       | IDX_45145e4953780f3cd5656f0ea6                          |   121311 | 948 MB
 __chart__per_user_notes             | UQ_5048e9daccbbbc6d567bb142d34                          |    96369 | 753 MB
 __chart__per_user_notes             | IDX_5048e9daccbbbc6d567bb142d3                          |    93127 | 728 MB
 drive_file                          | IDX_e74022ce9a074b3866f70e0d27                          |    86844 | 678 MB
 drive_file                          | IDX_c55b2b7c284d9fef98026fc88e                          |    86720 | 678 MB
 note                                | IDX_52ccc804d7c69037d558bac4c9                          |    81390 | 636 MB
 drive_file                          | IDX_d85a184c2540d2deba33daf642                          |    75701 | 591 MB
 __chart__instance                   | UQ_39ee857ab2f23493037c6b66311                          |    75607 | 591 MB
 drive_file                          | IDX_37bb9a1b4585f8a3beb24c62d6                          |    75162 | 587 MB
 __chart__instance                   | IDX_39ee857ab2f23493037c6b6631                          |    72420 | 566 MB
 note                                | IDX_17cb3553c700a4985dff5a30ff                          |    71607 | 559 MB
 drive_file                          | IDX_55720b33a61a7c806a8215b825                          |    69698 | 545 MB
 note                                | IDX_d4ebdef929896d6dc4a3c5bb48                          |    65598 | 513 MB
 note_reaction                       | IDX_13761f64257f40c5636d0ff95e                          |    62290 | 487 MB
 __chart__per_user_reaction          | UQ_229a41ad465f9205f1f57032910                          |    62182 | 486 MB
 __chart__per_user_reaction          | IDX_229a41ad465f9205f1f5703291                          |    59993 | 469 MB
 note                                | IDX_7125a826ab192eb27e11d358a5                          |    58818 | 460 MB
 note                                | IDX_NOTE_FILE_IDS                                       |    58486 | 457 MB
 note                                | IDX_f22169eb10657bded6d875ac8f                          |    57656 | 450 MB
 __chart__per_user_notes             | PK_334acf6e915af2f29edc11b8e50                          |    54367 | 425 MB
 __chart__instance                   | PK_1267c67c7c2d47b4903975f2c00                          |    35713 | 279 MB
 __chart__per_user_reaction          | PK_984f54dae441e65b633e8d27a7f                          |    33751 | 264 MB
 user_note_pining                    | PK_a6a2dad4ae000abce2ea9d9b103                          |    32525 | 254 MB
 drive_file                          | PK_43ddaaaf18c9e68029b7cbb032e                          |    30658 | 240 MB
 __chart_day__per_user_notes         | UQ_c5545d4b31cdc684034e33b81c3                          |    27553 | 215 MB
 __chart_day__per_user_notes         | IDX_c5545d4b31cdc684034e33b81c                          |    26564 | 208 MB
 __chart_day__per_user_reaction      | UQ_d54b653660d808b118e36c184c0                          |    16854 | 132 MB
 __chart_day__per_user_reaction      | IDX_d54b653660d808b118e36c184c                          |    16093 | 126 MB
 note                                | IDX_NOTE_TAGS                                           |    15379 | 120 MB
 __chart_day__per_user_notes         | PK_58bab6b6d3ad9310cbc7460fd28                          |    15263 | 119 MB
 note                                | IDX_NOTE_MENTIONS                                       |    13557 | 106 MB
 drive_file                          | IDX_860fa6f6c7df5bb887249fba22                          |    12070 | 94 MB
 user                                | IDX_fa99d777623947a5b05f394cae                          |    10610 | 83 MB
 drive_file                          | IDX_92779627994ac79277f070c91e                          |     9434 | 74 MB
 note                                | IDX_NOTE_VISIBLE_USER_IDS                               |     9322 | 73 MB
 __chart_day__per_user_reaction      | PK_8af24e2d51ff781a354fe595eda                          |     9061 | 71 MB
 drive_file                          | IDX_a40b8df8c989d7db937ea27cf6                          |     8955 | 70 MB
 drive_file                          | IDX_a7eba67f8b3fa27271e85d2e26                          |     8771 | 69 MB
 drive_file                          | IDX_8bdcd3dd2bddb78014999a16ce                          |     8763 | 69 MB
 drive_file                          | IDX_3b33dff77bb64b23c88151d23e                          |     8762 | 68 MB
 drive_file                          | IDX_bb90d1956dafc4068c28aa7560                          |     8753 | 68 MB
 drive_file                          | IDX_315c779174fe8247ab324f036e                          |     8734 | 68 MB
 __chart_day__instance               | UQ_fea7c0278325a1a2492f2d6acbf                          |     7203 | 56 MB
 __chart_day__instance               | IDX_fea7c0278325a1a2492f2d6acb                          |     6395 | 50 MB
 user_publickey                      | PK_171e64971c780ebd23fae140bba                          |     5869 | 46 MB
 user                                | IDX_be623adaa4c566baf5d29ce0c8                          |     5798 | 45 MB
 user                                | REL_58f5c71eaab331645112cf8cfa                          |     4981 | 39 MB
 user                                | IDX_5deb01ae162d1d70b80d064c27                          |     4499 | 35 MB
 user_profile                        | PK_51cb79b5555effaf7d69ba1cff9                          |     4266 | 33 MB
 __chart__per_user_drive             | UQ_30bf67687f483ace115c5ca6429                          |     4243 | 33 MB
 __chart__per_user_drive             | IDX_30bf67687f483ace115c5ca642                          |     4219 | 33 MB
 user                                | PK_cace4a159ff9f2512dd42373760                          |     3890 | 30 MB
 __chart_day__instance               | PK_479a8ff9d959274981087043023                          |     3648 | 29 MB
 user                                | REL_afc64b53f8db3707ceb34eb28e                          |     3426 | 27 MB
 emoji                               | IDX_4f4d35e1256c84ae3d1f0eab10                          |     3208 | 25 MB
 user_publickey                      | IDX_10c146e4b39b443ede016f6736                          |     2946 | 23 MB
 __chart__per_user_drive             | PK_d0ef23d24d666e1a44a0cd3d208                          |     2879 | 23 MB
 user                                | IDX_a27b942a0d6dcff90e3ee9b5e8                          |     2807 | 22 MB
 __chart__hashtag                    | UQ_25a97c02003338124b2b75fdbc8                          |     2669 | 21 MB
 __chart__hashtag                    | IDX_25a97c02003338124b2b75fdbc                          |     2661 | 21 MB
 __chart_day__per_user_drive         | IDX_62aa5047b5aec92524f24c701d                          |     2194 | 17 MB
 __chart_day__per_user_drive         | UQ_62aa5047b5aec92524f24c701d7                          |     2192 | 17 MB
 hashtag                             | PK_cb36eb8af8412bfa978f1165d78                          |     2037 | 16 MB
 user                                | IDX_a854e557b1b14814750c7c7b0c                          |     1732 | 14 MB
 user                                | UQ_a854e557b1b14814750c7c7b0c9                          |     1731 | 14 MB
 user                                | IDX_3252a5df8d5bbd16b281f7799e                          |     1658 | 13 MB
 user_profile                        | UQ_6dc44f1ceb65b1e72bacef2ca27                          |     1629 | 13 MB
 user                                | IDX_d5a1b83c7cab66f167e6888188                          |     1628 | 13 MB
 emoji                               | PK_df74ce05e24999ee01ea0bc50a3                          |     1589 | 12 MB
 user                                | IDX_c8cc87bd0f2f4487d17c651fbf                          |     1553 | 12 MB
 __chart__hashtag                    | PK_c32f1ea2b44a5d2f7881e37f8f9                          |     1522 | 12 MB
 __chart_day__hashtag                | UQ_8f589cf056ff51f09d6096f6450                          |     1499 | 12 MB
 __chart_day__hashtag                | IDX_8f589cf056ff51f09d6096f645                          |     1490 | 12 MB
 __chart_day__per_user_drive         | PK_1ae135254c137011645da7f4045                          |     1447 | 11 MB
 user_profile                        | IDX_dce530b98e454793dac5ec2f5a                          |     1394 | 11 MB
 user_note_pining                    | IDX_410cd649884b501c02d6e72738                          |     1352 | 11 MB
 user_profile                        | IDX_3befe6f999c86aff06eb0257b4                          |     1284 | 10 MB
 emoji                               | IDX_b37dafc86e9af007e3295c2781                          |     1251 | 10008 kB

[anatawa12]

私が直リンクで残っててほしいってのはノートリンクとかのことを想定してるので、普段ほぼアクサスされない別テーブルに分けておいて、 /notes/id の処理に見当たらない(かつ日時が一定以上古い id)ならそっちのテーブル引いて ap show したりリモートを見るようにすすめる代替表示をする形でも良い気もします。 orphan orローカルが関わってないノートを消す前提だと思うので

[tamaina]

ノート本文テーブル作るなら極論ローカルのノートも分離して…って悪いことを考えた(ノート編集機能とか作るのが簡単になりそう)(マイグレーションで絶対容量必要になるからやめて欲しいけど)

[tamaina]

EXPLAIN ANALYZE VERBOSE UPDATE note SET text = ''; したら50分以上連合が止まったのでなしで(なんでだろう?)

[tamaina]

(ローカルでは投稿できたんだよな)

[tamaina]

多数の行に影響する更新は、テーブルの肥大化、レプリカの遅延の増加、ロック競合の増加など、システムパフォーマンスに悪影響を及ぼす可能性があります。 このような状況では、より小さなバッチで操作を実行し、場合によってはバッチ間でテーブルに対してVACUUM操作をすることが理にかなっています。 UPDATEにはLIMIT句はありませんが、共通テーブル式と自己結合を使用して同様の効果を得ることができます。 標準のPostgreSQLテーブルアクセスメソッドでは、システム列ctidに対する自己結合は非常に効率的です。

https://www.postgresql.jp/document/17/html/sql-update.html

[tamaina]

いずれにしろシークは必要なのね

[tamaina]

やっぱ欲しいのでReopen?

[tamaina]

全文検索はともかくハッシュタグTLにいっぱいノートが残っていてほしいので

[tamaina]

日時の記録(参照からの時間経過の検出)は、Redisでexpire指定でnoteIdを記録するとか、PostgreSQLにlastSeenAtを記録するとかが考えられる。

Redisの方が良さそう(Postgresはsaveすることになってパフォーマンス的に不味そう)

Redisは吹っ飛ばしてもいい様に、idが0として統計開始日時を書き込んでおいて(起動時にチェックしてなかったら書き込む?)それが閾日時より前だったらジョブプロセスはスキップされるという実装がいいかもしれない