※Windows版PostgreSQL9.6を使用している。

• 2017年度WG3活動報告書Windows環境調査編
• 2018年度WG3活動報告書 性能トラブル調査編

代表的なチューニング項目を記す。
pg_confファイルの設定内容を見直す。設定変更後はPostgreSQLを再起動する。
チューニング設定生成ツール「PGTune」を使用すると、postgresql.confの推奨値を出力してくれる。

• まずやっとく PostgreSQLのチューニング - pdf
• WindowsでもPostgreSQLが使いたい - pdf
• PGTune チューニング設定生成ツール
• postgresqlのチューニング

PostgreSQL全体で試用する共有メモリキャッシュとなり、データの蓄積が進んだ際の応答性能に影響します。
サーバの搭載メモリの25％程度が目安となり、増やしすぎても効果はありません。
※Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。Windows版は64MBあれば十分のようなのでデフォルトのまま。
https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html

PostgreSQL 10以降のマニュアル(shared_buffers欄)には、「Windowsシステムでの有効なshared_buffersの範囲は一般的に64MBから512MBです。」の記載が消えている。https://github.com/le0pard/pgtune/discussions/50

既定値：128MB

PostgreSQLの各プロセスが使用するソート用のメモリバッファです。
瞬間最大でメモリを「work_mem」×「max_connection」＋α　を消費するため、ピークを想定した設定を行う。
搭載メモリに合わせる。

既定値：1MB

ディスクソートの発生を避ける

• メモリ上でソートが行えるようwork_memパラメータを調整
• work_memはセッションごとに確保される領域であるため、SETコマンドで処理に応じて調整するのが望ましい

postgres=# SET work_mem TO '256MB';
postgres=# SELECT　・・・・・・・　ORDER BY ・・・・・・・;

同時接続受付数となります。
CGI実行数＞max_connectionsになるとタイムアウトが発生し、応答性が大きく低下します。
参考事例：5,000ユーザー規模のピーク時瞬間最大アクセスが50～75CGI/秒程度

既定値：100

基本、変更は不要となります。

OSがディスクキャッシュにどれくらいメモリを使用できるかを推定するための参考値。
インデックスの格納に使えるメモリ量です。インデックスがメモリ上にのりきれば、ディスクアクセスが減る／効率化されます。
サーバの搭載メモリの25～50％程度が目安となり、実メモリの消費はしません。

既定値：128MB

HDDからランダムにブロックを読み取るコスト
この値を小さくすると、問い合わせオプティマイザが seq scan ではなく積極的に index scan を選択する効果があります。

既定値：4

下記サイトはHDDではなくSSDの場合、random_page_costを1にしてハッシュ結合ではなくネステッドループ結合されるようなり速くなった。
[翻訳] たった一つの設定変更が如何にしてクエリのパフォーマンスを50倍も改善したか

checkpoint_segmentsのデフォルト値は「3」ですので、デフォルトの設定だと48MB（16MB×3）のトランザクションログを書くとチェックポイントが発生することになります。
しかし、この設定のままだとチェックポイントが頻発してしまうことがあります。 checkpoint_segmentsに64～128程度を初期値として設定する。
checkpoint_segmentsのチューニングでPostgresのパフォーマンスが10倍以上に

PostgreSQL 9.5でcheckpoint_segmentsが2つのパラメータ(max_wal_size,min_wal_size)に置き替えられた。
【PostgreSQL 9.5】max_wal_sizeとmin_wal_sizeの概要

チェックポイント発生する間隔を「時間」で指定するものです。
一般的には30min～1hがいいと言われている。

checkpoint_timeoutのデフォルト値は「5分」になっていますが、少し短すぎるので「30分」を初期値として設定する。

• PostgreSQLのチェックポイント処理のチューニング

WALファイル数の上限
5分で400MBとするとcheckpoint_timeoutが30分なら6倍して、400MBx6=2400MB。
これを3倍したら7200MBなので10GBあればいい。

• PostgreSQLのチェックポイント処理のチューニング

バックアップ後のpg_logに下記のエラーが発生していました。

2019-08-06 02:18:35 JST ERROR:  requested WAL segment 000000010000005A000000D1 has already been removed
要求された wal セグメント * はすでに削除されています。

対処として、wal_keep_segmentsをゼロ以外の値に設定する。
これは、ストリームの危険性が追いつかなくなるのを防ぐためです。
ただしpg_basebackupを使用する場合は、 – checkpoint = fastも忘れないでください。
https://codeday.me/jp/qa/20190609/972945.html

#wal_keep_segments = 0		# in logfile segments, 16MB each; 0 disables
↓
wal_keep_segments = 128

backup.bat の見直し
pg_basebackup -U postgres -D "%bkPath%" -F t -x -z
↓
pg_basebackup -U postgres -D "%bkPath%" -F t -x -z --checkpoint=fast

パフォーマンスチューニング9つの技 ～「基盤」について～

1. VACUUMで不要領域を再利用可能な状態にする
2. REINDEXでインデックスの不要領域を削除する
3. ANALYZEで統計情報を最新化する
4. VACUUM FREEZEでトランザクションIDを凍結状態にする

コマンドプロンプトから実行する。initdb.exeはPostgreSQLのBINフォルダにある。

>initdb --locale=C --encoding=UTF-8 -D "D:\Program Files\PostgreSQL\9.6\data" -U postgres -A md

• PostgreSQL インストールのトラブルシューティング
• 4.3 インスタンスの作成 > 4.3.2 initdbコマンドを使用する場合

initdb: ディレクトリ “D:\Program Files\PostgreSQL\9.6\data”の権限を変更できませんでした:Permission denied

Userに変更のチェックを追加 することで回避した。
他にもrunasコマンドを使用する(データベースクラスタの作成過程でpostgresプロセスを起動しますが、このプロセスは管理者権限では実行できないため)

• PostgreSQL 10 インストール時の不具合解消方法
• WindowsXPでinitdb runasコマンドを使用

スーパーユーザー(例 postgres)にてデータベースを削除する。
データベースの削除

drop database test;

• 第 24章バックアップとリストア
• PostgresSQLのbackup, restore方法まとめ
• PostgreSQLのバックアップ手法のまとめ

ActiveImage Protector ではFドライブ丸ごとバックアップされるので、リストアする際にはFドライブを丸ごとコピーすればいい。
PostgreSQL 9.2のバックアップリストア　Windowsのフォルダコピーでバックアップとリストア

バックアップは以下の手順で行います。

1. PostgreSQLサービスの停止
2. バックアップ(Dataフォルダのコピー)
3. PostgreSQLサービスの開始

リストアは以下の手順で行います。

1. PostgreSQLサービスの停止
2. リストア(Dataフォルダの書き戻し)
3. PostgreSQLサービスの開始

PostgreSQLのバックアップはpg_dumpコマンドで取得するが、オプションによって大きく二つに分かれる。

• スクリプト形式(デフォルト)
• アーカイブ形式

スクリプト形式の出力は、リストアに必要な平文のSQL文で取得してpsqlコマンドでリストアする。
スクリプト形式の場合はプレーンテキストなので、リストアの際にエラーが出たら、中を見れるという利点がある。

pg_dumpはローカルだけでなくリモートからも実行できる

$ pg_dump -h hostname -p port database名 backup_file名

バイナリの形で出力される。リストアはpsqlコマンドでなくpg_restoreコマンドで行う。
アーカイブ形式の利点は、 ｢指定したtableのみを選択してリストアできる｣。

また、アーカイブ形式には、custom形式とtar形式の2種類がある。custom形式はデフォで圧縮されるのでファイルサイズが小さい。

バックアップをとる

$ pg_dump -Fc database名 backup_file名 

-Fオプションを付ければ良い。
-cはcustom形式を表す。-tとするとtar形式になる

リストアは次のように行う

$ psql database名 < backup_file名

リストアは次のように行う

$ pg_restore -C -d postgres バックアップファイル名

-C オプションを付けるとリストア前にデータベースを作成し、-d オプションは、その際に接続するデータベース名を指定

PostgreSQL 9.1からの標準コマンド。PostgreSQLを停止しない状態のオンラインバックアップができる。

• PostgreSQLのバックアップ手法のまとめ
• PostgreSQLのバックアップとリストア
• pg_basebackupを試す
• pg_basebackup in PG9.1

pg_basebackupでオンライン・バックアップを行う際の手順

1. pg_start_backup()を実行
2. データベースのファイルをコピー（オフライン・バックアップと同様）
3. pg_stop_backup()を実行

pg_start_backup()関数とpg_stop_backup()関数を実行することで、バックアップが始まった時点と終わった時点のWALファイルがどれなのかがPostgreSQLによって記録されます。この情報はWALファイルと同じ場所に保存されるので、WALアーカイブを行っていれば同じくバックアップすることができます。

上記の3ステップをすべてひっくるめて行ってくれるのが、「pg_basebackup」コマンドとなります。

pg_dump と同じ手軽さで、より処理の軽い物理バックアップを取得できます。自前で用意するには若干面倒な、テーブルスペース (表領域) に対応した物理バックアップとしても嬉しいところです。-P, –progress オプションでバックアップの進捗も確認できる。

コマンドプロンプト

> pg_basebackup -U postgres -D "E:\backup" -F t -x -z --checkpoint=fast
↓ PostgreSQL12では-xは使えない
> pg_basebackup -U postgres -D "E:\backup" -F t -X f -z --checkpoint=fast

-Dオプションでバックアップ保存先を指定する。サイズが大きい場合はtar-gz圧縮する。だいたい5分以内で完了する。

「FATAL: pg_hba.conf にホスト“127.0.0.1”、ユーザ“postgres”, SSL無効用のエントリがありません.」のエラーが出た場合、以下のコメントアウトを外す。

pg_hda.conf

host    replication     postgres        127.0.0.1/32            md5

postgresql.conf の max_wal_sender 設定のデフォルトは 0 です。最低でも 1 以上でないと pg_basebackup コマンドは実行できないので修正します。※PostgreSQL12ではデフォルトは 10 となった。

postgresql.conf

max_wal_sender = 10

pg_rmanという世代管理対応のツールがあるが、Windowsは動作保証されていない。

• pg_rmanによるPostgreSQLの簡単バックアップ＆リカバリ
• pg_rman (PostgreSQL のバックアップ/リストア管理ツール)

WALアーカイブログは、PostgreSQLが出力したトランザクションログ(WAL)ファイルを別の場所にコピーすることです。
WALファイルはデフォルト設定では直近の一定数しか保存されず、古くなったものは削除もしくは再利用されていきます。そのため、リカバリに使えるようにWALファイルを別の場所に保存しておく必要があります。

WALはログ先行書き込みのことで、トランザクションのログを残すための一般的な手法です。
データベースソフトウェアの本質として、ディスクに書き込まれていない状態で障害が発生するとリカバリにWALファイルが必要になる。

• PostgreSQLのWALファイル(トランザクションログ)について
• ログ先行書き込み - wikipedia
• PostgreSQL WALログの仕組みとタイミングを理解したい

postgresql.conf で設定する。

postgresql.conf

wal_level = replica  # リカバリに使える情報を保存するようにminimal以外の値にしておく
archive_mode = on   # PostgreSQLによるWALアーカイブ処理を有効にする
archive_command = 'copy "%p" "D:\\PostgreSQL\\ARCHIVELOG\\%f"'  # WALアーカイブ処理時に使われるコマンドを指定する　%pはwalファイル名、%fはwalアーカイブファイル名が自動で入力される

※PostgreSQL9.6を使用する場合、archiveとhot_standbyが新しい設定値「replica」に統合
※archive_commandのフォルダは先に作成しておく。
※archive_commandで「“%p”」などのようにパスの部分をダブルクォテーションでくくらないと正しく実行されません。

• 富士通の技術者に聞く!PostgreSQL最新技術 WALが損失するという課題の解決
• PostgreSQLのオンラインバックアップとリカバリ
• 溜まったアーカイブログを削除するタイミング

簡易的な確認は、WALのスイッチの実行を行う。
PSQLで下記を実行すると、archive_commandの内容を実行します。

SELECT pg_switch_xlog();
↓ PostgreSQL10以降ではコマンド名変更
SELECT pg_switch_wal();

PostgresのPITR

WALレコードが更新処理によって挿入され、16MBいっぱいになる、もしくはWALのスイッチが実行されると、pg_xlog/archive_statusディレクトリ配下に対象のWALファイルをアーカイブしてもよいことを示す「<WALファイル名>.ready」というファイルが作成されます。

アーカイブプロセスは、次のタイミングでarchive_statusディレクトリをチェックし、「<WALファイル名>.ready」があれば、archive_commandの内容を実行します。

• 60秒間隔(archive_timeoutが指定されている場合)
• PostgreSQLの停止時
  無事にWALファイルを処理できたら、archive_statusディレクトリ「<WALファイル名>.ready」ファイルは「<WALファイル名>.done」 にリネームされます。

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 - 本

archive_timeoutはトランザクションがほとんど発生しない「なぎ」のとき、WALにたまった内容がいつまで経ってもアーカイブされないことを防ぐ。デフォルトでは0（機能無効）

archive_timeout = 60 とした場合、16MB*60回/時*24時間=23040MB/日≒23GB/日 が発生しうる。 archive_timeoutの意味

pg_archivecleanupコマンドで削除する。

pg_basebackup を実行すると、WALアーカイブのフォルダに拡張子backupが作成される。
これにより指定した拡張子backupより前の不要なWALアーカイブが削除される。

pg_archivecleanup "D:\\PostgreSQL\\ARCHIVELOG" "000000020000000600000036.00000028.backup"

溜まったアーカイブログを削除するタイミング

recovery.conf設定ファイルにarchive_cleanup_commandを指定するとpg_archivecleanupコマンドの実行後に不要なWALアーカイブを自動で削除してくれる。
PostgreSQL の archive_cleanup_command はいつ実行されるのか？

recovery.conf

restore_command = 'copy "D:\\PostgreSQL\\ARCHIVELOG\\%f" "%p"'
archive_cleanup_command = 'pg_archivecleanup "D:\\PostgreSQL\\ARCHIVELOG" "%r"'

しかし、recovery.confはリカバリをする時に必要なので不要なWALアーカイブを削除するためだけに使うわけにはいかない。
よって、不要なWALアーカイブの削除は手動で行うこととする。
※postgreSQLサービスを再起動すればリカバリが実行され、recovery.confはrecovery.doneになります。

リストアとリカバリとは区別しないで使うときもあるが、今回は分けました。

• リストアはバックアップデータを、バックアップを取ったときと同じ状態に物理的に復元すること
• リカバリはリストアしたデータに何かの処理をして最新の状態や正常な状態に復旧させること

PITR(Point In Time Recovery)は、WALレコード適用によるリカバリが前提となっている。

• PostgreSQL 9.6.3で時刻指定のPITR(Point In Time Recovery)を実行する
• PostgreSQLのオンラインバックアップとリカバリ

WALレコードの適用までの流れ
リカバリを開始してWALレコードを適用するまでの流れは次のようになっている。

1. pg_controlファイルを読み込む
2. recovery.confを読み込む → PostgreSQL12ではrecovery.conf廃止、postgresql.confに統一
3. backup_labelを読み込む
4. pg_controlファイルを更新し、backup_labelを削除する
5. 必要なWALを繰り返し適用する

※backup_labelファイルから適用を開始すべきWALの位置を取得できなかった場合は、pg_controlファイルの情報を元にリカバリを開始します。

pg_controlファイルは、バイナリファイルなので通常のエディタでは内容を確認できません。そのために pg_controldata コマンドが用意されています。
pg_controldata コマンドで最終チェックポイントのREDO WALファイルが分かる。

pg_controldata -D C:\PostgreSQL\9.6\data

pg_controlバージョン番号:                         960
カタログバージョン番号:                           201608131
データベースシステム識別子:                       6599999130305146812
データベースクラスタの状態:                       運用中
pg_control最終更新:                               2019/07/28 20:58:08
最終チェックポイント位置:                         0/B01B140
前回のチェックポイント位置:                       0/B01B060
最終チェックポイントのREDO位置:                   0/B01B108
最終チェックポイントのREDO WALファイル:    00000004000000000000000B
最終チェックポイントの時系列ID:                   4
最終チェックポイントのPrevTimeLineID:   4
最終チェックポイントのfull_page_writes オン
最終チェックポイントのNextXID:                    0:610
最終チェックポイントのNextOID:                    24655
最終チェックポイントのNextMultiXactId:  1
最終チェックポイントのNextMultiOffset:  0
最終チェックポイントのoldestXID:      536
最終チェックポイントのoldestXIDのDB:  1
最終チェックポイントのoldestActiveXID: 610
最終チェックポイントのoldestMultiXid:   1
最終チェックポイントのoldestMulti'sのDB: 1
最終チェックポイントのoldestCommitTsXid:0
最終チェックポイントのnewestCommitTsXid:0
最終チェックポイント時刻:                         2019/07/28 20:58:08
ログを取らないリレーション向けの偽のLSNカウンタ:   0/1
最小リカバリ終了位置:                             0/0
最小リカバリ終了位置のタイムライン:   0
バックアップ開始位置:                 0/0
バックアップ終了位置:                 0/0
必要なバックアップ最終レコード:        no
wal_level の設定：                    replica
wal_log_hints の設定：                オフ
max_connections の設定：              100
max_worker_processes の設定：         8
max_prepared_xacts の設定：           0
max_locks_per_xact の設定：           64
track_commit_timestamp の設定：       オフ
最大データアラインメント              8
データベースのブロックサイズ:                     8192
ラージリレーションのセグメント当たりのブロック数: 131072
WALブロックのサイズ:                              8192
WALセグメント当たりのバイト数:                  16777216
識別子の最大長:                                   64
インデックス内の最大列数:             32
TOASTチャンクの最大サイズ:                               1996
ラージオブジェクトチャンクのサイズ:         2048
日付/時刻型の格納方式:                            64ビット整数
Float4 引数の渡し方：                 値渡し
Float8  引数の渡し方：                値渡し
データベージチェックサムのバージョン:           0

PostgreSQLは、追記型アーキテクチャ(MVCC:MultiVersion Concurrency Control)を採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。
VACUUMは、不要領域を再利用可能にし、その箇所をFSM(空き領域マップ)に登録するコマンドです。

不要領域を再利用可能な状態にします。

VACUUMは回収したレコードが占めていた領域を再利用可能にするだけであって、肥大化の進行を食い止めることはできますが、肥大化を解消することはできません。

PostgreSQLが管理するトランザクションIDのメンテナンスのために、定期的(デフォルトでは2億トランザクション毎)にデータベース全体のVACUUMが実行されます。

物理的にファイルを圧縮します。

VACUUM FULLは次のような処理を行っています。

1. テーブルのデータを1行ずつ取ってきて、別の新しいテーブルに詰め込む
2. 新しいテーブルのインデックスを作成する
3. テーブルを入れ替える

つまり、有効な行しか取ってこないので不要領域は一切存在しなくなる。VACUUM FULLは一時的に対象テーブルと新しいテーブルが同時に作成されるため容量不足で完遂できないことがあります。

排他ロックが必要なため、VACUUM FULL中はテーブルへアクセス不可となります。
より多くのディスク容量を回収することができますが、実行にとても時間がかかります。より高速な代替手段としては、テーブルの再構成を行うかバックアップ・リストアの実施があげられる。

重要な点は、VACUUM FULLはテーブルを小さくしますが、インデックスは小さくしないことです。
実際にはインデックスのサイズは大きくなる可能性があり、低速化、インデックスの使用時のより多くのディスクI/Oの発生、必要とするメモリ量の増加が起こります。VACUUM FULLの後にREINDEXが必要になるかもしれません。

VACUUM FULLを使うのではなく、pg_repack(Windows版は非対応)を使う。

autovacuumは定期的にテーブル状態を監視し、必要があれば自動でVACUUMやANALYZE を実施する機能です。※VACUUM FULLは行わない。
PostgreSQLのオフィシャル布告としてはAUTO推奨で、定期実行などはあまりすべきではない、という推奨状態になっている。

有効になっているかは、PostgreSQLの設定ファイル postgresql.conf を確認する。

postgresql.conf

autovacuum = on

※PostgreSQLの8.3からデフォルトは autovacuum = on であり、コメントアウトされていても明示的にしていることになる。

AUTOVACUUMが稼動するのは次の条件に当てはまった場合。

• Dead Tupleがデータ領域の一定割合を超えた場合(autovacuum_vacuum_scale_factor)
• Dead Tupleが各テーブルで定められた閾値を超えた場合(autovacuum_vacuum_threshold)

統計情報の再計算
以下の計算式以上のレコードが更新（UPDATE/DELETE）されたテーブルは自動的に実行 autovacuum_vacuum_thresholdがデフォルト 50で、autovacuum_vacuum_scale_factorがデフォルト 0.1となっている。

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * レコード数

AUTOVACUUMデーモン(Windowsサービス)が一定間隔でこれらの状況を監視し、ロック競合が発生しない限り、一定数量のAUTOVACUUM処理が実行される。

パラメータはテーブル単位でも決定可能
大規模テーブルではテーブル単位に autovacuum_vacuum_scale_factor を 0 に
autovacuum_vacuum_threshold を適切な値に変更し、頻繁に VACUUM を実行することを推奨
テーブルごとにAUTO_VACUUMおよびAUTO_ANALYZEをする閾値を設定する

例えば、10000件廃止行数が超えたら、自動的にVACUUMさせたいって場合

ALTER TABLE {table_name} SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE {table_name} SET (autovacuum_vacuum_scale_factor = 0);

巨大なテーブルに対して実行すると、vacuum処理に時間がかかりすぎることによって、表面のアプリケーション側に影響を及ぼすことがあるので、パラメータ変更はせずに非稼働時間に定期的にVACUME ANALIZEを各テーブルに行うようにしたい。

AUTO VACUUMが実行されていない場合、last_autovacuum がnullとなる。

SELECT n_live_tup,n_dead_tup,schemaname, relname,last_vacuum,last_autovacuum 
FROM pg_stat_user_tables WHERE n_dead_tup != 0

PostgreSQLの9.6からVACUUM実行中の進捗状況のレポート「pg_stat_progress_vacuum」ビューに書き込まれる。
PostgreSQL 9.6.5文書 28.4. 進捗状況のレポート

SELECT
 pid
, datname
, relname
, ((heap_blks_scanned / heap_blks_total::NUMERIC(10,2)) * 100)::NUMERIC(10,2) AS percentage
, p.phase
, index_vacuum_count
FROM pg_stat_progress_vacuum AS p, pg_class AS c
WHERE p.relid = c.oid

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public' -- schemaname or relnameで絞りこむと見やすい
ORDER BY relname

デッドタプルが多く、有効なレコードに対するデッドタプルの割合の多いテーブルに VACUUM をするようにしてみてください。

SELECT
    relname, n_live_tup, n_dead_tup, round(n_dead_tup*100/n_live_tup,2) AS ratio
FROM
    pg_stat_user_tables WHERE n_live_tup <> 0

• PostgreSQL の VACUUM をなんとなくでするのはやめよう
• autovacuum関連のパラメータ設定

pg_repackは、排他ロックを瞬間的にだけ掛けて、あとは必要最小限のロックだけかけるため、処理中もテーブルへの参照・更新処理が実施できます。

• pg_repack -- PostgreSQLデータベースのテーブルを最小限のロックで再編成します
• 運用で役立つツールpg_repackのご紹介
• PostgreSQLでWebサービスを運用するためにやっていること
• pg_repack のインストール
• PostgreSQL のテーブルを無停止で最適化する
• PostgreSQLの運用・監視にまつわるエトセトラ - Slideshare

• Inside vacuum - 第一回PostgreSQLプレ勉強会
• VACUUM FULL/ja
• Postgresql サーバを移行したらAUTO VACUUM が実行されず、データベースが肥大化した時の話
• postgresql.confの罠

PostgreSQL安定運用のための障害予防と検知 - pdf

ANYLYZEは、統計情報を行う。単独で動かせるし「VACUUM ANYLYZE」としてVACUUMと組み合わせることもできる。AUTOVACUUM時にもANYLYZEが行われている。
対象とするテーブルの読み取りロックのみ(読み込みはできるが書き込みができない状態)であり、そのテーブルに対する他の操作と並行して実行することができます。

• SQL文の実行計画作成には統計情報を使用
• 統計情報とはテーブルやインデックスのデータ情報
  • レコード数、ブロック数
  • ヒストグラム（データの最頻値、ばらつき）
• 取得契機
  • 自動 VACUUM
  • VACUUM ANALYZE文の実行
  • ANALYZE 文の実行
• 統計情報の確認
  • pg_statistic カタログ
  • pg_stats カタログ（pg_statistic カタログを見やすく整形している）

• サンプリング量はデフォルト 30,000 レコード（ MAX(列の STATISTICS 値)× 300 レコード）
• 列の STATISTICS 値とは？
  • ヒストグラムを収集するバケット数（データの範囲を入れる箱の数）
  • デフォルト値はパラメータ default_statistics_target で決まる（デフォルト 100）
  • 「ALTER TABLE table_name ALTER column_name SET STATISTICS value」文で変更

default_statistics_targetを単純に増やしてしまうとVACUUMの処理時間が長くなる。
よって、性能が低下しているSQLで参照しているテーブル、カラムを個別に拡張することが効果的。

ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET STATISTICS 200;

上記設定が適用されるのはVACUUM実行時で、速くなるのは統計精度を拡張したカラムを条件としたSELECTのみで統計精度が関係ないカラムのSELECTやINSERTには何の影響もない。

STATISTICS 値の拡大を推奨

デフォルトの統計情報計算式はテーブルのレコード数を 1,000,000 レコードと想定。
10,000,000 レコードのテーブルでは STATISTICS = 114 程度にすると必要なサンプリングが行われる

特徴をまとめると次の通り

• データベース、テーブル、ロール、関数、操作、データ型などに割り振られる
• 0は使わない（0は無効な値の扱い）。符号なし4バイト（最大値：4,294,967,295）の値をとる
• OIDは一周して同じ値をとる場合があるため、一意であると仮定してはならない
• 主キーを持たないテーブル、重複行があるテーブルなどに有効

※テーブル作成時の「WITH OIDS」はPostgreSQL12で廃止

【PostgreSQL】OIDとは？について調べたことのメモ

内部的にはレコードのデータにデータを識別するOID（オブジェクトID）とトランザクションを制御するXID（トランザクションID）をくっ付けて管理しています。
新しいトランザクションが発生すると元のデータはそのままにして、OIDはそのままでXIDを一個増やしたデータを追記しようとします。
第３回：アーキテクチャ比較 ファイル構造の違い - PostgreSQLの特徴　MVCCの仕組み

age が 20 億に近い場合、そのデータベースは、周回問題の危険性を回避するために、再度バキュームされなければなりません。
第 21章定常的なデータベース保守作業

postgresqlはすべてのトランザクションにxidが割り当てられている。xidは2の32乗(42億）。 VACUUMせずにほっておくとxidが0に戻った時点で行が見えなくなる。（行のxidが大きいので） VACUUMをかける時期はage関数で把握。

SELECT datname, age(datfrozenxid) FROM pg_database

ageが15億になる前にVACUUMをかける。かけるとまた10億になる。
VACUUMとage関数

PostgreSQL のインデックスサイズは一度大きくなると、その後小さくなるタイミングが限られています。

• DROP INDEX でテーブル自体を削除した場合
• TRUNCATE TABLE でテーブル全体を空にした場合
• REINDEX でインデックスを再構成した場合

インデックスが肥大化した状況では実行計画のコスト計算に影響することがあります。これは適切な実行計画を選択する妨げとなるかもしれません。

PostgreSQL のインデックス肥大化と実行計画のコストへの影響

reindexコマンドを使用して、インデックスの再構築する。

rem PK_REP_RES_QUEをリインデックスする
SET PGPASSWORD=wh_kousei
psql -U wh_kousei -c "reindex INDEX pk_rep_res_que;"

reindexコマンドはテーブルロックがかかってしまうので、運用中のDBに対して使うのは難しい。
ただ、PostgreSQLでは別名で全く同じインデックスの作成を行うことができる。
※PostgreSQL12からは、REINDEX CONCURRENTLY が導入され、REINDEX による重いロックなしでインデックスを再構築できます。

• PostgreSQLでテーブルロックせずにインデックスを再構築する方法
• index 再構築方法
• index 再構築 Primary key
• 定期的なインデックス再作成を自動化
• PostgreSQLのインデックス再編成

PostgreSQL 9.6上で疑似的にロック無しのインデックスの再構築

rem PK_REP_RES_QUEをリインデックスする
SET PGPASSWORD=wh_test
psql -U wh_test -f reindex_rep_res_que.sql

reindex_rep_res_que.sql

CREATE UNIQUE INDEX concurrently pk_rep_res_que_new ON rep_res_que(que_id);
 
ALTER TABLE rep_res_que DROP CONSTRAINT pk_rep_res_que;
ALTER INDEX pk_rep_res_que_new RENAME TO pk_rep_res_que;
ALTER TABLE rep_res_que ADD PRIMARY KEY USING INDEX pk_rep_res_que;

SELECT relname, relfilenode, relpages FROM pg_class;

SELECT procpid,START,now() - START AS lap,current_query 
FROM (SELECT backendid,pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity_start(S.backendid) AS START,pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S WHERE current_query <> '' ORDER BY lap DESC;

SELECT pg_cancel_backend(PID);

vacuumdb db_name -v -z -t 'tbl_name'

SELECT pg_database_size('db_name');

SELECT pgn.nspname, relname, pg_size_pretty(relpages::BIGINT * 8 * 1024) AS SIZE, CASE WHEN relkind = 
't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) WHEN nspname = 
'pg_toast' AND relkind = 'i' THEN (SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname 
FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '')) ELSE (SELECT pgc.relname FROM 
pg_class pgc WHERE pg.reltoastrelid = pgc.oid) END::VARCHAR AS refrelname, CASE WHEN nspname = 
'pg_toast' AND relkind = 'i' THEN (SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid = 
(SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname 
FROM 10), '_index', ''))) END AS relidxrefrelname, relfilenode, relkind, reltuples::BIGINT, relpages 
FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid AND pgn.nspname NOT IN 
('information_schema', 'pg_catalog') ORDER BY relpages DESC

PostgreSQLで各テーブルの総サイズと平均サイズを知る

pg_dump db_name --table=tbl_name > tbl_name.sql

マイナーバージョンアップは、バイナリ変更だけで対応できますが、メジャーバージョンアップでは、データファイルの移行の必要があるので、メジャーアップデート作業はアップデート計画に沿って、慎重に（シュミュレーション・予行演習など）行うことが大切となります。

PostgreSQLは、開発コミュニティによるサポート期間がメジャーバージョンアップから5年間となっている。

• 日本PostgreSQLユーザ会 ニュース(アップデート情報)
• PostgreSQL エンタープライズ・コンソーシアム 技術部会 WG#2

PostgreSQL X.Y.Z

例えば、9.6.3なら、メジャーバージョンが9.6、マイナーバージョンが3となります。
メジャーバージョンアップは１年毎、マイナーバージョンアップは、年に３回～５回程度実施されます。

X, Y, Z は以下の様な定義で管理されています。

※10系から、PostgreSQL X.Zと二つの数字で表記に変更。最初がメジャーバージョン：新機能追加、最後がマイナーバージョン：バグフィックスなど

インストーラの動作は普段と同じ。前のバージョンがあるので、プログラムの上書き確認と、既にあるデータフォルダを使う確認を経て始まります。PostgreSQL本体だけのアップデートなので、データは何もしない。
PostgreSQL 9.4.1 ほかアップデート版をインストール

メジャーアップデートでは、データのバックアップとリストアの作業が必要である。
メジャーアップデート用ツールとして、pg_upgradeがある。

• pg_upgrade を使ったDBクラスタ移行手順 on Windows
• 【PostgreSQL 9.4 → 11】pg_upgradeでデータベースクラスタをバージョンアップ
• PostgreSQL10へのアップグレード前に押さえておきたい3つの注意点
• PostgreSQL9.6からPostgreSQL10にバージョンアップ