🐙

新卒3年目DBエンジニアが教えるDB基礎講座（Postgres編第２回：PostgreSQLの内部構造）

2023/05/06に公開

PostgreSQL

アーキテクチャ

入門

tech

今回の内容

今回はPostgreSQLを動かすうえでは欠かせない以下5点を説明します。
1.PostgreSQLで動いているプロセス
2.PostgreSQLを動かしているメモリ
3.PostgreSQLに必要なファイル
4.クライアントとサーバーの通信が成立するまで
5.PostgreSQLでSQLが実行されるまで
6.参考サイト一覧

今回から参考にさせていただいたサイトや分かりやすくまとまっているサイトを下に書くようにします。私の内容で分かりにくいorもう少し知りたい場合は参考サイトをご確認ください。

過去記事

1.PostgreSQLを動かしているプロセス

まず初めにPostgreSQLを構成しているプロセス群を確認していきましょう。
本格的にアプリケーションを接続させていないので出ていないものもありますが、下記の通りにpsコマンドでどんなプロセスがあるか確認します。
下記のようにRDBMSではいろいろなプロセスで成り立っていることがわかりますね。

postgres     736       1  0 11:29 ?        00:00:00 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf
postgres     820     736  0 11:29 ?        00:00:00 postgres: 14/main: checkpointer 
postgres     821     736  0 11:29 ?        00:00:00 postgres: 14/main: background writer 
postgres     822     736  0 11:29 ?        00:00:00 postgres: 14/main: walwriter 
postgres     823     736  0 11:29 ?        00:00:00 postgres: 14/main: autovacuum launcher 
postgres     824     736  0 11:29 ?        00:00:00 postgres: 14/main: stats collector 
postgres     825     736  0 11:29 ?        00:00:00 postgres: 14/main: logical replication launcher

プロセス名	解説
Postmaster	PostgreSQLのメインプロセスで、他のすべてのプロセスの管理と調整、接続の処理、データベース機能の確保を担当
Background writer	共有バッファプールからディスクへのダーティ（変更された）バッファの書き込みを担当するプロセス
Checkpointer	このプロセスは、指定されたチェックポイントでディスクにデータを書き込み、トランザクションログファイル（WALファイル）をリサイクルする役割
WAL writer	データ変更をクラッシュリカバリとレプリケーションの目的でログに記録するために、Write-Ahead Log（WAL）レコードをディスクに書き込む役割
Autovacuum launcher	このプロセスは、autovacuumワーカープロセスを起動する役割を果たし、ストレージを回収し、データベースのパフォーマンスを最適化するためにデッドロウを削除し、統計を更新
Autovacuum worker	これらのプロセスは、テーブルのバキュームおよび解析作業を実際に実行
Stats collector	クエリ実行、テーブルアクセスパターン、インデックス使用など、データベースアクティビティに関する統計を収集し、データベースのパフォーマンスの監視と最適化
Archiver	完了したWALファイルを指定された場所にアーカイブし、古いログファイルがバックアップとリカバリの目的で利用可能に
Log collector	さまざまなPostgreSQLプロセスからのログメッセージを収集し、指定されたログ先に書き込み
Background session	ユーザーセッションに関連するプロセスで、ユーザークエリを実行するクエリ実行者などが含まれる

2.PostgreSQLのメモリ

それぞれ概要を見ていきましょう。

共有メモリは以下の4つです。

メモリ名	解説
shared_buffers	データのキャッシュ
wal_buffers	ディスクに書き込まれていないWAL(トランザクションログ)をキャッシュ
可視性マップ	テーブルのデータが参照できるか否かを管理する情報を扱う領域VACUUMの手助け
空き領域マップ	テーブル上の利用可能な領域を指し示す情報を扱う領域です。VACUUM処理の際にまったく参照されていない行を探し、空き領域として再利用へ

プロセスメモリは以下の3つです。

メモリ名	解説
work_mem	クエリ実行時にソートやハッシュ処理のために使われる領域。複雑なクエリの場合は1クエリを実行するためにソートやハッシュ処理が並行して複数回実行されるため注意が必要
temp_buffers	クエリ実行時に、一時テーブル(CREATE TEMP TABLE文の実行)へアクセスする時のみ使われる領域
maintenance_work_mem	メンテナンスコマンド(VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEYなど)の実行時に使われる領域

3.PostgreSQLに必要なファイル

続いてPostgreSQLがどのようなファイルでできているか見ていきましょう。
これを見るとDBというと難しそうですが、実態はファイルであることがわかると思います。
ツリー図にするとこのようになります。

pgdata/ (データベースクラスタのルートディレクトリ)
├── base/ (データベースの実際のデータが格納されるディレクトリ)
│   ├── 1/
│   ├── 12345/
│   └── ...
├── global/ (グローバルオブジェクトに関するデータが格納されるディレクトリ)
│   ├── pg_control
│   ├── pg_filenode.map
│   ├── pg_internal.init
│   └── ...
├── pg_hba.conf (クライアント認証の設定ファイル)
├── pg_ident.conf (ユーザ名マッピングの設定ファイル)
├── pg_logical/ (論理レプリケーション関連のディレクトリ)
│   ├── mappings/
│   └── snapshots/
├── pg_multixact/ (マルチトランザクションステータス情報のディレクトリ)
│   ├── members/
│   └── offsets/
├── pg_notify/ (LISTEN/NOTIFYメッセージキューのディレクトリ)
├── pg_replslot/ (レプリケーションスロットのディレクトリ)
├── pg_serial/ (シリアライザブル分離レベルでのシリーズ情報のディレクトリ)
├── pg_stat/ (統計情報のディレクトリ)
├── pg_stat_tmp/ (一時的な統計情報のディレクトリ)
├── pg_subtrans/ (サブトランザクション情報のディレクトリ)
├── pg_tblspc/ (テーブルスペースのシンボリックリンクのディレクトリ)
├── pg_twophase/ (2フェーズコミットのディレクトリ)
├── pg_wal/ (WAL（Write-Ahead Log）ファイルのディレクトリ)
├── pg_xact/ (トランザクションステータス情報のディレクトリ)
└── postgresql.conf (PostgreSQLの設定ファイル)

基本的に覚えておくのは下記のファイルだけで問題ありません。
PostgreSQLに主に必要なファイルはこのように分岐されます。

ファイル名	解説
テーブルファイル	データの実態が入っているファイル。8192bytesのページという単位で構成
索引ファイル	索引の入るファイル。 8192bytesのページという単位で構成
TOASTファイル	2KB超えるレコードを格納する際に作成されるファイル
FSMファイル	後述
VMファイル	後述
WALファイル	ディスクやメモリに書き込む前の更新操作を書き込むファイル
postgresql.conf	メインの設定ファイル
pg_hba.conf	接続に関連するファイル

FSMファイルの詳細は概要は下記の通りです
FSMファイル (Free Space Map):
1.空き領域管理: テーブルやインデックスのディスク上の空き領域を追跡し、管理するために使用されます。
2.サフィックス: リレーションファイル名に "_fsm" というサフィックスが付けられます
3.速化: タプルの挿入や更新時に適切な空き領域を素早く見つけるために使用されます。
4.メンテナンス: VACUUMやANALYZEなどのメンテナンス操作によって自動的に更新されます。
「INSERTやUPDATE時に、空き領域マップが参照され、利用可能な領域を見つけて新しいレコードを挿入するファイル」

VMファイルは詳細は概要は下記の通りです
VMファイル (Visibility Map):
1.可視性情報: テーブルの各ページにおいて、全てのタプルが特定のスナップショットに対して可視であるかどうかを追跡するために使用されます。サフィックス: リレーションファイル名に "_vm" というサフィックスが付けられます (例: 12102_vm)。
2.インデックスアクセスの最適化: 可視性情報を使って、インデックスアクセス時のタプルの可視性チェックを最適化します。
3.自動メンテナンス: VACUUMやANALYZEなどのメンテナンス操作によって自動的に更新されます。
**「VACCUMの効率化を目指すために必要なファイル」 **

4.クライアントとサーバーの通信が成立するまで

DBは単体でもちろん稼働してても役に立ちません。アプリケーションからの通信をして真価を発揮します。
登場人物はクライアントとサーバだけですので図はカットします。

1.クライアントからマスタサーバのPort(デフォルトでは5432)に対して接続を要求
2.サーバ側で接続許可しているクライアントからの要求か確認
3.認証が必要なときはサーバから認証要求が飛びクライアントはそれを満たすための認証情報を返す
4.認証する
5.接続要求ごとにfork()してプロセスの生成
*fork・・親プロセスのコピー
6.TCP/IPにより接続を確立

5.PostgreSQLでSQLが実行されるまで

a.クライアントアプリケーションの接続
アプリケーションは通常TCP/IPソケットでPostgreSQLデータベースに接続します。

b.認証
接続が成功すると、PostgreSQLはクライアントを認証

c.クエリ送信
クライアントアプリケーションはSQLクエリをテキスト形式でサーバに送信

d.クエリのパースを解析
構文解析と意味解析を行い、クエリが正しい形式であるかどうかをチェック

e.クエリの最適化
解析されたクエリはクエリプランナーによって最適化され、実行計画が作成され、データへのアクセス方法が決定されます。
ここでIndexScanがいいのかBitmapScanがいいのかなどの話が出てきます。
結合には「入れ子ループ」「マージ結合」「ハッシュ結合」がありますが、クエリによって適切な結合方法が異なります。
簡単にまとめると以下のようになります。
**外のテーブルが小さいとき→入れ子ループ
大きなテーブル同士→マージ結合
比較的小さなテーブルと大きなテーブルを結合→ハッシュ結合(ただしハッシュがメモリーサイズより大きくなってしまう場合は、ファイルアクセスが発生)
**

g.クエリーの実行
最適化された実行プランに基づき、クエリが実行され、データの読み書きが行われ、必要に応じてトランザクション処理またはロック

h.結果返却
実行されたクエリ結果は、行と列を持つテーブルの形でクライアントに返す。

i.接続終了
クエリの実行が完了し、結果が返されると、クライアントアプリケーションはデータベースへの接続を終了。

図解する下のとおりです。

これはPostgreSQLに限った話ではありませんがSQLは下記の順番で実行されます。
個人的にはSQL書く時も下記の順番で書くと思考が整理されるのでこう書くことが多いです。
FROM句
JOIN句（存在する場合）
WHERE句
GROUP BY句
HAVING句
SELECT句
DISTINCT句（存在する場合）
ORDER BY句
LIMIT句（存在する場合）

これでSELECT句のASはORDER BY句では使えるのに、WHERE句では使えない原因であったり、WHERE句でSUM関数(集約関数)は使えない理由がわかると思います。