概要
DBの read のパフォーマンスを改善したいとき、候補となる改善策をいくつか挙げたいと思います。
他の DB タイプへの移行(Amazon Redshift や Elasticsearch を使うとか)やテーブルスキーマの見直しなどは考えません。
候補
index
真っ先に思い浮かぶ選択肢で、最もシンプルで効果を発揮してくれるものです。
index が貼ってあるカラムを使った検索において、通常の探索よりも高速になります。index によって並び替えられた B-Tree を用意しておきます。検索時にはまず、B-Tree を探索して、該当するリーフノードを特定します。B-tree のリーフノードは、実データを持たず、実データへのポインタを持っているので、リーフノードを特定するだけでは、データの取得はできません。リーフノードが持つ実データへのポインタを使うことで、実データを取得します。この実データを取得する作業はディスクI/O です。
clustered index
物理的なデータ順序を利用している点で、通常の index と異なります。
リーフノードの特定までは同じです。そしで、リーフノードが持つ実データへのポインタを使って実データを取得するのも一緒なのですが、効率が異なります。clustered index において、実データは物理的に index の順番で並んでいます。なので、リーフノードが特定されたら、一回のディスクI/O でまとめてごっそり実データを取得可能です。一方、通常の index は、リーフノードが特定されたあとに、「リーフノード→実データ」のディスクI/O がデータの数だけ発生してしまうので、効率が悪いです。ディスクI/Oはパフォーマンス悪化の原因になりやすく、clustered index で大幅にパフォーマンスが大幅に改善することがあります。
物理的な順番なので、テーブル一つにつき一つの clustered index しか持てません。デフォルトでは PK が clustered index となっています。clustered index を PK 以外に貼ると、当然ながら PK の clustered index の恩恵が受けられなくなります。PK は頻繁に使われるカラムであり、clustered index が貼ってある効果は大きいと思うので、PK から clustered index をはがすときはしっかり検討する必要があります。集計のテーブルとかは、PK に貼ってあってもあまり意味がなかったりするので、別のカラムに貼ってもいいかもしれません。
index only scan (conveing index)
ディスクI/Oがパフォーマンスのボトルネックになり得る話をしましたが、index only scan はディスクI/O をゼロにする戦略です。取得したいカラムを全て index に含めます。こうすることで、リーフノードに到達した時点で、必要なデータがすべてある状態になります。なので、「リーフノード→実データ」のディスクI/O がなくなります。
この話を聞くと、conveing index にするために、index に含めるカラムを増やしたくなると思いますが、注意が必要です。そもそも、index は書き込みのパフォーマンスが落ちます。これは、convering index に限った話ではないですが、書き込みの負荷が高いのに、index を増やしすぎるのは注意しましょう。また、index が含むことのできるカラム数には上限があるので、それも気を付けましょう。
materialized view
集計テーブルを事前に作っておくイメージです。A テーブルと B テーブルと C テーブルを JOIN して...みたいな操作を毎回やってたら時間がかかるので、事前に集計したテーブルを作っておきます。materialized view は作成された時点でのデータを保持するので、リアルタイムで更新されていく実データとはずれてしまいます。なので、時々、refresh 操作が必要です。
materialized view ではなくて、普通に新しい集計テーブルを作ってしまうのも一つの方法です。集計には時間がかかるので、メインのテーブルの更新と同時にジョブを積んでおいて、非同期で集計テーブルを更新します。
partitioning
垂直パーティショニングと水平パーティショニングがあります。ほとんどの場合、水平パーティショニングです。水平パーティショニングは、ある基準に基づいて、テーブルを水平に分割します。例えば、一年ごとにテーブルを分割します。単純にレコード数を減らすことで、パフォーマンスを改善しようというものです。
(見たことないので、詳しくは知らない。すみません)
参考
追記
上記以外でパフォーマンス改善する方法を、DB 詳しい人にアドバイスもらったので紹介します。
DB
innodb_buffer_pool_size （postgres だと shared_buffers ?）
一般的に、データアクセスの際に都度データベースにアクセスするよりも、キャッシュから読み込む方が早いため、バッファプールを用意することがパフォーマンスの向上に繋がります。 しかし、この値を大きく設定しすぎると、サーバー本体の空きメモリが不足してパフォーマンスに問題が発生する可能性があるため、一概に大きい値に設定すれば良いというわけではありません。
上記で説明したディスク I/O のときに、ここで設定したメモリ量だけキャッシュを使ってくれます。
マシン全体のメモリ量を上げても、この設定を上げないと、実際に使用されるキャッシュは変わりません。
join_buffer_size （postgres だと work_mem ?）
単純インデックススキャン、範囲インデックススキャン、およびインデックスを使用しないため完全テーブルスキャンを実行する結合について、使用されるバッファーの最小サイズ。 通常の場合、高速な結合を得るための最適な方法は、インデックスを追加することです。 インデックスを追加できない場合、より高速な完全結合を得るために、join_buffer_size の値を大きくします。 2 つのテーブル間の完全結合 1 つに対して 1 つの結合バッファーが割り当てられます。 インデックスが使用されない複数テーブル間の複雑な結合については、複数の結合バッファーが必要になることもあります。
work_mem を上げすぎると、最大セッション数 * work_mem が合計のメモリを超えてしまい、OOMが起きる可能性があるので注意してください。
ソート処理とかで、work_mem を溢れて SWAP すると、メモリじゃなくてディスクでソードされるので、めちゃくちゃ遅くなることがあります。
クエリ
断片化の解消
PostgreSQLのインデックスは、データベースのテーブルに対する操作（INSERT、UPDATE、DELETEなど）によって徐々に大きくなることがあります。これをインデックスの肥大化と言います。肥大化したインデックスはディスクスペースを無駄に消費し、インデックスの検索性能を低下させる可能性があります。
統計情報収集
??
ヒント句
??
join や subquery を別のクエリに分けて実行することで、DBのCPUの占有時間やメモリ使用量を削減できるかも
subquery はオプティマイザが良くないと N 回実行するので、CTE(WITH) に切り出してみるといいかもしれません。また、このとき、CTE(WITH) は work_mem を消費するので、work_mem を溢れることが心配なら、完全に別クエリに切り出すことも考えるのがよいです。