インデックスのないカラムが、いかにして私たちのデータベースを壊したか
たった一つのインデックスの欠如が、8ミリ秒のAPIを8秒間の悪夢へと変えた。
大規模なリリースを前に、負荷テストを実施した。ローカルの開発環境では、100行程度のデータなら問題なく動作していた。ステージング環境では、150万行のデータに対して500人のユーザーをシミュレートした。
すべてが崩壊した。
- APIのレスポンスタイムが45ミリ秒から8,000ミリ秒へと跳ね上がった。
- データベースのCPU使用率が100%に達した。
- コネクションプールが枯渇した。
- リクエストがタイムアウトした。
原因は、user_id と status で注文履歴を取得する単純なクエリだった。
PostgreSQLで EXPLAIN ANALYZE を実行したところ、Sequential Scan（シーケンシャルスキャン）が発生していることが判明した。user_id にインデックスがなかったため、エンジンはリクエストごとに150万行すべてを読み取っていた。
100件の同時リクエストが発生した際、データベースは一度に1億5,000万行をスキャンしていた。
修正にかかった時間はわずか5分だった。テーブルをオンライン状態に保つため、CREATE INDEX CONCURRENTLY を使用して (user_id, status, created_at DESC) に複合インデックスを作成した。
結果：
- クエリ実行時間は8,150ミリ秒から0.14ミリ秒に短縮された。
- APIのレイテンシは8秒から12ミリ秒に低下した。
- CPU使用率は100%から8%未満へと急落した。
学んだ教訓：
- ローカルテストは誤解を招く。100行のデータは、100万行の代わりにはならない。
- 外部キーにはインデックスを貼ること。ほとんどのORMはこれを見落としがちだ。
EXPLAIN ANALYZEを実行すること。データベースが問題箇所を教えてくれる。- ハードウェアを追加購入する前に、クエリを最適化すること。
サーバーをスケールさせる前に、クエリをスケールさせよ。