Database Design Mistakes I Made So You Don't Have To \u2014 TXT1.ai

三年前のブラックフライデー、午前2時47分に私たちのスタートアップのデータベースが完全に停止したのを見ました。50,000人の同時ユーザー、200万ドルの保留中の取引、そして製品の在庫状況を返すのに45秒かかるクエリがありました。CTOはSlackで叫んでいました。投資家からの電話が鳴っていました。そして、私は6ヶ月前にデザインしたスキーマを見つめて、私が行ったすべての「賢い」決定が、今では失われた収益で約1分当たり8,000ドルのコストをかけていることに気づきました。

私はマーカス・チェンです。過去12年間、データベースアーキテクトとして、スクリュープなSaaSスタートアップからフォーチュン500の企業まで、さまざまな人たちと仕事をしてきました。5億の毎日の取引を処理するシステムのスキーマをデザインし、重要なパスから200msを削減するクエリを最適化し、そして、はい、私はほぼすべてのデータベース設計のミスを経験してきました。あのブラックフライデーの事件は、私のコンピュータサイエンスの学位よりもデータベース設計について多くのことを教えてくれました。

今日はTXT1.aiの主任データベースアーキテクトとして、私たちのAIパワードコミュニケーションプラットフォームを介して年間30億件以上のテキストメッセージを処理しています。しかし、私は失敗から進化することでここにたどり着きました。そして、2時のパニック攻撃や投資家からの怒った電話をスキップできるように、私が学んだ高価な教訓を共有したいと思います。

「UUIDをどこにでも使う」という災厄

私が$40,000の失敗と呼ぶものから始めましょう。2019年、私は中規模のEコマース企業のために顧客関係管理システムをデザインしていました。私はちょうどUUIDが主キーを扱う「現代的」な方法だというブログ投稿を読みました—オートインクリメント整数も、連続的なIDの露出も、分散システムに完璧です。私は心を掴まれました。

だから、システム内のすべての主キーをUUIDにしました。ユーザーテーブル？UUID。オーダーテーブル？UUID。注文のラインアイテム？お察しの通り—UUID。私は天才だと思った。スキーマはきれいに見え、連続IDの脆弱性はなく、必要であればクライアント側でIDを生成できました。何が問題になるでしょうか？

すべてのことが。完全にすべてがうまくいかなかったのです。

6ヶ月以内に、私たちのデータベースサイズは340GBに膨れ上がり、180GBであるべきところがそうなりました。クエリのパフォーマンスは週ごとに悪化していました。インデックスサイズは膨大で、オーダーテーブルのインデックスだけで12GBでした。オーダーとラインアイテムの間の結合は、50msで済むべきところが800msかかっていました。データベースはディスクI/Oに膨大な時間を費やしていて、私たちのAWS RDSコストはほぼ倍増していました。

私が苦労して学んだこと：UUIDは128ビット（16バイト）に対して、4バイトの整数または8バイトのbigintです。これは、主キーごとに4倍のストレージを意味します。しかし、真の問題はストレージではありません—それはインデックスの断片化です。UUIDはランダムなので、すべての挿入はBツリーインデックスのランダムな書き込みを引き起こします。順序整数では、新しい行はインデックスの終わりに追加されます。UUIDでは、データベースは常にインデックス構造全体の再バランスを行っています。

私たちは影響を測定しました：整数IDを持つ100,000行を挿入するのに8秒かかりました。同じ操作をUUIDで行うと34秒かかりました。これは、主キーの選択から生じる4.25倍のパフォーマンスペナルティです。1日あたり50,000件の注文を処理していると、それはすぐに積み重なります。

修正には、3週間の開発時間がかかり、メンテナンスウィンドウの間に慎重に整えられた移行が必要でした。高ボリュームテーブルにはbigint主キーに移行し、分散システム全体で真にグローバルにユニークな識別子が必要なテーブルにはUUIDを保持しましたが、それは47のテーブルの中で正確に2つだけでした。

私のルール：特定の文書化された理由がない限り、主キーにはオートインクリメント整数またはbigintを使用します。「より現代的に見える」という理由は文書化された理由ではありません。

早すぎる正規化: 第三正規形が敵になるとき

大学を出たばかりの私は、正規化に夢中でした。私はすべての正規形を暗記し、Coddのルールを寝ている間に唱え、適切に正規化されたデータベースが設計の卓越性の頂点であると信じていました。だから、私が最初の生産システム—コンテンツ管理プラットフォーム—をデザインするとき、私はすべてを第三正規形以上に正規化しました。

「今日あなたが行うすべての「賢い」データベースの決定は、6ヶ月後の午前2時の危機の潜在的な原因です。あなたの持つべきシステムのために設計してください、望むシステムのためではありません。」

私はユーザーテーブル、user_addressesテーブル（ユーザーは複数の住所を持つ可能性があるため）、user_phone_numbersテーブル（複数の電話！）、user_preferencesテーブル、user_settingsテーブル、およびuser_metadataテーブルを持っていました。単一のユーザーのプロフィールを読み込むには6つのテーブルを結合する必要がありました。私はすべてが「クリーン」に見えることに非常に誇りを持っていました。

そして、私たちは立ち上げました。ユーザープロファイルページ—アプリケーション全体で最も頻繁にアクセスされるページ—は、1.2秒かかりました。私たちは、各ページビューのために6つの結合を行っており、1万人の日次アクティブユーザーがいるため、プロファイルビューだけで毎日60,000回の結合を行っていました。データベースのCPUは常に70％以上でした。

目が覚めたのは、リード開発者が私を呼び寄せて、クエリ実行プランを見せてくれたときでした。「マーカス」と彼は言いました、「ユーザーの名前、メール、電話番号を表示するために6つのテーブルを結合しています。これはクレイジーです。」彼は正しかった。私は理論的な純度のために最適化し、実際的なパフォーマンスを考慮していなかったのです。

私たちは戦略的に非正規化しました。ユーザーの主な住所はユーザーテーブルに戻りました。彼らの主な電話番号も同様です。私たちは追加の住所と電話番号のための別々のテーブルを保持しましたが、94％のユーザーはそれぞれ1つしか持っていませんでした。その単一の変更により、平均プロファイルページのクエリ時間を1.2秒から180msに短縮しました—85％の改善です。

私が学んだこと：正規化はツールであり、宗教ではありません。第三正規形は素晴らしいスタートポイントですが、現実のパフォーマンスはしばしば戦略的な非正規化を必要とします。今では私は「80/20非正規化ルール」と呼ぶものに従っています—もし80％のクエリが複数のテーブルからデータを必要とする場合、そのデータはおそらく1つのテーブルに所属するべきです。私は本番環境でのクエリパターンを測定し、理論的な純度ではなく実際の使用に基づいて非正規化を行います。

鍵は非正規化が必要なときを知ることです。読み取りが多く、書き込みが少ないテーブルは完璧な候補です。ユーザープロフィール、商品カタログ、構成データ—これらは非正規化するのに優れた場所です。書き込みボリュームが高いトランザクションテーブル？それらは更新の異常を避けるために正規化を保ってください。

NULLの悪夢: 任意が不可能になるとき

私はかつてNULL許容カラムが好きでした。彼らは非常に柔軟で、受け入れ能力があるように見えました。ユーザーにはミドルネームがないかもしれません？NULLを許可します。注文には割引コードがないかもしれません？NULLを許可します。製品には重さがないかもしれません？あなたはアイデアを得ています。

特にひどいプロジェクトでは、全テーブルの約60％のカラムがNULL許容でした。合理的だと思えました—すべてのフィールドに常にデータがあるわけではありませんよね？ NULLが「値がない」を明確に伝えるのに、デフォルトを強制する必要はありますか？

問題はすぐに始まりました。クエリはNULLチェックの地雷原になりました。重さのないすべての製品を見つけたいですか？「WHERE weight IS NULL」が機能すると思ったでしょうが、重さが明示的にゼロに設定された製品はどうしますか？「WHERE weight IS NULL OR weight = 0」が必要です。注文合計を合計したいですか？ COALESCEを使わないと、個々の値がNULLの場合、SUMがNULLを返す可能性があります。

B