再編成

レコードの追加・削除を繰り返していくと、ファイルに断片的で使用できないデータ格納領域を再配置する必要がでることは前述しましたが、PostgresSQLでは「VACUUM FULL」コマンドで実施します。
PostgreSQLでデータベースのメンテナンスする内容について記載します。

メンテナンス

PostgresSQLのメンテナンス方法には次のものがあります。

1. VACUUM
通常のVACUUMでは不要領域の再利用(更新/削除された行の再利用)とトランザクションID周回の回避対策のために実施します。
VACUUMの遅延実行はVACUUMの内部的な処理を実行するごとにコストを加算しますので、一定数を超えた場合に遅延します。
vacuum FULLは更新/削除された行を詰めて検索などの性能低下予防の期待できますが、テーブルロックが発生いたします。
FSMが不足した場合、VACUUM FULL実行して回収されなくなっている不要領域を解消します。
※VACUUM FULLじゃなくても不要領域返還する場合があります。
postgresql.confのmaintenance_work_memを大きくすることで、保守性能向上が見られますが、設定以上のメモリー利用に注意が必要です。
VACUUMの実行権限がない場合はスキップいたします。
トランザクションブ内でVACUUMは実施できません。

VACUUMが必要かどうかを判断するにはpgstattupleのリレーションの物理長、不要なタプルの割合を確認いたします。
indexの場合はselect * from pgstatindex()で確認できます。

# psql -f /usr/pgsql-9.6/share/extension/pgstattuple--1.4.sql postgres
postgres=# CREATE EXTENSION pgstattuple;
postgres=# \x
postgres=# SELECT * FROM pgstattuple('pg_catalog.pg_proc');
-[ RECORD 1 ]------+-------
table_len | 598016
→リレーションのバイト単位の物理長
tuple_count | 2872
→有効なタプル数
tuple_len | 565464
→有効なタプルの物理長（バイト単位）
tuple_percent | 94.56
→有効タプルの割合
dead_tuple_count | 8
→無効なタプル数
dead_tuple_len | 1972
→バイト単位の総不要タプル長
dead_tuple_percent | 0.33
→不要タプルの割合
free_space | 7348
→バイト単位の総空き領域
free_percent | 1.23
→空き領域の割合

vacuumdbコマンド
Usage:
vacuumdb [OPTION]... [DBNAME]

Options:
-a, --all すべてのデータベースにバキュームします。
-d, --dbname=DBNAME バキュームするデータベースを指定します。
-e, --echo サーバに送るコマンドを表示します。
-f, --full バキュームフルを実行します。
-F, --freeze タプルをfreezeさせます。
-j, --jobs=NUM 指定した数を並列で処理します。
-q, --quiet 進行メッセージを表示しません。
-t, --table='TABLE[(COLUMNS)]' 指定したテーブルをバキュームします。
-v, --verbose 詳細なログを表示します。
-V, --version バージョン情報を表示します。
-z, --analyze アナライズも同時に実行します。
-Z, --analyze-only バキュームをしないで、アナライズのみ実施します。
--analyze-in-stages アナライズで使用される統計情報の計算のみを行います。
-?, --help ヘルプを表示します。

Connection options:
-h, --host=HOSTNAME 接続するホスト名を指定します。
-p, --port=PORT 接続するポート番号を指定します。
-U, --username=NAME 接続するユーザ名を指定します。
-w, --no-password 接続時にパスワード無しを指定します。
-W, --password 接続時にパスワードを指定します。
--maintenance-db=DBNAME データベース名を指定します。

2．自動vacuum
8.3移行ではデフォルトで自動vacuumが有効になっています。
track_countsがtrueである必要があります。
有効になっている場合、テーブルへの挿入/更新/削除に応じて起動します。 offに設定していても、以下の閾値を超えると自動でバキュームを実施します。
一時テーブルは、autovacuumの対象外ですので注意が必要です。

autovacuum_vacuum_threshold + autovacuum_vacuum_sacale_factor × pq_class.reltuples

vacuum FULLは実行されないので、運用に応じて定期的にvacuum FULLを行う必要はあります。
設定により、ANALYZEも合わせて自動実行してくれます。
自動VACUUMの実行・遅延値・限界値などはテーブル毎にALTER TABLE文で設定可能です。
自動OFFにしてもトランザクションIDの周回が発生しそうになると強制で実行いたします。
autovacuum workerというプロセスが実行しています。

3. ANALYZE
統計情報の収集を実行します。
autovacuum・create indexなどの一部のDDL実行時に統計情報は更新されます。
プランナーが正しいindexを利用するためにも更新は必要です。
大量のデータの更新などをした場合は手動で実行した方がいいです。
ANALYZEでは、SHARE UPDATE EXCLUSIVEロックが取得されます。
ANALYZEによって、SELECT文やINSERT文などの処理が阻害されることはありません。
default_statistics_targetの値を大きくすると、ANALYZE時間が長くなって品質が向上します。

postgres=# ANALYZE;

4. REINDEX
インデックスの再構築・破損インデックスの修復・インデックス利用時の速度改善などで利用します。
インデックス利用で遅い場合は、postgres.confのパラメータでrandom_page_costの値が小さく設定し・effective_cache_sizeを大きく設定すると改善されるケースもあります。
pg_classのrelpages列、reltuples列でページ数・行数を確認して行数に比べてページ数が想定以上に多くなっている場合に実行する必要がある可能性があります。

reindexdbコマンド
Usage:
reindexdb [OPTION]... [DBNAME]

Options:
-a, --all すべてのデータベースでインデックスを再作成します。
-d, --dbname=DBNAME データベースを指定します。
-e, --echo サーバに送るコマンドを表示します。
-i, --index=INDEX インデックス名をインデックスを再作成します。
-q, --quiet 進行メッセージを表示しません。
-s, --system システムカタログのインデックスを再作成します。
-S, --schema=SCHEMA 指定したスキーマのみインデックスを再作成します。
-t, --table=TABLE 指定したテーブルのみインデックスを再作成します。
-v, --verbose 詳細なログを出力します。
-V, --version バージョンを表示します。
-?, --help ヘルプを表示します。

Connection options:
-h, --host=HOSTNAME 接続するホスト名を指定します。
-p, --port=PORT 接続するポート番号を指定します。
-U, --username=NAME 接続するユーザ名を指定します。
-w, --no-password 接続時にパスワード無しを指定します。
-W, --password 接続時にパスワードを指定します。
--maintenance-db=DBNAME データベース名を指定します。

4. 終わらないトランザクション
長時間コミットもロールバックも行われないトランザクションをロングトランザクションといいます。
ロングトランザクション中は、対象のテーブルがロックされ、VACUUMは正しく動作しません。
トランザクション中のロックですので、メンテナンス系コマンドは実行しても待機中になります。
以下のコマンドで停止させることができます。

postgres=# pg_cancel_backend('999');
postgres=# pg_terminate_backend('999');
※本クエリでキャンセルしてもセッションが切断されるわけではありません。

5. postgresへの接続可否
contribモジュールのpg_isreadyを利用するとPostgreSQLサーバの接続状態を検査できます。
詳細はこちらでご確認ください。

6. postgresqlを起動しないでpostgresql.conの設定変更
ALTER SYSTEMコマンドを使用するとサーバの設定(postgres.confで設定した一部の内容)を変更できます。
コマンド実行後に、postgresql.auto.confファイルに書き出し、postgresサービスの再起動または設定再読み込み時にpostgresql.confに加えて読み込まれます。
ALTER SYSTEMで変更したパラメータをDEFAULTまたは変更を有効にしたくない場合、RESETを使えば、postgresql.auto.confファイルからエントリを削除します。
そのような設定のエントリをすべて削除するにはRESET ALLを使用してください。
ALTER SYSTEMを実行できるのはスーパーユーザーだけです。

wal_levelをhot_standbyに変更する場合、次のコマンドです。

postgres=# ALTER SYSTEM SET wal_level = hot_standby;