インデックスを張るだけでは足りない。数億件の名刺データを扱うSansanのSQLパフォーマンス改善

ソフトウェアエンジニアにとって、SQLは身近な存在です。しかし、同時に「真に使いこなす」ことが難しい技術の一つでもあります。RDBMSの内部構造や実行計画を深く理解しなければ、大規模システムで発生する深刻なパフォーマンス課題は解決できません。

ビジネスデータベース「Sansan」の開発・運用では、まさにそうしたSQLの高度な知見が試されるといいます。今回は、「Sansan」の開発に10年以上携わり、数々の技術的難局を乗り越えてきた清水裕紀さんに、現場で培われたSQLパフォーマンス改善の実践知についてお話を伺いました。

• keyboard_arrow_down PostgreSQL行数推定との戦いが、エンジニアを強くする
• keyboard_arrow_down 不確実性を「計測」で潰す。バージョンアップを支えた緻密な検証
• keyboard_arrow_down スペックを上げたのになぜ遅い？ 「NUMA」という意外な壁
• keyboard_arrow_down 「その機能は本当に必要？」ユーザビリティ向上のため“引き算”に取り組む
• keyboard_arrow_down 内部構造を読み解き「なぜこの挙動になるのか」を理解すべし

――「Sansan」の開発・運用に携わるエンジニアは、SQLの深い知見を持っている方が多いと伺っています。この要因は何にあるのでしょうか？

清水：プロダクトが持ついくつかの特徴が、エンジニアのスキルの向上につながっています。まずは、扱うデータ量が非常に多いことです。現在、契約企業数は約1万社、ユーザー数は約200万人にのぼります。そして、名刺データは数億件規模に上ります。

加えて、「Sansan」のデータベース設計も大きく影響しています。 私たちのサービスはいわゆるマルチテナント形式なのですが、 その中でも「リソース共有型」 というモデルを採用しています。多数のテナントデータを共通のデータ基盤上で扱うため、テナントごとのデータ特性の違いがクエリ性能に影響しやすくなります。

この構造が、 PostgreSQLとの相性において技術的課題を生んでいます。 具体的には、行数推定が大幅にズレてしまうことです(※)。 PostgreSQLは統計情報をもとに「このクエリを投げれば、これくらいの行数が返ってくるだろう」と推定し、最適な実行計画を立てます。
※参考資料：「PostgreSQL：行数推定を読み解く/row-estimation」（Sansan株式会社のテクニカルリード加畑博也氏の登壇資料）

しかし、テナントごとにデータ量や分布の偏りが大きく、条件列どうしの相関も強くなりがちなため、統計情報にもとづく行数推定が実態と大きく乖離してしまうことがあります。私たちは、この行数推定のズレと日々格闘しています。 一般的なパフォーマンスチューニングであればインデックスの追加で済むことも多いですが、私たちの環境ではそれでは不十分です。

さらに深刻なのが、テーブル結合の場面です。結合先のデータ量を読み誤ると、PostgreSQLが不適切な結合（ジョイン）アルゴリズムを選択してしまいます。これに対処するために、私たちは実行計画を詳細に読み解き、クエリの書き方を工夫することで実行順序を制御しています。

「Sansan」に携わるエンジニアはこうした技術課題と日々向き合っているため、自然と知識量やスキルが向上していきます。

――具体的には、どのような手法で実行順序をコントロールしているのでしょうか？

清水：よく用いる手法の一つが、CTE（共通テーブル式）でMATERIALIZEDを指定することです。これにより、中間結果を内部的に一時保持させ、後段の結合や推定がぶれにくい形に整えるようにしています。

また、PostgreSQLの拡張モジュールであるヒント句（pg_hint_plan）も、影響範囲を見極めたうえで最後の手段として活用しています。ヒント句を使い、エンジニアが自ら実行計画を誘導する。言わば、エンジニア自身が「人間オプティマイザ」になっているわけです。

――これまでデータベースに関連する複数のプロジェクトを経験されたと思いますが、特に印象に残っているものはありますか？

清水：直近で大きな挑戦だったのは、PostgreSQLのメジャーバージョンアップに伴うデータベース構造の見直しです。バージョン13から17へ移行しました。

また、単なるバージョンアップにとどまらず、過去に施した自社独自のカスタマイズを解消するという施策も行いました。PostgreSQLが内部的にデータを管理するページのブロックサイズを、従来の32KBからデフォルトの8KBに戻したんです。

――以前は、なぜ32KBという大きなサイズに設定されていたのでしょうか？

清水：当時は、一度に読み取れるデータ量を増やすことで、検索効率が向上するという仮説がありました。ページ内にレコードがまとまっていれば、インデックススキャンなどの際に複数のページをまたぐコストが抑えられ、パフォーマンスに有利に働くだろうと考えられていたんですね。

しかし、「Sansan」のインフラ環境には大きな特徴があります。それは、潤沢なリソースを投じて、ほぼ全てのデータをOSのファイルキャッシュ（メモリ）上に載せて運用している点です。

全てのデータがメモリ上にある状況では、ディスクI/Oが主要なボトルネックになりにくいため、ページサイズ拡大による恩恵は相対的に小さくなります。つまり、実際には32KBに設定する利点は乏しかったわけです。

一方で、32KBという設定を維持するにはPostgreSQLをソースコードからビルドし直す必要があり、インフラのメンテナンス工数が膨れ上がるという課題が顕在化していました。そこで、今回のバージョンアップに合わせてデフォルトの8KBへ戻すことを決めました。

――ブロックサイズを4分の1に変更し、かつバージョンも上げるとなると、実行計画への影響が大きいように思います。どのようなアプローチで安全性を担保したのでしょうか？

清水：おっしゃるとおり、ブロックサイズはPostgreSQLのコストモデル計算に直結するため、実行計画が崩れるリスクが非常に高いです。そこで、徹底した「クエリのサンプリング解析」と「パフォーマンステスト」を行いました。

まず、普段から監視ツールでモニタリングしているスロークエリに加え、今回は全クエリの約0.5%をランダムにサンプリングしてログを収集しました。これにより、本番環境でどのようなクエリが実行されているかを可視化したんです。

その上で、本番相当のデータを投入した「バージョン17＋8KB」の検証インスタンスと、既存設定の「バージョン13＋32KB」のインスタンスを並列で用意し、全く同じクエリを投げて差分を徹底的に比較しました。

――テストをしてみて、想定外の挙動は見られましたか？

清水：はい、複数ありました。PostgreSQLはバージョンが上がるとオプティマイザのアルゴリズムが改善されますが、それが必ずしも私たちのクエリにとってプラスに働くとは限りません。挙動が変わることで、特定のクエリが遅くなる事象がいくつも確認されました。

内部実装の細かな変更点は、リリースノートですべて公開されているわけではありません。最終的には、実際の挙動からボトルネックを一つひとつ特定し、CTEによる処理順序の制御や、ヒント句を用いた介入によって改善を進めていきました。

――緻密な工程ですね。この事例を踏まえて、データベースに関わるエンジニアが学ぶべき汎用的な知見はあるでしょうか？

清水：データベースを扱う上で重要なのは「計測する」という点に尽きると思います。本番環境で具体的にどんなクエリが流れているのか、構成変更に伴ってそれらのパフォーマンスがどう変わるのか。それらを地道に計測したからこそ、致命的なパフォーマンス劣化を未然に防ぐことができました。

――続いて、2つ目の事例についてもお聞かせください。

清水：次はデータベースのスケールアップにまつわる、少し特殊なパフォーマンス課題の話をします。先ほどお話ししたとおり、「Sansan」では「全てのデータをOSのファイルキャッシュに載せる」という、いわば“札束の弾丸”で解決するような贅沢なメモリ運用をしています。

この構成において、メモリ容量はデータベースの寿命そのものです。顧客数やデータ量が増えれば、よりメモリの大きいインスタンスへスケールアップし続けなければなりません。幸い、AWSが次々とハイメモリなインスタンスを提供してくれるおかげで、これまでは寿命を延ばし続けることができていました。

ところが、特定のインスタンスタイプへ移行しようとした際、これまでにない壁にぶつかったんです。先ほどの事例と同様に「同じクエリを新旧両方のインスタンスで動かす」という検証を行いました。スペックアップなのだから当然速くなるだろうと予想していたのですが、実際にはいくつかのクエリでパフォーマンスの劣化が見られました。

――スペックの高いインスタンスに上げたにもかかわらず、問題が起きた。不思議な事象ですね。

清水：不可解だったのは、新旧のインスタンスで実行計画が全く同じだったことです。インデックスも適切に使われている。それなのに、データの参照そのものが遅くなっている。一体何が起きているのか、最初は全くわかりませんでした。

――いったい、原因は何だったのでしょうか？

清水：インフラチームと密に連携して仮説を立てていった結果、たどり着いた原因がNUMA（Non-Uniform Memory Access）というアーキテクチャの特性でした。

論理的なメモリ容量が増えると、CPUとメモリを物理的に接続するノードの数が増えるケースがあります。私たちが採用しようとしたインスタンスは、これまでの2ノード構成から4ノード構成に増えていたんです。

すると、あるCPUがデータを参照しようとした際、そのデータが別のノード（物理的に遠い場所）にあるメモリに載っている確率が上がります。このノードをまたぐアクセスが物理的なレイテンシとなり、クエリの速度を足止めしていました。これが、スペックを上げたはずなのにパフォーマンスが落ちた正体でした。

――そこまで低レイヤーな原因を特定するのは至難の業だと思います。どのようにして、NUMAの可能性にたどり着いたのでしょうか？

清水：実はこの事例では、AIにヒントをもらった部分が大きかったんです。新旧のインスタンスでどのようなスペックの差分があるか、パフォーマンスのボトルネックとして考えられる候補は何かを挙げてもらったところ、AIが出した仮説の一つにNUMAがありました。

それを基にノード数を確認したところ、まさにそのとおりでした。最終的には、メモリ容量を確保しつつもノード数が2に抑えられている別のインスタンスタイプを選定し直すことで解決しました。

――パフォーマンスのボトルネック調査にAIを活用する。現代的なアプローチですね。

清水：ただ、AIを使う上で重要なのは「質問の仕方」と「情報の取捨選択」です。こちらが具体的な状況や仮説を持って問いかけなければ、AIは的外れな回答を返してきます。また、回答もすべてが正しいわけではないので、利用者が情報を取捨選択する必要があります。

これは弊社のCTOである笹川裕人がよく言っていることなのですが、「AIは使う人の実力以上には賢くならない」んです。出てきた仮説が妥当かどうか、あるいは全くの的外れかどうかを判断できるのは、エンジニア自身の基礎知識があってこそです。

――読者も、同様の「原因不明のパフォーマンス低下」に直面するケースがあるはずです。そんなとき、汎用的に使えるアドバイスはありますか？

清水：基本的には「差分」に当たることです。何を変えて、何が変わっていないのか。今回でいえば「実行計画は一緒だけれど、ハードウェアの構造が変わった」という差分を切り分けたことが突破口になりました。

――それでは、3つ目の事例について伺えますか？

清水：これは現在進行形で取り組んでいる課題なのですが、最近「Sansan」において、特定のタイミングでパフォーマンスが著しく劣化するという事象が発生しました。不可解だったのは、ステージング環境ではまったく同じクエリが高速に返ってくるにもかかわらず、本番環境では時間がかかってしまう点です。この原因を探る必要がありました。

何が起きているのかを可視化するために、PostgreSQLの拡張モジュールである「pg_wait_sampling」を導入しました。これにより、クエリが実行中に「何に待たされているのか（Wait Event）」を詳細に追えるようになります。

解析を進めた結果、待ちの中心にあったのは LWLock:BufferMapping でした。つまり、Shared Bufferそのものというより、Shared Buffer上に載っているページの所在確認や割り当てといった“管理処理”でロック待ちが発生していたんです。

――どのような挙動をしていたのでしょうか？

清水：PostgreSQLは、ディスクから読み取ったデータを一度Shared Bufferという共有メモリ領域にキャッシュし、そこから読み出して処理を行います。しかし、テーブルを広範囲にスキャンするクエリが実行されると、参照するページが次々とShared Bufferに載り、バッファの管理処理が集中的に発生します。

このとき、複数のクエリが同時に「ページがShared Buffer上のどこにあるか」を確認したり、載っていないページを割り当てたりすることで、バッファ管理まわりのロック待ちが増えてしまいます。「Sansan」の機能が増えるにつれて、こうした広範囲なスキャンを行うクエリが本番環境で頻発し、待ち時間が膨らむようになっていたんです。

――原因が「仕様上必要なスキャン」にある場合、クエリの書き換えだけでは限界があるように思えます。どのような解決策をとられたのですか？

清水：まさにそこが重要で、今回は技術的なチューニングだけでなく「仕様そのものを見直す」というアプローチに踏み込んでいます。

調査を進めると、パフォーマンスを圧迫している原因の一部に、実はそれほど重要ではない「件数カウント」などの処理が含まれていることがわかりました。そこでプロダクトマネージャーを交えて、「この数字は本当にお客様の役に立っているか」「この機能を削除、あるいは仕様変更することは可能か」について議論を始めたんです。

プロダクトは運用年数が長くなるほど、利用頻度が低い機能や、他の機能で代替可能な機能が蓄積されていきます。これらをどうやって「捨てていくか」は、持続可能なシステムを維持する上で避けて通れない課題です。代替手段を丁寧に提供しながら「引き算」をすることで、サービス全体のパフォーマンスと品質を守ることが重要だと思っています。

――読者の中にも「SQLに強いエンジニアになりたい」と志している方は多いと思います。スキルの向上に向けて、どのような勉強や実践をおすすめしますか？

清水：第一に、データベースが「なぜこのように動くのか」という内部構造を知ることから始めるのがよいと思います。RDBMSやPostgreSQLなどの技術解説本、公式ドキュメントを読み込み、クエリが実行される仕組みを理解することです。構造を把握していなければ、パフォーマンス改善のために的確なアプローチをすることは困難です。

そして、もう一つ重要なのが「実際に計測すること」です。エンジニアの世界には「推測するな、計測せよ」という有名な格言がありますが、データベースにおいてもこれは真実です。

「Sansan」の開発に携わるエンジニアがオプティマイザの挙動を読み解くために、最も頼りにしている情報源はEXPLAIN（実行計画）です。その際、必ずANALYZEオプションをセットで実行します。単なるEXPLAINはオプティマイザが立てた予測を出すだけですが、ANALYZEを付けることで実際にクエリを実行し、予測値と実測値の差分を表示してくれます。「なぜ差分が生じたのか」を深掘りしていくことが、効果的な学習につながるはずです。

――最後に、「Sansan」でこれほどまでに膨大かつ複雑なデータ、そしてSQLに向き合うことのやりがいについて教えてください。

清水：数億件規模の名刺データやテナントごとの激しいデータの偏りがあるため、「Sansan」の開発は難易度が非常に高いです。しかし、難易度が高いからこそ、エンジニアの力量がダイレクトに試されます。サービスの安定稼働に責任を持ち、技術で課題を解決することに喜びを感じられる人にとって、「Sansan」は本当に面白い環境だと思います。

取材：中薗昴、池田恵実
執筆：中薗昴
編集：池田恵実
撮影：山辺恵美子