cloud spanner設計
WIP】のっかり的中投稿作成時に、予想師にリアクション通知を飛ばす機能のテーブル設計 #14976
Open
Open
WIP】のっかり的中投稿作成時に、予想師にリアクション通知を飛ばす機能のテーブル設計#14976
@t38miwa
Description
t38miwa
opened 3 weeks ago · edited by t38miwa
２つのパターンの設計で迷っている。それぞれメリットデメリットを比較した結果、パターン2を採用するべきだと考える
Big Query調査結果
最悪のケース：一人の予想師にのっかりが集中、かつその予想が全て当たった場合約5000件挿入されることとなる
パターン1
DDL
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
user_nokkari_hit_notice_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL, -- のっかりで的中したユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, user_nokkari_hit_notice_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
メリット
・表示用APIで実装する読み込み操作時は予想師のidをwhere句に指定して取得することができるため、高速
・ユーザー目線で重要な読み込み操作が高速
→ だと思っていた
デメリット
・特定の予想師に書き込みが集中した場合、最悪のケースを考えた時ホットスポットとなりうる（１レースあたり5000件のレコードの書き込みが短時間で行われる可能性あり）
・仮にホットスポットとなった場合、同じ予想師のuser_idを持つ他のテーブルにも影響が出る可能性がある
・のっかりユーザーの user_activities は別スプリットにあるため、インターリーブの恩恵を受けられない
パターン2
DDL
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
-- user_idはのっかりで的中したユーザーを指す（user_activitiesとのJOIN最適化のため）
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- のっかりしたユーザー
user_nokkari_hit_notice_id STRING(36) NOT NULL, -- UUID
predictor_user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, user_nokkari_hit_notice_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
-- 予想師ごとの通知一覧を新着順で取得するためのインデックス
CREATE INDEX idx_user_nokkari_hit_notices_predictor_user_id_created_at_desc
ON user_nokkari_hit_notices(predictor_user_id, created_at DESC);
メリット
・PKがのっかりした人となるため、配置が分散されホットスポットが起きにくい
・usersテーブルとのインターリーブによってuser_activitiesテーブルと物理的に近い位置に配置されるため、JOIN操作がパターン1より高速となる
・表示用APIの読み込み操作もpredictor_user_idにインデックスを貼っているため、速度にそこまで差がない
デメリット
・読み込み操作のためのインデックスが追加で必要
・user_id がのっかりユーザーを指すので、他の通知テーブルと意味が異なる
スケールした時どうなるか
的中投稿作成数が増えた場合
パターン1はより書き込み操作に負荷がかかりホットスポットになる可能性が高くなる
パターン2はPKがのっかりした人のため、分散されやすく問題になりにくい
読み込みの件数の上限が増えたとき、リクエスト数が増えたとき
パターン1はPK、パターン2はインデックスを貼ったキーを元に読み込み操作を行うため、理論的には差は生まれない
結論
パターン2を採用するべきだと思いました、的中投稿が削除された際の挙動の仕様が確定すればDDLのPR出せます
Activity
t38miwa
t38miwa commented 3 weeks ago
t38miwa
3 weeks ago
Author
MTGでいただいた指摘
・パターン2で予想師idにインデックスを貼っているが、結局そこがホットスポットとなる
・パターン2はテーブルが持つ意味と矛盾したPKとなる
・prioritylowを使用すればパターン1のホットスポット問題を解決できるのでは
・user_nokkari_hit_notice_idは不要では
・PRIMARY KEY(user_id, nokkari_user_activity_id DESC,nokkari_user_id)とするのがいいのでは
t38miwa
t38miwa commented 3 weeks ago
t38miwa
3 weeks ago · edited by t38miwa
Author
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
nokkari_user_id STRING(36) NOT NULL, -- のっかりで的中したユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
インターリーブを設定する理由
・親テーブルのuser_idが分散されているため、インターリーブを設定することでより分散される。インターリーブを設定しない場合、より特定の予想師のスプリットに書き込みが集中することとなる
result_create_workerは６台存在
一番盛り上がったレースでresult_create_workerは１件1-2秒で処理している、全ユーザーの反映処理には２分ほどかかっている
2025-12-30_35_11は5000件ののっかり予想があった
仮に最悪想定の5000件を2分間で処理する場合、平均で42write/secとなり、ホットスポットとなることはないと考えられる
t38miwa
mentioned this
3 weeks ago
のっかりタブへのリアクション通知用のテーブルを追加 #14980
t38miwa
t38miwa commented 3 weeks ago
t38miwa
3 weeks ago
Author
テーブル設計の過程
インデックスの設計などはまだ考慮できていません
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
user_nokkari_hit_notice_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL, -- のっかりで的中したユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, user_nokkari_hit_notice_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
通知を基本新着順で並べるため、user_nokkari_hit_notice_idとuser_activity_idにはULIDが値として入る。user_idとnokkari_user_idには従来通りUUIDが値として入る。
ULIDを設定することの懸念はuser_idがUUIDによって分散されるが、特定のユーザーに書き込みが集中した際に先頭に集中しやすくホットスポットとなる可能性があること。
・このテーブルにどの程度書き込みがあるか
・1ユーザーあたりの書き込み偏りはどの程度か
を考慮する必要がある
競技アイコンの表示について
・表示用APIにてtextIconTypeという値が返される。これはどの競技かを判定するもの。リアクション通知タブのいいねタブだとこれはuser_activity_reactionsテーブルからuser_activity_idを取得し、そこからrace_idを取得、race_idをもとにtextIconType（どの競技か）がわかる
user_activity_idのみをuser_nokkari_hit_noticesテーブルに持たせ、レースの場所＋ラウンド数の情報（いわき平 12R）はuser_activitiesテーブルに紐づくrace_idを使用して取得する
場所の名前（いわき平）はrace_idをもとに外部APIから取得
ラウンド数（12R）はrace_idをパースして取得、末尾がラウンド数となっているため
どの競技かの情報が必要である
result-creatorは車券反映処理から重い処理を担当する、また、ticket-updaterはtipstarの根幹となる仕組みで、result-createrはそれに付随する付加価値を生む仕組みを担当する
user_activity_idは名前変更、user_activitiesを見て設計再考する
usersテーブルのインターリーブ必要？user_idをPKにするのはなぜ？user_nokkari_hit_notice_idをPKにすればいいのでは？
論理削除、物理削除について調べる
user_nokkari_hit_notice_idのDESCで新着順にするのはどうなの
書き込み操作と読み込みどっちが重くなりそうか、またユーザーにとってはどっちの方が重要か
サブコメント確定かどうか坂田さんに確認
的中投稿を消した時に「表示できません」と表示するのか（他のいいねなどのタブでどうなっているかロジック含め確認、また企画側としてはどうしたいのか坂田さんと確認）
• [ ]
usersテーブルをインターリーブするべきか
坂田さんにサブコメント確定がどうかを確認、そのために山谷さんに聞いていいか相談
優先順位
必要な情報を全て網羅すること
• [x]
リアクションコメントに加えてサブコメントを追加するか、またそのコメント内容は現在のもので確定か
アイコンは競技ごとに異なるのかそれとも共通か
「初めてハイパーGET！、、、」サブコメントは確定か
的中投稿が削除されたとき、「表示できません」と表示するのかどうするか
ユーザー目線で処理が重いと困る部分はどこか調べる
• [x]
ユーザー目線で考えると書き込み操作が多少遅くても、予想師としてはリアクション通知がリアルタイムでくることはそんなに重要でないように思えるが実際どうか
のっかりタブの読み込みが遅いのはユーザーとしては嫌な気がするがどうか
書き込み、読み込みどちらを重要視するか決める
現在どの程度の負荷がかかることがわかっていて、今後どうなりそうかを調べること
• [ ]
現在の負荷を調べる方法を言葉で説明できるようになる
Big Queryから計測する方法を教えてもらう
今後どうなるか坂田さんやえばたさん、サーバーのメンバーに相談
2,3から負荷対策を考えてテーブル設計する
big queryで調べること
・全体の1日あたりの的中投稿数
・一人の予想師あたり平均いくつの的中投稿が１レースで作成されるのか
・一人の予想師あたりの的中投稿数の最大値（レースが同じ時間、かつ人気予想師などを想定）
・予想師ベースでいいんだっけ、ユーザーベースがいいんだっけ
・pkは乗っかりした人ベースが良さそう、予想師ベースだとホットスポットの可能性あり
big queryからわかったこと
・2026年2月1日、36_01レースで1556件の的中投稿が作成された、これが一つのレースでもっとも的中投稿が作成されたレースである
・2026年2月1日、36_01レースで3362件ののっかり車券が購入された、つまり全部当たっていた場合、一つのレースで3362件の的中投稿が作成されることとなる（また、別日だが、5362件購入されたレースもある）
t38miwa
mentioned this
3 weeks ago
【WIP】のっかり的中投稿作成時に、予想師にリアクション通知を飛ばす #14952
t38miwa
t38miwa commented 2 weeks ago
t38miwa
2 weeks ago
Author
2月19日（木）の振り返りMTGのレビューから変更する点
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC, nokkari_user_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
・user_idをテーブルの一番上に記載（慣習に合わせる）
・PKの最後にnokkari_user_idを追加
のっかりタブへのリアクション通知用のテーブルを追加#14980Mergedt38miwamerged 19 commits intodevelopfromfeature/add-user-nokkari-hit-notices-tablelast week+159Lines changed: 159 additions & 0 deletionsConversation26 (26)Commits19 (19)Checks10 (10)Files changed3 (3)Mergedのっかりタブへのリアクション通知用のテーブルを追加#14980t38miwamerged 19 commits intodevelopfromfeature/add-user-nokkari-hit-notices-table
Conversation
@t38miwa
t38miwacommented3 weeks ago
なぜこの変更が必要なのか (why & what)
のっかり的中通知機能とのっかり通知機能のために新たなテーブルが必要なため
どのように実現するのか (how)
user_nokkari_hit_noticesテーブルを追加する
DDL
-- のっかり的中通知
-- 予想師がのっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_nokkari_hit_notice_id STRING(36) NOT NULL, -- サロゲートキー(UUID)
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
nokkari_user_id STRING(36) NOT NULL, -- のっかりしたユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- のっかりユーザーのアクティビティID(ULID)
shard_key INT64 NOT NULL, -- 書き込み分散用
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
) PRIMARY KEY (user_id, user_nokkari_hit_notice_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
CREATE INDEX idx_user_nokkari_hit_notices_shard_key_user_id_created_at_desc
ON user_nokkari_hit_notices (shard_key, user_id, created_at DESC);
PKにそれぞれuuidのuser_idとnokkari_hit_notice_idを持たせることで、ホットスポットとならないよう対策している
シャードキーをインデックステーブルの先頭に持たせることで、ホットスポットとならないように対策している
このインデックスを貼ることで、シャードキーの値ごとにuser_idを検索するようになるため、フルスキャンより読み取り操作が高速となる。その理由はSQL文が以下のようになる予定だからである。
WHERE shard_key BETWEEN 0 AND 99
AND user_id = '特定の予想師のユーザーID'
これにより
shard_key = 1 user_id = '特定の予想師のユーザーID' を探す
shard_key = 2 user_id = '特定の予想師のユーザーID' を探す
shard_key = 3 user_id = '特定の予想師のユーザーID' を探す
という検索方法となり、フルスキャンに比べて早く読み取り操作ができる
実装上の懸念（特にレビューしてほしい部分など）
・テーブル名に問題はないか
・インデックスの貼り方が検索を早める実装となっているか
・テーブル、インデックステーブル両者に対して十分なホットスポット対策がなされているか
仕様 (JIRA, Issue, design doc へのリンク)
https://www.notion.so/FEED-2fecee5fb72581e1b8a0c79f185ca19c
参考リンク
テーブル設計に関するissue
#14976
テーブル設計の議論のslackスレッド
https://mixi-tipstar.slack.com/archives/C022L6YSV1U/p1771401181064409
@t38miwa
feat: のっかりタブへのリアクション通知用のテーブルを追加
d0309b5
github-actions bot added the size_s label 3 weeks ago
@github-actions
@github-actions
github-actions botcommented3 weeks ago
[test-warn] These files have no test files 🐶
Every changed file has test file 🎉
@github-actions
github-actions botcommented3 weeks ago
spanner-diff
CREATE TABLE user_nokkari_hit_notices (
nokkari_user_activity_id STRING(36) NOT NULL,
user_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
diff checksum: e5f4995380f99f5b1128219e53cbd2d8
t38miwa added 2 commits 3 weeks ago
@t38miwa
feat: 新規テーブル追加のため、構造体定義を追加
b146015
@t38miwa
Merge branch 'develop' into feature/add-user-nokkari-hit-notices-table
19fbf75
github-actions bot added size_s and removed size_s labels 3 weeks ago
@github-actions
t38miwa marked this pull request as ready for review 3 weeks ago
@t38miwa
Copilot AI review requested due to automatic review settings 3 weeks ago
sb-dev-app bot requested a review from mixi-sb/project-z-server 3 weeks ago
@sb-dev-app
Copilot started reviewing on behalf of t38miwa 3 weeks ago
github-actions bot added size_s and removed size_s labels 3 weeks ago
@github-actions
Copilot AI reviewed 3 weeks ago
Copilot AIleft a comment
Pull request overview
のっかり的中投稿作成をトリガーに、予想師へリアクション通知を出すための永続化テーブル（Spanner）と、それに対応するGoモデルを追加するPRです。
Changes:
Spanner に user_nokkari_hit_notices テーブルを追加
Go のドメインモデル UserNokkariHitNotice を追加
model.gen.go に当該モデルのテーブル/カラム/PKメタ情報を追加
Reviewed changes
Copilot reviewed 3 out of 3 changed files in this pull request and generated 2 comments.
File Description internal/api/domain/model/user_nokkari_hit_notice.go user_nokkari_hit_notices に対応するモデルを追加 internal/api/domain/model/model.gen.go 生成コードに UserNokkariHitNotice のテーブル定義（TableName/Columns/PK等）を追加 db/spanner.sql user_nokkari_hit_notices テーブルDDLを追加 db/spanner.sqlOutdateddb/spanner.sqlOutdated
t38miwa changed the title feat: のっかりタブへのリアクション通知用のテーブルを追加 のっかりタブへのリアクション通知用のテーブルを追加 3 weeks ago
@t38miwa
@t38miwa
Author
t38miwacommented3 weeks ago
のっかりされた通知が今後増える予定
・テーブルを分けるか、それとも共通にするか
わけてもPKは同じ、同じスプリットに配置される可能性が高い
共通ならtypeのカラムが必要、それ用のインデックスも必要
・非同期でのっかり関連の処理をするため、のっかり用のワーカーを別で立てる
→ 今後のっかり系の実装が増えたとき、対応可能
のっかり通知作る時に考えるでもいいかも
このタイミングでワーカーに切り出す
リトライの際に再度車券反映も行う必要がある
車券反映のトランザクションが成功した後に通知のトランザクションこけたとき、問題になる
t38miwa added 3 commits 2 weeks ago
@t38miwa
feat: のっかり通知共通テーブルとするため、カラムを追加
d96d5b1
@t38miwa
chore: submoduleの参照コミットをorigin/developに合わせる
22cfa08
@t38miwa
Merge branch 'develop' into feature/add-user-nokkari-hit-notices-table
3ff0b69
github-actions bot added size_s and removed size_s labels 2 weeks ago
@github-actions
@github-actions
github-actions botcommented2 weeks ago
spanner-diff
CREATE TABLE user_nokkari_notices (
user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
nokkari_notice_type STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
CREATE INDEX idx_user_nokkari_notices_user_id_nokkari_notice_type_nokkari_user_activity_id_desc ON user_nokkari_notices(user_id, nokkari_notice_type, nokkari_user_activity_id DESC), INTERLEAVE IN users;
diff checksum: b51ac11adab4c964b252e5f7d4b8e658
@t38miwa
fix: カラム追加に合わせて構造体を修正
facc463
github-actions bot added size_s and removed size_s labels 2 weeks ago
@github-actions
@t38miwa
fix: コメントを適切なものに修正
5b5fd1e
github-actions bot added size_s and removed size_s labels 2 weeks ago
@github-actions
@t38miwa
Author
t38miwacommented2 weeks ago
元のテーブル設計
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
改善したテーブル設計
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
shard_key INT64 NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_id, nokkari_user_activity_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
CREATE INDEX idx_user_nokkari_hit_notices_shard_key_created_at_desc ON user_nokkari_hit_notices(shard_key, user_id, nokkari_activity_id DESC);
インターリーブすると検索性能が早くなる
PKをuser_idにしないといけない
シャードキー先頭を先頭に置けない
ホットスポットが起きやすい設計
prioritylowでゆっくり書き込めば解決するのでは
トランザクション分ける理由
このテーブルに対しては優先度低い
書き込み優先か、読み込み優先か
カラムにシャードキーを追加、PKの先頭にシャードキー
PKの先頭にuser_id、２番目をUUID、ただし追加で時系列のインデックス必要本当はシャードキーをPK先頭において、PKだけで対策できる、でもインターリーブをusersに設定する場合は先頭に置けないからインデックスの先頭にシャードキーを置く
type追加する場合も楽になるかも
トレードオフの考え方
秒間リクエストが300回とかだと読み込み遅くなるかも
秒間リクエストが１桁台なので、問題ないのでは
インターリーブ設定してないとdevelop環境で消えない
インターリーブを維持したい
結論
ワーカーは実装しない
cloud spanner設計
WIP】のっかり的中投稿作成時に、予想師にリアクション通知を飛ばす機能のテーブル設計 #14976
Open
Open
WIP】のっかり的中投稿作成時に、予想師にリアクション通知を飛ばす機能のテーブル設計#14976
@t38miwa
Description
t38miwa
opened 3 weeks ago · edited by t38miwa
２つのパターンの設計で迷っている。それぞれメリットデメリットを比較した結果、パターン2を採用するべきだと考える
Big Query調査結果
最悪のケース：一人の予想師にのっかりが集中、かつその予想が全て当たった場合約5000件挿入されることとなる
パターン1
DDL
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
user_nokkari_hit_notice_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL, -- のっかりで的中したユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, user_nokkari_hit_notice_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
メリット
・表示用APIで実装する読み込み操作時は予想師のidをwhere句に指定して取得することができるため、高速
・ユーザー目線で重要な読み込み操作が高速
→ だと思っていた
デメリット
・特定の予想師に書き込みが集中した場合、最悪のケースを考えた時ホットスポットとなりうる（１レースあたり5000件のレコードの書き込みが短時間で行われる可能性あり）
・仮にホットスポットとなった場合、同じ予想師のuser_idを持つ他のテーブルにも影響が出る可能性がある
・のっかりユーザーの user_activities は別スプリットにあるため、インターリーブの恩恵を受けられない
パターン2
DDL
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
-- user_idはのっかりで的中したユーザーを指す（user_activitiesとのJOIN最適化のため）
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- のっかりしたユーザー
user_nokkari_hit_notice_id STRING(36) NOT NULL, -- UUID
predictor_user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, user_nokkari_hit_notice_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
-- 予想師ごとの通知一覧を新着順で取得するためのインデックス
CREATE INDEX idx_user_nokkari_hit_notices_predictor_user_id_created_at_desc
ON user_nokkari_hit_notices(predictor_user_id, created_at DESC);
メリット
・PKがのっかりした人となるため、配置が分散されホットスポットが起きにくい
・usersテーブルとのインターリーブによってuser_activitiesテーブルと物理的に近い位置に配置されるため、JOIN操作がパターン1より高速となる
・表示用APIの読み込み操作もpredictor_user_idにインデックスを貼っているため、速度にそこまで差がない
デメリット
・読み込み操作のためのインデックスが追加で必要
・user_id がのっかりユーザーを指すので、他の通知テーブルと意味が異なる
スケールした時どうなるか
的中投稿作成数が増えた場合
パターン1はより書き込み操作に負荷がかかりホットスポットになる可能性が高くなる
パターン2はPKがのっかりした人のため、分散されやすく問題になりにくい
読み込みの件数の上限が増えたとき、リクエスト数が増えたとき
パターン1はPK、パターン2はインデックスを貼ったキーを元に読み込み操作を行うため、理論的には差は生まれない
結論
パターン2を採用するべきだと思いました、的中投稿が削除された際の挙動の仕様が確定すればDDLのPR出せます
Activity
t38miwa
t38miwa commented 3 weeks ago
t38miwa
3 weeks ago
Author
MTGでいただいた指摘
・パターン2で予想師idにインデックスを貼っているが、結局そこがホットスポットとなる
・パターン2はテーブルが持つ意味と矛盾したPKとなる
・prioritylowを使用すればパターン1のホットスポット問題を解決できるのでは
・user_nokkari_hit_notice_idは不要では
・PRIMARY KEY(user_id, nokkari_user_activity_id DESC,nokkari_user_id)とするのがいいのでは
t38miwa
t38miwa commented 3 weeks ago
t38miwa
3 weeks ago · edited by t38miwa
Author
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
nokkari_user_id STRING(36) NOT NULL, -- のっかりで的中したユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
インターリーブを設定する理由
・親テーブルのuser_idが分散されているため、インターリーブを設定することでより分散される。インターリーブを設定しない場合、より特定の予想師のスプリットに書き込みが集中することとなる
result_create_workerは６台存在
一番盛り上がったレースでresult_create_workerは１件1-2秒で処理している、全ユーザーの反映処理には２分ほどかかっている
2025-12-30_35_11は5000件ののっかり予想があった
仮に最悪想定の5000件を2分間で処理する場合、平均で42write/secとなり、ホットスポットとなることはないと考えられる
t38miwa
mentioned this
3 weeks ago
のっかりタブへのリアクション通知用のテーブルを追加 #14980
t38miwa
t38miwa commented 3 weeks ago
t38miwa
3 weeks ago
Author
テーブル設計の過程
インデックスの設計などはまだ考慮できていません
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
user_nokkari_hit_notice_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL, -- のっかりで的中したユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- このidを使用して的中投稿の情報を取得
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, user_nokkari_hit_notice_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
通知を基本新着順で並べるため、user_nokkari_hit_notice_idとuser_activity_idにはULIDが値として入る。user_idとnokkari_user_idには従来通りUUIDが値として入る。
ULIDを設定することの懸念はuser_idがUUIDによって分散されるが、特定のユーザーに書き込みが集中した際に先頭に集中しやすくホットスポットとなる可能性があること。
・このテーブルにどの程度書き込みがあるか
・1ユーザーあたりの書き込み偏りはどの程度か
を考慮する必要がある
競技アイコンの表示について
・表示用APIにてtextIconTypeという値が返される。これはどの競技かを判定するもの。リアクション通知タブのいいねタブだとこれはuser_activity_reactionsテーブルからuser_activity_idを取得し、そこからrace_idを取得、race_idをもとにtextIconType（どの競技か）がわかる
user_activity_idのみをuser_nokkari_hit_noticesテーブルに持たせ、レースの場所＋ラウンド数の情報（いわき平 12R）はuser_activitiesテーブルに紐づくrace_idを使用して取得する
場所の名前（いわき平）はrace_idをもとに外部APIから取得
ラウンド数（12R）はrace_idをパースして取得、末尾がラウンド数となっているため
どの競技かの情報が必要である
result-creatorは車券反映処理から重い処理を担当する、また、ticket-updaterはtipstarの根幹となる仕組みで、result-createrはそれに付随する付加価値を生む仕組みを担当する
user_activity_idは名前変更、user_activitiesを見て設計再考する
usersテーブルのインターリーブ必要？user_idをPKにするのはなぜ？user_nokkari_hit_notice_idをPKにすればいいのでは？
論理削除、物理削除について調べる
user_nokkari_hit_notice_idのDESCで新着順にするのはどうなの
書き込み操作と読み込みどっちが重くなりそうか、またユーザーにとってはどっちの方が重要か
サブコメント確定かどうか坂田さんに確認
的中投稿を消した時に「表示できません」と表示するのか（他のいいねなどのタブでどうなっているかロジック含め確認、また企画側としてはどうしたいのか坂田さんと確認）
• [ ]
usersテーブルをインターリーブするべきか
坂田さんにサブコメント確定がどうかを確認、そのために山谷さんに聞いていいか相談
優先順位
必要な情報を全て網羅すること
• [x]
リアクションコメントに加えてサブコメントを追加するか、またそのコメント内容は現在のもので確定か
アイコンは競技ごとに異なるのかそれとも共通か
「初めてハイパーGET！、、、」サブコメントは確定か
的中投稿が削除されたとき、「表示できません」と表示するのかどうするか
ユーザー目線で処理が重いと困る部分はどこか調べる
• [x]
ユーザー目線で考えると書き込み操作が多少遅くても、予想師としてはリアクション通知がリアルタイムでくることはそんなに重要でないように思えるが実際どうか
のっかりタブの読み込みが遅いのはユーザーとしては嫌な気がするがどうか
書き込み、読み込みどちらを重要視するか決める
現在どの程度の負荷がかかることがわかっていて、今後どうなりそうかを調べること
• [ ]
現在の負荷を調べる方法を言葉で説明できるようになる
Big Queryから計測する方法を教えてもらう
今後どうなるか坂田さんやえばたさん、サーバーのメンバーに相談
2,3から負荷対策を考えてテーブル設計する
big queryで調べること
・全体の1日あたりの的中投稿数
・一人の予想師あたり平均いくつの的中投稿が１レースで作成されるのか
・一人の予想師あたりの的中投稿数の最大値（レースが同じ時間、かつ人気予想師などを想定）
・予想師ベースでいいんだっけ、ユーザーベースがいいんだっけ
・pkは乗っかりした人ベースが良さそう、予想師ベースだとホットスポットの可能性あり
big queryからわかったこと
・2026年2月1日、36_01レースで1556件の的中投稿が作成された、これが一つのレースでもっとも的中投稿が作成されたレースである
・2026年2月1日、36_01レースで3362件ののっかり車券が購入された、つまり全部当たっていた場合、一つのレースで3362件の的中投稿が作成されることとなる（また、別日だが、5362件購入されたレースもある）
t38miwa
mentioned this
3 weeks ago
【WIP】のっかり的中投稿作成時に、予想師にリアクション通知を飛ばす #14952
t38miwa
t38miwa commented 2 weeks ago
t38miwa
2 weeks ago
Author
2月19日（木）の振り返りMTGのレビューから変更する点
-- のっかり的中通知
-- 予想師がフォロワーののっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC, nokkari_user_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
・user_idをテーブルの一番上に記載（慣習に合わせる）
・PKの最後にnokkari_user_idを追加
のっかりタブへのリアクション通知用のテーブルを追加#14980Mergedt38miwamerged 19 commits intodevelopfromfeature/add-user-nokkari-hit-notices-tablelast week+159Lines changed: 159 additions & 0 deletionsConversation26 (26)Commits19 (19)Checks10 (10)Files changed3 (3)Mergedのっかりタブへのリアクション通知用のテーブルを追加#14980t38miwamerged 19 commits intodevelopfromfeature/add-user-nokkari-hit-notices-table
Conversation
@t38miwa
t38miwacommented3 weeks ago
なぜこの変更が必要なのか (why & what)
のっかり的中通知機能とのっかり通知機能のために新たなテーブルが必要なため
どのように実現するのか (how)
user_nokkari_hit_noticesテーブルを追加する
DDL
-- のっかり的中通知
-- 予想師がのっかり的中を通知タブで確認するためのテーブル
CREATE TABLE user_nokkari_hit_notices (
user_nokkari_hit_notice_id STRING(36) NOT NULL, -- サロゲートキー(UUID)
user_id STRING(36) NOT NULL, -- 予想師（通知を受け取る側）
nokkari_user_id STRING(36) NOT NULL, -- のっかりしたユーザー
nokkari_user_activity_id STRING(36) NOT NULL, -- のっかりユーザーのアクティビティID(ULID)
shard_key INT64 NOT NULL, -- 書き込み分散用
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
) PRIMARY KEY (user_id, user_nokkari_hit_notice_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
CREATE INDEX idx_user_nokkari_hit_notices_shard_key_user_id_created_at_desc
ON user_nokkari_hit_notices (shard_key, user_id, created_at DESC);
PKにそれぞれuuidのuser_idとnokkari_hit_notice_idを持たせることで、ホットスポットとならないよう対策している
シャードキーをインデックステーブルの先頭に持たせることで、ホットスポットとならないように対策している
このインデックスを貼ることで、シャードキーの値ごとにuser_idを検索するようになるため、フルスキャンより読み取り操作が高速となる。その理由はSQL文が以下のようになる予定だからである。
WHERE shard_key BETWEEN 0 AND 99
AND user_id = '特定の予想師のユーザーID'
これにより
shard_key = 1 user_id = '特定の予想師のユーザーID' を探す
shard_key = 2 user_id = '特定の予想師のユーザーID' を探す
shard_key = 3 user_id = '特定の予想師のユーザーID' を探す
という検索方法となり、フルスキャンに比べて早く読み取り操作ができる
実装上の懸念（特にレビューしてほしい部分など）
・テーブル名に問題はないか
・インデックスの貼り方が検索を早める実装となっているか
・テーブル、インデックステーブル両者に対して十分なホットスポット対策がなされているか
仕様 (JIRA, Issue, design doc へのリンク)
https://www.notion.so/FEED-2fecee5fb72581e1b8a0c79f185ca19c
参考リンク
テーブル設計に関するissue
#14976
テーブル設計の議論のslackスレッド
https://mixi-tipstar.slack.com/archives/C022L6YSV1U/p1771401181064409
@t38miwa
feat: のっかりタブへのリアクション通知用のテーブルを追加
d0309b5
github-actions bot added the size_s label 3 weeks ago
@github-actions
@github-actions
github-actions botcommented3 weeks ago
[test-warn] These files have no test files 🐶
Every changed file has test file 🎉
@github-actions
github-actions botcommented3 weeks ago
spanner-diff
CREATE TABLE user_nokkari_hit_notices (
nokkari_user_activity_id STRING(36) NOT NULL,
user_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
diff checksum: e5f4995380f99f5b1128219e53cbd2d8
t38miwa added 2 commits 3 weeks ago
@t38miwa
feat: 新規テーブル追加のため、構造体定義を追加
b146015
@t38miwa
Merge branch 'develop' into feature/add-user-nokkari-hit-notices-table
19fbf75
github-actions bot added size_s and removed size_s labels 3 weeks ago
@github-actions
t38miwa marked this pull request as ready for review 3 weeks ago
@t38miwa
Copilot AI review requested due to automatic review settings 3 weeks ago
sb-dev-app bot requested a review from mixi-sb/project-z-server 3 weeks ago
@sb-dev-app
Copilot started reviewing on behalf of t38miwa 3 weeks ago
github-actions bot added size_s and removed size_s labels 3 weeks ago
@github-actions
Copilot AI reviewed 3 weeks ago
Copilot AIleft a comment
Pull request overview
のっかり的中投稿作成をトリガーに、予想師へリアクション通知を出すための永続化テーブル（Spanner）と、それに対応するGoモデルを追加するPRです。
Changes:
Spanner に user_nokkari_hit_notices テーブルを追加
Go のドメインモデル UserNokkariHitNotice を追加
model.gen.go に当該モデルのテーブル/カラム/PKメタ情報を追加
Reviewed changes
Copilot reviewed 3 out of 3 changed files in this pull request and generated 2 comments.
File Description internal/api/domain/model/user_nokkari_hit_notice.go user_nokkari_hit_notices に対応するモデルを追加 internal/api/domain/model/model.gen.go 生成コードに UserNokkariHitNotice のテーブル定義（TableName/Columns/PK等）を追加 db/spanner.sql user_nokkari_hit_notices テーブルDDLを追加 db/spanner.sqlOutdateddb/spanner.sqlOutdated
t38miwa changed the title feat: のっかりタブへのリアクション通知用のテーブルを追加 のっかりタブへのリアクション通知用のテーブルを追加 3 weeks ago
@t38miwa
@t38miwa
Author
t38miwacommented3 weeks ago
のっかりされた通知が今後増える予定
・テーブルを分けるか、それとも共通にするか
わけてもPKは同じ、同じスプリットに配置される可能性が高い
共通ならtypeのカラムが必要、それ用のインデックスも必要
・非同期でのっかり関連の処理をするため、のっかり用のワーカーを別で立てる
→ 今後のっかり系の実装が増えたとき、対応可能
のっかり通知作る時に考えるでもいいかも
このタイミングでワーカーに切り出す
リトライの際に再度車券反映も行う必要がある
車券反映のトランザクションが成功した後に通知のトランザクションこけたとき、問題になる
t38miwa added 3 commits 2 weeks ago
@t38miwa
feat: のっかり通知共通テーブルとするため、カラムを追加
d96d5b1
@t38miwa
chore: submoduleの参照コミットをorigin/developに合わせる
22cfa08
@t38miwa
Merge branch 'develop' into feature/add-user-nokkari-hit-notices-table
3ff0b69
github-actions bot added size_s and removed size_s labels 2 weeks ago
@github-actions
@github-actions
github-actions botcommented2 weeks ago
spanner-diff
CREATE TABLE user_nokkari_notices (
user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
nokkari_notice_type STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
CREATE INDEX idx_user_nokkari_notices_user_id_nokkari_notice_type_nokkari_user_activity_id_desc ON user_nokkari_notices(user_id, nokkari_notice_type, nokkari_user_activity_id DESC), INTERLEAVE IN users;
diff checksum: b51ac11adab4c964b252e5f7d4b8e658
@t38miwa
fix: カラム追加に合わせて構造体を修正
facc463
github-actions bot added size_s and removed size_s labels 2 weeks ago
@github-actions
@t38miwa
fix: コメントを適切なものに修正
5b5fd1e
github-actions bot added size_s and removed size_s labels 2 weeks ago
@github-actions
@t38miwa
Author
t38miwacommented2 weeks ago
元のテーブル設計
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_activity_id DESC),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
改善したテーブル設計
CREATE TABLE user_nokkari_hit_notices (
user_id STRING(36) NOT NULL,
nokkari_user_id STRING(36) NOT NULL,
nokkari_user_activity_id STRING(36) NOT NULL,
shard_key INT64 NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
) PRIMARY KEY(user_id, nokkari_user_id, nokkari_user_activity_id),
INTERLEAVE IN PARENT users ON DELETE CASCADE;
CREATE INDEX idx_user_nokkari_hit_notices_shard_key_created_at_desc ON user_nokkari_hit_notices(shard_key, user_id, nokkari_activity_id DESC);
インターリーブすると検索性能が早くなる
PKをuser_idにしないといけない
シャードキー先頭を先頭に置けない
ホットスポットが起きやすい設計
prioritylowでゆっくり書き込めば解決するのでは
トランザクション分ける理由
このテーブルに対しては優先度低い
書き込み優先か、読み込み優先か
カラムにシャードキーを追加、PKの先頭にシャードキー
PKの先頭にuser_id、２番目をUUID、ただし追加で時系列のインデックス必要本当はシャードキーをPK先頭において、PKだけで対策できる、でもインターリーブをusersに設定する場合は先頭に置けないからインデックスの先頭にシャードキーを置く
type追加する場合も楽になるかも
トレードオフの考え方
秒間リクエストが300回とかだと読み込み遅くなるかも
秒間リクエストが１桁台なので、問題ないのでは
インターリーブ設定してないとdevelop環境で消えない
インターリーブを維持したい
結論
ワーカーは実装しない