インデックスのないカラムがいかにして私たちのデータベースを壊したか
たった一つのインデックスの欠如が、8ミリ秒のAPIを8秒間の悪夢に変えた。
メジャーリリースの前に負荷テストを実施しました。ローカル開発環境では100件のレコードで問題なく動作していました。その後、本番環境規模のデータ（150万行）に対して、500人のユーザーをシミュレートしました。
結果は悲惨なものでした：
- APIのレスポンスタイムが45msから8,000msに急増。
- データベースのCPU使用率が100%に到達。
- コネクションプールが枯渇し、タイムアウトエラーが発生。
スロークエリログを確認したところ、ユーザーIDとステータスによって注文履歴を取得しているエンドポイントが見つかりました。
PostgreSQLで EXPLAIN ANALYZE を実行したところ、Sequential Scan（シーケンシャルスキャン）が示されました。user_id にインデックスがなかったため、エンジンはリクエストごとに150万行すべてを読み取っていました。
100件の同時リクエストにより、一度に1億5,000万行のスキャンが行われていました。
修正にかかった時間はわずか5分でした。
単純な user_id へのインデックスではなく、(user_id, status, created_at DESC) に対する複合インデックスを作成しました。これにより、データベースは以下のことが可能になりました：
user_idによるフィルタリング。statusによるフィルタリング。- 最新の行を即座に返す。
- 余分なソート工程をスキップする。
操作中にテーブルがロックされないよう、CREATE INDEX CONCURRENTLY を使用しました。
修正後：
- クエリ時間が8,150msから0.14msに短縮。
- APIのレイテンシが8秒から12msに低下。
- CPU使用率が100%から8%未満に低下。
学んだ教訓：
- ローカルテストは当てにならない。100行は100万行の代わりにはならない。
- 外部キーにはインデックスを貼ること。多くのORMはこれを忘れている。
- ハードウェアを追加購入する前に
EXPLAIN ANALYZEを実行すること。 - 実際に実行するクエリに基づいてインデックスを設計すること。
サーバーをスケールさせる前に、クエリをスケールさせなさい。