PostgreSQL基礎まとめ

PostgreSQL全機能バイブル

PostgreSQL全機能バイブル

  • データベースは一つのディレクトリ、テーブルは一つのファイルとして保存されている
  • テーブルは8KBのブロック単位で構成されている
  • ブロックは、ページヘッダデータ、ラインポインタ、フリースペース、ヒープタプル、から構成される
  • 共有メモリ上にはシステムカタログと呼ばれるメタ情報（pg_classやpg_indexなど）が管理されており、各postgreプロセスはシステムカタログのコピーを持つ
  • MySQLがマルチスレッドモデルであるのに対して、PostgreSQLはマルチプロセスモデルである

ページヘッダデータ

  • XLogRecPtr、TimeLineID、pd_lower、pd_upper、などから構成される
  • XLogRecPtrはWALログの位置（LSN）を格納する
  • TimeLineIDはリカバリを行った後の世代を記録する
  • pd_lowerは末尾のラインポインタを指す
  • pd_upperはヒープタプルの先頭を指す（つまり、pd_lowerの指すラインポインタの指す領域とpd_upperの指す領域は同じ）

ラインポインタ

  • 各ラインポインタは、それぞれヒープタプルのどの位置にあるかを指す

フリースペース

  • 空き領域

ヒープタプル

  • ヒープタプルヘッダとタプル（つまり行データ）から構成される
  • タプルはブロックの後ろから前に向かって書き込まれていく

ヒープタプルヘッダ

  • t_xmin、t_xmax、t_cid、t_ctid、t_infomask2、などからなる

t_xmin

このタプルを追加したトランザクションIDを格納する

t_xmax

このタプルを更新・削除したトランザクションIDを格納する

t_cid

このタプルを追加したトランザクションにおいて、何番目に追加したものかを記録する

t_ctid

このタプルを更新したタプルの領域を指す

t_informask2

  • タプル更新時にインデックステーブルを更新しない(HOT=Heap Only Tuple)場合に、古いタプルにはHEAP_HOT_UPDATEDビットが設定される（このタプルを指すインデックスは存在するが、実際はすでに更新済みであり、可視でない状態であることを示す）

インデックステーブル

  • indexはB-Treeからなるインデックステーブルに格納される
  • テーブルと同じようにブロック上で管理される
  • indexはTIDとインデックスキーで表現され、TIDはブロックIDとラインポインタIDで表現される（そのため、テーブルスキャン不要）

Index Only Scan

  • MySQLのCovering Index相当のもの
  • 9.2以降のPostgreSQLで利用できる
  • indexのタプルをインデックスキーして、インデックステーブルに格納することで、ヒープタプルへのアクセスを避ける
  • 問い合わせするカラムが全てindexのタプルに含まれていなければならない
  • ただし、HOTの場合はタプルが更新（削除）されてもインデックステーブルは更新されていないため、実際には、該当インデックスが指すタプルは、あるトランザクションにとってヒープタプル上では可視でないかもしれない
  • 結局はヒープタプルにアクセスする必要があることになってしまう
  • そこで、Visibility Mapを確認し、そのブロックに可視でないタプルが存在しないことが分かった場合には、ヒープタプルへアクセスせず、インデックステーブルにある内容を返すことができる

WAL(Write Ahead Log)

  • WALログというディスク上の領域（pg_xlogディレクトリ配下の16MBのファイル）と、WALバッファというメモリ上の領域を利用する
  • 共有バッファ上で追加・更新したタプルは、WALバッファにも書き出され、トランザクションが閉じたときにWALログに書き出される
  • WALログへの書き込みはシーケンシャルに行われるので、テーブルブロックへの書き込みに比べてより高速である
  • CHECKPOINTとして全てのブロックデータが書き出され、WALログにはCHECKPOINTからの差分データが書き出される
  • CHECKPOINT後に初めてタプルが追加・更新されるブロックは、バックアップブロックとしてブロック全体がWALログに書き出される
  • 書き出したWALログ上の位置はLSNとして、共有バッファ上のブロックのヘッダ（XLogRecPtr）に記録される
  • WALログ上に書き出されたブロックに対するタプルの追加・更新については、タプルだけがWALログに書き出される

MVCC

  • PostgreSQLのMVCCは追記型で、ヒープタプル上に古いタプルと新しいタプルが存在する
  • あるトランザクションが更新したとき、そのトランザクションIDを古いタプルのt_xmaxに書き込み、新しいタプルへのポインタをt_ctidに書き込む
  • また、新しいタプルを追加し、t_xminにはそのトランザクションIDを書き込み、t_cidにはそのトランザクションにおける何番目の追加かを記録（同一トランザクションにおける複数回の更新に対応するため）する
  • そのトランザクションよりも新しいトランザクションに新しいタプルを見せないために、CLOG（コミットログ）とプロセステーブルを活用する
  • CLOGには、実行中や実行完了した全てのトランザクションIDに対して、in_progress、committed、aborted、sub_committedの４つの状態のうち、一つが記録される
  • プロセステーブルには、実行中のトランザクションIDが格納されており、これはスナップショットと呼ばれる
  • スナップショットを確認することで、どのタプルがトランザクションに対して可視か、が分かる
  • また、どの時点のスナップショットを確認するか、で分離レベルが変わってくる（トランザクション開始時点でのスナップショットを要求すればそれはSerializableであり、そのクエリ開始時点でのスナップショットであればそれはREAD_COMMITTEDになる）

レプリケーション

  • Master側のwalsenderプロセスと、Slave側のwalreceiverプロセスが、TCPコネクション上でWALログを転送する
  • 9.0の非同期レプリケーションは、wal_sender_delayで指定した周期でWALログを転送する（walreceiverからACKは送信されない）
  • 9.1の非同期レプリケーションは、コミット時にWALログが転送されるが、walreceiverからのACKは待たずにトランザクションを閉じる
  • 9.1の同期レプリケーションは、コミット時にWALログが転送され、walreceiverからのACKを待って、トランザクションを閉じる
  • walreceiverからのACKは二種類に分かれており、write完了後に返すACKと、flush完了後に返すACKである
  • synchronous_commitの値で同期レベルを指定でき、localの場合はMasterのディスク書き込み完了、remote_writeの場合はSlaveのwrite完了後のACK受信、onの場合はSlaveのflush完了後のACK受信、となる（offの場合は非同期となる）
  • また、同期レベルはトランザクション毎に指定することができる（SET synchronous_commit TO xxx）
  • なお、walreceiverはACKにWALログのLSNを設定して返すことで、walsenderにwrite/flush/リカバリしたLSNを通知している
  • walreceiverはACKを定期的にwalsenderに送ることによりkeep-aliveを実現している

VACUUM

  • 不要なタプルに対するインデックスやタプル自体を削除し、各ブロックのフリースペースの割合（FSM=Free Space Map）を更新する
  • Visibility Mapを参照することにより、可視でないタプルが存在しないブロックは、VACUUM処理をスキップできる

AUTO VACUUM

  • デフォルト60秒でVACUUM処理を行うワーカープロセスを起動する（ワーカープロセス数や、VACUUMを行うかどうかを判断する閾値（テーブル内の更新・削除が行われた回数）、などを指定できる）
  • バッファの検索・更新の状況を監視してコストを算出し、そのコストが閾値を超えた場合は、VACUUM処理を遅延する

FREEZE