自分が知らなかったことや興味があったことだけをメモしているので、全体が知りたい人は WEB+DB PRESS を買って読みましょう

- 作者: 中野暁人,山本浩平,大和田純,曽根壮大,ZOZOTOWNリプレースチーム,権守健嗣,茨木暢仁,松井菜穂子,新多真琴,laiso,豊田啓介,藤原俊一郎,牧大輔,向井咲人,大島一将,上川慶,末永恭正,久保田祐史,星北斗,池田拓司,竹馬光太郎,粕谷大輔,WEB+DB PRESS編集部
- 出版社/メーカー: 技術評論社
- 発売日: 2018/12/22
- メディア: 単行本
- この商品を含むブログを見る
バージョニングについて
9系までは、x.y.z の x.y がメジャーバージョン(9.6.5 ならば、9.6がメジャーバージョン)
- ちなみに9系の最後のメジャーバージョンは9.6
10系からは、x.y の x がメジャーバージョン(10.1 ならば、10がメジャーバージョン)
2017年からは、2Q末にメジャーバージョンが毎年リリースされている
最新のメジャーバージョンはPostgreSQL11で、12が開発中
なお、各メジャーバージョンのサポート期限は4世代(4年)
初期設定いろいろ
ロケールのデフォルトは環境の言語に依存するので、依存しないように C にするのが一般的
文字コードはデフォルトは sql_ascii なので、UTF-8にしておくのが無難
パスワードと、IPアドレスによるアクセス制限をしっかりつけておく
PostgreSQLの内部構造
PostgreSQLはマルチプロセス(MySQLはマルチスレッド)
いろいろなプロセスが、それぞれの役割をこなしている
- マスタプロセス : 最初に起動して、各プロセスを制御する
- バックエンドプロセス : 接続要求毎に生成される
- ライタ : 共有メモリの内容を定期的にストレージに書き込む
- WALライタ : WAL(後述)をストレージに書き込む
- チェックポインタ : ライタに書き込みのタイミングを通知する
など
PostgreSQLのデータ更新方法
基本的にはメモリ内(共有バッファ)上で、データを更新する
そして、ライタプロセスが、 チェックポイント と呼ばれる特定のタイミングで、メモリの内容をストレージに書き込む
- これは、ストレージへの書き込みには時間がかかるので、高速化のため
- なお、ストレージに書き込まれる前のデータを ダーティーページ という
それとは別に、WALライタが、共有バッファの更新毎に、差分をWALファイルに書き込んでいる
サーバがクラッシュした時には、 チェックポイントとメモリの差分はWALファイルに格納されているので、それを使ってリカバリを行う
メモリ
共有メモリ と プロセスメモリ の2種類がある
共有メモリ
PostgreSQL全体で使用するメモリ
- 共有バッファ : テーブルやインデックスのデータをキャッシュする領域。 総メモリの25%程度が適切な値
- 可視性マップ 、 空き領域マップ : VACUUM処理(後述)が使用
- WALバッファ : 上述のWALをキャッシュする領域
プロセスメモリ
バックエンドプロセス毎に持つメモリ
- 作業メモリ : クエリ実行時に使用するメモリ。
- 実行されるクエリのデータサイズが大きければ必要量が増える
- ここが減るとswapが発生するので、 swapを監視すると良い
(OSのメモリ - 共有バッファのサイズ)/max_connection
を超えない範囲でチューニングしていく必要がある- 最初は 32MB 程度にして、様子を見ていくのが良い
- 一時バッファ : 一時テーブル作成時に使用
- メンテナンス用作業メモリ : VACUUM(後述)、インデックス作成、外部キー制約の追加などのメンテナンス用の作業メモリ
- 小さいとメンテナンス作業時にswapが発生して処理が遅くなる
ファイル
- データファイル : テーブルのデータ。8192バイト毎の複数のファイルの格納されている
- インデックスファイル : インデックスのデータ。同じく、8192バイト毎の複数のファイル
- WALファイル : 16MB固定
- TOASTファイル : 8192バイトを超えるデータの格納先、ただし最大1GBまで
- そのため、PostgreSQLのTEXT型が持てる最大サイズも1GBまで
追記型アーキテクチャとVACUUM
PostgreSQLのデータの更新は、参照を置き換えるという形
- 遅いが、トランザクションやロックの制御がシンプルになるというメリットがあるらしい
参照を置き換えた結果、使用されなくなったデータファイルは VACUUM という仕組みを使って再利用される
- 共有メモリの 可視性マップ にデータファイルの参照状態が格納されているので、VACUUMはそれを参照する
なお、PostgreSQLでは データを削除してもデータファイルの合計サイズは減らない
- VACUUMは、使用されなくなったデータファイルを再利用可能にするだけ
- データファイルの総量を減らしたい場合は
CLUSTER
コマンドが適切らしい - また、
pg_repack
というツールもある
他のVACUUMの機能
- 統計情報の更新
- トランザクションID(XID)の再利用
- PostgreSQLは、テーブルやレコード全てにXIDを割り当てている
- XIDを使い切る前に、未使用のXIDを再利用可能にしている
バックアップ
pg_dump
- スタンダードな論理バックアップ
- カスタムアーカイブ形式がオススメ(テーブルやスキーマ定義だけ分離できる&いざとなったらSQLに戻せる)
pg_basebackup
- DBを停止せずにバックアップを取る方法
- データファイルのコピーをしながら、コピーしている間に発生したWALもアーカイブする(WALアーカイブモードを有効にする必要あり
pg_rman
は、上記のバックアップ&リストアを簡単に行えるツール
レプリケーション
ストリーミングレプリケーションと、ロジカルレプリケーションがある
ストリーミングレプリケーション
- 主流。スタンバイが、プライマリのWALの更新を受け取って自身のDBを更新する
- プライマリとスタンバイのマイナーバージョンが異なっても、レプリケーションができる
ロジカルレプリケーション
- PostgreSQL10から追加
- 自由度が高い
- メジャーバージョンが異なってもレプリケーションができる
- 複数のプライマリを集約したりとかもできる
- その分壊れやすい
- AWS DMSっぽい印象
バージョンアップについて
マイナーバージョンアップ
積極的に対応するべき
PostgreSQLの再起動が必要だが、ストリーミングレプリケーションを使って、スタンバイから更新すれば難しいことはないはず
メジャーバージョンアップ
ストリーミングレプリケーションは使えない
データファイルやWALに互換性がないこともあるため、物理バックアップからの復旧もできない
pg_dump
の結果を新バージョンのDBに反映させるのが一番スタンダード
- デメリット: 停止時間が長い。サーバを新規に用意する必要がある
- メリット: 元のサーバが残っているため復旧が容易
現在のサーバで pg_upgrade
するほうが、停止時間は短い
- PostgreSQL 8.4 移行はデータファイルのフォーマットが変わっていないので、テーブルやインデックスの再構築が行われない
- デメリットは、バージョンダウンはできない
- EC2を使っている場合、スナップショットから復旧すれば良いので、デメリットは無視できそう
参考書籍

- 作者: 高塚遙,桑村潤
- 出版社/メーカー: 技術評論社
- 発売日: 2018/07/26
- メディア: 大型本
- この商品を含むブログを見る
![[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus) [改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)](https://images-fe.ssl-images-amazon.com/images/I/51G-Ui96m4L._SL160_.jpg)
[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)
- 作者: 勝俣智成,佐伯昌樹,原田登志
- 出版社/メーカー: 技術評論社
- 発売日: 2018/09/14
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
参考リンク
記事だけよんでわからなかった部分を調べた時に読んだページ
www.slideshare.net