PostgreSQL 纏め - 着眼点の構想

仕事でPostgreSQL 9.6 を使っており、インフラを担当している。
PostgreSQLについて、ポイントを纏めてみた。

■fillfactor（空き領域）
　空き領域のデフォルト値は、以下の通り。
　　　　　　ORACLE　PostgreSQL
　テーブル　　10　　　　 0
　INDEX 　　　10　　　　10
　※PostgreSQLのテーブルが0であることに注意！
　　設定値は以下で確認できる。
　　pg_class.reloptions（デフォルト値の場合はNULL）

　PostgreSQLは追記型アーキテクチャーのため、これを確認するのが重要！

■トランザクション：デフォルトがCOMMIT
　ORACLEはROLLBACK。
　COMMITだと、以下の場合に注意が必要。
　・異常が発生し、再処理が走った場合
　・クラスター環境で、切り替わった場合

■スキーマ：ユーザ作成時、作成されない
　ORACLEでは作成されるが、PostgreSQLでは作成されず、以下の場合に注意。
　・インスタンス管理者でオブジェクトを作成する場合
　　特に関数を実行して、裏でオブジェクトを作成している場合。

■システムカタログ：確認すべきポイント
　・pg_class.reloptions
　　fillfactorが正しく設定されているか
　・pg_stat_user_tablesのn_tup_updとn_tup_hot_upd、
　　更に、autovacuum_count（HOTが効いているか）
　・pg_stat_user_tablesのseq_scan、idx_scan
　・pg_stat_user_indexes.idx_scan
　　Primary Key以外で0のINDEXは、未使用のため削除を検討
　　※INDEXが存在することにより、処理が遅くなっている
　　　PostgreSQLの場合、pg_stat_database.stats_reset以降の累計値が格納されている
　・pg_stat_activity.state
　　activeとidleの数

■オブジェクトのID
　ORACLEはオブジェクトのIDを保持していないが、
　PostgreSQLはオブジェクト毎にIDを保持している。
　そのため、以下の双方実施は不可能になる。
　・統計情報の固定化
　・INDEX再構築を「create index concurrently」で実施

■postgresql.confのポイント
・shared_buffers
　以下で決める。
　・キャッシュ：オブジェクトをどれだけメモリに載せたいか
　・work_mem

　アプリチームに聞くといつも大きめに言ってくるが、
　それ程大きくなくても、大問題にはならない。
　キャッシュヒット率で確認できる。

　postgresql.confの内容は、pg_settingsで設定内容を確認できるが、
　shared_buffersは 8KB単位になっている。（pg_settings.unitが8KB）
　テーブルやINDEXは、8KB単位でキャッシュに載るため、以下の場合に注意。
　・fillfactorで空きを取り過ぎているテーブル
　・断片化が進んでいるINDEX

　PostgreSQL専用のサーバで、全インスタンスのshared_buffersの合計が、
　実メモリの40%までになるようにしている。

・work_mem
　connection毎に必要なため、max_connectionsを大きくすると注意が必要だが、
　activeなconnectionで考慮すべき。
　pg_stat_database.temp_filesとtemp_bytesを確認すれば、
　work_memが足りているか確認できる。

　今担当しているシステムでは、アプリがidleセッションで繋ぎっぱなし
　にしているため注意。
　※繋ぎっぱなしにする必要はない。
　　アプリTは性能のために行っているようだが、他に考慮すべき点が山程あり、
　　そちらを対処すれば、繋ぎっぱなしにする必要はない。

・checkpoint_timeout
　デフォルトは5分で、サイトでよく見かけるが、
　デフォルト値のままだと負荷が高くなるため、30分に変更している。

・checkpoint_completion_target
　デフォルトは0.5だが、負荷が高くなるため、0.9に変更している。
　0.5だと、急いでディスクに書き込もうとする。

・wal_log_hints
　クラスタ環境で、pg_rewind（差分コピー）を使用するためには、
　このパラメータをONに設定する必要がある。
　が、これが曲者で、マニュアルに以下と書かれている。
　-----
　チェックポイント後にはじめてページを変更する際に、
　ディスクページの全内容をWALに書き出します。
　-----
　クラスタ構成の場合、切り替わった直後が問題となる。
　旧Standbyの時、アクセスしていなければ、初回のSELECTは
　「チェックポイント後にはじめてページを変更する」に該当し、
　アクセスした分、ログが出力され、ログ領域がパンクし易い！

■INDEXの状態
INDEXは本の索引のようなもの。
検索時を考慮し、INDEXのキー項目順で並べている。
そのため、INSERTしているだけでも断片化していき、（虫食い状態になる）
定期的にINDEXを再構築しないと、検索が遅くなる。

ORACLEは、以下の場合に再構築した方が良い、という指針を出している。
・HEIGHTが4以上で、DEL_LF_ROW/LF_ROWSが0.2を超える場合

が、PostgreSQLには指針が出ていない。

pgstattupleを使用すると、INDEXの状態を把握できるが、
そこに「leaf_fragmentation（リーフページの断片化）」という項目がある。
が、この値は間違えている。
断片化していないのに断片化していると出たり、
断片化しているのに断片化していないと出る。
※断片化はサイズで判断できる

今のシステムで、「avg_leaf_density（リーフページの平均密度）」
を断片化の判断で利用している。この値は正しい。

以下の条件で再構築すると良い。
・サイズ　：50～60MB超
・平均密度：60～70未満
※平均密度=50 というのは、半分が虫食い状態で、
　サイズが2倍に膨れ上がっていることを指す。

断片化が進むと、範囲検索への影響が大きく出る。

サイズを指定しているのは、PostgreSQLはオブジェクト毎にOS上の
ファイルが生成され、8KB単位だから。
データを1件登録すると、OS上のファイルが8KBになり、
サイズが小さいほど、平均密度が小さくなりやすい。