GitLabのPostgresスキーマ設計に関する私のメモ (2022)
(shekhargulati.com)GitLabのPostgresスキーマ設計に関する私のメモ
- GitLabのPostgresスキーマを調べることで、独自に設計するスキーマと比較し、GitLabのスキーマ定義からベストプラクティスを学び取ることを目的とした。
- GitLabはオープンソースのDevOpsプラットフォームで、GitHubの代替として、自社でホスト可能。
適切な主キー型の選択
- データベースが小さいうちは目立たないが、成長するにつれて主キーはストレージ容量、書き込み速度、読み取り速度に影響を与える。
- GitLabでは、573のテーブルのうち380が
bigserialの主キー型、170がserial4を使用し、残り23が複合主キーを使用している。
内部IDと外部IDの利用
- 主キーを外部世界に公開しないことはよいプラクティスである。
- GitLabは、
issues、ci_pipelines、deployments、epicsなどのテーブルで、内部ID（id）と外部ID（iid）の両方を使用している。
text型とCHECK制約の使用
- GitLabのスキーマは
character varying(n)とtextの両方を使用しているが、text型の方が頻繁に使われている。 text型は長さの制約を持たず、CHECKを使って長さ制約を定義する。
命名規則
- すべてのテーブルに複数形を使用し、モジュール名のプレフィックスでネームスペースを提供する。
- テーブルと列名はsnake_case規則に従う。
タイムスタンプのタイムゾーン設定
- GitLabは
timestamp with timezoneとtimestamp without timezoneの両方を使用している。 - システム操作には
timestamp without timezoneを、ユーザー操作にはtimestamp with timezoneを使用している。
外部キー制約
- GitLabはほとんどのテーブルで外部キー制約を使用しているが、
audit_events、abuse_reports、web_hooks_logs、spam_logsなどの一部のテーブルでは使用していない。
大きなテーブルのパーティショニング
- GitLabはクエリのパフォーマンス向上のため、サイズが拡大しうるテーブルをパーティション分割する。
Trigramとgin_trgm_opsを使用したLIKE検索ユースケースのサポート
- GitLabはGIN（Generalized Inverted Index）インデックスを使用して効率的な検索を実行している。
jsonbの使用
- GitLabのスキーマでは複数のテーブルで
jsonbデータ型を使用している。
その他のヒント
- 更新可能なテーブルには
updated_atのような監査フィールドを使用し、更新不可能なログテーブルでは使用しない。 - Enumsは
character varyingの代わりにsmallintで保存され、スペースを節約している。
GN⁺の意見:
- GitLabのスキーマ設計はデータベース設計に対する洞察を提供し、特に大規模システム向けのスキーマ最適化に関する重要な教訓を含んでいる。
- GitLabがオープンソースであるため、このようなスキーマ設計の意思決定は、他の開発者が自分のプロジェクトに適用できる実践的な例を提供する。
- GitLabのスキーマから学べることは、データ型の選択、インデックス戦略、パーティショニング、外部キー制約の使用など、データベースの性能と保守性に大きな影響を与える要素を慎重に考慮することが必要だという点だ。
1件のコメント
Hacker News のコメント
主キーを外部に公開するなという慣行がなぜ必要なのか疑問。リクエストはいずれにせよ認証されるべきなら、ID の推測を防ぐことにどんな価値があるのかも分からない
推測した ID だけで認証・認可なしに何か有用なことができるなら、すでに別の場所が深刻に壊れているということであり、スキーマに不要な複雑さを加えるより、そこに注力すべき。競合他社が顧客数を推定できないようにする競争情報の秘匿程度なら価値があるかもしれないが、GitLab がそれを大きく気にしていたとは思えない。GitLab の
id + iidという判断は、内部 ID の推測防止よりもクエリ性能上の要件のほうが大きかった可能性が高そうUUID をユーザーに公開していれば、同じ脆弱性があっても攻撃者は UUID を当てなければならないためずっと難しくなり、二次的な情報源が必要になる場合もある。データ漏えいは起きても対応する時間ができ、漏えい量も算定可能になる。逆に連番 ID なら、問題は即座に全面的な漏えいへと拡大し、個人情報監督機関へ義務報告が必要な大規模インシデントになり得る。こういうものは本来役に立たないはずの多層防御だが、実際にこのやり方で破られたひどいソフトウェアは存在する
Issue テーブルの主キー
idを公開すると、プロジェクトで Issue を作るときに 1 から始まらないため、GitLab 全体にどれだけ Issue があるかを簡単に推測できるもちろん、推測した ID だけで認証・認可なしに何かできるなら大問題であることは確か。しかしその時点で他の層がなければ、すでにゲームオーバーだ。バグは事前に知らせてくれないし、特に微妙なバグはなおさらだ。そうしたバグが現れたとき、せめて ID を推測不能にしておけば、システムの全ユーザーアカウントにまで簡単にアクセスされる事態を避けられたと感謝することになるだろう
当然、まずは正しいアクセス制御を保証することに集中すべきだが、推測不能な ID はひどい災害とヒヤリハットの差を生み得る。UUID が合わないなら、自動増分のデータベース ID を使い、それを暗号化する方法も可能で、適切なソフトウェア層があれば暗号化 ID もほぼ自動で機能する
公開リポジトリ 1億2,800万個の大半は他のリポジトリのフォークで、主リポジトリにプルリクエストを作るためだけに存在しているので、ミスをしない限り Issue はなさそう
小さなトイプロジェクトやすぐ放棄されたプロジェクトも、Issue がないかごく少ない可能性が高い。数百・数千件の Issue があるプロジェクトは確かにあるだろうが、1億2,800万リポジトリ全体の平均はかなり低く、20億の上限内に収まる可能性が高い。それでも、そのテーブルに 4 バイト型、正確には 31 ビットを使うのは、github.com を含む一部の組織にとって時限爆弾だという点には同意する
https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
PRIMARY KEY(repo_id, issue_id)のような基本的な要件が ActiveRecord の中では不必要に複雑になり、単一の主キーカラムを要求する ActiveRecord に合わせるため、一意キーと別の主キーを併用する回避策が必要になる。UUID 主キーも回避策のように見えるが、(repo_id, issue_id)ペアの一意制約は依然として必要で、データベースサイズとオーバーヘッドが増える。より広く見ると、Ruby on Rails の単一モデル・コントローラ・ビュー層に基づくモノリシックな MVC 構造も、アプリケーションが大きくなるほどスケーラビリティと保守性の問題を生み、MVC はモジュール型またはコンポーネントベースの構造のほうが合っていると思うセルフホスティングとクラウドの両方を提供する製品は、顧客別データベースを好むことが多かった。ホスティング形態に関係なく同じクエリを使えるように、コードベースの共有部分がずっと単純になるからだ。顧客別データベースなら、そのような利用量上限に近づくことはほとんどなく、近づくならセルフホスティングのほうが適しているだろう
intからbigintへマイグレーションすることは可能。準備とカスタムコードが少し必要だが、無停止でできるおおむねこの手順に従って大規模マイグレーションを管理しており、少し修正して使っている: http://zemanta.github.io/2021/08/25/column-migration-from-in...
外部キー、インデックス、制約条件全般がプロセスを難しくするが、不可能ではない。自分の場合、データマイグレーションには数時間かかったが、速い必要はなかった。GitLab には、バージョンアップ中のどこでも動作するように、アップグレード後ジョブを実行するツールがあると認識している
UUID カラムの保存サイズについての話は説得力が弱い。テーブルにほかのカラムが 5 つあるなら、128ビット対64ビットの差は大きくない
より重要な懸念は性能である。UUIDv4 は広くサポートされているが、完全にランダムなのでインデックス性能には理想的ではない。UUIDv7[0] は Snowflake[1] に近く、時間的局所性があるが、実装はまだそれほど広く普及していない。別のアプローチとして、
bigserialを使ってキーを暗号化する方法もある: https://github.com/abevoelker/gfc64しかしこの方式は、1) 秘密値をローテーションできず、2) 一度漏えいすると誰でもテーブルサイズをフェルミ推定できる。公開 ID と内部 ID を分けるのは面倒で、公開 ID が UUIDv4 なら性能も犠牲にする。UUIDv7 が最も多くの条件を満たす解だと考える
[0]: https://uuid7.com/
[1]: https://en.wikipedia.org/wiki/Snowflake_ID
idが外部キーとして使われるすべての場所と、それらの外部キーカラムに必要なインデックスサイズも問題になるユーザー ID のように、データベース全体で数十、数百の外部キーから参照されうる値を考えればよい
データベース性能には 3 つの段階がある。1) インデックスとデータの両方がメモリに収まる。2) インデックスはメモリに収まるがデータは収まらない。3) インデックスとデータの両方がメモリに収まらない。1 なら望ましいが、そうでないなら何としても 2 を守らなければならない。インデックスサイズが 2 倍になることは、それをより難しくする
typedef void*のような基本ポインタだと考えればよい。サイズはメモリ・ディスク使用量、スループットのボトルネック、結合や検索の最も深い内部ループでキーを比較する CPU 時間まで、全体的な性能に影響するx86-64 CPU が登場したとき、64 ビットポインタ移行の性能影響があまりに大きかったため x32/ilp32 が作られ、.NET が今でも “prefer 32-bit” をデフォルトにしている理由もそこにある。データベースの主キーに128ビット UUIDを使うのはひどい間違いである
たとえば動画を公開の 1 か月前にアップロードしても、視聴者にはその事実を知られたくないことがある
URL やメールなどに入れて表示するときに値を暗号化することも可能である: https://wiki.postgresql.org/wiki/Pseudo_encrypt
こうすれば、連番インデックスの利点をかなり維持しつつ、キーの変更も可能になる。ただしキーを変えるとブックマークが壊れ、過去のメールで送ったリンクが無効になり、実質的にはすべての名前を変更するのと同じ効果が残る
細かい揚げ足取りだが、
text対varcharのセクションには触れておきたい筆者は存在しない性能差を証明しようとして多くを語った後で、「2 つの型の間に大きな性能差はない」と結論づけている。これはずっと前に決着のついた話題で、「大きくない」のではなく「ない」のである。PostgreSQL Wiki[1] は、非常によい理由がない限り
textを使うよう明記しており、ドキュメント[2] も「多くの目的ではcharacter varyingはtext上のドメインのように動作する」と述べ、緑色の Tip ボックスで「この 3 つの型の間に性能差はない」と言っている。したがって GitLab がほとんどtextを使っているのは、ドキュメントを読み、中途半端に「移植可能」なスキーマではなく PostgreSQL に合わせてスキーマを設計したということに見える[1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...
[2] https://www.postgresql.org/docs/current/datatype-character.h...
varchar(300)をvarchar(200)に変えるにはすべての行を書き直す必要があるが、textカラムの制約条件を更新するのは基本的にほぼ無料で、既存の値が新しい制約を満たすか確認する全テーブルスキャンだけで済む。本文でも、text型とCHECK制約を使えば、長さチェックがある場合にcharacter varyingやvarchar(n)よりもスキーマ進化が容易だとしている外部キーは高コストだという話はよく繰り返されるが、ベンチマークは少ない主張である
誤った実装方法はいくらでもあるが、いずれにせよスタックのどこかでは整合性を強制している。それを再実装するよりデータベースを活用するには知識と実験が必要で、たいてい大きな事故を防いでくれる
GitLabとGitHubの性能差を誰かが整理したり、注目して見たことがあるのか気になる
どちらもRailsベースのアプリケーションだが、全体的なページ読み込み時間はGitLabがGitHubに比べてひどいと感じる
ChromeとGitHubは、ユーザー体験を損ねてでもあらゆる小技を使うだろう。たとえば会社のGitHubでマージdiffを開き、
Ctrl Fで検索して結果がなければ次へ進み、Git履歴を手作業でたどっていたところ、100番目のdiffになってようやく、いちばん重要なファイルが奥深くに隠れていたことに気づいたことがある。誰かがページ読み込み指標を満たして昇進しやすくするためだったのだろうGitLabの文化と性能軽視を理解する助けになるかもしれない元開発者の記事がここにある: https://news.ycombinator.com/item?id=39303323
私はGitLabを性能問題を実感するほど十分には使っていないが、この記事は参考になると思う
CI変数
CI_PIPELINE_IIDとCI_MERGE_REQUEST_IIDに付いている追加のIが何を意味するのか、いつも気になっていたデータベース関連の選択だろうと推測していたが、この記事がそれを確認してくれた
「1 quintillionは1,000,000,000 billionに等しい」と見ると、私たちがだいたい
int32とint64の間でしか選ばないのはかなり不思議に思える。約1兆個のカーディナリティをサポートする5バイト整数型があってもよさそうだ自動増分IDを使わないのは合理的かもしれないが、内部用と外部用でIDを2つ持つ利点はよく分からない
カラムとインデックスの数が増え、常に先に検索が必要になり、内部キーを変えつつ外部キーは変えないというセキュリティ上のシナリオも思い浮かばない。何か見落としているのだろうか?
PostgreSQLネイティブのUUID v4型を
bigserialの代わりに使うと、テーブルサイズが25%大きくなり、挿入率がbigserialの25%まで落ちるというが、なぜUUIDv4がそれほど悪いのか気になるUUIDはただの128ビット数値ではないのか? 生成コストがものすごく高いのか、何が起きているのか分からない
そのためUUIDv4カラムのインデックス処理が遅くなり、これがUUIDv6とUUIDv7の開発動機になった
より大きな問題は挿入率だ。UUIDの場合、挿入率は利用可能なRAM量によって制限される。自動増分整数はそうではない。整数は時間と相関するが、UUID4はランダムなので、規模が大きくなると性能特性が根本的に変わる。小さなテーブルでは挿入ペナルティはほぼ無視できるが、Bツリーインデックスのサイズがメモリ限界に達すると、PostgreSQLはUUIDのBツリー全体をメモリに置けず、ディスクページの入れ替えに依存する。自動増分整数では、時間的に近い行が同じインデックスページを使うため、同じ負荷でもディスクに当たる必要がない。この規模に達すると、差は一定の25%低下ではなく25倍の性能の崖であり、スキーマ移行を除けばRAMを増設するしかない
UUIDはソートされていない書き込みを大量に生み、ページ肥大につながる
バッチ挿入の局所性も後で照会するときに悪くなり、関連レコードをランダムに探す必要が出る。結局、挿入時点とその後の選択時点の両方でコストを払うことになる