これは 😺TECHSCORE Advent Calendar 2019😺の14日目の記事です。

今回はPostgreSQLのパフォーマンスに関する小ネタをあつめてみました。
知ってるよ！というものが多いかもしれませんが、どれか一つでも参考になれば幸いですo(ツ)9

データ投入(COPY)を高速化

レプリケーションがオフの場合、トランザクション内でTRUNCATE後にCOPYを実行するとWALが省略されるため、通常に比べかなり高速にデータを投入することが可能です。PostgreSQLの設定は以下の状態である必要があります。

　wal_level -> minimal
　archive_mode -> off
　max_wal_senders -> 0

この状態で、

とすると、高速にCOPYが可能です。COPY以外にも CREATE TABLE AS SELECT にも有効です。

Spring JDBC パラメータ型情報取得をやめる

Spring JDBCを利用している場合、クエリパラメータに型指定なしでnull値を渡すと、データベースから型情報を取得するクエリがnull値の数だけ余分に発行されます。
思いがけないところで何倍にもクエリを発行してしまっていることになるので注意が必要です。
常に正しく型を指定するよう実装するか、この機能をオフにしてしまいましょう。
オフにするにはJava起動オプションに以下を指定します。

-Dspring.jdbc.getParameterType.ignore=true

カーソルを使わない（必要な時だけに限定する）

PostgreSQL9.6以降で搭載されたパラレルクエリは非常に強力ですが、カーソルを使う(JDBCでsetFetchSize()を指定する）とパラレルクエリは機能せず、従来通りシングルプロセスで処理されます。
これがまた意外にはまりどころで、コンソールからpsqlでSQLを実行し、実行計画をみるとパラレルになっているにも関わらず、なぜかアプリケーションを動かしてみると遅い！ということがあります。
setFetchSize()は実行するクエリが取得するデータ量・メモリ搭載量を踏まえたうえで慎重に使いましょう。

effective_io_concurrencyを0にしてみる

以前に記事で紹介したものです。

PostgreSQL 「effective_io_concurrency」の設定について

データベースの全体設定で0にしてしまうのはやりすぎかもしれませんが、特定のトランザクションだけSET句で変更する、特定のテーブルスペースだけに適用するということも可能です。

テーブルを作りなおす

追加・更新・削除操作が発生するテーブルを長く運用すると、運用初期と比べデータ物理位置が大きく変わり、クエリ実行時にストレージアクセスでランダムアクセスが増加し、パフォーマンスが悪化することがあります。
これを改善するにはCLUSTERまたはVACUUM FULLを実行することが有効です。どちらのコマンドも新しいファイルにデータを詰めなおします。
ただし、テーブルサイズによっては長時間テーブルをロックすることになるので注意が必要です。
運用しているシステムで参照しか行わない時間帯を調整可能な場合、CREATE TABLE AS SELECTで別名テーブルにデータを詰めなおし、新・旧テーブルをリネームすることでテーブルを入れ替える手段も有効です。
リネーム操作をワントランザクションにすることでシステム無停止で対応が可能となります。

synchronous_commitをオフに

比較的小さいトランザクションが多く発生するシステムの場合、synchronous_commitをオフにすることでスループットが向上する可能性があります。
synchronous_commitをオフにすると、コミット時にストレージへの書き込み完了を待たずにアプリケーションに応答を返すようになります。
synchronous_commitをオフにすることによるリスクは、PostgreSQLのクラッシュ／immediateモードでのシャットダウンが発生した場合に、直近のコミット分でストレージに書き込みが完了していなかったデータをロストすることです。
次回起動時、ロスト直前のトランザクションまで正常にリカバリされるため、データ破壊のリスクはありません。
似た雰囲気を感じる設定値にfsyncがありますが、こちらはオフにしてしまうとデータ破壊のリスクがあり、PostgreSQLを起動できなくなる可能性があるのでオフにしてはいけません。

shared_buffersが大きい場合はhugepagesを使う

shared_buffersに32 - 64GB以上を割り当てるような場合、hugepagesを設定することでパフォーマンスが向上する可能性があります。
デフォルトでは huge_pages=try で、OSの設定でhugepagesが準備されていなければ利用しません。
hugepagesを利用する前提の場合は huge_pages=on としましょう。

wal領域、一時ファイル領域を高速なストレージにおく

ストレージ装置が高速であればパフォーマンスがいいのは当然ではありますが、データベースすべてを高速なストレージにのせるのは予算的に困難なこともあります。
wal領域と一時ファイル領域だけに絞るとそれほど大きなサイズとはなりません。これらの領域だけ別で高速なストレージを調達するというのも効果的です。
wal領域を高速なストレージにのせることでトランザクションのスループット／レスポンスタイムの改善が見込めます。
一時ファイル領域はwork_memに乗り切らないクエリのパフォーマンス改善を期待できます。

チェックポイント間隔の調整で書き込みＩＯ負荷を分散

チェックポイントは、テーブルの変更内容を定期的にテーブルの実ファイルに書き込む処理です。
ここの設定が適切でなかった場合、定期的に書き込みＩＯが集中することになり、
「あれ？なんかたまにデータベースの応答が悪いなぁ。。」
ということになります。

関連する主な設定値は以下の3つです。

　checkpoint_timeout -> チェックポイント間隔(秒)
　checkpoint_completion_target -> チェックポイント処理完了目標
　max_wal_size -> チェックポイント間での最大WALサイズ

checkpoint_timeoutはチェックポイントの間隔で、デフォルト値の300の場合、5分おきにチェックポイント処理が行われます。
checkpoint_completion_targetは、チェックポイントの間隔に対してどれくらいの速さでチェックポイント処理を行うかを設定します。
デフォルトの設定は0.5で、checkpoint_timeoutが300の場合、150秒を期限として緩やかにチェックポイント処理を行います。
max_wal_sizeは許容する最大WALサイズで、デフォルトでは1GBです。

３つの設定がすべてデフォルトの場合、

「5分ごとにチェックポイント処理を開始し、150秒かけて緩やかに書き込み行う。ただし、1GB以上WALが進行した場合は即座に書き込む。」

ということになります。

多くのシステムでこのデフォルト値は全体的に低すぎる可能性が高いです。
チェックポイントでの書き込みI/Oがより分散するよう、調整してみましょう。
checkpoint_completion_targetは最大で0.9であるべきとされていますので注意してください。
また、全体的に設定値を大きくした場合、未反映のWALが増えることになるため、クラッシュ時のリカバリ処理が長くなることに留意してください。
（リカバリできなくなるわけではありません）