動機

仕事で PostgreSQL を使う機会あったので、本を読んで基礎をお勉強しました。

クラウド上に Streaming Replication を利用して Active-Standby 構成（非同期）で組みましたが、
思っていたよりも簡単にできてよかった反面、苦労したことも多かったです。

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

読んだ本。

https://amzn.to/3r6WmqV

アーキテクチャ

• プロセス
  • 特徴
    • 必要な処理ごとに「プロセス」として起動する
      • スレッドではない
        • プロセス起動することの無駄についてたくさん議論されてきたが、スレッド対応はしない
  • マスタサーバプロセス
    • 最初に起動する親プロセス。バックエンドプロセスをフォークする。
  • ライタプロセス
    • 共有バッファ内の更新された「一部の」ページを定期的にデータファイルへ書きだす。
    • クライアントからの更新をメモリ上にスタックして処理することで応答性を上げている。
  • WAL ライタプロセス
    • WAL（Write Ahead Logging）をディスクへ書き出す。
    • WAL とは
      • クライアントからのデータ更新クエリを記録したログ
      • クラッシュ時にデータを直前まで復旧させるために使用する
    • Point
      • WAL を利用すると通常のデータ更新と 2 倍の I/O になって遅くなるのでは？
      • WAL があっても遅くならない。WAL はファイルの末尾に追記するだけでシーケンシャルリード／ライトになる。
      • データファイル書き出しはランダムアクセスになるので遅い。
  • チェックポインタプロセス
    • チェックポイントを設定に従って自動実行するプロセス。
    • チェックポイントとは、「すべての」ダーティページをデータファイルに反映し、特殊なチェックポイントレコードがログファイルに書き込まれた状態のこと
    • つまり・・・チェックポイントが動かない限り、メモリ上に更新されたデータがある（ファイルへは未書き込み）
    • チェックポイントでデータを書き込むと同時に、不要となった WAL を削除する。
    • Point
      • チェックポイントは非常に I/O 負荷が高い
      • クラッシュ後のリカバリ処理量／時間に関係する
  • 自動バキュームランチャ／自動バキュームワーカプロセス
    • 自動バキュームを制御／実行するプロセス。
    • ランチャが設定に従ってワーカを起動する。
    • ワーカは統計情報を参照し、大量の更新があるか確認し、対象があればバキュームとアナライズを実行する。
      • バキュームとは、
        • データの更新や削除で発生したデータファイル／インデックスファイルの不要領域を再利用できるようにする処理（PostgreSQL は追記型であり、バキュームするまでは Delete 文でレコード削除しても使用領域は減らない）
      • アナライズとは、
        • 統計情報（データの分布（度数分布）、典型的な値や件数）を収集して pg_statistic システムカタログを更新する。
        • 統計情報は、オプティマイザがクエリ実行するための実行計画を作成する際に、適切な実行計画になるように統計情報を参照する。
  • 統計情報コレクタプロセス
    • 統計情報を一定間隔で収集するプロセス。
  • バックエンドプロセス
    • クライアントからの接続要求を受けて生成されるプロセス。
    • クライアントとの接続を確立し、クエリの実行やデータの送受信を行う。
    • 接続順序
      • クライアント（マスタサーバプロセスのポートへ要求） → マスタサーバプロセス（認証／認可） → マスタサーバプロセスがバックエンドプロセスを作成 → クライアントからバックエンドプロセスへ接続（Unix Domain Socket or TCP/IP）
  • パラレルワーカプロセス
    • パラレルクエリが実行されるたびにバックエンドプロセスから起動されるプロセス。
    • パラレルクエリを実行する。
• メモリ管理
  • 共有メモリ
    • サーバプロセス全体で共有して利用する領域（バックエンドプロセス含む）
    • PostgreSQL 起動時に OS メモリ領域を確保する。
    • 使い方（詳細な領域）
      • 共有バッファ（shared_buffers）
        • テーブルやインデックスのデータキャッシュ
      • WAL バッファ（wal_buffers）
        • ディスクに書き込んでいない WAL のキャッシュ
      • 空き領域マップ（Free Space Map）
        • テーブル上の利用可能な領域を指し示す情報を格納する。
        • バキュームで見つけた空き領域を再利用可能な領域をとして空き領域マップに格納する。
      • 可視性マップ（Visibility Map）
        • テーブルデータが可視かどうかを管理ための領域。
        • バキューム高速化のために、処理対象かどうかを可視性マップで判断する。
        • また、インデックスオンリースキャンでも参照する。
  • プロセスメモリ
    • バックエンドプロセスで利用する領域
    • 利用時に領域を確保する
      • 接続数に依存するので青天井。要注意。
    • 使い方（詳細な領域）
      • 作業メモリ（work_mem）
        • クエリ実行時のソート、ハッシュテーブル操作で利用する。
        • 大量データのソートをしたい場合は、作業メモリをチューニングする。
      • メンテナンス用作業メモリ（maintenance_work_mem）
        • バキューム、インデックス作成、外部キー追加など DB メンテで利用する。
        • 常時発生／確保する領域ではないので、メンテナンスを高速化するために work_mem より大きい値を設定することが望ましい。
      • 一時バッファ（temp_buffers）
        • 一時テーブル（create temp table 〜）を格納する領域。
• ファイル構成
  • PostgreSQL の設定／DB データ／インデックスなど全てのデータはファイルシステム上のファイルとして管理する
    • RAW デバイスは利用できない
    • base ディレクトリ配下にすべての設定、データがあるので、base ディレクトリを他のサーバへコピーするとそれだけで動く（シンプル）
  • PG_VERSION ファイル
    • バージョンが記載されている。
    • 起動時チェック、クラスタ可能かどうかのチェックで利用する
      • PostgreSQL は異なるメジャーバージョン間でのクラスタはできない
    • テーブルファイル／インデックスファイル
      • 8,192 バイトのページという単位で構成されている時つデータを格納しているファイル
  • TOAST ファイル
    • ページをまたがるデータは許容していないため、テーブル内に大容量なレコード（通常は 2kB 超え）を格納する際は、TOAST ファイルへ分割格納する。
    • テーブル領域には TOAST 用の oid を格納して参照できる
  • Free Space Map ファイル
    • 「テーブル／インデックスを示す数字_fsm」
  • Visibility Map ファイル
    • 「テーブルを示す数字_vm」
  • WAL ファイル
    • データの更新操作を記録するファイル。
    • データ永続性の保証／リカバリで利用する。
    • 16 MB 固定のサイズ
    • max_wal_size パラメータの値まで 16 MB の WAL を増やし続ける（デフォルトの 1 GB であれば 64 個の WAL ファイル）。
  • postmaster.pid
    • 二重起動を防ぐためのファイル
• 設定ファイル
  • postgresql.conf
    • PostgreSQL 全体の動作を制御する。
    • 直接編集の他、ALTER SYSTEM 文を利用することでの更新も可能
  • pg_hba.conf
    • クライアントからの接続／認証を制御する。
    • あれ？いつも postgres ユーザだとパスワードが聞かれない・・・ってのは pg_hba.conf でローカル接続で postgres ユーザに対しては未認証でアクセス可能と書いてあるから（デフォルト設定）
    • 接続元 IP、接続先 DB、ユーザ、認証方式（なし、パスワード、LDAP、PAM...etc.）など細かな制御可能。セキュリティの要。
• クエリの流れ
  • SQL 発行
  • パーサ（字句解析／構文解析）
    • 字句解析とは、SQL がどういうトークン（構文の単位）で構成しているかを解析する。OSS の flex を利用している。
    • 構文解析とは、字句解析で分解された字句の並びが PostgreSQL で扱える SQL の記述ルールにあっているか検査する。OSS の bison を利用している。
      • 構文解析の時点でエラーがあると「syntax error」となり、後続処理はしない。
    • 字句解析と構文解析で妥当な SQL だと判断すると、パーサは SQL をツリー構造で表現した「問い合わせツリー」を作成する。
  • リライタ
    • DB にルールが定義されている場合、ルールに基づいてリライタが問い合わせツリーを修正する。
    • 例：
      • view に対する問合せ（select _ from ex_view）の場合、実際にはクエリを書き換え（select _ from tbl_a, tbl_b where tbl_a.id = tbl_b.fid）ている。
  • プランナ／オプティマイザ
    • プランナはリライタで修正された問合せツリーをもとに、最適な実行計画を作成する。
    • 実行計画のつくりかた
      • 1. 個々のテーブルに対するアクセス方法を決める
        • フルスキャン（テーブル全体をスキャン）を候補としてあげる。
        • クエリで検索条件が指定され、かつ、インデックスが使用可能であればインデックススキャンやビットマップスキャンを候補する。
      • 2. テーブルの結合方法を決める
        • PostgreSQL では以下の３つをサポートしている
          • 入れ子ループ結合
          • マージ結合
          • ハッシュ結合
        • 統計情報をもとに上記３つから適切な方法を決める
        • テーブルが 3 つ以上の場合は、結合順序の候補からもっとも効率の良い（コストが小さい）ものを選択する。
  • エグゼキュータ
    • 実行計画に従って対象レコードを抽出する。
    • エグゼキュータは DML のみ処理する。
      • DDL、DCL はスルー
• トランザクション
  • RDBMS の根幹機能で以下の４つを満たすこと
    • 原子性
      • 複数の処理を 1 つにまとめてすべて実行 or すべて未実行を担保する
    • 一貫性
      • トランザクションの開始／終了時点で整合性満たす
    • 独立性
      • 作業中の更新されたデータは確定するまで他のトランザクションから不可視となること
    • 永続性
      • 確定したトランザクションは永続的に保存されること
  • BEGIN（トランザクション開始）、COMMIT（確定）、ROLLBACK（破棄）
  • PostgreSQL で指定できるトランザクションレベル
    • READ COMMITTED（デフォルト）
      • 問合せを実行する直前のコミットデータを参照する
    • REPEATABLE READ
      • トランザクションを開始する前のコミットデータを参照する
    • SERIALIZABLE
      • 最も厳しい分離レベルで、並列実行した複数トランザクションの実行であっても逐次実行として扱う
  • ロック
    • トランザクションの同時実行を確実なものとするために、テーブル／レコードに対して明示的なロックを取得できる。
      • 別トランザクションでロック中のテーブル／レコードに対するアクセスはロックが取得できるまで待機する。
    • 明示的にロック指定しない場合でも、適切なモードでロックを自動的に取得する。
    • 行ロックなどでよくデッドロックが発生する
      • デッドロックは自動解消しないので都度対処が必要となる。
• 同時実行制御（MVCC）
  • PostgreSQL は追記型アーキテクチャを採用することで、MVCC（Multi Version Concurrency Control）を実現している。
  • 追記型アーキテクチャとは、
    • データ更新時に元々あったデータを書き換えるのではなく、更新前データはそのままで更新後データを追記する仕組み。

設計

• テーブル設計
  • 基礎の基礎は置いておくとして個人的に重要だと思うところだけ
  • バイナリデータの扱い
    • 1 GB を超えるデータは bytea 型に格納できないためラージオブジェクトを利用する。
    • ラージオブジェクトの場合、通常のレコード格納ではなくオブジェクトファイルを個別に用意し、そこへのポインタ（oid）をレコードへ格納する。
    • Point
      • bytea 型は 1 GB まで格納できるが内部でのデータコピー量が大きくなり性能が悪い（単純にメモリ 1 GB を使う）ので、100 kB までを bytea 型とし、それより大きいものはラージオブジェクトを利用することがよい。
  • 主キー（一意正制約）／NOT NULL 制約
    • 暗黙的に B-tree インデックスを作る
  • 外部キー
    • 暗黙的なインデックス作成がない
      • フルスキャンが走りがちなので注意
    • テーブル間でキーの型を一致させること
    • とはいえ、あまり外部キー制約を入れているシステムは見たことない・・・
• ビュー設計（クエリ単純化、セキュリティ）
  • ビュー
    • 実態なし
    • 集計などを含まない単純なクエリであれば View に対する更新（insert/update/delete）も可能
  • マテリアライズドビュー
    • 実態あり
    • マテビューに対する更新は不可
    • データは断面で管理され、更新するには REFRESH MATERIALIZED VIEW コマンドを発行する
      • すべてのレコードを更新するため、更新分だけ取り込むといったようなことはできないので注意
      • 更新時も初期生成時と同じコストがかかる
    • View と違って結合処理がなく実データを持つことから早い。
  • インデックス設計
    • データ更新時に都度都度インデックスを更新する
    • 大量データをロードする場合など、インデックスを大量更新する場合は大抵においてインデックス更新に伴う I/O ボトルネックとなる。
    • そのため不要なインデックスは作成しない。
    • また、初期構築時など大量データをロードする場合には、インデックスを削除してロードし、その後にインデックス作成する方が早い。
    • インデックスが利用されているのか実行計画を確認すること
      • オプティマイザがインデックススキャンではなくフルスキャンを選択してしまう場合に性能劣化が発生するが、インデックスキャンを強制したい場合は SQL にヒント句を入れる（PostgreSQL の場合、ヒント句の利用にはプラグイン導入が必要）
    • 関数インデックス（SUM した値など）も利用可能
    • PostgreSQL は複数インデックスロジックをサポート
      • B-tree（デフォルト）、Hash、GiST...etc.
• 物理設計
  • クラウド上で構築する場合は、HW を意識しないので物理設計はそこまで重要ではないが、OS 上でのパーティションの切り方では考慮が必要。
  • データファイル（テーブル、インデックス）
    • 8,192 バイトの固定長ページ単位で最大 1 GB まで拡張して 1 ファイルを構成する。
    • 1 GB を超える場合は、同一ファイル名で異なる拡張子（.1 のような連番）で分割管理する。
      • OS 制約で大きいファイルを管理できない場合でも大きなサイズのテーブル／インデックスを管理できるようにしている。
  • WAL ファイル
    • PostgreSQL に更新要求が発生すると、まず WAL バッファに書き込む
    • トランザクションがコミット or 更新量が多く WAL バッファが溢れる場合に WAL バッファを WAL ファイルへ書き込む
  • アーカイブファイル
    • PITR（Point In Time Recovery）で必要となる過去の更新ログファイル
    • アーカイブモードを有効にすると、WAL ファイル削除時に WAL ファイルをコピーして保管する
    • アーカイブファイルは最新のベースバックアップ取得後のもののみを使用する。
      • ベースバックアップより古いアーカイブファイルは削除していく運用が必要となる。（自動で消えない）
    • アーカイブファイルは容量上限まで増え続け、ディスクフルの状態でも PostgreSQL は異常終了しないため、専用のパーティションを用意することが望ましい（特に WAL 領域とは必ず分ける）
    • WAL が大量に更新される場合 = 大量データの更新 には、大量のアーカイブログ作成が行われる。アーカイブログ作成は遅い（I/O ボトルネック）ので初期構築時などで大量データをロードする場合には一時的にオフにする。
  • HOT（Heap Only Tuple）
    • 更新性能を向上させる仕組み。
    • UPDATE 時のインデックス更新をスキップする
      • インデックスが貼られていないカラム更新時のみ
    • バキュームをまたずに不要領域を再利用可能にする
  • FILEFACTOR
    • ページ内の空き領域をどの程度データ挿入用に利用するのかを定義するパラメータ
• パラメータ設計
  • 文字エンコーディングとロケール
    • 基本的には UTF-8 を利用する（Shift-JIS は RHEL で未サポート）
    • ロケールは単位、記号、日付、通貨に適用する規則。
      • 文字列ソートで利用するので要注意
        • デフォルト（C）だとバイトコード順
        • 日本ロケールにすると日本語順
      • 基本的には利用しないことが推奨
      • ロケール利用時のデメリットが多いので要注意
        • 変換処理が入るのでオーバヘッドが発生する
        • 前方一致検索（LIKE 'hoge%'）でインデックスが利用できなくなる
        • OS ロケール機能に依存するため、環境が異なると動作が異なる可能性がある

バックアップ設計

• 非機能要件に従って必要な方式を選択する
  • オフラインバックアップ
    • 物理バックアップ（コールドバックアップ）
    • バックアップ取得時までリストア可能。
  • オンラインバックアップ
    • 論理バックアップ
      • pg_dump, pg_dumpall で取得する。簡単。基本はテキスト出力。
      • パラレル実行できるため、CPU / Disk I/O に余裕があるケースで効率的。
      • バックアップ取得時までリストア可能。
      • DB データ のバックアップであり、PostgreSQL のバックアップではない。
    • 物理バックアップ
      • ベースバックアップ（pg_basebackup）と WAL ファイルをバックアップ（アーカイブ）する。
      • ベースバックアップ取得後から、WAL を用いて直前までリストア可能。
      • 手順が煩雑。

監視計画

• 稼働統計情報 View
  • pg_stat_databse
    • データベースに対するコミット／ロールバック数、DB キャッシュヒット率、デッドロック発生有無など
  • pg_statio_user_tables, pg_statio_user_indexes
    • テーブルキャッシュヒット率、インデックスキャッシュヒット率
  • pg_stat_activity
    • バックエンドプロセスに対するトランザクション実行時間、実行中のクエリ
• 監査ログ
  • PostgreSQL には監査ログ出力機能がない
    • もちろん監査テーブルもない
  • log_statement = all
    • 実行したクエリを PostgreSQL ログに出力する
      • 既存のサーバログとクエリログが混じって膨大、かつ、読めない
      • ログトラップしようにもパースに時間がかかる
  • ベストプラクティスはないが、pgaudit（サードパーティツール）で別ファイル出力が可能。
• プロセス監視
  • PostgreSQL は処理ごとに OS プロセスが立ち上がり、処理が終わるとプロセスがキルされるため、プロセス監視での正常性確認はなかなか難しい（マスタサーバプロセスぐらいしか監視できない）
  • pg_isready コマンドで死活監視する（PostgreSQL に接続しての死活監視）
    • pgisready -h XXX.XXX.XXX.XXX
    • echo $?
  • SQL 実行可能なのかの死活監視
    • SELECT 1
    • 通常は LB からのヘルスチェックアプリケーションに組み込む

チューニングパラメータ

• OS ディスク設定
  • RAW デバイスを使えないので OS ファイルシステムの制御をうける
  • I/O スケジューラを deadline へ
    • 通常は cfq （I/O 均等処理）が多いが、deadline（I/O 要求の限界に近い要求を優先する）が推奨。

ストリーミングレプリケーション

• PostgreSQL 本体の機能
• WAL をファイル単位でセカンダリへ送るのではなく、変更内容（WAL レコード単位）で送る（ストリーミングする）。
• セカンダリは受け取った WAL レコードを再実行することでセカンダリのデータを更新する
• walsender/walreceiver プロセス
  • プライマリ（walsender）とセカンダリ（walreceiver）やりとりして同期する。
  • walsender 側で archive_mode が有効になっていないといけない
  • walreceiver 側で restore_command （アーカイブファイルを pg_wal へ移すコマンド）が有効になっていないといけない
• 1:N の構成が可能
  • マルチスタンバイ構成（プライマリからすべてのスタンバイへ同期する）
  • カスケード構成（プライマリから一台のスタンバイへ同期、スタンバイから他のスタンバイへ同期する）
• 同期モード／非同期モード
  • 同期モード
    • WAL がスタンバイで WAL 書き込みが完了したことをもってクライアントへコミット通知をする。
  • 非同期モード
    • スタンバイの WAL 書き込みを待たずに処理を終了する。
• ストリーミングレプリケーションの設定は HW 構成やトリガでのスタンバイ昇格方法など設定が複雑
  • 簡易にするために PG-REX がある。
  • が、PG-REX も正直なところ複雑。

VACUUM

• VACUUM は手動ではなく自動実行が推奨（デフォルトも自動実行）
• FULL VACUUM は VACUUM がうまく機能しなかったときに行う
  • long tranxaction 問題
    • VACUUM は最も古い実行中のトランザクション ID より前のものに対して行う
    • 長期間実行中のトランザクションがあるとそのトランザクションより後に行われた変更がいつまでも対象にならない
• FULL VACUUM の違い
  • VACUUM と違い実行中は処理中のテーブルを排他ロックする
  • 処理
    • テーブルの有効なレコードを一行づつ取得して新しいテーブルに格納する
    • 新しいテーブルのインデックスを作る
    • テーブルを新しいテーブルと置き換える
  • 一時的にテーブルが倍になるので容量が足りなくなることがあるので要注意
    • VACUUM FULL ができなくなった場合は dump ロードや copy コマンドで再格納して作り直す
  • VACUUM は不要行を再利用できるようにするだけで実際に容量を解放しない

インデックスメンテナンス

• インデックスファイルの肥大化
  • レコード削除などによりページ内の有効なインデックスが少なく、インデックス読み込みに大量のページ読み込みが発生する
  • バキュームしましょう
• インデックスファイルの断片化
  • B-Tree インデックス特有で、レコード挿入時にキー情報をリーフページに格納していくがその際にいっぱいになるとページを分割する。分割されるとページ内のデータ量が半分になるため断片化する
  • 増えてしまった場合は REINDEX しましょう

Analyze

• PostgreSQL はコストベースオプティマイザ
  • もっともコスト大きいと判断するのは、HDD へのランダムアクセス(random_page_cost : 4)
  • シーケンシャルアクセスはコスト 1
• 統計情報取得のサンプリング行数はデフォルトだと 3 万件
  • 当然少ないのでチューニング必須
  • ただ、行数を増やせば遅くなるので注意
• 基本的には自動バキュームと合わせて自動取得がよい