【ITニュース解説】I Nearly Lost All My Users to 30-Second Database Queries

ソロ開発者が手がけたMylinxというプロジェクトは、Linktreeの代替サービスとして開始された。このサービスはわずか3ヶ月で構築され、レイアウトや色、テーマのフルカスタマイズ、音楽や動画の埋め込み機能、オフラインプロモーション用のQRコード、SEO制御、URL短縮機能など、多岐にわたる機能を提供し、月額750ドルもの収益を上げるまでに成長した。

プロジェクト開始から約1年間は順調に稼働していたが、分析機能で大きな問題が発生し始めた。開発当初、一般的な開発者と同じように「後で最適化しよう」という考えのもと、ウェブサイトへのすべてのクリックや閲覧を詳細な生データとして、二つのテーブル（HitPageとHitPageLink）に直接保存していた。このシンプルな方法が、時間が経つにつれて問題の根源となった。

12ヶ月が経過すると、データベースには毎月15万行を超える新しいデータが追加されるようになり、データ量が急増した。その結果、分析レポートを表示するためのデータベースクエリが30秒以上かかるようになり、最終的にはタイムアウトして機能しない状態に陥った。ユーザーからは分析機能が壊れているという苦情が相次ぎ、中には返金を求める声も上がった。月750ドルの収益がある中で、このような技術的な問題が原因で顧客を失うことは、開発者にとって非常に痛い経験であった。さらに、データベースの運用コストも上昇し、経済的な負担も増大していた。

この深刻な状況に対し、開発者は二つの解決策を検討した。一つは、ClickHouseやTimescaleDBのような高性能なデータベースシステムを導入し、大規模な分析インフラを再構築するという企業向けのアプローチである。もう一つは、既存のシステムや予算という制約の中で、創造的な方法で解決策を見出すという、ブートストラップ（自力での立ち上げ）型のアプローチだった。ソロ開発者として、限られたリソースの中で現実的な解決策を探すため、後者のアプローチを選択した。

解決策を探すにあたり、開発者はInstagramのような大手プラットフォームが膨大な分析データをどのように扱っているかを研究し、その知見を自身のプロジェクトに適用した。彼らが採用しているのは「階層型ストレージ」という考え方で、データの鮮度や利用頻度に応じて異なる扱いをするものである。具体的には、直近0日から90日分の最も新しいデータは「ホットデータ」として、リアルタイムに近いクエリのために元のHitPageテーブルにそのまま保持する。次に、3ヶ月から12ヶ月前のデータは「ウォームデータ」として、毎日集計処理を行い、日ごとのサマリーデータとして別のテーブルに保存する。最後に、1年以上前の古いデータは「コールドデータ」として、必要に応じてアーカイブするか、あるいは削除する。このアプローチの重要なポイントは、ほとんどの分析クエリは個々の詳細なヒットレベルのデータまで必要とせず、日ごとの集計データで十分だという洞察に基づいている。これにより、膨大な生データから必要な情報を素早く引き出すことが可能になる。

この階層型ストレージを実現するため、開発者はHitPageDailyという新しいデータベーステーブルを追加した。このテーブルは、日ごとの集計データを保存するために設計されており、id（ユニークな識別子）、kyteId（リンクの識別子）、date（日付）、hitCount（その日の総ヒット数）、uniqueVisitors（その日のユニーク訪問者数）、countryStats（国ごとの統計）、deviceStats（デバイスごとの統計）、referrerStats（参照元ごとの統計）、createdAt（作成日時）、updatedAt（更新日時）といった項目を持つ。特に、kyteIdとdateの組み合わせでデータが一意になるように設定し、これら二つのカラムにインデックス（データベースの検索を高速化するための仕組み）を設定した。これにより、数百万件にも及ぶ個々のヒットデータが、年間でたった365行程度の管理しやすい日次集計データに変換され、データベースの負担が大幅に軽減される。

日次集計データを作成するために、開発者は夜間に自動で実行されるサーバーレス関数を構築した。この関数は、設定された期間（例えば90日以上前）よりも古いHitPageテーブルの生データを一度に1万件ずつバッチ処理で取得する。取得したデータは、リンクのIDと日付をキーとして、日ごとの総ヒット数とユニーク訪問者数を集計する。ユニーク訪問者の判別には、訪問者のIPアドレスとデバイス情報を組み合わせた識別子を使用し、重複を除外して数える。集計が完了すると、その結果をHitPageDailyテーブルに挿入または更新する。既存の日付のデータがあれば更新し、なければ新規作成するという「upsert」操作を利用する。そして、集計済みの古い生データはHitPageテーブルから削除され、データベースの肥大化を防ぐ。

ユーザーに最新情報と過去の情報をシームレスに提供するため、開発者は「ハイブリッドクエリ」を考案した。これは、二つの異なるクエリを同時に実行し、その結果を統合して表示する方法である。具体的には、直近の期間（例えば過去90日間）については、詳細な生データが保存されているHitPageテーブルから、日付ごとのユニーク訪問者数を集計するクエリを実行する。一方、それ以前の過去の期間については、すでに日次集計されているHitPageDailyテーブルから、日付ごとのユニーク訪問者数を取得するクエリを実行する。これらのクエリは並行して実行され、それぞれの結果を結合することで、ユーザーは最新の詳細な分析データと、長期間にわたるトレンドの両方を、高速に参照できるようになる。

この新しいシステムを導入するにあたり、開発者はまず本番環境とほぼ同じ構成の開発環境を用意し、実際のデータを複製して移行と動作のテストを徹底的に行った。これにより、データが正しく集計され、クエリが期待通りに動作することを確認した。テストで問題がないことを確認した後、トラフィックが少ない時間帯を選んで本番環境へのデプロイ（システム導入）を実行した。万が一問題が発生した場合に備え、すぐに元の状態に戻せる「ロールバック計画」も準備していた。

この取り組みの結果は非常に明確であった。以前は30秒以上かかっていた分析クエリの実行時間が、2秒未満へと劇的に短縮された。データベースのサイズも約60%削減され、運用コストの削減にもつながった。最も重要なことは、ユーザーからの分析機能に関する苦情が一切なくなり、サービスへの信頼が回復したことである。データ損失や分析の精度に関する問題も発生しなかった。

この一連の経験から、開発者はいくつかの重要な教訓を得た。「後で最適化する」という考え方は、最初は効率的に見えるが、実際には後の大きなストレスや顧客喪失といった高額な代償を伴うことを痛感した。また、予算やリソースの制約がある中でも、それは創造性を引き出す原動力となり、大手企業が見過ごすようなシンプルな解決策を見つけることができると学んだ。ソロ開発者であることの利点として、複雑な承認プロセスなしに、実際のデータを使って迅速にテストし、デプロイし、改善を繰り返せる柔軟性があることを認識した。そして何よりも、システムのパフォーマンスは単なる技術的な側面ではなく、ユーザーにとっての重要な機能そのものであり、ユーザーはシステムの裏側にある技術的負債には関心がなく、ただ高速で動作することを求めていると理解した。

最終的に、この解決策はGoogleやFacebookのような巨大企業が採用するような大規模な分析システムではないかもしれないが、月750ドルの収益を持つソロ開発者のサイドプロジェクトには完璧に機能した。システム導入からわずか2ヶ月で、当初の10倍のトラフィックを、最初の稼働時よりも優れたパフォーマンスで処理できるようになっている。この経験は、時には最も優れたアーキテクチャとは、自分で実際に構築し、展開し、そして保守できる、現実的で実践的なものであることを示している。